If you are like me, you may find yourself writing a fair number of MS Excel macros to automate repeatable tasks. One day it dawned on me, however, that several of the aspects of writing macros were repeatable tasks themselves. When writing a macro I would often start by:
- Creating a data structure that would hold my data,
- Creating an array of those data structures and
- Writing a routine to read data from a worksheet into my array of data structures I just created.
Realizing that much of my MS Excel macro writing represented repeatable tasks, I thought, “Why don’t I write a macro that will do these three things for me?” After writing it, the macro worked so well, I thought others could benefit from it. At the bottom of this article is a macro you can copy and paste into your MS Excel Visual Basic script which will perform these repeated tasks.
Below is a brief explanation of the macro and how to use it. Along with this is a discussion on some limitations of the macro and how you might make adjustments to it to meet your needs.
Advantages of Macros
Macros come in handy when you need quick repeatable measures. This is excellent for the Control phase of your DMAIC (Define, Measure, Analyze, Improve, Control) improvement projects. For the Control phase you want to set up a repeatable process that does not take up a lot of time. Often you will need to measure something on a regular interval to make sure your improvements stay in control. Macros can help automate this process so the act of measuring does not take near as long. You can even add a “smoke alarm” component to the macro if you like to send an email if it detects your process is outside of the parameters you set.
However, many macros start with the three steps mentioned above. So why not start your macro writing process by using a macro that writes macros.
What Does the Macro Do?
The macro (which I call “The Macro That Writes Macros”) does the following three things:
- It creates a data structure type that matches your data. It does this by looking at the header row of the worksheet housing your data and creates variable names based on the header row. Then it looks at the first row of actual data (the one just following the header) and determines the data types needed based on the data there.
- It then creates an array of this data structure type of the correct size so that the array will hold all the data on your data sheet. It does so by counting the number of rows of data you have on your data worksheet and making the array this size.
- Finally it writes a macro that will read in all your data from the data worksheet into the data structure array described in step one above.
Once you have this code which the macro creates you can write subroutines to go along with it to evaluate, combine, or write your evaluated data into reports, alerts, etc. as you see fit.
For example, if your data looks like this:
| LastName | Sales | DateHired |
| Smith | $50,000 | 1/1/2012 |
| Jones | $35,000 | 3/15/2015 |
| Thomas | $45,000 | 7/1/2010 |
The “Macro_That_Writes_Macros” creates a data structure using the header as variable names that looks like this:
Type DataSheet
LastName As String
Sales As Currency
DateHires As Date
End Type
It will create an array of this data type of the size that matches the number of rows of data you have minus the header:
Public Data(1 To 3) As DataSheet
And it will create a macro that will read in the data. Once you have run the macro it creates, the data structure will house all the data from your data worksheet. For example the array of structures created above would have the following values.
Data(1).LastName = Smith
Data(1).Sales = $50,000
Data(1).DateHired = 1/1/2012
Data(2).LastName = Jones
Data(2).Sales = $35,000
Data(2).DateHired = 3/15/2015
Data(3).LastName = Thomas
Data(3).Sales = $45,000
Data(3).DateHired = 7/1/2010
This may not add a lot of value if your data table is 3 by 3. However, suppose you have a data table with 200 columns and several thousand rows of data. It would be nice to have a great start on a macro that could hold all the data you have in memory. You could then write additional subroutines and functions to work with the data as needed.
- Open an MS Excel file and save it as an “Excel Macro-Enabled Workbook”.
- Next open the Visual Basic Editor. Copy and paste the code at the bottom of this article into the editor.
- Create a worksheet within the file called “Data” then copy your data in this worksheet. You can do this in one of two ways:
- Copy and paste your data into the “Data” worksheet. Make sure your data has a header row as the header names will become the variable names in your data structure.
- Or perform a Copy worksheet using the Excel Copy command from one Excel file to another. Then rename the worksheet to the name “Data”.
- Run the Macro – “Macro_That_Writes_Macros.” This will create the data structure, array and macro that will read in all data on your data tab into the array of data structures. This code will be automatically written on the worksheet named “Code”.
- Copy and Paste the code from the “Code” worksheet into the Visual Basic Editor. You can overwrite the code that is there if you like (overwriting the Macro That Writes Macros as you can get it again from this article). Or you can insert the code from the “Code” worksheet directly after the Public variable declarations that are already there.
- Add to the macro as you see fit. The new macro you pasted will have a Main routine that looks like this:
Sub Main()
Call ReadDataSheet
End Sub
The ReadDataSheet routine will load all your data from your “Data” worksheet into memory in the array of data structures the macro created. Once your data is in the data structure in memory you can write addition vba code to work with your data as needed. You can write additional subroutines and call them from the Main routine. Or you can add code to the ReadDataSheet file to evaluate your data, create reports or send alerts as needed.
Limitations and Advantages of the Macro That Writes Macros
Limitations
The macro assumes you have less than 10,000 rows of data and less than 500 columns of data. If you have more than 10,000 rows or 500 columns simply do a global replace for the occurrence of the number 500 with your actual number of columns or a global replace of 10000 with the actual number of rows you have.
Also the macro assumes the data type for each column is consistent throughout the entire column and that there are values in every column in the second row so that it can determine the data type needed.
Advantages
The macro creates a routine called Main. You can add to this routine by adding code directly into Main or by making subroutine calls from Main once the data is read in.
The Macro
Cut and paste the code for the Macro That Writes Macros into your Visual Basic Editor.
