The Ultimate Excel Vba Arrays

Learn To Manipulate Data Invisibly and At Lightning Speeds with Arrays!

Last updated 2022-01-10 | 4.5

- Speed Up Your Macros in a Few Short Lessons!
- Create and Manipulate Data Invisibly in the Background
- Learn to populate Arrays several different ways

What you'll learn

Speed Up Your Macros in a Few Short Lessons!

Create and Manipulate Data Invisibly in the Background

Learn to populate Arrays several different ways

Understand multiple ways to put Array data onto worksheets

Store Data in Hidden Locations

Where only you can find and Manipulate it...

Return Multiple Pieces of Information from a Single Function

Pass Arrays into Subs and Functions to Manipulate Lots of Data at Once!

Learn Special Built-In Functions specifically for Arrays as well as Custom Functions

Master the Art of Array Sorting - (3 different Methods!)

Glean information from and Manipulate text like never before

Store MULTIPLE pieces of information in a single cell to keep your tables tidy

Create your own functions that return more than one result!!

Master Populating

Looping through and Manipulating Arrays for greater efficiency

* Requirements

* You should already have Microsoft Excel (version 2003
* 2007
* 2010
* 2013 or 2016). Obviously the newer the better
* but VBA works the same in all versions. It's just that newer versions have more options
* thus more VBA Commands to control those options.
* Basic Excel skills needed for this course. Everything I do is explained thoroughly.
* Everything is explained in detail
* but any Excel VBA experience you have will help.

Description

Speed Up Your Macros in a Few Short Lessons!
Create and Manipulate Data Invisibly in the Background
Learn to populate Arrays several different ways
Understand multiple ways to put Array data onto worksheets
Store Data in Hidden Locations, Where only you can find and Manipulate it...
Return Multiple Pieces of Information from a Single Function
Pass Arrays into Subs and Functions to Manipulate Lots of Data at Once!
Learn Special Built-In Functions specifically for Arrays as well as Custom Functions
Master the Art of Array Sorting - (3 different Methods!)
Glean information from and Manipulate text like never before
Store MULTIPLE pieces of information in a single cell to keep your tables tidy
Create your own functions that return more than one result!!
Master Populating, Looping through and Manipulating Arrays for greater efficiency

Course content

4 sections • 48 lectures

I Need Arrays, Boss! 9 lectures

Introduction and Workbooks Download Preview 01:30

An intro to the course…

Getting FAST Help and Other Freebies with Excel Ninjas Group Preview 02:05

What's An Array - Overview and Sheet Demonstration Preview 02:54

In this lecture, we show what an array is and what it does so that we can be poised for the next lessons…

What's An Array - Technical Demonstration With Code Preview 04:47

In this lecture, we're going to see a technical demonstration on using arrays and how to work the code.

More On One-Dimensional Declaration Preview 02:14

Let's talk a little bit about one-dimensional arrays and their declaration

Declaring Non-Traditional Boundaries and Thresholds Preview 00:37

Learn how to use nontraditional boundaries and thresholds. You don't necessarily have to start with a one or a zero, you don't necessarily have to even start with something above zero.

Data Types in Arrays Preview 04:08

In this lecture will talk about all the different datatypes in arrays and how you can store multiple data types in a single array! Fun.

Cells Object Lesson and Comparison Preview 02:13

Arrays are actually a pretty familiar subject once you know what's going on. In this lecture will explore an old friend that we've known for a long time to help us better understand arrays.

Multidimensional Arrays Preview 05:21

Let's talk a little bit about multidimensional arrays. This is where it gets fun!

Populating and Manipulating Arrays 20 lectures

Populating an Array with Array() Function Preview 03:09

The first way were going to populate an array is using the Array() function itself.

Populating an Array with Split() Function Preview 04:28

Now will populate an array using the Split() function.

Section 02 Exercise Preview 00:12

Coding exercise for section 2.

Exercise - Solution Preview 05:53

This lecture will take you through my particular steps of solving the exercise. Don't be discouraged if you find another way, be encouraged! There are lots of ways to solve almost every problem!

Populating an Array with a Range Via (FOR NEXT) Loop Preview 05:00

