PM33830 Macro function TM1RECALC1: ScreenUpdating handling

[Ben Stremme]

Problem description

We are about to update from TM1 9.1.2 to 9.5.2.
While testing our own xls-reports (with macros, Excel 2003) we see unusual screen flickering while macro execution.
Everytime

Code: Select all

Application.Run("TM1RECALC1")

is used the property "Application.ScreenUpdating" seems to be set to true in the background.

Since we have elaborate macros with a couple of TM1RECALCs in a row. Our TM1 users don't tolerate the flickering while macro execution. After revising the code by adding

Code: Select all

Application.Screenupdating = False 

after each "Application.Run("TM1RECALC1")" we reduce screen flickering. But the screen still refreshes after each recalc.

Is there a way to undo / deactivate the command "Application.ScreenUpdating = True" in the tm1p.xla (Version 9.5.2, 32-bit)?
This command must have been added after TM1 9.1.2. In this version there is no flickering. To prevent excel from screen updating it used to be sufficient to set "Application.Screenupdating" at the beginning and at the end of a Sub.

Using

Code: Select all

Application.Visible = False / True

instead of "Application.ScreenUpdating" at the beginning and at the end (un)hides the Excel window. This is as well very irritating for users.

I found PM33830 during a web search and informed our TM1 consultants to contact the IBM support regarding this open support case. A VBA or Excel workaround would also be appreciated.

Business impact
Without removing "Application.Screenupdating = True" in tm1p.xla or an adequate workaround, we must postpone the TM1 9.5.2 update.

Thank you for any hint.

[lotsaram]

We are about to commence testing on 9.5.2 but have also found the screen flickering when updating workbooks that contain multiple active forms to be a huge user annoyance issue as the TM1Refresh macro seems to always set screenupdating to true.

The only solution we have found is quite an elaborate workaround but it seems to work quite well from a user acceptance point of view. Where a central macro is controlling workbook updating we have a hidden sheet which contains a drawing object sized to fit the screen which just contains a plain background and the text "Report refresh in progress, please wait ...". Immediatelly before the refresh is triggered screen updating is set to false and the "please wait picture" is copied to A1 on each sheet in the workbook, then then the screen is updated then set back to false. After this the refresh/recalc is called. At the conclusion all the pictures are deleted.

This way if the code in the tm1p.xla modules change the screenupdating and cause screen flickering then the user doesn't notice as all sheets contain the same please wait picture so this is not noticed. Also because the sheets are all a picture with a blank background the more impatient users are less inclined to wildly click about the screen while the macro is running which can frequently cause issues in code execution. The solution may seem clunky (and it is) but it works!

[Steve Vincent]

Glad it's not just me but it has other impacts too. Not only does it hack users off (epilipsy anyone?) but it dramatically slows multiple report creation down. Many of the things i have developed use a template report, feed data in, recalc, filter then save as before going to the next selection of data. In some cases a 15s refresh is now taking 30s simply due to the screen refresh issue. Multiply that by 250 reports and you get a serious degradation in performance which the users use as a complaint against us.

I like the idea of a picture holding page but does that solve the speed issue? Whats the latest on the PMR?

[Ben Stremme]

I just implemented lotsarams proposal. Even though the "please wait shape" is displayed, the screen still flickers during the execution of "TM1RECALC". For some instants you still see the sheet changing to the original report surface and back.

The speed issue isn't solved by that workaround. Though the refresh performance isn't much slower through the flickering. A reference macro which took 15s to execute before the update now takes 17s.

[moby91]

Are you using an english version of Excel 2003 ?
Or are you using some localized non-english version of Excel 2003 ?

[Ben Stremme]

We use to work with the german version. But the ScreenUpdating behavior is exactly the same when working with the english version of Excel.
The problems I'm facing are definitely caused by the source code in tm1p.xla.
IBM considers the removing of "Application.ScreenUpdating = True" at the end of the TM1RECALC/TM1RECALC1 macro in tm1p.xla as an enhancement request and not as a bug fix.

[MSidat]

We went to 9.5.2 a few months ago and had the same issues.

We are utilising a similar technique to lotsaram but instead we have one sheet which has the processing message which is visible and every other sheet is invisible until all the calcs and reformatting etc is completed. Once completed we unhide all the sheets and hide the processing sheet. Seems to work quite well.

At the same time though we have also consciously coded the vba without any activesheet.range or .selected type methods using things like the range1.value = range2.value method instead of the copy and paste method of moving data around the screen.

Sheets seems to be much faster and user friendly.