TM1 Forum

Current version in Production:
TM1 10.2.2 FP7
Windows Excel Client runs TM1 Perspectives 10.2.2 FP7 and Excel 2007 SP3 MSO 32-bit

We have many production TM1 Perspectives Excel workbooks for input and reports that contain bespoke Excel VBA Macros.
All the macros work fine in Production.

In our Development environment, we upgraded Excel 2007 to
Office 365 MS Excel 2016 MSO (16.0.8201.2171) 32-bit
(everything else is the same)

Now, using Excel 2016, some macros error out (they run fine in Excel 2007). We get a message box Microsoft Visual Basic Run-time error '6': Overflow or Subscript out of Range or some other error that indicates Excel VBA is hosed.
The issue occurs in Workbooks with multiple worksheets. Excel loses track of which worksheet is the active worksheet. Meaning I open the Excel file, select the second worksheet and click the Macro button to refresh the report, which executes a macro. The second worksheet should remain as the selected and active worksheet. But for some unknown reason, during macro execution Excel makes the first worksheet the active worksheet.
When the macro error occurs, I click the debug option and we get 'Can't execute in break mode'. So stepping through the macro is not helpful.
The error occurs in different situations and it not predictably reproducible.

Has anyone sees this type of issue when converting to Excel 2016 ?
I am absolutely not asking for assistance on macro coding...

Hi

I few suggestions

a) If all the macro is doing is recalculating the sheet then you could possibly replace it with an Action Button.

b) Did you install Excel 2016 after installing TM1? If so, try re-installing TM1. There have often been issues with the Microsoft Visual C++ run time library. You might want to try downloading the appropriate version of that and installing it.

Regards

Paul Simon

I have seen this issue before. In my case the culprit was named ranges that had "Workbook" as their scope instead of the particular tab they were in. The fix was to re-do all the named ranges called from the VBA to have a scope equal to the tab they were located in. TIFWIW.

paulsimon wrote: ↑Wed Aug 23, 2017 7:14 pm Hi

I few suggestions

a) If all the macro is doing is recalculating the sheet then you could possibly replace it with an Action Button.

b) Did you install Excel 2016 after installing TM1? If so, try re-installing TM1. There have often been issues with the Microsoft Visual C++ run time library. You might want to try downloading the appropriate version of that and installing it.

Regards

Paul Simon

The macro(s) do a whole bunch of stuff, not just recalculating a report.
Yes, Excel 2016 was installed after TM1 10.2.2 FP7 was installed.
We will try to re-install TM1 client and see if that helps.

tomok wrote: ↑Wed Aug 23, 2017 8:15 pm I have seen this issue before. In my case the culprit was named ranges that had "Workbook" as their scope instead of the particular tab they were in. The fix was to re-do all the named ranges called from the VBA to have a scope equal to the tab they were located in. TIFWIW.

I understand. For our first attempt to resolve the problem, in one of the Workbooks that is now exhibiting the macro issue, I changed all the macros that referenced worksheet objects or ranges to be explicit to the specific Worksheet name.

As an example, I changed this

Range("UserStartCell").Activate

to this
ThisWorkbook.Worksheets("ProductGuide").Range("UserStartCell").Activate

Unfortunately, that did not fix the issue we are seeing using Excel 2016.

Why do you activate a range ?

Wim Gielis wrote: ↑Wed Aug 23, 2017 9:12 pm Why do you activate a range ?

OK, fair enough. For a single cell range, I think activate and select end up doing the same thing.
I have changed all Range activate command lines to Range Select.

The problem persists. However we have found something that points more to a Microsoft issue...

We use a button on the Excel worksheet that is assigned to run a macro.
In Excel 2007, no problems.
In Excel 2016, when executing the macro via the Worksheet button, sometimes it loses track of which worksheet is active.
If we execute the macro directly (not via the button), using the Developer Ribbon group and selecting 'Macro', then selecting the macro we want to run, then clicking the 'Run' button, everything works correctly.
So the troubles seem to be related to the worksheet button executing the macro.

So, what happens if you delete and recreate the button in 2016?

jcr55 wrote: ↑Thu Aug 24, 2017 2:09 pm

Wim Gielis wrote: ↑Wed Aug 23, 2017 9:12 pm Why do you activate a range ?

OK, fair enough. For a single cell range, I think activate and select end up doing the same thing.
I have changed all Range activate command lines to Range Select.

In general you don't have to select nor activate ranges and sheets.

But I do agree that the problem is in the sheet and the button (though I haven't seen it in my Excel 2016 installation).

paulsimon wrote: ↑Wed Aug 23, 2017 7:14 pm Hi

I few suggestions

a) If all the macro is doing is recalculating the sheet then you could possibly replace it with an Action Button.

