Problems using EPM functions via VBA

[former_member414993]

Hello, experts!

I am trying to call EPM function EPMSaveComment in VBA using Application.Run Method. This code launchs on event, when user tries to change text in textbox. It works not stable, very often it returns value "#RFR", but sometimes it works ok and returns correct comment. I need to read this comment, not to save, but functions EPMCommentPartialContext or EPMCommentFullContext do not work at all returning error "#Error - unexpected error. Check parameters" so i was forced to try EPMSavecomment instead. Code example above.

My software versions:

BPC 10.0 NW

EPM SP22 NET3.5 9188

Also tried on EPM SP34 Patch 2. NET 4.7.2 x86 10361 - facing the same problem.

[former_member186338]

Sorry, but if you want to post code - there is a special "CODE" button...

By the way, what is the idea behind reading comment in VBA?

[wf]

Ivanov,

Not very clear yet what you are doing, but the #RFR is a rather common issue working with EPM retrieve/send formulas. I am not sure there is a logic to it but basically it seems to happens when the excel sheet is being calculated without refreshing the EPM report.

The workaround for me is to add with your Code "RefreshSelectedCells" targeting the specific range with your EPM formulas to ensure they are refreshed at the right moment.

Below a basic example using the After refresh where it is used to refresh a few columns having EPMretrieve formulas:

Function AFTER_REFRESH()
Dim EPM As Object

Dim RngReport As Range
Dim ws As Worksheet
Dim EndReport As String

'Define EPM report range
Worksheets("INPUT").Activate
    Set ws = ActiveSheet
    EndReport = EPM.GetDataBottomRightCell(ws, "000")
Set RngReport = Range(Range("Start_Pos").Offset(1, 0), EndReport)

'Refresh columns 5 to 10
Range(RngReport.Columns(5), RngReport.Columns(10)).Select
EPM.RefreshSelectedCells

Range("Start_Pos").Select

End Function

[former_member186338]

Strange logic to my mind!

RefreshSelectedCells

in AFTER_REFRESH event function...

Double refresh as a result.

And also AFTER_REFRESH will be triggered after any refresh of any report ...

[wf]

I agree, but here the point is just to show how to get rid of the #RFR. The wider context of this example is that the after refresh is used to overcome a loss of functionality that came with the introduction of EPM reports. The "logical" solution would be to use local members but I prefer a negligible loss in performance with this extra refresh over a considerable change effort as editing reports having a certain amount of local members is an incredible tedious affair.

[former_member186338]

Still unable to completely understand the logic...

EPMSaveComment will show #RFR when:

1. list of dimension members in EPMSaveComment parameters was changed

2. Cell containing EPMSaveComment was not refreshed (using Refresh Worksheet/Workbook or RefreshSelectedCells or if EPMSaveComment is in local member - Refresh report)

Also results of EPMSaveComment are cached duiring single session.