In VBA, we could use for example Wb.VBProject.VBComponents to fetch all the VBA macros of a workbook Wb:
These VBA macros, even in form of text, will enable (existing and emerging) analysis tools over VBA macros. Although such tools are not a lot (because they are hard to build), it is not impossible, and it is necessary. For instance,
1) some big companies have in-house tools to inspect their VBA macros against programming guidelines, and find code smells.
2) there are software to measure program metrics of VBA macros.
3) advanced software can detect bugs in VBA macros.
4) analysing VBA macros can complete existing Excel analyses such as "Worksheet Relationship" and "Cell Relationship" under "Inquire". These analyses are based on Excel formulas, however, two worksheets or two cells could well have a relationship via macros.
Developing VBA may not be the strategy of Microsoft. But there are millions of spreadsheets having VBA macros, and having access to them permits of a better control of them, which does not mean encouraging people to code VBA macros...
Some of us will only ever be in the Windows world for certain clients and we have written large and complex VBA applications for them. Applications that access vital APIs like Power Query, which perhaps will never become a part of the JS world.
So we want to augment and modernise those apps with the latest office.js add-in panels, dialogs and content panels, but we are locked out of the treasure trove hidden within our VBA.
And so we try hacky things like using VBA Worksheet.change events to watch cells that our JS add-in writes to so as to instruct VBA to fire off Power Query to extract data from an external server and load it into another worksheet, which the JS add-in will be watching.
So far, the JS side successfully triggers on a data changed by VBA in a worksheet, but the VBA change event is oblivious to JS making changes. I have seen links where extra hack was applied to make this work, so I am hoping I will be able to make this connection eventually.
I think it would be fair to at least make it available to those (many of us, actually) who would like to access functionality in VBA which is not currently, or never will be available on the JS side.
Would be most grateful.
Voting this up but great care should be used while adding this as if working as described, it would also allow foreign add-ins from stealing VBA code. Sounds like something which should require admin rights.