您如何使用 vba 插件并制作安装程序？

How do you take a vba addin and make an installer?

提问by Cameron Aziz

I have written a vba module that installs an addin button to excel. I would like to deploy it on many computers as easy as possible. Currently, these are my steps.

我已经编写了一个 vba 模块，它安装了一个插件按钮来表现出色。我想尽可能轻松地将它部署在多台计算机上。目前，这些是我的步骤。

1. Save the excel as *.xlsm
2. Open the doc on the computer that I want to install
3. Save the document as an extension (*.xlam) which places it in the addin folder
4. Go to options>Add-Ins>Manage /Go>
5. Click the check box for my add in

1. 将excel另存为*.xlsm
2. 在我要安装的计算机上打开文档
3. 将文档另存为扩展名 (*.xlam)，将其放在插件文件夹中
4. 转到选项>加载项>管理/前往>
5. 单击我的加载项的复选框

Is there a way to automate this process? Write something in vb?

有没有办法自动化这个过程？用vb写东西？

回答by Peter

You can build an exe or msi installer with a tool such as Advanced Installer (the free edition). One thing you can do is copy files to a specific location and if you put it in the XLSTART folder then that add-in will automatically load for the user when starting Excel. There's a complimentary folder within the Excel program files directory, usually something like this

您可以使用 Advanced Installer（免费版）等工具构建 exe 或 msi 安装程序。您可以做的一件事是将文件复制到特定位置，如果您将其放在 XLSTART 文件夹中，则该加载项将在启动 Excel 时自动为用户加载。Excel 程序文件目录中有一个免费文件夹，通常是这样的

C:\Program Files (x86)\Microsoft Office\Office15\XLSTART

C:\Program Files (x86)\Microsoft Office\Office15\XLSTART

which if you drop xlam files into that folder they'll load by default too. The user specific option is

如果您将 xlam 文件放入该文件夹，它们也会默认加载。用户特定的选项是

C:\Users[UserName]\AppData\Roaming\Microsoft\Excel\XLSTART

C:\Users[用户名]\AppData\Roaming\Microsoft\Excel\XLSTART

there are PROS/CONS to both models. One is will all users have access or just the user that installs. The benefit of the user specific option is it requires limited rights for installation (no admin privileges)

两种型号都有优点/缺点。一种是所有用户都可以访问，还是只有安装的用户才能访问。用户特定选项的好处是它需要有限的安装权限（无管理员权限）

回答by jerussell

As others have said, you can easily copy the files to the needed folder in each users Apps directory. They will then need to check the box in the Add-Ins menu, but at least the file will be there. I use this at work with some success by putting the Add-ins and .bat file on a shared drive that everybody has access to. You can then just provide a link to the .bat file which will copy the file from the shared drive to the person's computer in their Apps directory. Here's an example .bat that I use:

正如其他人所说，您可以轻松地将文件复制到每个用户 Apps 目录中所需的文件夹。然后他们需要选中“加载项”菜单中的框，但至少文件会在那里。我通过将加载项和 .bat 文件放在每个人都可以访问的共享驱动器上，在工作中取得了一些成功。然后，您只需提供一个指向 .bat 文件的链接，该文件就会将该文件从共享驱动器复制到该人的计算机的 Apps 目录中。这是我使用的示例 .bat：

xcopy "\server\share\folder\Addins\CRWScleanup.xlam" "%APPDATA%\Microsoft\AddIns\" /y

Initially I had the .bat files setup to detect and handle Win7 and WinXP because we have a mix at work, but then I realized that the folder location after %APPDATA% (which leads to a different place for XP vs Win7) is the same for each version of Windows. I.e. \Microsoft\AddIns\ %APPDATA% is a global Windows variable and its value will vary for each user (that is good).

最初我设置了 .bat 文件来检测和处理 Win7 和 WinXP，因为我们在工作中混合使用，但后来我意识到 %APPDATA% 之后的文件夹位置（导致 XP 与 Win7 的不同位置）是相同的对于每个版本的 Windows。即 \Microsoft\AddIns\ %APPDATA% 是一个全局 Windows 变量，它的值因每个用户而异（这很好）。

It works brilliantly once the Add-In is installed because to update it all I have to do is put the new version on the shared drive and have the user click the link to the .bat while Excel is closed. The new version gets copied over and the user doesn't have to do anything.

