How to suppress .NET error message triggered by EPM add-in

Question

I wrote a basic VBA script to simulate user menu functionality in excel. It interacts with the EPM add-in of SAP BPC and am facing an issue were it throws a .NET error "InvalidArgument=Value of '0' is not valid for 'index'". (more details of error below)

The objective of the code is that upon a selection change of the entity dimension in the EPM context menu, the corresponding menu (excel tab of an excel spreadsheet) will be selected (and the others hidden). Practically the entity dimension has a property HotelType with 3 possible types Lease, Manag & Admin. The EPM formula EPMMemberProperty() will retrieve this type for each tab separately and in VBA this will be the criteria to select the corresponding menu tab.

Considering the arranged marriage between Ms Office and SAP EPM has reached an all time depth with office 365 and SAP BPC 10.0 I tried to use VBA only but no avail. I tried Worksheet_Change(ByVal Target As Range) and Sub Worksheet_Calculate() but an EPM context change does not trigger these.

So I went for the EPM Function AFTER_CONTEXTCHANGE() which executes correctly apart from the fact it throws this . NET error on some occasions, when pressing continue the code continues and finishes correctly, however i'd like users not to be faced with this.

Having limited programming skills altogether, I am guessing this an error of not having (or losing) a selection of the list reprenting the entity selection in the EPM context menu. And from the below error code i can see it this selection is actually a combobox but since it is part of the EPM add-in I wouldn't know how to refer/control to it from VBA.

The ugly but the very tempting On error resume next doesn't work.

Likely there is a beginner's mistake in my code, so all help is appreciated.

Kind regards,

Wim

.NET error "InvalidArgument=Value of '0' is not valid for 'index'"

************** Exception Text **************
System.ArgumentOutOfRangeException: InvalidArgument=Value of '0' is not valid for 'index'.
Parameter name: index
   at System.Windows.Forms.ComboBox.ObjectCollection.get_Item(Int32 index)
   at FPMXLClient.UILayer.Controls.CurrentViewComboBox.DrawComboBox(DrawItemEventArgs e, Boolean rezisable, Boolean inverseText) in d:\Olympus_100_REL_XLCLIENT\src\FPMXLClient\src\UILayer\UI\Controls\CurrentView\CurrentViewComboBox.cs:line 570
   at FPMXLClient.UILayer.Controls.CurrentViewComboBox.OnDrawItem(DrawItemEventArgs e) in d:\Olympus_100_REL_XLCLIENT\src\FPMXLClient\src\UILayer\UI\Controls\CurrentView\CurrentViewComboBox.cs:line 561
   at System.Windows.Forms.ComboBox.WmReflectDrawItem(Message& m)
   at System.Windows.Forms.ComboBox.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

VBA Context change code

Function AFTER_CONTEXTCHANGE()
Dim ShName As String
     ShName = ActiveSheet.Name
     Select Case ShName
        Case "Fin_L"
            Call ContextChange_FinL
        Case "Fin_M"
            Call ContextChange_FinM
        Case "Fin_A"
            Call ContextChange_FinA
        End Select
End Function

Sub ContextChange_FinL()
Dim HotelType As String
HotelType = Range("HotelType_FinL")
Select Case HotelType
Case "LEASE"
        Sheets("Fin_L").Visible = True
        Sheets("Fin_M").Visible = xlVeryHidden
        Sheets("Fin_A").Visible = xlVeryHidden
        Sheets("Fin_L").Select
    Case "MANAG"
        Sheets("Fin_M").Visible = True
        Sheets("Fin_L").Visible = xlVeryHidden
        Sheets("Fin_A").Visible = xlVeryHidden
        Sheets("Fin_M").Select
    Case "ADMIN"
        Sheets("Fin_A").Visible = True
        Sheets("Fin_L").Visible = xlVeryHidden
        Sheets("Fin_M").Visible = xlVeryHidden
        Sheets("Fin_A").Select
End Select
End Sub

Sub ContextChange_FinM()
Dim HotelType As String
HotelType = Range("HotelType_FinM")

    Select Case HotelType
    Case "LEASE"
        Sheets("Fin_L").Visible = True
        Sheets("Fin_M").Visible = xlVeryHidden
        Sheets("Fin_A").Visible = xlVeryHidden
        Sheets("Fin_L").Select
    Case "MANAG"
        Sheets("Fin_M").Visible = True
        Sheets("Fin_L").Visible = xlVeryHidden
        Sheets("Fin_A").Visible = xlVeryHidden
        Sheets("Fin_M").Select
    Case "ADMIN"
        Sheets("Fin_A").Visible = True
        Sheets("Fin_L").Visible = xlVeryHidden
        Sheets("Fin_M").Visible = xlVeryHidden
        Sheets("Fin_A").Select
    End Select
End Sub

Sub ContextChange_FinA()
Dim HotelType As String
HotelType = Range("HotelType_FinA")

    Select Case HotelType
    Case "LEASE"
        Sheets("Fin_L").Visible = True
        Sheets("Fin_M").Visible = xlVeryHidden
        Sheets("Fin_A").Visible = xlVeryHidden
        Sheets("Fin_L").Select
    Case "MANAG"
        Sheets("Fin_M").Visible = True
        Sheets("Fin_L").Visible = xlVeryHidden
        Sheets("Fin_A").Visible = xlVeryHidden
        Sheets("Fin_M").Select
        Case "ADMIN"
        Sheets("Fin_A").Visible = True
        Sheets("Fin_L").Visible = xlVeryHidden
        Sheets("Fin_M").Visible = xlVeryHidden
        Sheets("Fin_A").Select
End Select
End Sub

Answer 1

The issue is not consistent but seems to be triggered by the step of hiding sheets. So moving the hide of the source sheet (when different from target) only after selecting the target sheet prevents this.

Example:

    Sub ContextChange_FinM()
     HotelType = Range("HotelType_FinM")

        Select Case HotelType
    Case "LEASE"
            If Sheets("Fin_L").Visible = xlVeryHidden Then Sheets("Fin_L").Visible = True Else 'do nothing
            Sheets("Fin_L").Select
            Sheets("Fin_M").Visible = xlVeryHidden

    Case "MANAG"
        'do nothing
 Case "ADMIN"
        If Sheets("Fin_A").Visible = xlVeryHidden Then Sheets("Fin_A").Visible = True Else 'do nothing
        Sheets("Fin_A").Select
        Sheets("Fin_M").Visible = xlVeryHidden

    End Select
End Sub