In this blog, we explain NEXT’s implementation of Visual Basic for Applications (VBA) and how it may be used to improve dashboard in a number of ways. With VBA, you can control Microsoft Excel by using function calls in a number of ways:
- Automatically invoke NEXT Analytics “Refresh Data” when the workbook opens. Give a workbook with the most recent data to your business users to make sure that the decisions they make are based on the most up-to-date numbers.
- Make it easier for end-users to refresh a worksheet themselves by adding a button to the worksheet to trigger a Refresh Data. This could be useful when you have provided on screen controls such as list and combo boxes to let people choose values that modify the NEXT commands that fetch and process the data.
- Select what labels and values to show after you run a query by using functions. You can position labels very precisely in any worksheet. They won’t get overwritten and they won’t change their format. This allows you to, for example, blend the values from different actions into the same worksheet that’s acting as a Dashboard or Summary worksheet.
Here’s how to add these operations to your workbook.
Functions Support Uses VBA
Using VBA is an Excel feature that a power-user would use. If you want the benefits of functions, but find the topic of VBA to be confusing, please contact NEXT Professional Services and we work with you to implement it.
Triggering a Data Refresh When a Workbook Opens
From the Excel Ribbon Bar, click the item that says “Add VBA”.
A new dialogue box will appear.
Click the appropriate button, and then save your workbook as a Macro Enabled Workbook (XLSM extension). From then on, your workbook will perform a NEXT Data Refresh when the workbook is opened.
Add a Button to the worksheet that can trigger NEXT Data Refresh
Perhaps you don’t want the data refresh to occur when the spreadsheet is opened, but you want the user to be able to supply some values and then refresh the data. If you have performed the previous operation “Trigger a Data Refresh”, then it’s easy to add a button to a worksheet that communicates with NEXT Analytics using VBA. To start, select the Developer tab of the Excel Ribbon bar. If you don’t see the Developer tab in Excel, you need to add it from the Excel Options dialog.
Click the “Insert” and choose the top left icon:
When you do that, the following dialog pops up.
Click the New button to get an event handler in the embedded VBA layer of Excel.
The event handler “Button1_Click” can make function calls into the NEXT software. In this case, it will invoke a function which performs NEXT Data Refresh.
Inside of that event handler, paste this text:
Run_Nextanalytics_Scripts “”, “”
It will look like this:
If you haven’t already, save your workbook as a macro-enabled workbook. Clicking the button will now trigger a data refresh. This can be quite useful if you use cell values in conjunction with NEXT Scripting to allow the user to provide inputs. You could, for example, let them choose Google Analytics segments, Facebook Fan Page IDs, or values for filter expressions in URL tags.
As a closing point, you may want to disable the automatic data refresh when the workbook is opened. You can do that by commenting the call to Run_Nextanalytics_Scripts that’s in the hidden worksheet “This_Workbook” in the VBA project.
Use Excel Functions to insert Analytics values anywhere on any worksheet
How to use NEXT as function calls within Excel cells.
First, let’s create a scenario to explain what you can do.
Suppose you had part of a dashboard such as the following image:
Let’s examine that in detail:
- You formated the output
- In cell B7, you’ve inserted an Excel formula, in the middle of the data table…
You want to refresh the data, but you don’t want to lose that work. Follow these steps:
- On the RibbonBar, click “Add VBA” to your workbook (if you haven’t already).
- After “Add VBA”, you can examine a sample worksheet to see the functions.
- Those are the functions that get data from NEXT Analytics software, call them from Excel cells (examples follow).
Here are the functions, in text, followed by screen captures.
|GetValueGets a value at the designated row and column from the most recent query. The row and column numbers start at zero and go upwards.||GetValue(page_caption As String,row As Integer,
col As Integer) As Double
|GetChartColumnLabelByIndexGets the labels in the top row. The number goings left to right, starting from -1.||GetChartColumnLabelByIndex(page_caption As String,delim As String,
positionIndex As Integer)
|GetChartCombinedRowLabelsByIndexGets the label of each row. If there is more than one, because it is a nested query, it will concatenate them. You can provide the delimiter. Similarly to the previous function call, these row numbers are zero based.||GetChartCombinedRowLabelsByIndex(page_caption As String,delim As String,
positionIndex As Integer)
Each function takes parameters (in the second column of the table).
The first parameter is a page_caption and is used to identify which query result set you are referring to. This will be described in more detail in a following chapter. Right now, just assume that the first query is identified as “GoogleData.csv”.
The second and third parameter give you choices in what data is returned, and how it appears in Excel. The first value is the row number, the second parameter is the column number. The numbers are zero-based.
The following screen capture of Excel shows the functions as they would be if you typed them out.
Notice that the sample script contains cells that have this text already entered for you. You can cut/paste them and move them to your dashboard and, afterwards, delete the vba_sample worksheet from your workbook.
It’s important to note that after a Refresh Data, the data is sitting in Excel “memory”. You do not need to re-Refresh Data after a query has been run and it doesn’t need to be visible in a worksheet. You are referring to the objects in memory, not in a worksheet.
Cleaning up your workbook — you don’t need the data worksheets any more.
If you have done this, then you no longer need the NEXT Script to create temporary worksheets of data.
You can edit the worksheets that end in _Actions, and look for the instance of “SaveInWorksheet” and delete it, or put a “;” in front of the word. (hint: ; instructs NEXT to ignore the line).
If you’ve done that, then “SaveInWorksheet” does not generate the _data worksheet. You can most likely delete that worksheet, but be aware that another worksheet might have cell references to it which is probably something that’s unwanted after you’ve implemented the VBA function calling technique.
In the foregoing function calls, you’ll see a parameter named page_caption. The example used the value of “GoogleData.csv”.
Before explaining further, let’s clarify some background knowledge:
- Each time you run a query or perform an operation in NEXT script, an in-memory page gets created.
- As seen in the example, the ImportDataFiles assigns the name of the data file, GoogleData.csv, to be the page caption.
- Other commands, such as Sort or Combine or Filter also create “in-memory” pages which also increments the page counts and the names being assigned. Those pages, by default, assigned a default page caption such as Page 1, Page 2, Page 3, etc.
- There is a command called PageCaption which lets you assign a specific name. It takes the format:
- PageCaption,<any name>
- Where <any name> is anything you care to type except that it shouldn’t have a comma in it
- This caption is used in the first parameter of each of the three VBA functions, the ones which were explained earlier.
That being said, if you are working in a dashboard, it is unlikely that a useful Page Caption has been assigned. You will have to edit the NEXT script prior to referring to the page. Following are the steps:
This first parameter is the technique to identify which one of potentially many queries for which you want numbers.
The value “GoogleData.csv” is a default value. If you have multiple queries in a workbook, you will have to edit the _actions sheet manually to assign unique page captions. You can do this when you are suppressing the SaveInWorksheet commands.
On any _actions work sheet, just prior to the SaveInWorksheet Command, insert the following text:
You can substitute any text you want. In this case, as an example, I made it to be MY-PAGE-1.
This allows me to refer to that page in VBA. On any worksheet, you can paste the following three lines:
=GetValue( “MY-PAGE-1”, 0, 0)
=GetChartColumnLabelByIndex( “MY-PAGE-1”, “ “, -1 )
=GetChartCombinedRowLabelsByIndex( “MY-PAGE-1”, “ “, 0 )
When you refresh the data, the functions will do the following:
- GetValue will get the top left numeric cell value of the page which you named MY-PAGE-1.
- GetChartColumnLabelByIndex will get the top left column label of the page which you named MY-PAGE-1. Even if you have put multiple dimensions, it will concatenate them with the character you provided, in this case the “ “ character (a blank).
- GetChartCombinedRowLabelsByIndex will get the first row label of the page which you named MY-PAGE-1. Even if you have put multiple dimensions, it will concatenate the dimension values with the character you provided, in this case the “ “ character (a blank).
As a hint, you can use Excel functions as parameter values to make it easier to copy/paste the script, but it does potentially generate dependencies. In this example (below), it anchors from Column B, Row 2.
=GetValue( “MY-PAGE-1”,ROW()-2,COLUMN()-2 )
Dynamic Scripting – Add a specialized button that adds to the standard NEXT Script
perform the steps in “Add a Button…”
Assign proper page-captions, e.g.
You need to modify the VBA. As a hint, to get at the VBA programming environment, you can click Alt + F11.
Navigate to Module2.
Modify the line of text in the Button1_Click to be the following:
This example has two NEXT commands, separated by the word “_delim_”. You can construct this string using VBA looping, storing the commands in arrays. This option is not shown here because it would make the example cluttered and therefore harder to understand.
The first command identifies the page you want to work on, the second command tells NEXT to sort by the last numeric column descending. Here are the commands in a format that you can copy/paste into VBA:
Run_Nextanalytics_Scripts “”, “SelectPage,MY-PAGE-1_delim_Sort,Last,Descending”
Having done that, save your work and switch back to the view of Excel. When you click that button, it will sort that NEXT page by the trailing numeric column, in descending order. It would make sense to right-mouse click the button, and give it a name of: “Sort Descending”. You could repeat this operation, adding other NEXT functions in various combinations. If you did, it’s like building your own RibbonBar, on the worksheet, with custom business rules. You have full access to NEXT Scripting, which is quite powerful and can greatly simplify your worksheets. There are downloadable documents on our support page on the NEXT Scripting language.