TM1RECALC bug in an event

Post bug reports and the status of reported bugs
Post Reply
Wim Gielis
MVP
Posts: 1480
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

TM1RECALC bug in an event

Post by Wim Gielis » Fri Sep 23, 2011 11:42 am

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
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

User avatar
Alan Kirk
Site Admin
Posts: 5673
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: TM1RECALC bug in an event

Post by Alan Kirk » Fri Sep 23, 2011 7:55 pm

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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

Wim Gielis
MVP
Posts: 1480
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: TM1RECALC bug in an event

Post by Wim Gielis » Mon Sep 26, 2011 8:46 am

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
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

lotsaram
MVP
Posts: 2973
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: TM1RECALC bug in an event

Post by lotsaram » Tue Sep 27, 2011 9:34 pm

Wim do you have this logged as a bug? What's the PMR number?

Ben Stremme
Posts: 5
Joined: Tue Mar 02, 2010 7:33 pm
OLAP Product: TM1
Version: 9.5.2 FP1
Excel Version: 2003 + 2010

Re: TM1RECALC bug in an event

Post by Ben Stremme » Tue Jan 10, 2012 3:37 pm

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...

Gregor Koch
MVP
Posts: 263
Joined: Fri Jun 27, 2008 12:15 am
OLAP Product: Cognos TM1, CX
Version: 9.0 and up
Excel Version: 2007 and up

Re: TM1RECALC bug in an event

Post by Gregor Koch » Tue Jan 10, 2012 9:56 pm

PM33830

lotsaram
MVP
Posts: 2973
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: TM1RECALC bug in an event

Post by lotsaram » Wed Jan 11, 2012 8:17 am

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

Wim Gielis
MVP
Posts: 1480
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: TM1RECALC bug in an event

Post by Wim Gielis » Wed Jan 11, 2012 12:26 pm

Oops, I completely forgot posting the bug.
Thanks for doing this instead.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

Ben Stremme
Posts: 5
Joined: Tue Mar 02, 2010 7:33 pm
OLAP Product: TM1
Version: 9.5.2 FP1
Excel Version: 2003 + 2010

Re: TM1RECALC bug in an event

Post by Ben Stremme » Wed May 23, 2012 9:01 am

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.

lotsaram
MVP
Posts: 2973
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: TM1RECALC bug in an event

Post by lotsaram » Wed May 23, 2012 10:54 am

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?

Gregor Koch
MVP
Posts: 263
Joined: Fri Jun 27, 2008 12:15 am
OLAP Product: Cognos TM1, CX
Version: 9.0 and up
Excel Version: 2007 and up

Re: TM1RECALC bug in an event

Post by Gregor Koch » Thu May 24, 2012 3:38 am

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?

Post Reply