b) Did you install Excel 2016 after installing TM1? If so, try re-installing TM1. There have often been issues with the Microsoft Visual C++ run time library. You might want to try downloading the appropriate version of that and installing it.

Regards

Paul Simon

FYI -
Today I reinstalled TM1 10.2.2 FP7 Client (after Excel 2016). No change in behavior - the macro issue still occurs.

Paul Segal wrote: ↑Thu Aug 24, 2017 2:22 pm So, what happens if you delete and recreate the button in 2016?

I just tried that - In Excel 2016, I deleted the form button on the Worksheet and created a new button and assigned it to the same macro.
I saved the workbook, exited TM1, logged back into TM1, opened the updated workbook, clicked the button to run the macro, and the behavior was the same.
So that did not fix the issue. But it was worth a try!

Do you disable events and screen updating as one of the first commands when the button is clicked?

And another thing we had to do was to make sure with all the buttons that we set the property "TakeFocusOnClick" to false. We've definitely found Excel 2016 somewhat more "sensitive" shall we say about where it thinks it is and we find we need to be uber-specific about where things are.

TJMurphy wrote: ↑Mon Aug 28, 2017 10:42 am And another thing we had to do was to make sure with all the buttons that we set the property "TakeFocusOnClick" to false. We've definitely found Excel 2016 somewhat more "sensitive" shall we say about where it thinks it is and we find we need to be uber-specific about where things are.

We have done more debugging, and we have also found that Excel 2016 is quite sensitive to what is in focus and what the current selected worksheet is, particularly when involved with TM1. When the VBA command selects a cell that contains a TM1 DBRW formula, it appears to us that TM1 causes Excel 2016 to change the selected worksheet to the first tab in the workbook, regardless of what the VBA command is telling it to do.

In the two problem child workbooks we have, we have changed all of the VBA commands to use explicit worksheet name references in all commands.
That did not correct the problem.

Thank you for the additional idea, we will try the takefocusonclick property.

gtonkin wrote: ↑Thu Aug 24, 2017 4:44 pm Do you disable events and screen updating as one of the first commands when the button is clicked?

Thank you. We have already tried the disable events commands, to no avail.
We sometimes turn off screen updating in the VBA macros (and on at the end), but I will go through and make sure it is consistently done.

Just out of curiosity, do you have the same behaviour when you run the macro after opening the workbook from applications vs opening from your local drive/network?
My own experience (but could be my machine) is that when I open a workbook from Applications and work with it, I have some inexplicable things happen which sound similar i.e. right-click and some other workbook/sheet has the focus, type values in and they look to have not entered only to find them on another workbook that was actually active but not in view.

gtonkin wrote: ↑Mon Aug 28, 2017 1:46 pm Just out of curiosity, do you have the same behaviour when you run the macro after opening the workbook from applications vs opening from your local drive/network?
My own experience (but could be my machine) is that when I open a workbook from Applications and work with it, I have some inexplicable things happen which sound similar i.e. right-click and some other workbook/sheet has the focus, type values in and they look to have not entered only to find them on another workbook that was actually active but not in view.

Well, we never use the Application folder to open Excel files. We use a drop down menu system using VBA which essentially does an Excel File/Open.

However, we have opened a ticket with IBM to investigate a possible bug in Tm1 Client 10.2.2 FP7 and Excel 2016 where the selected worksheet gets changed unexpectedly.

And, I added the disable events and screen updating false to the start of each VBA subroutine, and that workaround fixed the issue (at least in one workbook). yay!!

jcr55 wrote: ↑Mon Aug 28, 2017 1:32 pm

TJMurphy wrote: ↑Mon Aug 28, 2017 10:42 am And another thing we had to do was to make sure with all the buttons that we set the property "TakeFocusOnClick" to false. We've definitely found Excel 2016 somewhat more "sensitive" shall we say about where it thinks it is and we find we need to be uber-specific about where things are.

We have done more debugging, and we have also found that Excel 2016 is quite sensitive to what is in focus and what the current selected worksheet is, particularly when involved with TM1. When the VBA command selects a cell that contains a TM1 DBRW formula, it appears to us that TM1 causes Excel 2016 to change the selected worksheet to the first tab in the workbook, regardless of what the VBA command is telling it to do.

In the two problem child workbooks we have, we have changed all of the VBA commands to use explicit worksheet name references in all commands.
That did not correct the problem.

Thank you for the additional idea, we will try the takefocusonclick property.

I found that the type of Excel button we use (a shape) to assign to a macro does not have the TakeFocusOnClick type of properties. So that does not apply to what we are doing.