Determine Analysis Version using VBA - Part 2

[Former Member]

In my last article, I explained how we can get the version of Analysis by using the Scripting.FileSystemObject, and provided a VBA function for returning the Analysis version. However, the version returned, for Analysis 1.4 SP4_1 is "1.4.4.2737". That's an accurate version, but it's obviously got too many decimal points to be a valid number, so it's very hard for us to use it when comparing for minimum versions etc.

The following VBA enhances the original function by adding an argument to the function that lets you specify which version you'd like, and it uses VBA's enumerations to force certain argument values, intellisense.

So, using "1.4.4.2737" as an example, calling the function with various arguments results in more precise versions. They're still returned as strings (in order to preserve leading/trailing zeros) but the Major and Minor version are more readily converted to decimal numbers, using VBA's CDbl.

Immediate Window Results:

?GetAnalysisVersion()

1.4.4.2737

?GetAnalysisVersion(FullVersion)

1.4.4.2737

?GetAnalysisVersion(MajorVersion)

1.4

?GetAnalysisVersion(MinorVersion)

4.2737

?CDbl(GetAnalysisVersion(MajorVersion)) >= 1.4

True

VBA Code:

Public Enum eVersion

  FullVersion = 0

  MajorVersion = 1

  MinorVersion = 2

End Enum

Public Function GetAnalysisVersion(Optional VersionType As eVersion = eVersion.FullVersion) As String

  'Assumes the Analysis Addin is loaded in Excel

  'If Analysis functions are not registered, returns empty string

  'Assumes version is in format M.M.m.m or M.m (M = Major, m = minor)

  'Returns versions as strings in order to preserve leading/trailing zeros

  'For example 1.4.04.120 has Minor version "04.120", not 4.12

  Const BI_DLL As String = "BiXLLFunctions.dll"

  Dim fso As Object 'Scripting.FileSystemObject

  Dim aFuncs As Variant

  Dim iCounter As Integer

  Dim bytFirstPeriod As Byte

  Dim bytMajorMinorPeriod As Byte

  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

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

      bytFirstPeriod = InStr(1, sVersion, ".")

      bytMajorMinorPeriod = InStr(bytFirstPeriod + 1, sVersion, ".")

      If bytMajorMinorPeriod = 0 Then

       'sVersion Format is M.m

        bytMajorMinorPeriod = bytFirstPeriod

      End If

      Select Case VersionType

        Case Is = eVersion.FullVersion

          GetAnalysisVersion = sVersion

        Case Is = eVersion.MajorVersion

          GetAnalysisVersion = Mid(sVersion, 1, bytMajorMinorPeriod - 1)

        Case Is = eVersion.MinorVersion

          GetAnalysisVersion = Mid(sVersion, bytMajorMinorPeriod + 1, Len(sVersion))

      End Select

      Exit Function

    End If

  Next iCounter

End Function