Page 1 of 1

TM1RECALC bug in an event

Posted: Fri Sep 23, 2011 11:42 am
by Wim Gielis
Hello all

We experience a strange bug after upgrading a customer from 9.4.1 to 9.5.2.

To reproduce the behavior: In a new workbook, in cell A1, we have a simple SUBNM formula:

=SUBNM("tm1server:FIN_Fisc_Entiteit";"";"FE051";"NL")

There is a Worksheet_Change event linked to that cell:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        Application.Run "TM1RECALC1"
    End If
End Sub
Upon changing cell A1 with the SUBNM double click functionality, Excel and Perspectives crash...

If I replace
Application.Run "TM1RECALC1"
with:
Me.Calculate

(hence the normal Excel recalculation), it works well.
The Excel version used is 2010, I have tested on Excel 2003 with TM1 client 9.5.2 and that seems to work as expected.
Anyone seen this before, can explain it or acknowledge it's a bug?

Thanks heaps,
Wim

Re: TM1RECALC bug in an event

Posted: Fri Sep 23, 2011 7:55 pm
by Alan Kirk
Wim Gielis wrote:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        Application.Run "TM1RECALC1"
    End If
End Sub
Upon changing cell A1 with the SUBNM double click functionality, Excel and Perspectives crash...

If I replace
Application.Run "TM1RECALC1"
with:
Me.Calculate

(hence the normal Excel recalculation), it works well.
The Excel version used is 2010, I have tested on Excel 2003 with TM1 client 9.5.2 and that seems to work as expected.
Anyone seen this before, can explain it or acknowledge it's a bug?
I installed 2010 yesterday. After navigating my way through the deletion of the .exd files so that VBA code would actually run (2010's equivalent of giving you a Glasgow Kiss :evil: ) I tried this and can confirm that it does appear to be a version-specific bug.

If the Application.Run "TM1RECALC1" line of code is run in a normal Sub procedure in the sheet's module, it's fine. If it's run as a Sub procedure in a standard module, it's fine. It's only when it's run in the Worksheet_Change event that it's a problem.

I then switched back to Excel 2007. (And people say I'm crazy for having every version of Office from 97 to 2010 on my notebook. This is why. ;) ) In 2007 the problem doesn't occur; the code runs normally.

Based on that I'd say that in 2010 MS introduced some change to the way that the Worksheet_Change event is handled, and it was a change that doesn't sit well with whatever 9.5.2 does when it runs TM1RECALC1.

Re: TM1RECALC bug in an event

Posted: Mon Sep 26, 2011 8:46 am
by Wim Gielis
Thanks Alan for confirmation.

Indeed, it does not need to bne a bug in TM1, Excel behavior can also have changed.
I know how to work around it, so let's just be warned and use the workaround.

Wim

Re: TM1RECALC bug in an event

Posted: Tue Sep 27, 2011 9:34 pm
by lotsaram
Wim do you have this logged as a bug? What's the PMR number?

Re: TM1RECALC bug in an event

Posted: Tue Jan 10, 2012 3:37 pm
by Ben Stremme
Same issue in my company.

Updating Office 2010 to SP1 and TM1 9.5.2 to FP1 doesn't help either.
Going back to previous Excel or TM1 versions can't be the only way out.

I've just sent a new bug report to our TM1 service provider...

Re: TM1RECALC bug in an event

Posted: Tue Jan 10, 2012 9:56 pm
by Gregor Koch
PM33830

Re: TM1RECALC bug in an event

Posted: Wed Jan 11, 2012 8:17 am
by lotsaram
Gregor Koch wrote:PM33830
Thanks Gregor. Hope this one is fixed soon. It remains the one real irritant in our 9.5.2 FP1 upgrade.

Quick clarification. This also applies to running the TM1Refresh macro for which there is no workaround like Wim's suggestion with the only option at this point to disable automatic event driven rebuilding of active forms.

A slight change to Wim's code selection also.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    With Application
    If Not Intersect(Range("rngRecalc"), Target) Is Nothing Then
        If .Version >= 14 Then
            Me.Calculate
        Else
            .Run "TM1RECALC1"
        End If
    End If
    End With
End Sub

Re: TM1RECALC bug in an event

Posted: Wed Jan 11, 2012 12:26 pm
by Wim Gielis
Oops, I completely forgot posting the bug.
Thanks for doing this instead.

Re: TM1RECALC bug in an event

Posted: Wed May 23, 2012 9:01 am
by Ben Stremme
IBM recently solved this bug. It is part of the hotfix "IBM TM1 9.5.2 FP2 HF2"
PM56397 - TM1 Perspectives Office 2010 – Using TM1 Recalc1 causes Excel Crash.
Now TM1RECALC/TM1RECALC1 in a Worksheet_Change Sub should work again.

Re: TM1RECALC bug in an event

Posted: Wed May 23, 2012 10:54 am
by lotsaram
Ben Stremme wrote:IBM recently solved this bug. It is part of the hotfix "IBM TM1 9.5.2 FP2 HF2"
PM56397 - TM1 Perspectives Office 2010 – Using TM1 Recalc1 causes Excel Crash.
Now TM1RECALC/TM1RECALC1 in a Worksheet_Change Sub should work again.
Woo hooo!
Did they also fix PM33830 - screen flickering?

Re: TM1RECALC bug in an event

Posted: Thu May 24, 2012 3:38 am
by Gregor Koch
Not sure about PM33830, but Windings finally became the default font on the action buttons. Who wants to read what a button does any ways?