Moore - Mastering Excel Macros: Arrays

Mastering Excel Macros

Arrays Book 10

Mark Moore

Copyright 2016 by Mark Moore. All rights reserved worldwide. No part of this publication may be replicated, redistributed, or given away in any form without the prior written consent of the author/publisher or the terms relayed to you herein.

Introduction

Welcome to another Mastering Excel lesson. If you have previous lessons, thanks for sticking around. If you are new, I hope you enjoy the lesson. The lessons are an easy-going, relaxed, no-nonsense easy to understand. I try my best to explain complex topics in a simple and entertaining way. My goal is that you will finish reading each lesson and have immediately applicable skills you can use at work or home.

If you want to work along the exercises in this lesson (I strongly recommend this) please go to my website and download the follow-along workbook. My website is: http://markmoorebooks.com/mastering-excel-macros-arrays/

A bit of clarification on how to get the follow-along workbooks. You will input your name and email address. You will receive a confirmation email. Once you confirm, you will receive a second email with the follow-along workbook.

Why do I do this?

I cant package an Excel file with an eBook. Amazon will not allow it. Also, the only thing I do with your email is send you the workbook and periodically send you updates about new lessons that I am working on.

Introduction

Suppose you have thousands of data points in a worksheet that need to be processed. You could populate a single variable with each cell value, process it, then put the new value back in the cell. This would work but it is very, very slow. Alternatively, you could create thousands of variables in your macros (i.e. Item1, Item2, Item3, etc.), populate them, process them, then put them back in the worksheet. This is marginally better. However, do you really want to be managing thousands of variables? Of course not.

VBA has special types of variables that let you manage large amounts of data easily. They are Arrays, Collections and the Dictionary. This lesson will focus on how to use these objects to greatly speed up your macros.

As an example, lets look at the follow-along workbook, Speed.xlsx. This workbook has about 5,000 data points in column A. I need to calculate the sum of squares for column A. In other words, I need to raise the number to the power of 2 and then take the sum of all the squares.

There are two macros in the workbook. They each take the value from a cell in column A, square it and store the value. One macro does this cell by cell, the second macro does it by using an array.

Look at the performance improvement just by using arrays! It took 14 seconds to process the data cell by cell but just milliseconds to use an array.

If you dont believe me, go ahead and run the macros to see for yourself.

Why is the improvement so drastic? Because array are stored in the computers memory. It is exponentially faster for a computer to use memory space for calculations versus having to constantly go back and read the data cell by cell.

If you are dealing with large amounts of data and calculations, you are better off using arrays, collections or dictionaries.

Arrays

An array is a variable that can store multiple values of the same type. You can think of an array as a shoe rack. Each pair of shoes has a location where you can store the shoes until you need them. An array is similar except that instead of shoes you are storing data of the same type . Shoe racks are only for shoes; you cant stick a coat in there (well you could, but it would get all wrinkled). In an array, you have to declare the data type and then put data in it that matches the data type. For example, if you create an array to store integers, you cant store text in it.

Arrays are excellent for when you need to:

• Store many numbers and use them in calculations
• Change the numbers in the array to other numbers

Arrays are not great when you need to:

• Find one particular number
• Sort the numbers

I want to set the expectations for these exercises before I start. These exercises are designed to be simple on purpose. Almost everything you are going to do in this lesson can be done using formulas. The goal here is not to show you the best way to perform a calculation in Excel, the goal is to show you how to do it in VBA.

If you find yourself thinking, Why would I do this task this way when a simple formula would work? then suppose you are working on a very large, complex workbook that takes 10 or more minutes to calculate. Now the convenience of doing the calculation in a macro versus waiting for 10 minutes becomes apparent.

Types of Arrays

Arrays can be static or dynamic . Static arrays are fixed in size; you know exactly how many items you are going to store. Dynamic arrays are variable and can be resized as needed.

Array Dimensionality

Arrays have the concept of a dimension. You can think of a dimension as a field. A one-dimensional array will have one field available to store data. A two-dimensional array will have two fields to store data. You can have as many dimensions as you like in an array, but anything past two dimensions get really tricky to work with. This lesson will not go into the topic of arrays with more than two dimensions.

Lets begin with a hands-on exercise. I will explain new topics as you work through the exercise.

The goal of this first exercise is to create a one-dimensional array, calculate the average score of all the students, and put the average score in a cell in the worksheet.

1. Open the follow-along workbook, One-dimensional Array.xlsm.

This is an image of the sample data you will be working with.

1. Insert a new VBA module (Developer>Visual Basic).
2. Create a new macro called CalcAverage.

Declaring Arrays

The first thing you have to do when using array is to declare (i.e. create) it. When you declare an array, Excel sets apart a portion of memory to handle that data.

The data has 50 students. You are going to create a 50-item static array to process the data.

Data Types

Ive covered data types before, but lets have a quick refresher. Specifying the exact type of data that the array will store will make the array more efficient. Excel will store just enough memory to handle the data. You can use a bigger data type that is needed and it wont break anything. Honestly, you might never see any performance improvements depending on how complex your code or calculations are.

VBA also has a data type called Variant. This is a catch-all data type that can store both text and numbers. However, it is not as efficient as the other data types.

For your reference, here are the various data types you can use:

1. Declare a new 50-item static array of type Integer. Name the array Scores.

A one-dimensional array is really just a list of numbers. Thats it. In this case, you created a 50-item list of numbers. This array starts a 1 and extends up to 50 items. As you get exposed to arrays, you will notice that some arrays start at 0, not 1. The default behavior is for an array to start at 0. Its not that big of a deal, just remember that if you start at 0, you will need one less item. For example, if I started at 0, the array would be 0 to 49, since the first score would be in the 0 spot.