Does a workflow exist that permanently adds a macro to a user’s Personal Macro Workbook (PERSONAL.XLSB)?
I am not talking about having a bot run a macro located somewhere else. I would like the workflow to alter the user’s current PERSONAL.XLSB file, adding the macro and assigning a shortcut, so that the user only has to run the bot one time to “install” a VBA macro.
This would be extremely helpful in a corporate setting where some people may not be great with VBA navigation. Simply run the bot, and the new Macro is added to the personal repository, with new hotkeys assigned with Application.OnKey.
This would be great to add a suite of common functions to a user’s personal Excel instances (disable F1 key, Center Across Selection hotkey, Scroll-to-A1 hotkey, etc.).
This does not solve the original question, unfortunately. I do not want to run an external VB script, I want to input external VBA code into the PERSONAL.XLSB so that the user has access to the Macro without running the bot.
The steps I would like the bot to take:
If PERSONAL.XLSB does not exist, create it (I have this completed)
Navigate into PERSONAL.XLSB → Modules, and add a module with prewritten VBA code
Navigate into PERSONAL.XLSB → Microsoft Excel Objects → This Workbook, and add an Application.OnKey within a Workbook_Open() sub to link the newly created Module to a hotkey
Save the workbook, and exit
The next time the user opens Excel, they can Alt+F11 to VBA editor and see the new Module in their PERSONAL.XLSB repository. The new Macro is also linked to a hotkey, which is available for use on the Excel sheet
And step 2,3,4 have a Potential to get Automated with approaches from above. In this direction my Post was pointing. You are interrested to import code and make IT available through Key short Cuts as a one time and permanent Action, right?
Yes, the action of importing pre-written code should be one-time and permanent. Of course, the user could delete the code in the future in VBA Editor if they wanted, so in that sense it is not permanent, but yes, the change should affect the PERSONAL.XLSB such that the bot does not need to run every time the Macro is used.
The idea for this automation is that extremely useful pre-built Macros could be made available to someone with no knowledge of VBA.
Yes, i can Unserstand your Point and IT IS reasonable what you are stating. So manually steps of Doing this have a Potential to get Automated. A Potential approach to crosschecked was mentioned above
ITS Just a Suggestion for Doing rnd in some directions.
Manually you can import macrocode.bas Code
What about when this can be scripted by Code lines Like e g.
oXL.VBE.ActiveVBProject.VBComponents.Import “C:\KbTest.bas”
Then KbTest.bas Could be your prewriten Code
The entire Code Line as an importer example Could be Part of that Code Base that is doing this what you have done manually in the past.
And this importer/Setup Code maybe can be realized and one time executed by activities mentioned from above
Yes, your idea is much clearer now. I had not thought of doing it this way, but it makes sense and could be much more intuitive than my previous approach.
Do you know of any examples I could work off of? I have not manipulated VB Projects (i.e. moving modules between Projects, etc.) using code like yours in the past, so a reference / dummy code would be very useful. I understand the logic, I just don’t have enough experience in the VBA language to know proper syntax for implementation.