TM1RECALC bug in an event

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

TM1RECALC bug in an event

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

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: TM1RECALC bug in an event

Post 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.
"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: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1RECALC bug in an event

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

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1RECALC bug in an event

Post by lotsaram »

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 »

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 »

PM33830
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1RECALC bug in an event

Post 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
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1RECALC bug in an event

Post by Wim Gielis »

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

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
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 »

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: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1RECALC bug in an event

Post 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?
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 »

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