TM1 Perspectives interacts with other VBA code in Personal.xlsb

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:

TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by Wim Gielis »

Hi all,

I come across a bizarre case. It's for Planning Analytics 2.0.6 but I have seen this in earlier versions too. I never bothered to find out the real cause. Now I do :-)

In my Personal.xlsb I have a macro to open files I use on a regular basis. This macro can be launched by clicking an icon in the QAT (Quick Access Toolbar). This (small) macro uses a second procedure, where a password is asked from the user ( me :-) ). The password is entered in a userform with a textbox since I want to hide the password - just in case someone is looking over my shoulder.

Now, opening Excel (therefore opening Personal.xlsb) works fine. Nothing happens initially, until I click the icon, I enter a password and a file is opened. But when I start Perspectives the macro in which the userform is shown also starts. Therefore, anytime I open Perspectives, I also get the question to enter a password to open a file I don't want to open.

How is it possible that opening Perspectives executes a macro in my Personal.xlsb ? It's not even a function but a procedure.
As soon as I have the code to .Show the userform, the macro is eligible for execution whenever Perspectives starts. No other VBA-code / addin is running at all.

I reduced all code to the below. Legend:
1: the icon to launch the macro in Personal.xlsb to open files I use often (macro: 'Open_My_Files' calling a second macro)
2: the userform pops up when I start Perspectives
3: the userform in VBA, nothing fancy
4: the code, reduced to the essential part, issue is still reproducible
5: the code for the userform. Other than a couple of variables, I have nothing there. I could even delete those given that in the Get_Password macro I commented out code as well.

Does anyone have an idea please ?

Happy festivities !

Wim
04.png
04.png (158.7 KiB) Viewed 8945 times
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
bgregs
Posts: 77
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by bgregs »

Hi Wim,

This sounded like a pretty interesting problem to kick off the new year with, so I figured I'd give it a go. Unfortunately, I was unable to reproduce the issue you are encountering. The following are the things I've tried (please let me know if I didn't recreate a step correctly):
  • Opened Excel by itself - no auto execution of the form .Show property
  • Opened the .xlsb file directly - no auto execution of the form .Show property
  • Opened Perspectives directly - no auto execution of the form .Show property
  • Opened Excel by itself, then opened Perspectives after in the same session - no auto execution of the form .Show property
  • Tweaked all possible "Macro" settings in the Excel Trust Center (under Options) - no auto execution of the form .Show property
  • Moved the code into an add-in file (.xlsa) - no auto execution of the form .Show property
Testing Environment:
  • Windows 10
  • Microsoft Excel for Office 365 MSO (16.0.11001.20064) 32-bit
  • No add-ins or macros set to load on startup ("clean" Excel install)
This leads me to believe that it must be something outside of your VBA code. I'm thinking it's some obscure Excel setting that was set that may be throwing it off. Sorry I couldn't help further, but hopefully this helps in some way!

Config Information Attached
Attachments
Capture.PNG
Capture.PNG (76.73 KiB) Viewed 8906 times
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: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by Wim Gielis »

Hello bgregs,

Thank you for your interest. Indeed, strange case :-)

I can confirm that the setup is similar. So it's either someting in Excel, either something in my Excel environment which is not a macro or addin.

I will look at it further once I get to it. Please don't throw away your testing environment if you can, I might ask a few questions for verification.
I have a new Excel version it seems, other than that it should be equal.

Thanks,

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
bgregs
Posts: 77
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by bgregs »

Not a problem! Feel free to send any verification requests over my way! :D
User avatar
gtonkin
MVP
Posts: 1192
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: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by gtonkin »

Hi Wim/bgregs, I have had some inexplicable issues in the past when loading the add-in when Excel start, i.e. configured under the Excel Addins, versus opening Excel then opening TM1p.xla to load Perspectives - From you initial post Wim, sounds like you are loadind TM1p.xla later - is that correct?

