Written by: Jennifer Thompson
Macros and automation can save so much time, ulcers, gray hair, etc. In STATISTICA, creating macros to automate tasks is as easy as hitting record on your DVR. They are fast, and they make sure the analysis is done consistently with the same options and analysis procedures. One drawback, when comparing macros to people, is macros don’t think for themselves. They just run the script, even if the analysis it is performing is absurd. So they may need taught to play nice with their data sets.
Recorded macros reference the variable’s position in the spreadsheet. The macro can be run on this data set as often as needed, always giving the proper results. If a new variable is added or one is deleted, the position of the variables for analysis may change. A new data set may also organize the variables differently. If this is the case, running the macro will no longer give the expected results. Where the macro was supposed to build a regression model between X and Y, it is now treating X as dependent and using ID number as the independent variable. Chaos ensues.
What if there was an easy way to customize the macro to protect against this potential problem? I’ll give you two. The easy way is to simply remove the variable reference and the then you will select variables each time the macro is run. The more involved method takes a few steps to have the macro reference the variables by name, but the resulting macro runs without your input.
Method 1: Delete Variable Reference
Delete the variable selection portion of the macro. Variable selection in recorded macros behaves basically the same way regardless of analysis or graphing procedures used. The macro selects variables similarly to how it’s done in this example. The variable selection line is oAD2.Variables = “3-5”. Variables 3 through 5 will be used for this descriptive statistics analysis.
By deleting 3-5, leaving empty quotes, the variable position reference is removed. Now running the macro will prompt you to select appropriate variables. As is typical of life, taking the easy option now, means more work in the future. Each time you run this macro, you will need to select variables for analysis. The plus side is that the macro is compatible with any data set now.
Method 2: Customizing Macro to Reference by Name
The alternative is to add some custom, yet simple, programming to reference the variables by the name. Then variables can be added, deleted, rearranged, etc., and the macro will still use the proper variables. The macro can be used on a new spreadsheet with the same variable names with no issues.
I will not claim that my way is the only way that works, or that it is the best way. (It may be the best way.) If nothing else, this example can spark ideas for improving your custom macros in STATISTICA!
I started with a loop to access the variable names and pick out the three variable names of interest. You may recognize them from an example data set, Adstudy.sta. They are MEASURE01, MEASURE02, and MEASURE03. Then I modified the variable selection line to use references based on variable names. The steps are listed below.
- Reference the spreadsheet, S1.
- Create placeholders, v1, v2 and v3, for the 3 variable positions.
- Create an array, VarList, for storing the variable names.
- In a loop, find the spreadsheet position of each of the variables and store them in the placeholders created in step 2.
- Modify the recorded macro, variable selection line, using the variable position variables.
- Delete any unnecessary lines of recorded code for simplicity.*
Now running the macro will select the variables named MEASURE01, MEASURE02, and MEASURE03, regardless of their position in the spreadsheet. The macro is ready to play nice with other data sets.
*Note: Recorded macros list all available options and settings. This makes modifying the macro much easier later on. It is easier to change an option from “True” to “False” than to come up with the line of code to access that option. For the purpose of showing others how a macro works, it helps to clean up the code, removing unnecessary parts. So I removed the lines of the recorded macro that simply set the default settings. The macro works the same with or without these lines.