3

I'm using Excel 2010. I have some code in background (VBA) that is growing up from time to time. I'm trying to find a way to separate the source code from the xls file, so I could compare the code changes. In other words, I want that the code will be in a textual file, and every time I'll open the Excel file, the source code for macros will be taken from this file.

Thanks in advance!

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
ItayB
  • 10,377
  • 9
  • 50
  • 77
  • Use a macro to automatically export all your VBA code to text files. I recommend [this macro](http://stackoverflow.com/a/608899/15639) from a [similar question](http://stackoverflow.com/questions/608872/exporting-vba-code-from-multiple-excel-documents-to-put-into-version-control) about using version control with Excel VBA – MarkJ Apr 22 '12 at 18:44
  • and what about the import? i don't want to do it manually for every change i'll make in the code.. – ItayB Apr 23 '12 at 07:32
  • @ItayB: Take a look at the answer given by me & MarkJ's comments on a tool called SourceTools.xla. – shahkalpesh Apr 24 '12 at 04:14
  • first of all, thanks. my solution was to add the following line: Application.VBE.ActiveVBProject.VBComponents.Import (filename) – ItayB Apr 24 '12 at 08:49

4 Answers4

2

Take a look at this question on SO.

It has the mention of addin called SourceTools, that I have used & find it worthwhile.
Also, it comes with source code so it can be modified to point it to the source code control software (such as SVN) that is specific to your use.

Feel free to close this question as the link I gave has the same question as yours & answers what I suppose you are looking for.

Community
  • 1
  • 1
shahkalpesh
  • 33,172
  • 3
  • 63
  • 88
  • Definately use SourceTools.xla for one click export of the entire project. (http://www.codeproject.com/Articles/18029/SourceTools-xla) – John Alexiou Apr 22 '12 at 20:50
1

Have a look at the various code cleaner apps/code available for VBA, such as:

http://www.appspro.com/Utilities/CodeCleaner.htm

Among other things, these export the modules/forms/classes to text files, delete them, then re-insert them into your projects.

With a few mods, that'll form the basis for what you're after.

Another possibility: I don't do much in Excel, but if its add-ins behave like those in PowerPoint, that might help also. In PPT, installed add-ins load automatically when PowerPoint starts, create any user interface needed and are available to use with any open files in the app. To update the code, you modify it, create a new add-in, put it wherever PPT is looking for it, and restart PPT. Voila ... code's updated for all PPT files.

Steve Rindsberg
  • 3,470
  • 1
  • 16
  • 10
1

First of all, thank you all for your answers. my solution was: 1. export all the modules to *.bas (one file per module). 2. add the modules code my calling:

Application.VBE.ActiveVBProject.VBComponents.Import (filename)

for each file.. 3. after finishing:

Set VBComp = VBProj.VBComponents(moduleName)
If Err.Number = 0 Then 'no error
    VBProj.VBComponents.Remove VBComp

that's remove the module so it won't be saved in the xls before quiting

ItayB
  • 10,377
  • 9
  • 50
  • 77
0

I would recommend a manual process in such a scenario.

Suppose you want to take a backup of Module1, then right click on it and click on "Export File". You will get an "Export File" dialog box. Save it as, say Module1 - 22 Apr - 2012.bas at a relevant location. And you are done

How would this help?

1) The dates in the file name will tell you what date the backup was taken so you can actually keep track of the date when the macro was changed.

2) .Bas files can be opened with Notepad. This will help you in comparing the current VBA code with the relevant backup file.

3) If at any point of time you want to retrieve the backup from a particular date, simply delete the existing module (take a backup of it if you want) and then click on "Import File" and import it in your VBA.

enter image description here

HTH

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • This is a good process but [why not do it automatically](http://stackoverflow.com/a/608899/15639) in one click – MarkJ Apr 22 '12 at 21:06
  • Because I don't need a `tool` per se just to export my Module. If I need to do some serious analysis then yes, I might go for a tool :) or unless I have 10-15 modules that I need to export every day.. If it is just 2-3 modules, Manual method is just fine. – Siddharth Rout Apr 22 '12 at 21:09
  • Thanks for the idea but i'm looking for auto solution. I'm not the only programmer and I don't want to miss someone changes because of manually process. – ItayB Apr 23 '12 at 07:31