Exploring the Analysis functions – Part 3 – Hiding Analysis functions
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:
Application.ExecuteExcel4Macro(“SET.NAME(“”SAPGetInfoLabel””,Null)”)
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
Sub Initialize()
‘Run this procedure on workbook open
‘Remember the SAPGetInfoLabel Register ID
dblSAPGetInfoLabel = Application.ExecuteExcel4Macro(“SAPGetInfoLabel”)
‘Now remove the name referring to the Register ID
Application.ExecuteExcel4Macro(“SET.NAME(“”SAPGetInfoLabel””,Null)”)
‘Users will no longer be able to call SAPGetInfoLabel directly
End Sub
Sub Test()
‘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”)
End Sub
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…