If so, have you tried adding it to Excels add-ins to see what happens when you start Excel?
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: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by Wim Gielis »

You are right about the behaviour, George. But starting Perspectives together with Excel causes the same userform to appear.

It's easier for testing though since I just have to start Excel :D
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
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: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by Wim Gielis »

Update. It's not related to any particular userform. I reduced the issue to the following:
04.png
04.png (247.89 KiB) Viewed 8861 times
So, the icon in the QAT has the macro 'Open_My_Files' attached. That macro is just a Debug.Print of the current time.
Perspectives starts with Excel. This somehow executes the macro from the QAT icon, leaving the current time in the Immediate Window in VBA.

With Perspectives not loaded with Excel, this does not happen.
With the macro renamed to Sub Test() and the icon's macro unchanged, I get the error message (a beep) when Perspectives opens. Excel can't find the macro 'Open_My_Files' obviously. So I rename it back.

Go figure...
Perspectives executes a macro in one of the QAT icons. The QAT icon holds for all files, not just for a specific file.
I'll continue testing. I tend to agree that it's not related to the VBA-code but a relationship between Perspectives and a QAT icon.

Thanks,

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
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: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by Wim Gielis »

Adding a second QAT icon, totally similar, does not execute a second macro:
06.png
06.png (230.71 KiB) Viewed 8857 times
- Renaming the main QAT icon macro (NEW_NAME) and deleting the second icon and code again, now executes this NEW_NAME macro.

- Removing the main icon in the QAT and closing Excel (saving Personal.xlsb always when asked for), restarting Excel and adding the icon again: next time the macro is still executed !

- Starting Excel with Shift pressed does not launch Perspectives (since it disables code in the Open event of a workbook) and therefore the debug macro is not executed.

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
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: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by Wim Gielis »

The tension builds... Nearly got this one solved.
By playing around with the QAT icons (and reordering them) it appears that an Alt-8 is executed when Perspectives is opened.
This executes the macro attached to the 8th QAT icon. If I reshuffle icons and put a different icon in the 8th spot, that one gets executed.

On my keyboard, Alt-8 is done on the numbers at the top of the keyboard, not the numeric portion of my keyboard. Here's a picture showing that it's the 8 entered as Shift-!

my keyboard

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
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: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by Wim Gielis »

I found the root cause !

Perspectives tries to be helpful and selects the TM1 ribbon. The VBA-code creates the ribbon for TM1 with the name !TM
Therefore pressing Alt, then the ! key and Enter will activate that ribbon.

But if on my keyboard this is Alt-8 it executes a different macro, the one attached to the 8th QAT icon.

File a bug with IBM.
06.png
06.png (66.62 KiB) Viewed 8846 times
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
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by Steve Rowe »

Nice detective work Van der Valk!
Technical Director
www.infocat.co.uk
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: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by Wim Gielis »

It took a while but I got there 😉
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
bgregs
Posts: 77
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by bgregs »

WOW!!! Great job on digging that one up! I'm honestly surprised that this is the first time it's come up (with the variety of keyboards/layouts that exist in the wild), but either way, I guess we can look forward to an update in the next fix pack ;) Nicely done!
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: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by Wim Gielis »

Thanks bgregs.

The bug has been raised with IBM. Feedback was received after a couple of hours, yet it was useless (the numlock thing and so on).

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
Bakkone
Posts: 119
Joined: Mon Oct 27, 2014 10:50 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by Bakkone »

So... I'm not helping in any way.

But that was some impressive detective work. Really glad you're a part of the community.
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: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by Wim Gielis »

Thanks for the nice comments, you're welcome.
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
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: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by Wim Gielis »

Status: this case is now forwarded as a defect to development:
"PH08270 TM1 tab in Excel can not selected with Keyboard shortcuts"
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
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: TM1 Perspectives interacts with other VBA code in Personal.xlsb

Post by Wim Gielis »

Closed by Development, no plans to change the currenjt behavior.
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
Post Reply