[SOLVED] Macros (global) not working in Excel workbooks opened from OneNote

The Issue

In a workbook opened from OneNote (2016), macros are not working, or have stopped working - these are the macros that were earlier saved globally by the user.

If you go to the Macros console from the Developer tab, you'll simply not see the macro(s) there - see the screenshot below - there is no PERSONAL.XLSB to be seen in the "Macros in:" dropdown.

If the macros are not there, consequently, if you had set up shortcut keys, they will not work as well.

The Reason

The reason behind this issue is that the workbook PERSONAL.XLSB isn't getting opened automatically with Excel (as is supposed to happen and does happen if we start Excel from outside)!

This file is generally located in C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART, and this is where global macros are saved.

When we start Excel generally, this file is opened automatically and provides its macros to Excel which then become visible to all the other opened workbooks.

You can check this by opening Excel directly (i.e. not via OneNote) - you'll see the cache file created temporarily in the XLSTART location.

Now why OneNote is not able to open this file automatically is probably because it doesn't have its location (...XLSTART/) in its Trusted Locations.

And the insult to injury is that the option to add this location as part of Trusted Locations in OneNote or OneNote 2016 is not there (any longer, seems like it was there earlier).

You can confirm this by going to File -> Options -> Trust Center -> Trust Center Settings.
Here, you will not see any menu item for Trusted Locations if you have come here via OneNote, however, you'll see it if you have come here from Excel, Word etc.!

So, we see that this Trusted Locations for OneNote or OneNote 2016 is different from the Trusted Locations for Office (or any other Office App)!
And hence, even if you have this location configured as a Trusted Location in Microsoft Office in general, that is not going to help with OneNote!

The Workaround

So, for now, the simplest workaround is this.

Simply open Excel from anywhere outside OneNote before opening any workbook from OneNote.

This will open PERSONAL.XLSB in the background and make its macros available to other workbooks.
We don't even need to open any workbook from outside - just that the Excel program is running (before we open any workbook from OneNote) is sufficient.

When we do this, the workbooks that we open from OneNote will see the macros coming from PERSONAL.XLSB - as you can see below.

Well, this is the only way I'm able to make it work as of now.

It's simple but it's a little bit of hassle.
Please let me know in the comments below if you come to know of a better solution to this problem.