Saturday, May 7, 2016

Smart View Custom Excel Formulas - Member Formula

Have you ever wanted to know what the member formula of a particular member is with ease? Quickly? On the fly? From Excel even? And without a Smart View grid refresh!?!?

Use the Smart View VB toolkit and "retrieve" the member formula of a particular member or members with an Excel Function!

Member Formula - Excel Formula:

Option Explicit
Declare Function HypOtlGetMemberInfo Lib "HsAddin.dll" (ByVal vtSheetName As Variant, ByVal vtDimensionName As Variant, ByVal vtMemberName As Variant, ByVal vtPredicate As Variant, ByRef vtMemberArray As Variant) As Long

Public Function GetMemberFormula(InputVal As Variant) As String

    Dim vtMemberName As Variant
    Dim vt As Variant
    Dim vtRet As Variant
    Dim cbItems As Variant
    Dim i As Variant
    vtMemberName = InputVal
    vtRet = HypOtlGetMemberInfo(Null, Null, vtMemberName, 2, vt)

Do Until vt(i) <> ""
    If IsArray(vt) Then cbItems = UBound(vt) + 1
        For i = 0 To UBound(vt)

        GetMemberFormula = (vt(i))

End Function