TM1 Forum

Hi,

I've been having some issues with the TM1RECALC1 macro function. I would like to see a sheet's data being recalculated. An easy option would be to turn on automatic calculation, but I was wondering whether it was possible to force the recalculations through VBA.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count < 1 Or IsEmpty(Target) Then Exit Sub

Application.Run ("TM1RECALC1")

End Sub

I would have expected the active sheet to be recalculated, but it appears that it does not do so for the consolidated values. If, however, I check the data in the cube itself, I can see that it has updated them as well, they are simply not being displayed on the Excel sheet.

Is this normal behaviour or has anyone ever come across this issue?

Thanks in advance.

Edit: I am using TM1 9.4.2 and Excel 2003.

AFAIK you can't selectively recalc a range with the TM1RECALC1 function. Try taking out your IF condition (do you really need it???) and see if that works.

Hi Tomok, the IF structure is not necessary indeed, but it is not the root cause of the issue I am having. Both with and without the structure, the TM1RECALC1 function doesn't update the consolidated values on the sheet.

So, what you are telling us is that your sheet is filled with DBRW formulas and when you call the TM1RECALC1 function via VBA, the leaf level elements from TM1 are being updated with the current values but any item that represents a consolidated node in TM1 is coming back with an old value? Coming back with 0? What do you mean by "update"?

Is it an active form? Because if it is, TM1RECALC1 won't work.

I did another check and it turns out what I said initially is not entirely correct. Long story short, what I wanted to achieve with this setup is that if somewhere on the sheet a cell (not necessarily one containing TM1 data, just any cell) is updated, the VBA code is triggered because of the WorkSheet_Change event. That VBA code simply consists of the Application.Run("TM1RECALC1") code, implying that normally all the active forms with the DBRW formulas on that active sheet should be updated with the new values that could either come from for example:

Values I updated straight on the cube
Say a volume change at leaf level from 40 pounds to 50 pounds, done straight on the cube. If I then alter a volume elsewhere, but this time on the form, I would expected to see both values and their respective consolidated values updated on the form.

Values I updated on the form itself
Say I perform a volume change of 40 to 50 pounds at leaf level again, on the form, it will trigger the VBA code. I would expect the form to update itself and see the consolidated value go up by 10 pounds. Instead, I see my 50 pounds at leaf level and nothing else. The value I entered on the form and its respective consolidated value is however updated in the TM1 cube, as expected.

At this point though, without doing a recalc (F9) or rebuild, it will not show on the form.

So to get to the point, the TM1RECALC1 doesn't seem to work at all, it doesn't update both new values entered as well as their respective consolidated values.

Sorry about the confusion, though I'm not sure it makes any sense to you now, but I sure hope so.

Paul, it is indeed an active form. Why exactly does it not work?

Nick_Blommaerts wrote:Paul, it is indeed an active form. Why exactly does it not work?

Because the TM1RECALC1 function does not rebuild an active form, it only recalculates the existing formulas that exist on the sheet when you call it. Unfortunately, there is no TM1 Macro function for rebuilding active forms so you are not going to be able to do what you want in an onChange event. The only option I know of is to add a TM1 Action button that rebuilds the sheet when clicked.

That's what I usually do indeed, because at the end of the day it does the trick. This was more of a test to see if I could achieve it without having to click the button or changing the calculation method from Excel, but as it turns out it is not feasible in this way.

Thanks for the great answers to the both of you, it is well appreciated.

tomok wrote:Unfortunately, there is no TM1 Macro function for rebuilding active forms

There is a macro function for that - TM1REFRESH.

qml wrote:

tomok wrote:Unfortunately, there is no TM1 Macro function for rebuilding active forms

There is a macro function for that - TM1REFRESH.

Added in 10.1? It is not in the 9.5.2 documentation or earlier.

Documentation? Who said anything about documentation?

The function itself has been available since the introduction of Active Forms in 9.4. It hasn't found its way to the documentation, but that shouldn't come as a surprise to you, tomok.

qml wrote:

tomok wrote:Unfortunately, there is no TM1 Macro function for rebuilding active forms

There is a macro function for that - TM1REFRESH.

I have actually tried using that earlier today, the problem I have with that function is that it will just refresh the active form as soon as I alter a value, leaving the old value on the form as well as in the cube. It appears to do nothing at all.

I think there has been some confusion in this thread. So, let's start from the beginning.

You don't need to use TM1REFRESH. You don't want to rebuild your Active Form as its structure is not changing. You are correct in calling TMRECALC1 to retrieve the latest data from the server. The issue that I believe you're running into is that by capturing the Worksheet_Change event you force the recalculation to happen before the DBR(W) formula has had the chance to send the number to the server. So you're basically cutting in line - refreshing first, then updating the value. No wonder your consolidations are not showing you what you would expect. I think you will need to come up with a different approach, maybe try a different workbook event.

I can't test any of the above at the moment, so treat the above theory as somewhat speculative for now. I will try and offer some solution tomorrow unless someone else can propose something before then.