Determine Analysis Version using VBA – Part 1

Analysis versions are changing frequently, and each new version brings new fixes and features. If you’re using/relying on certain functionality in your workbooks, you might need to check the Analysis version before processing.

The VBA function below will determine the version, as a string. It is callable from within VBA, and from Excel formulas.

Public Function GetAnalysisVersion() As String

  ‘Assumes the Analysis Addin is loaded in Excel

  ‘Otherwise returns an empty string

  Const BI_DLL As String = “BiXLLFunctions.dll”

  Dim fso As Object ‘Scripting.FileSystemObject

  Dim aFuncs As Variant

  Dim iCounter As Integer

  Set fso = CreateObject(“Scripting.FileSystemObject”)

  aFuncs = Application.RegisteredFunctions

  ‘Loop through the registered functions

  For iCounter = LBound(aFuncs) To UBound(aFuncs)

    ‘Check if the function is an Analysis function

    If InStr(1, aFuncs(iCounter, 1), BI_DLL, vbTextCompare) > 0 Then

      GetAnalysisVersion = fso.GetFileVersion(aFuncs(iCounter, 1))

      Exit Function

    End If

  Next iCounter

End Function