Let’s say you have an Analytics Edge workbook that uses a Repeat Macro function to loop through several web properties, each writing to a separate worksheet. Since Analytics Edge doesn’t help you with worksheet formatting much, let’s say you whipped up a little VBA code to do it. How would you integrate that VBA code into the Analytics Edge macro?
Passing Parameters
Analytics Edge macros are standalone code snippets that start and stop. In order to send some data into a macro, from one macro to another, or from an Analytics Edge macro to a VBA macro, you need to use the Excel workbook as an intermediate storage space. Simply write the data to a worksheet, and let the next macro pick it up from there.
In the Analytics Edge macro, simply use a Write to Worksheet function to store the parameters you want to pass into a temporary worksheet. That worksheet can even be hidden in Excel, so no one sees it by default (if the information is sensitive, you can always overwrite it in a later macro step).
To pick up parameters in an Analytics Edge macro, use the Read Worksheet function. You can check the option to load it directly into a named table in memory, or assign range names to each column so you can use individual values.
To pick up values in a VBA macro, you can simply grab the value from a specific worksheet cell using a VBA command like:
'Get the name of a worksheet from a cell G2 (row 2, column 7):
cellvalue = Worksheets("Sheet1").Cells(2, 7).Value
Values can be passed back in a similar fashion.
Don’t be afraid of passing parameters in this fashion — the workbook is in memory, so it is fast. You can always clear the data from any temporary worksheet by overwriting the location with a small set of meaningless data. That could be simply use Filter with your data to remove numbers greater than 0, and then Arrange to remove all but the first column. Write that one cell to the same location as your temporary data, and let the automation clean up after you.