一旦安装了插件，它就可以很好地工作，因为要更新它，我所要做的就是将新版本放在共享驱动器上，并让用户在 Excel 关闭时单击指向 .bat 的链接。新版本被复制，用户无需做任何事情。

回答by Renier Wessels

I researched a lot of the methods described above and on other sites, but have managed to create my own install and uninstall using Excel add-ins (*.xlam) themselves. My add-ins now install themselves the first time and I have an uninstallation script as well. Works like a charm.

我研究了很多上述和其他网站上描述的方法，但已经设法使用 Excel 加载项 (*.xlam) 自己创建了我自己的安装和卸载。我的加载项现在第一次安装，我也有一个卸载脚本。奇迹般有效。

I used my own variation and derivations of Ivan's Solutions': https://grishagin.com/vba/2017/01/11/automatic-excel-addin-installation.html

我使用了我自己的 Ivan 解决方案的变体和派生：https: //grishagin.com/vba/2017/01/11/automatic-excel-addin-installation.html

回答by varocarbas

With VBA you have to rely on Office files (Excel ones, in this case) and cannot move to executables/installation packages. You might create a program (or a macro) performing the steps you want in an automated way. But if what you want is relying on a standard installation package, which the user might execute (as usual, when installing a program), you would have to move to VB.NET.

使用 VBA，您必须依赖 Office 文件（在本例中为 Excel 文件）并且无法移动到可执行文件/安装包。您可以创建一个程序（或宏）以自动方式执行您想要的步骤。但是，如果您想要依赖于用户可能会执行的标准安装包（像往常一样，在安装程序时），您将不得不转移到 VB.NET。

VB.NET and VBA are not too different (well... actually, VB.NET includes many more things, but "understands" most of the VBA code) and VB.NET is quite programmer friendly; so a conversion from VBA to VB.NET wouldn't take you too long. In VB.NET you have different ways to interact with Excel; from your question, I understand that you want the Add-in alternative: it generates a custom "installation package" which, once clicked, will install the give Excel Add-in in the target computer. Relying on this option is easy: in your Visual Studio (you need a VS to work with VB.NET), open "New Project" and, within the Visual Basic Templates, select Office (, your version) and Excel Add-in.

VB.NET 和 VBA 并没有太大区别（嗯……实际上，VB.NET 包括更多的东西，但“理解”了大部分 VBA 代码）并且 VB.NET 对程序员非常友好；所以从 VBA 到 VB.NET 的转换不会花费你太长时间。在 VB.NET 中，您有多种与 Excel 交互的方式；从您的问题中，我了解到您需要外接程序替代方案：它会生成一个自定义的“安装包”，单击该包后，将在目标计算机中安装给定的 Excel 外接程序。依赖此选项很容易：在您的 Visual Studio（您需要一个 VS 才能使用 VB.NET），打开“新项目”，然后在 Visual Basic 模板中，选择 Office（您的版本）和 Excel 插件。

NOTE: useful linkprovided my Mehow: it refers to an old VS (2008) but things haven't changed too much since then.

注意：我的 Mehow 提供了有用的链接：它指的是旧的 VS (2008)，但从那时起事情并没有太大变化。

NOTE 2: the aforementioned suggestion is available in any fee-based VS version since the 2008 one. Not sure about the support in free versions (Express ones).

注 2：上述建议适用于自 2008 年以来的任何收费 VS 版本。不确定免费版本（Express 版本）是否支持。

回答by David wyatt

The easiest way would be to add some install code to the xlsmfile on open.
This code could then do the following:

最简单的方法是xlsm在打开的文件中添加一些安装代码。
此代码然后可以执行以下操作：

• Turn off the install code by changing a variable on sheet (this will stop the on open code running next time the file opens);
• Save a copy of the file as an xlamto the add-in folder;
• Activate the add-in;
• Then close the xlsmfile.

• 通过更改工作表上的变量来关闭安装代码（这将在下次打开文件时停止运行打开的代码）；
• 将文件的副本另存为xlam加载项文件夹；
• 激活插件；
• 然后关闭xlsm文件。

All you need to do is then email the xlsmfile out and ask people to open it.

您需要做的就是将xlsm文件通过电子邮件发送出去并要求人们打开它。

The code is quite simple, but I can show if you need.

代码非常简单，但如果您需要，我可以显示。