Error in EPM Add-in when double click to select filter with multiple choice

[Former Member]

Hi experts,

I'm facing an issue in a BPC 10.1 workbook, with a report that has a multiple selection filter. When we double click to change the filter, with multiple choice an vba error occurrs and the filter doesn't change.

This error appears because a VBA macro is triggered on double click, and it returns the error (file attached error.png).

Facts:

This was working fin in the Office 2010 version and early in version 2013 was working also. It changed it's behaviour since January this year.

It appears that some automatic update was made to office or EPM, and now it is behaving differently.

Versions:

• EPM 10.0 SP 27 Patch 2 .NET4
• MS Office Professional plus 2013

Attachments:

on-double-click-window-multiple-filter.png

macro-vba-error.png

Can you please help us with this matter?

Thanks in advance.

Kind regards,

Raquel Oliveira

[Former Member]

Thank you Vadim.

We are having trouble also in simulating this error because it only give us with a certain excel version.

Could you please share the Excel version that you have?

Thank you.

Raquel Oliveira

[former_member186338]

I am using Excel 2016 32-bit:

[Former Member]

Dear Vadim,

Thank you for answer. I have provided the full code as requested.

Regarding the questions you asked, please let me explain better

The goal of this VBA is to allow the user to select more than one member of the dimension, in the report filter.

We have implemented a logic that does the following:

1 - The user opens the report and double clicks the filter

2 - The VBA code is executed and opens a window where the user can select multiple dimension members

3 - After the selection, the VBA code writes the selection into excel cell $A$12

4 - We apply a "dimension members override" function to the report, to expand based on the selected members that are listed in cell $A$12

This allows the report to expand per multiple selection members.

The problem is that somewhere in time, this logic stopped working in our key-users computer, because after the members are selected and after click "ok", the macro gives the attached error.

Have I made myself clearer?

How should I attach the images?

Thank you 🙂

Raquel Oliveira

[former_member186338]

I was unable to reproduce the error... you have to perform more investigation... May be conflict with antivirus software or some other strange things!

"How should I attach the images?" Easy:

[Former Member]

    Option Explicit


    Dim epm As New FPMXLClient.EPMAddInAutomation


    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


    Dim strCellDoubleClickAdr As String


    Dim strCellMemberListAdr As String


    Dim wshCurrent As Worksheet


    Dim strConnName As String


    Dim strDimName As String


    Dim strDimMembers As String


    Dim strDimMembersArr() As String


    Dim strFinalDimMembersArr() As String


    Dim strFinalDimMembersWDescArr() As String


    Dim strDimMembersWDesc As String


    Dim lngMaxMemberNum As Long


    Dim lngTemp As Long


    strCellDoubleClickAdr = "$E$9"


    If Target.Address(True, True, xlA1, False) = strCellDoubleClickAdr Then


        Cancel = True


        Set wshCurrent = ThisWorkbook.ActiveSheet


        strCellMemberListAdr = "$A$12"


        strConnName = epm.GetActiveConnection(ActiveSheet)


        strDimName = "SOC_DIV_PARCEIRA"




        strDimMembers = epm.OpenMemberSelector(strConnName, strDimName, "")
 


        strDimMembers = Left(strDimMembers, Len(strDimMembers) - 1)


        strDimMembersArr = Split(strDimMembers, ";")


        lngMaxMemberNum = UBound(strDimMembersArr)


        ReDim strFinalDimMembersArr(0 To lngMaxMemberNum)


        ReDim strFinalDimMembersWDescArr(0 To lngMaxMemberNum)
      


        For lngTemp = 0 To lngMaxMemberNum


        strFinalDimMembersArr(lngTemp) = Mid(strDimMembersArr(lngTemp), InStrRev(strDimMembersArr(lngTemp), "[") + 1, Len(strDimMembersArr(lngTemp)) - InStrRev(strDimMembersArr(lngTemp), "[") - 1)


        strFinalDimMembersWDescArr(lngTemp) = strFinalDimMembersArr(lngTemp) & " - " & epm.GetMemberCaption(strConnName, strDimMembersArr(lngTemp))


        Next lngTemp


        strDimMembers = Join(strFinalDimMembersArr, ",")


        strDimMembersWDesc = Join(strFinalDimMembersWDescArr, ",")


        Target.Value = strDimMembersWDesc


        wshCurrent.Range(strCellMemberListAdr).Value = strDimMembers


    End If
   
    End Sub

[former_member186338]

And what???

Code is presented in unreadable form with empty lines between each line of code!

strCellMemberListAdr

is empty! The rest of the code looks OK.

Start with simple code and try to reproduce the issue...

[former_member186338]

Just to test the following code is working absolutely fine on my computer:

Option Explicit

Dim epm As New FPMXLClient.EPMAddInAutomation

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim strDimMembers As String
Dim strConn As String

If Target.Address(True, True, xlA1, False) = "$A$1" Then
    Cancel = True
    strConn = epm.GetActiveConnection(ActiveSheet)
    strDimMembers = epm.OpenMemberSelector(strConn, "CATEGORY", "")
    MsgBox strDimMembers
End If
    
End Sub

[former_member186338]

Sorry, but the issue is not clear!

1. Explain the required logic! "multiple selection filter" - ???? Provide step by step user actions and expected results!

2. Never attach images using "Insert File"

3. Post code using "Insert Code" - it's not possible to copy code from screenshot! Provide full code.