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…