*RECALC_ Error in dynamic Form

[Martin]

Hi everyone,

I'm currently working on TM1 input sheets that use a large array of DBRW formulas for data entry and drop-down-boxes to select dimension elements for filtering:

Code: Select all

$A$1="AAAA"
$A$2=IF($A$1="ABC";"YYYY";"ZZZZ")
$A$3=VIEW("server:cube";"!";"!";$A$1;$A$2)

$A20="XXXX"
B$19=YEAR(TODAY())-1
B20=DBRW($A$3;$A20;B$19;$A$1,$A$2)

Where AAAA is the name of a dimension element selected through the drop-down-box and XXXX, YYYY, ZZZZ are names of fixed/constant dimension elements.

My event handler looks (simplified) like this:

Code: Select all

Public Sub cmbInput_change()
    wsWorksheet.Range("$C$1").Value = sNewValue
    wsWorksheet.Calculate
    Application.run "TM1RECALC1"
End Sub

Although I would expect this to work, when I select a value in the drop-down-box and the above event handler gets executed, my DBRW cells are filled with "*RECALC_9999" messages (with differing numbers) instead of values from the cube.

Can anyone give me a hint where I might look for the problem or possibly even a solution?

EDIT:
Software Versions:

TM1 9.5.2 Client 32bit
Excel 2010 32bit
Windows 7 64bit

Kind regards
Martin

[lotsaram]

You should be aware that due to some (unknown) change under the hood in Excel 2010 (14) in how the sheet change event is handled calling TM1RECALC or TM1REFRESH from that event will crash Excel. However by calling from a form or activeX control change event I think you should be OK.

Note the wsWorksheet.Calculate is redundant as the TM1RECALC1 macro will do this for you. Try without the calculate call.

[Martin]

Thanks for that pointer on event handling, I already noticed this problem but as you mentioned I am calling TM1RECALC from a control change event, which works fine.

As for omitting wsWorksheet.Calculate, I already tried that and it doesn't help.

The "*RECALC_..."-message seems to somehow be related to timing, since I'm not always seeing it and not in all cells of the grid, but it seemed to happen more often when I left out the "Calculate" call before.