If you’ve written a few lines of script and aren’t afraid of words like “variables”, this guide will get you going quickly with Analytics Edge macros. Even if you’ve just played with a few Excel formulas, you might surprise yourself with how much you can pick up.
First of all, Analytics Edge macros are not like Excel VBA macros, and they do not require any “programming”. It is all wizard-based and you never actually write any lines of code, but I chose to stick with the Excel term of ‘macro’ since they share a lot of other characteristics. The application is designed to keep your focus on your data and to get the job done quickly. No one wants to spend all day with a spreadsheet.
Macros Are Made Of Functions
Each Analytics Edge macro is nothing more than a series of functions, and each function is created and edited using a wizard. There is no syntax to worry about — just fill in the fields or check the options in the wizard.
Functions create or change a table in memory. Functions do something (like making a query), and if it results in data, then the data is held in a table in memory. Subsequent functions can change that data in memory, or write it out to a worksheet or file. The results of the first function, usually a query or read worksheet function, are fed to the next function. The results of the second function are fed to the third function, and so on. This mirrors the way you work in Excel — you filter, then sort, then re-arrange the columns, and so on. Macros typically start with a query or read worksheet function, and end with a write to worksheet function, and they automate the step-by-step transformations in between. See: Active table in memory
Working With Tables
The table in memory is like an Excel Table. It has columns of text, numbers or dates. All the values in a column are text, or all the values are numbers, or all the values are dates. Individual columns can never contain mixed types. If you try to force mixed types (e.g. by reading mixed data), the add-in will convert everything to text.
Functions change the table. You can add functions that change individual columns (like Calculate or Replace), or change the entire table (like Duplicates or Pivot). You can also convert columns from one type of data to another, like Converting a text column into a date column. See Using convert
Naming a table creates and freezes a copy. You can take a ‘snapshot’ of the table in memory at any time by simply assigning a name to it (using the Table > Table Name function). These named tables are held in memory until the macro finishes, allowing you to perform multi-table functions like Combine or Lookup. See: Table name
All tables get dropped at the end of the macro. When a macro ends, the working memory is cleared and all the named tables are purged. Tables do not ‘persist’ between macros, so if you want to access data from one macro in another macro, you will need to write it to a worksheet in the first, and read it from the worksheet in the second.
Range Names (a.k.a Variables)
Range names are table column names. All good programming languages support the concept of variables, and Analytics Edge macros call them ‘range names’. Range names are simply the column names of a table in memory, and are referenced in square brackets, just like when referencing an Excel Table column (e.g. “[Sessions]” refers to the values in the Sessions column).
Range names can be used in most wizards. Wherever a wizard allows you to enter a value, it will probably accept a range name. When the macro is run, the range name’s value will be evaluated and used in the function. See: Using range names
Range names can refer to a single value, or a range of values. In wizard locations looking for a single value, the value in the first row of the column referenced by the range name will be used. There are a few locations in the product where the entire column of values could be used, but most functions are looking for single values.
Quick ways to create range names: aside from using Table > Table Name to assign range names to a table in memory, you can use a Read Worksheet option to assign them to data you read from a worksheet (in one step). You can also refer to columns from the current table in memory — if there are no previously defined range names with the same name. See: Using read worksheet
Program Flow Control
While you can string a large number of functions in a single macro, development can become overly complicated by doing this. A better approach is to break up the flow into manageable chunks, and relying on Refresh All to run all of the macros in succession.
Macros are executed in alphabetical order when the workbook is refreshed, so you can control the order of actions by simply naming the macros appropriately (e.g. 01, 02, 03, etc).
Underscore macros (e.g. “_01”) are NOT part of Refresh All. If you want to create a macro that can only be run manually (or by calling it from a Run Macro Function in another macro), simply name it starting with an underscore character. This allows you to develop macros that are for one-time uses like setup or data cleaning operations. See: Using the task pane
You can run Analytics Edge macros from VBA macros and vice versa. The product allows you to run an Analytics Edge macro from VBA. You can also run a VBA macro from an Analytics Edge macro. Note that if there is a problem with one side or other, Excel could ‘hang’ and crash in which case you may need to re-enable the add-in. See: Run macro function and Run macros from VBA
You can stop execution of a macro with a Filter function option (Stop macro if empty). If you want to perform certain functions only if certain conditions occur, then manipulate some data so you can test for that condition, and use the Filter function to test for that condition, proceeding with the rest of the macro only if the condition contains data (Stop macro if empty option). Stopping the macro in this way does not cause an error or stop other macros from running during a Refresh All. See: Using the filter function
Conditional macros are easy to create. A typical usage scenario is to use a series of macros to refresh a report’s values, then add a macro to the check the results before it sends the report by email. Use Read a Worksheet to get a key value from your report, then Filter to keep only a good value (with the Stop if empty option). Repeat for other key values in your report. End the macro with a Save/Email PDF function, and it will only get that far if it passes all the tests. You could even set up another macro that filters out all the good values (which would leave an empty table), and then sends you an email notification of bad or questionable data (if the table is not empty). See: Save/email PDF
Repeat Macro (a.k.a. Looping)
A Repeat Macro will loop through all values in a specified worksheet. The values from each column in your source worksheet are assigned range names, and those can be used through the rest of the macro. The repeat macro will repeat the macro for each row of data in your source worksheet. During development, only the first row is processed, but when you run the macro, it will loop through all the rows, running the macro with each row’s values.
Note that each loop of the repeat macro starts empty, and nothing is passed to the next loop. To avoid the Write to Worksheet function in one loop overwriting the previous loop’s results, use an Append or Append to Worksheet function. Note that the Repeat Macro function can optionally clear a target worksheet before the first loop runs, making it easy to append results to a clean worksheet every time. See: Using repeat macro
Repeat Macros are typically used in a sequence. One macro will typically set up the data to be looped through, the repeat macro runs the loop and appends data to a results worksheet, then a third macro would process the results worksheet and summarize it for the report.
Designed For Easy Editing
Since there is no programming code to struggle with, don’t be afraid to copy, paste or reorder the functions in your Analytics Edge macro. You can execute the functions one at a time with the Step button, and the “ᴁ Step Results” worksheet will keep you aware of what the data looks like every step of the way. See: Using the task pane and Active table in memory
Copy and paste between macros or between workbooks. Just make sure the destination is available before you make the copy — Windows has an annoying habit of clearing the clipboard at times.
Step quickly to a function by selecting it, then clicking the Step button — the macro will run to that function and stop. Run the remainder of a macro by clicking the Run button. Clear your selections and stepping status by clicking Stop.
Don’t Be Afraid To Ask
I have been programming for decades and playing with my product for years, so if you can’t figure out how to do something, drop me an email. I do not do consulting work, but I do give away a lot of my time helping people with their analytics challenges. See: Asking for help