vba 在不同的excel文件中使用一个宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9480936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
use one macro in different excel files
提问by Tima
I wrote a macro, which I would like to use in different excel files, which have almost the same table structure but different data.
我写了一个宏，我想在不同的excel文件中使用它，这些文件具有几乎相同的表结构但不同的数据。
So is it possible anyhow just to "include" my macro-script to any excel file?!
那么是否有可能将我的宏脚本“包含”到任何 excel 文件中？！
I read already this tipbut it sounds for me like a bad joke.
我已经阅读了这个提示，但对我来说这听起来像是一个坏笑话。
Thank you
谢谢
回答by Hassan
You need to save the document as an Excel Add-in (.xla) and distribute that. Users can then go Tools>Add-insto install the add in. They will need to browse to the file you sent them and verify the add-in is checked on the add-in list. Note that add-ins do not show up in Alt+F8or in Tools>Macro. You need to make a menu for that in your add-in code. see this post http://spreadsheetpage.com/index.php/tip/creating_custom_menus/
您需要将文档另存为 Excel 加载项 (.xla) 并分发。然后用户可以去Tools>Add-ins安装加载项。他们需要浏览到您发送给他们的文件并验证加载项是否在加载项列表中被选中。请注意，加载项不会出现在Alt+F8或Tools>Macro. 您需要在加载项代码中为此创建一个菜单。看到这篇文章http://spreadsheetpage.com/index.php/tip/creating_custom_menus/
If you want your code to be available in all your workbooks, then use your
PERSONAL.XLSor in Excel 2007-2010yourPERSONAL.XLSBfile. These are normally held in theXLSTARTfolder under your documents and settings.
如果您希望您的代码在您的所有工作簿中可用，请使用您的
PERSONAL.XLS或Excel 2007-2010 中的PERSONAL.XLSB文件。这些通常保存在XLSTART您的文档和设置下的文件夹中。
This is a hidden workbook that opens when you start Excel. The code you copy in this workbook is available in all workbooks you have opened in Excel.
这是在您启动 Excel 时打开的隐藏工作簿。您在此工作簿中复制的代码可用于您在 Excel 中打开的所有工作簿。
The easiest way to make one is to record a dummy macro, then select Personal Macro Workbook under the Store The Macro in drop-down list.
制作一个最简单的方法是录制一个虚拟宏，然后在将宏存储在下拉列表中选择个人宏工作簿。
Open up macro editor (ALT+F9) and then save the PERSONAL.XLSfile. write up a macro, e.g
打开宏编辑器 ( ALT+ F9)，然后保存PERSONAL.XLS文件。写一个宏，例如
Public Sub Testing()
MsgBox "Hey im from Personal.xls"
End Sub
Remember to hide the workbook named
personal.xls(Window>>Hide). Now on any workbook, this macro is available.
请记住隐藏名为
personal.xls(Window>>Hide)的工作簿。现在在任何工作簿上，此宏都可用。
回答by BeachBum68
You have two options; you can
你有两个选择；你可以
1) include your macro in your personal.xlsb, which will be hidden, yet available for your use or
1) 在您的personal.xlsb 中包含您的宏，该文件将被隐藏，但可供您使用或
2) include your macro in an add-in file which will startup with excel every time. Add-in files give you the added benefit of easy distribution if someone else would like to use your macro(s).
2) 将您的宏包含在一个加载项文件中，该文件每次都会以 excel 启动。如果其他人想要使用您的宏，则加载项文件可为您提供易于分发的额外好处。
For the tool I'm building, I chose to use an add-in file and it's worked out great.
对于我正在构建的工具，我选择使用加载项文件并且效果很好。
Put your code in a module in the addin, make sure the macro is not private, and you can even make a button for easy use on your quick-access-toolbar.
将您的代码放在插件的模块中，确保宏不是私有的，您甚至可以在快速访问工具栏上制作一个按钮以便于使用。
Problem solved.
问题解决了。
回答by ExactaBox
The simplest, best way to do this is to create a "commander" XLSM which contains the macro but none of the data. All it does is open the specified Excel files and run the macro on them. You can either type/paste in the name of a specific file(s), you can loop through an entire directory, etc. It is very easy to run a macro on a different workbook than the one which actually contains the macro. Something like this below... piece of cake.
最简单、最好的方法是创建一个“指挥官”XLSM，其中包含宏但不包含任何数据。它所做的只是打开指定的 Excel 文件并在其上运行宏。您可以键入/粘贴特定文件的名称，也可以遍历整个目录等。在与实际包含宏的工作簿不同的工作簿上运行宏非常容易。像下面这样的东西......小菜一碟。
Sub MultipleFileMacro()
Dim wb As Excel.Workbook
Dim fso As Scripting.FileSystemObject
Dim f As Scripting.File
Set fso = New Scripting.FileSystemObject
For Each f In fso.GetFolder("c:\samplefolder").Files
Set wb = Excel.Workbooks.Open(f)
MyMacro wb
wb.Close
Next f
End Sub
------------
Sub MyMacro(wb As Excel.Workbook)
'do something here
End Sub
回答by Patrick Honorez
If the personnal.xls solution does not suit your needs, you can just save the macro(s) to an Excel workbook, and ask the users to have it loaded (open) while working in the "other" document.
You might need to correct your macro in that case. eg: replace Thisworkbookby Activeworkbook.
如果personnal.xls 解决方案不适合您的需要，您可以将宏保存到Excel 工作簿，并要求用户在“其他”文档中工作时加载（打开）它。
在这种情况下，您可能需要更正您的宏。例如：替换Thisworkbook为Activeworkbook。
回答by Rajesh S
If you want to use the Macro in more than one workbook, either convert it to AddIn or save in Personal. Xlsb file,, and if the Database structure is similar in other Workbooks, your Macro will be useful, but always remember each code has a specific task, ?
如果要在多个工作簿中使用宏，请将其转换为 AddIn 或保存在 Personal 中。Xlsb 文件，如果数据库结构在其他工作簿中类似，您的宏将很有用，但请始终记住每个代码都有一个特定的任务，？
