vba VB 代码和 Excel 的单独文件

separate files for VB code and Excel

提问by ItayB

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.

我正在使用 Excel 2010。我在后台（VBA）中有一些不时增长的代码。我试图找到一种将源代码与 xls 文件分开的方法，以便我可以比较代码更改。换句话说，我希望代码位于文本文件中，并且每次打开 Excel 文件时，宏的源代码都将从该文件中获取。

Thanks in advance!

提前致谢！

采纳答案by ItayB

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:

首先，感谢大家的回答。我的解决方案是： 1. 将所有模块导出到 *.bas（每个模块一个文件）。2.添加模块代码我的调用：

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

for each file.. 3. after finishing:

对于每个文件.. 3. 完成后：

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

那是删除模块，所以它不会在退出之前保存在 xls 中

回答by shahkalpesh

Take a look at thisquestion on SO.

看看这个关于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.

它提到了名为 SourceTools 的插件，我使用过并发现它值得。
此外，它还附带源代码，因此可以对其进行修改以将其指向特定于您使用的源代码控制软件（例如 SVN）。

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.

随意关闭此问题，因为我提供的链接与您的问题相同，并回答了我想您正在寻找的问题。

回答by Steve Rindsberg

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

查看可用于 VBA 的各种代码清理应用程序/代码，例如：

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

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.

另一种可能性：我在 Excel 中没有做太多事情，但是如果它的加载项的行为与 PowerPoint 中的加载项一样，那也可能会有所帮助。在 PPT 中，安装的加载项在 PowerPoint 启动时自动加载，创建所需的任何用户界面，并可用于应用程序中的任何打开文件。要更新代码，您可以修改它，创建一个新的加载项，将它放在 PPT 寻找它的任何地方，然后重新启动 PPT。瞧……所有 PPT 文件的代码都已更新。

回答by Siddharth Rout

Iwould 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.basat a relevant location. And you are done

假设您要备份Module1，然后右键单击它并单击“导出文件”。您将看到一个“导出文件”对话框。将其另存为，例如Module1 - 22 Apr - 2012.bas在相关位置。你已经完成了

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.

1)文件名中的日期会告诉您备份的日期，因此您可以实际跟踪更改宏的日期。

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

2）.Bas文件可以用记事本打开。这将帮助您将当前的 VBA 代码与相关的备份文件进行比较。

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.

3)如果您想在任何时候从特定日期检索备份，只需删除现有模块（如果需要，请进行备份），然后单击“导入文件”并将其导入到您的 VBA 中。

HTH

HTH