Let's learn how to use a for next loop in order to populate an array

Populating an Array with a Range Via (FOR EACH) Loop Preview 07:35

I will use a for each loop to get the job done…

Populating an Array with a Range Directly Preview 03:37

This is particularly powerful! Will learn how to take in a rate directly from a range. Check it out!

Getting the Start and End Boundaries Preview 02:44

In this lecture, we learn how to get the start and end boundaries of an array.

Looping Through a 1 Dimensional Array Preview 03:36

Further exploration of looping through a one dimensional array

FOR EACH Loop Through an Array Preview 01:53

In this lecture will use a for each loop through an array

Looping Through a 2 Dimensional Array Preview 05:45

Now let's loop through a two dimensional array! Fun fun fun!

Subscript Out Of Range - Reasons for Run-time error 9 Preview 01:59

We all hate subscript out of range errors. Here's why they happen and how to avoid them!

Static vs Dynamic Preview 01:56

Let's learn a little bit about the difference between static versus dynamic arrays.

Change The Size (on the Fly)! Preview 05:54

Learn how to change the size and scope of your array at any time.

Resize Array Without Losing Everything Preview 03:52

Usually when you resize an array you lose all of the previous entries, but with this tip you can save what you were using and just expand your array a bit. This is cool!

Returning Array to a Worksheet Preview 08:37

This is one of the most important lectures in this entire course!! In this lecture, we're going to learn how to return an array to a worksheet.

BONUS - Real World Speed Comparison - Case Study Preview 09:07

Showing just how bad to the bone Arrays can be. This is a real world speed comparison, showing how much faster and efficient arrays are! The results will blow you away...

-Dan

Storing Array Data... Preview 04:31

Let's talk a little bit about storing array data…

Storing Array in Names Preview 05:18

Storing arrays in names

Accessing Array from Invisible Name in Names Manager Preview 02:07

Let's learn how to access an array from an invisible name in the names manager! Very few people know this trick!

Functions, Arrays and You - A Magical Combination 16 lectures

Using WorksheetFunctions on Arrays Preview 06:14

Using worksheet functions on your array so you don't have to do everything from scratch.

Passing an Array Into a Function or Sub Preview 07:25

Learn how to use an array by placing it inside of a function and having the function perform actions on any and all elements in your array!

Passing an Array Into a Function or Sub (Advanced Example) Preview 11:19

Using the advanced example on how you can pass your array into a function or sub

Returning an Array FROM a Function Preview 09:06

Did you know you can also extract multiple answers from one single function? Here's how!

IsArray Function Preview 03:27

Learn the IsArray() function

Filter() Function Preview 05:37

Learn the Filter() Function

Join() Function Preview 03:05

Join Function

Join() Function - Practical Example Preview 09:04

A practical example on the join() function

The Erase Method Preview 03:10

Using the erase function in an array

Transposing an Array Preview 05:19

Sometimes you have to transpose an array for it to work properly on a worksheet. Sometimes, you may need to flip-flop the information so that it's easier to loop through or look at also.

Determining Worksheet Range from Array Size Preview 07:01

Determining worksheet range using the size of the array itself.

Dynamic Sorting 01 - 2d Array On Sheet Dynamically Preview 05:16

This is one way to sort your data and it's my recommendation.

Dynamic Sorting 02 - Dynamic Sort Record, Syntax and Explanation Preview 13:20

BONUS - ReDim Preserving a Multi Dimensional Array - Part 1 Preview 03:29

Bonus lecture Discusses using a two-dimensional or greater multidimensional array when you want to resize and preserve the records that you already have.

BONUS - ReDim Preserving a MD Array - 02 Transpose Method Preview 04:57

This lecture reviews a workaround using the transpose method, wherein we will transpose in order to make the rows into the columns, increase the number of columns and then transpose back to the original format.

BONUS - ReDim Preserving a MD Array - 03 Custom Function Preview 04:46

This lecture reviews a workaround using a custom function that allows you to expand your multidimensional array anyway you want using special loops and basically using a temporary array in the function created with the expanded dimensions and filled in using the original array.