Improving Analysis function calls in VBA – Part 1

Most of the Analysis functions are exposed to Worksheet formulas, and have their parameters exposed to the Excel function wizard, which is useful when you’re using Analysis functions in worksheet formulas, but you don’ get the same parameter prompting when you’re working in VBA. Furthermore, some of the Analysis functions aren’t available as Worksheet functions, but they also don’t expose parameter prompting in VBA.

I’ve previously written about the dangers of typos in Parameters when calling some Analysis functions, so this series of articles is designed to make calling Analysis functions more robust. I appreciate that many developers will have varying degrees of knowledge and experience with both Analysis and with VBA, so I’ll be providing a number of approaches, starting with the simplest.

I’ll be using SAPGetProperty as an example. SAPGetProperty is one of the Analysis functions that is NOT available to Excel formulas, but IS available to VBA.

We can call SAPGetProperty with a variety of parameters, but we’ll be focusing, at first, on a call that checks if the workbook contains changed planning data. The simplest way of calling the function is to run this VBA:

Dim vResult As Variant

vResult = Application.Run(“SAPGetProperty”, “HasChangedPlanData”)

Assuming the Workbook is an Analysis workbook, and the workbook is connected, the code above will return either True or False.

So far, so good, but what if we need to call this function multiple times, in multiple places within our code? We’d need to remember the syntax and spelling of the parameters, every time we wrote the function call. With so many functions and parameters, that’s a great deal of syntax and spelling to remember. Surely there’s an easier way?

VBA can help solve this problem. We’ll create a custom function that wraps the call to Application.Run(“SAPGetProperty”, “HasChangedPlanData”), and then we’ll have a single instance of the Analysis function that can be used by any other part of our code. And we won’ have to remember any parameters or syntax.

In order to keep our code organised, I suggest that you create a module for these Analysis-wrapping functions. I’ve called my module “mSAP”.

In module mSAP, add the following VBA:

Public Function HasChangedPlanData() as Boolean

  HasChangedPlanData = Application.Run(“SAPGetProperty”, “HasChangedPlanData”)

End Function

In another module, say Module 1, we can now include a call to our custom function without needing to remember the syntax or spelling of parameters.

In module Module 1, add the following VBA:

Private Sub Test()

  ‘Do Plan altering activity here

  ‘….

  ‘Check if we changed the Plan Data

  If mSAP.HasChangedPlanData Then

    ‘Alert the user that the Plan Data has changed

    ‘….

  End If

End Sub

If you typed in the code, rather than copying it, you’ll have notice that as you typed “mSAP.”, the Visual Basic Editor (VBE) showed “IntelliSense” or a dropdown-list of procedures that were available, immediately after you typed the period after “mSAP”, as per the image below.

Now you’ve got one centralised location where Application.Run(“SAPGetProperty”, “HasChangedPlanData”) gets called, so you only have to remember the syntax once, and every time you need to call the function, you can just type mSAP followed by a period, and you’ll be prompted by the VBE, to choose the relevant function, and without the need for further parameters. Furthermore, the return value will be strongly typed as a Boolean data type.

Stay tuned for more articles that will cover more advanced techniques for wrapping Analysis function calls.