1

Instead of having the VBA code saved in a module inside the Excel file, I would like to have the code saved in a text file, for example module1.vba. In Excel, module1.vba would be loaded or imported and run as it was a normal module.

Is that possible? How to do that?

In Excel, in the VBA editor, there is the option Insert > File... in the menu that does sort of what I want, but I don't know how to automate that with a minimal VBA code to load the real code saved as text.

Reason for this is to allow code revision control using text based applications like git.

--- edit ---

This answer shows a good alternative work around. However I'm still not too happy in duplicating things.

Raf
  • 1,628
  • 3
  • 21
  • 40
  • Possible duplicate of [How to put Excel VBA into git](https://stackoverflow.com/questions/36024342/how-to-put-excel-vba-into-git) – Pᴇʜ Jun 26 '19 at 08:13
  • @Pᴇʜ That doesn't provide a way to do it through VBA. – Erik A Jun 26 '19 at 08:14
  • Or make use of one of the tools that already exist. Don't re-invent the wheel: Just one example (you will find more on Google): https://xltools.net/version-control-for-vba-macros/ – Pᴇʜ Jun 26 '19 at 08:14
  • @ErikA Yes, but since the *"Reason for this is to allow code revision control"* under git it, is not necessary to do this with VBA. See [What is the x/y problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). Sometimes people just ask for X because they didn't realise that their actual problem is Y. – Pᴇʜ Jun 26 '19 at 08:20
  • If you want to run the VBA code from a text file then you can do this. **[1]** At run time, create a copy of the text file **[2]** Rename the file to `.vbs` **[3]** Execute the `.vbs` file **[4]** Delete the copy. **[Note]** I would recommend slightly amending the code to make it compatible for `vbscript`. It is almost same to vba :) – Siddharth Rout Jun 26 '19 at 08:21
  • @Pᴇʜ The XY problem is not a valid close reason, just a reason why a question might be silly. This one isn't, in my opinion. Importing modules from text files has many uses, such as actively updating the VBA part of Excel files with managed content. – Erik A Jun 26 '19 at 08:26
  • @ErikA did you see the git project in the second answer of my duplicate link? It provides VBA code which *"… primary use case is for you to use this code as it is, without modifying it, as a tool for importing/exporting your own code."* … to *"put it under version control e.g. Git."* – Pᴇʜ Jun 26 '19 at 08:29
  • @Pᴇʜ Yes, but this question is narrowly defined. While the larger goal can be achieved with the duplicate, this specific approach is not covered, and the question is about a specific approach... – Erik A Jun 26 '19 at 08:30
  • @raf Check if this VBA module meets your requirements https://github.com/ColmBhandal/VbaSync – Pᴇʜ Jun 26 '19 at 08:32
  • @Pᴇʜ Thanks for the link. It looks interesting but the work around is larger than my module I'm trying to import - it feels like using a cannon to kill a fly =) – Raf Jun 26 '19 at 08:42
  • @Raf Well, don't get how this is an argument not to use it. Eg. the code of git is even larger than your code, so you cannot use this too? – Pᴇʜ Jun 26 '19 at 08:46
  • @Pᴇʜ KISS - see Erik A answer below. Just testing it before I accept. – Raf Jun 26 '19 at 08:50

1 Answers1

3

You can easily import a VBA file using a simple one-liner:

Application.VBE.ActiveVBProject.VBComponents.Import "C:\Path\To\File.bas"

This works with files exported from the VBA IDE, which include information like module name and other properties that can be configured on a per-module basis.

You need to enable Trust access to the VBA project object model for this to work. You can find that in the trust center settings, under Macro Settings.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thanks Erik. This seems to be the right way to go. For completeness would be good to include code to handle previous existence of a module with same name and also removing the module when done so it doesn't stick to the excel file. Also, worth mentioning that it only works for unprotected projects. – Raf Jun 26 '19 at 10:49
  • Feel free to edit that in if you feel it's relevant, these things seem obvious to me. I'll try to review it before it goes through the queue/ – Erik A Jun 26 '19 at 10:52