Development
Programming Languages
Excel VBA
The Ultimate Excel VBA Arrays Course
4.5 (393 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
2,339 students enrolled

The Ultimate Excel VBA Arrays Course

Learn To Manipulate Data Invisibly and At Lightning Speeds with Arrays!
4.5 (393 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
2,339 students enrolled
Created by Daniel Strong
Last updated 4/2019
English
English [Auto]
Preview this course
The Ultimate Excel VBA Arrays Course
Current price: $71.99 Original price: $119.99 Discount: 40% off
2 days left at this price!
Buy now
30-Day Money-Back Guarantee
This course includes
  • 3.5 hours on-demand video
  • 1 article
  • 6 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
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
Course content
Expand all 47 lectures 03:43:46
+ – I Need Arrays, Boss!
8 lectures 23:44

An intro to the course…

Introduction and Workbooks Download
Preview 01:30

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 - Overview and Sheet Demonstration
02:54

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

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

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

More On One-Dimensional Declaration
02:14
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.
Declaring Non-Traditional Boundaries and Thresholds
00:37

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.

Data Types in Arrays
Preview 04:08
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.
Cells Object Lesson and Comparison
02:13
Let's talk a little bit about multidimensional arrays. This is where it gets fun!
Multidimensional Arrays
05:21
+ – Populating and Manipulating Arrays
20 lectures 01:27:13

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

Populating an Array with Array() Function
03:09

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

Populating an Array with Split() Function
04:28

Coding exercise for section 2.

Section 02 Exercise
00:12
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!
Exercise - Solution
05:53

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

Populating an Array with a Range Via (FOR NEXT) Loop
05:00
I will use a for each loop to get the job done…
Populating an Array with a Range Via (FOR EACH) Loop
07:35

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

Populating an Array with a Range Directly
03:37

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

Getting the Start and End Boundaries
02:44
Further exploration of looping through a one dimensional array
Looping Through a 1 Dimensional Array
03:36
In this lecture will use a for each loop through an array
FOR EACH Loop Through an Array
01:53

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

Looping Through a 2 Dimensional Array
05:45

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

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

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

Static vs Dynamic
01:56

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

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

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!

Resize Array Without Losing Everything
03:52

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.

Returning Array to a Worksheet
08:37

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

BONUS - Real World Speed Comparison - Case Study
Preview 09:07
Let's talk a little bit about storing array data…
Storing Array Data...
04:31
Storing arrays in names
Storing Array in Names
05:18

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

Accessing Array from Invisible Name in Names Manager
02:07
+ – Functions, Arrays and You - A Magical Combination
16 lectures 01:42:35

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

Using WorksheetFunctions on Arrays
06:14

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
07:25
Using the advanced example on how you can pass your array into a function or sub
Passing an Array Into a Function or Sub (Advanced Example)
11:19
Did you know you can also extract multiple answers from one single function? Here's how!
Returning an Array FROM a Function
09:06
Learn the IsArray() function
IsArray Function
03:27

Learn the Filter() Function

Filter() Function
05:37

Join Function

Join() Function
03:05

A practical example on the join() function

Join() Function - Practical Example
09:04

Using the erase function in an array

The Erase Method
03:10
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.
Transposing an Array
05:19

Determining worksheet range using the size of the array itself.

Determining Worksheet Range from Array Size
07:01
This is one way to sort your data and it's my recommendation.
Dynamic Sorting 01 - 2d Array On Sheet Dynamically
05:16
More on dynamic sorting…
Dynamic Sorting 02 - Dynamic Sort Record, Syntax and Explanation
13:20

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 Multi Dimensional Array - Part 1
03:29

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 - 02 Transpose Method
04:57

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.

BONUS - ReDim Preserving a MD Array - 03 Custom Function
04:46
+ – Custom Functions - Because Microsoft Can't Think Of It All
3 lectures 10:13

A custom user-defined function - the Bubble Sort method of sorting data.

Bubble Sorting
03:45

A custom user-defined function - the Quick Sort method of sorting data.

Quick Sorting
06:25
BONUS LECTURE
00:03
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

Arrays are present in many programming languages, but most people don’t take the time to master them and can get frustrated when their code seems sloppy, difficult to read, and often can take a long time to run.

Arrays are often a subject of confusion or irritation - it doesn’t make much sense when trying to understand using a huge blog post or cryptic book. What you need is a visual explanation of these concepts in bite sized pieces – something to make it REAL!

With over three hours of over the shoulder, real-world training, this course will show you how to increase the speed of your VBA applications, build code that is easier to read and maintain  -  using less lines of code than you ever imagined!

I’ll show you the easiest way to understand this highly convoluted topic and once you understand the power of arrays, you’ll be able to:

  • Glean information from and Manipulate text like never before
  • Store MULTIPLE pieces of information in a single cell to keep your tables tidy
  • MASSIVELY speed up your macros that deal with worksheets or cells!
  • Understand multiple ways to put Array data onto worksheets
  • Create your own functions that return more than one result!!
  • Store Data in Hidden Locations, Where only you can find and Manipulate it...
  • Master Populating, Looping through and Manipulating Arrays for greater efficiency
  • Learn special built-in Array functions as well as custom ones Included in the course
  • Best methods of Sorting Array data
  • And much more!

You’ll receive 100% of the source code, downloadable materials and course workbooks instantly, yours to keep FOREVER. In addition, you’ll receive LIFETIME ACCESS to the course videos, including updates, BONUS videos and lectures as they come out and any future source code and workbooks – Yours to keep – FREE.

All students will receive LIFETIME ACCESS to ask me any questions about the materials in the FORUM section – as well as chat with other students and help each other. As always, this is backed by a 30 day 100% No questions asked refund as well.

So Join today – and increase your Excel Mastery and I’ll see you in the FORUMS!

Who this course is for:
  • Take this course if you want to increase the SPEED of your macros and be more efficient. No prior programming knowledge is needed, but it helps.
  • If you want to learn the basic of Arrays - even if you don't use Excel VBA - Arrays work very similarly in other programs. This course primarily covers Excel VBA Arrays though.
  • If you want to know the hidden secrets of Excel for speed, and heightened data manipulation skills, this course is for you.
  • Anyone looking for the next step in their Excel Ninja skillset.
  • If you need a simple explanation for a complex topic (Arrays) in a way that you can take and immediately use.
Featured review
Consuelo Palmas
Consuelo Palmas ( 29 courses, 7 reviews )
9 months ago
This course is awesome! The instructor explains everything very clearly and the pace is just right. All the information I was looking for is covered. I certainly have benefited greatly from this course, highly recommended.