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:
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.
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.
The macro (which I call “The Macro That Writes Macros”) does the following three things:
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:
The “Macro_That_Writes_Macros” creates a data structure using the header as variable names that looks like this:
LastName As String
Sales As Currency
DateHires As Date
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.
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.
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.
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.