Exploring the Analysis functions - Part 2

[Former Member]

In Part 1, I discussed how we can find the functions that are registered by Analysis, and how we can determine their return types and argument types. This article goes further and exposes how those functions are made available to Excel and to VBA.

We saw in the last article that an Analysis function such as SAPGetInfoLabel is actually registered as "SAPGetInfoLabel_v12", so how does Excel expose the function to the Worksheet and to VBA?

Remember, with an Analysis Workbook open and connected, we can call SAPGetInfoLabel in 1 of 2 ways:

Method 1 - From the worksheet, by entering a formula:

=SAPGetInfoLabel("LastRefreshedAt")

Method 2 - From VBA, by calling Application.Run

Dim vResult as Variant

vResult = Application.Run("SAPGetInfoLabel", "LastRefreshedAt")

So, how does Excel know that SAPGetInfoLabel should actually call the function registered as SAPGetInfoLabel_v12? The answer lies in a little known feature of Excel's Application.Run method....

The Application.Run method takes up to 31 Parameters, the first parameter defines the macro or function that should be run, and the optional 2nd through 31st parameters define between 1 and 30 arguments to the macro or function. When you're calling a VBA macro, you provide the name of the macro as a string, but when you're calling a function from a DLL or XLL (as we are with Analysis functions), you provide the "Register ID".

So if we're calling a registered DLL function, how are we able to call it with the function name, as a string, and not be forced to provide the Register ID? Herein lies the little understood aspect of registered functions.... The exposed/friendly function name is defined as a hidden Name in the Application, so when you call the function by its exposed name, Excel is resolving that name to the Register ID of the registered function. You can find out more about hidden Application level names here, but in short, we need to use the Application.ExecuteExcel4Macro Method to resolve a friendly function name to the registered function name.....

To continue our example of SAPGetInfoLabel.... When the Analysis Add-in is loaded, it registers the function "SAPGetInfoLabel_v12" and a "Register ID" is allocated for that function for the life of the Excel session, or until Analysis is unloaded (the Register ID will likely be different every time you load Analysis). Furthermore, a name is added to the Application's hidden names collection (not the Application.Names collection) that associates friendly function name "SAPGetInfoLabel" with the Register ID.

We can find the Register ID from the hidden names collection, but only by calling the Application.ExecuteExcel4Macro method with the friendly function name as the only argument, as follows:

Dim dblRegisterID as Double

dblRegisterID = Application.ExecuteExcel4Macro("SAPGetInfoLabel")

'dblRegisterID = 841089055 in my current Excel/Analysis session

So, now you know more than you probably need to about how Excel lets you call Analysis functions. In part 3, we'll return to a more practical application, and see how we can use this knowledge to hide or hook/enhance the Analysis functions...