VBA Macro errors in Excel 2016
-
- Posts: 54
- Joined: Tue May 08, 2012 3:58 pm
- OLAP Product: TM1
- Version: 9.5.2 FP2
- Excel Version: Excel 2007
VBA Macro errors in Excel 2016
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...
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...
- paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: VBA Macro errors in Excel 2016
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 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
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: VBA Macro errors in Excel 2016
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.
-
- Posts: 54
- Joined: Tue May 08, 2012 3:58 pm
- OLAP Product: TM1
- Version: 9.5.2 FP2
- Excel Version: Excel 2007
Re: VBA Macro errors in Excel 2016
The macro(s) do a whole bunch of stuff, not just recalculating a report.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
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.
-
- Posts: 54
- Joined: Tue May 08, 2012 3:58 pm
- OLAP Product: TM1
- Version: 9.5.2 FP2
- Excel Version: Excel 2007
Re: VBA Macro errors in Excel 2016
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.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.
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.
-
- MVP
- Posts: 3128
- 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: VBA Macro errors in Excel 2016
Why do you activate a range ?
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
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
-
- Posts: 54
- Joined: Tue May 08, 2012 3:58 pm
- OLAP Product: TM1
- Version: 9.5.2 FP2
- Excel Version: Excel 2007
Re: VBA Macro errors in Excel 2016
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.
-
- Community Contributor
- Posts: 306
- Joined: Mon May 12, 2008 8:11 am
- OLAP Product: TM1
- Version: TM1 11 and up
- Excel Version: Too many to count
Re: VBA Macro errors in Excel 2016
So, what happens if you delete and recreate the button in 2016?
Paul
-
- MVP
- Posts: 3128
- 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: VBA Macro errors in Excel 2016
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).
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
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
-
- Posts: 54
- Joined: Tue May 08, 2012 3:58 pm
- OLAP Product: TM1
- Version: 9.5.2 FP2
- Excel Version: Excel 2007
Re: VBA Macro errors in Excel 2016
FYI -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
Today I reinstalled TM1 10.2.2 FP7 Client (after Excel 2016). No change in behavior - the macro issue still occurs.
-
- Posts: 54
- Joined: Tue May 08, 2012 3:58 pm
- OLAP Product: TM1
- Version: 9.5.2 FP2
- Excel Version: Excel 2007
Re: VBA Macro errors in Excel 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.Paul Segal wrote: ↑Thu Aug 24, 2017 2:22 pm So, what happens if you delete and recreate the button in 2016?
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!
- gtonkin
- MVP
- Posts: 1210
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: VBA Macro errors in Excel 2016
Do you disable events and screen updating as one of the first commands when the button is clicked?
-
- Posts: 74
- Joined: Mon May 12, 2008 12:25 pm
- OLAP Product: TM1
- Version: PA 2.0.6 Local
- Excel Version: Excel 2016
Re: VBA Macro errors in Excel 2016
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.
-
- Posts: 54
- Joined: Tue May 08, 2012 3:58 pm
- OLAP Product: TM1
- Version: 9.5.2 FP2
- Excel Version: Excel 2007
Re: VBA Macro errors in Excel 2016
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.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.
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.
-
- Posts: 54
- Joined: Tue May 08, 2012 3:58 pm
- OLAP Product: TM1
- Version: 9.5.2 FP2
- Excel Version: Excel 2007
Re: VBA Macro errors in Excel 2016
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.
- gtonkin
- MVP
- Posts: 1210
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: VBA Macro errors in Excel 2016
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.
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.
-
- Posts: 54
- Joined: Tue May 08, 2012 3:58 pm
- OLAP Product: TM1
- Version: 9.5.2 FP2
- Excel Version: Excel 2007
Re: VBA Macro errors in Excel 2016
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.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.
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!!
-
- Posts: 54
- Joined: Tue May 08, 2012 3:58 pm
- OLAP Product: TM1
- Version: 9.5.2 FP2
- Excel Version: Excel 2007
Re: VBA Macro errors in Excel 2016
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.jcr55 wrote: ↑Mon Aug 28, 2017 1:32 pmWe 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.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.
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.