Exploring the Analysis functions - Part 4 - Enhancing Analysis functions

[Former Member]

In Part 3, I demonstrated how we can hide/deactivate specific Analysis functions, but what if we want to allow users to only use certain parameters for a function, or we want to perform additional steps before/after the function is called? We can achieve this by enhancing and extending our code, from Part 3, that hides the Analysis function. We'll be hooking (technically speaking, hooking a function is achieved rather differently) or hi-jacking the SAPGetCellInfo function.

You'll recall from Part 3 that we were able to hide the Analysis function "SAPGetInfoLabel", but we were still able to call that function from VBA by using the underlying function's Register ID. We can take this approach one step further by adding our own public function that will be usable by both VBA and by Excel formulas, but we'll prevent users from being able to provide specific parameter values to the function.

To continue with our example of the SAPGetInfoLabel function. Let's say we want to prevent users from calling this function with a parameter of "LogonUser", but we want to allow all other parameter values. Let's also output the parameter value, and the function result to the debug window every time the function is called.

'-----------------------------------------------

'The following is unchanged from Part 3...

'-----------------------------------------------

'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

'-----------------------------------------------

'The following is new code...

'-----------------------------------------------

'Create a public function that is callable from Excel formulas

'Make sure it has the same name as the SAP function that we have hidden

Public Function SAPGetInfoLabel(PropertyName As String) As Variant

   'Make this function Volatile, as the Excel calculation engine won't know

  'if SAP values have changed...

  Application.Volatile

  'Output the PropertyName value to the debug window, BEFORE we run any functions

  Debug.Print PropertyName

  If StrComp(PropertyName, LogonUser, vbTextCompare) = 0 Then

     SAPGetInfoLabel = "LogonUser - Access Denied"

  Else

     SAPGetInfoLabel = Application.Run(dblSAPGetInfoLabel, PropertyName)

  End If

  'Output the result to the debug window, AFTER we've run any functions

  Debug.Print PropertyName

End Function

Hopefully you can see the opportunities for hiding/exposing functions, validating parameters, executing pre and post function-call code and/or implementing custom logging.

You should be able to apply similar approaches to all of the Analysis functions, but I'd suggest keeping the Analysis functions that aren't already exposed to Worksheet formulas as privately declared functions, as I suspect you'll experience strange behaviour if you make them public.