In parts 1 and 2, I covered a lot of technical background about how Excel exposes Analysis functions to Worksheet formulas and to VBA. In this article, I’ll explore a basic application of this knowledge, by hiding one of the Analysis functions from users. You might want to prevent your users, for example, from being able to run the SAPGetInfoLabel function.
Hiding a function
We can easily hide a function by removing the hidden name:
But that might be a bit extreme… We’ve removed the name, but Excel has cached the function (although it will resolve to #NAME? if it is calculated), so it will appear to the user that the function is available, but it won’t calculate. Furthermore, we’ve removed our ability, as the developer, to call the function ourselves from within VBA. Had we stored the Register ID before we removed the name, we’d at least be able to call the function from within VBA, as follows….
‘Declare the variable as Global, so its value persists…
Dim dblSAPGetInfoLabel as Double
‘Run this procedure on workbook open
‘Remember the SAPGetInfoLabel Register ID
dblSAPGetInfoLabel = Application.ExecuteExcel4Macro(“SAPGetInfoLabel”)
‘Now remove the name referring to the Register ID
‘Users will no longer be able to call SAPGetInfoLabel directly
‘This procedure is still able to call SAPGetInfoLabel because we still know its Register ID
Dim vResult as Variant
‘Call the SAPGetInfoLabel function by its Register ID
vResult = Application.Run(dblSAPGetInfoLabel, “LastRefreshedAt”)
In Part 4, I’ll take this one step further, and hook a function so that we can have more granular control over Analysis function calls…