将宏作为外接程序分发给其他用户 [英] Distributing macro as an Add-In to other users

问题描述

我正在寻找一些技巧，以解决如何将宏分发给公司中的其他用户的问题.

I am looking for some tips how to resolve a problem with distributing macro to other users in my company.

我创建了一个特定的工作簿，其中包含许多类型的数据，例如日期，字符串和数字.每个用户都必须管理相同类型的数据，因此我们将此工作簿用作模板.数据存储在一列中，我对其应用了条件格式和数据验证，以防止用户插入错误的值.该工作簿包含一些具有特定名称的工作表.该工作表的名称会出现在代码中–为每个工作表进行一些特定的计算(基于工作表的名称).

I’ve created a specific workbook which holds a bunch of various types of data such as dates, strings and numbers. Each of the users have to manage the same types of data, so we use this workbook as a template. Data is stored in a columns to which I’ve applied a conditional formatting and data validation to prevent users from inserting wrong values. This Workbook contains few Worksheets with specific names. Names of this worksheets appear in the code – to make some specific calculations for each one of them (based on the name of the worksheet).

问题是，将来我有可能希望对代码进行一些更改，例如使我的宏更高效或实施一些必要的更改.

The problem is that there is a probability that in the future I would like to make some changes to the code for example to make my macro more efficient or to implement some changes that are necessary.

我已经在互联网上进行搜索以找到最佳的解决方案，我认为最好的方法是创建一个Excel加载项.但是我对此有一些疑问.

I’ve searched through the internet to find the best solution and I think the best is to create an Add-In to Excel. But I’ve some questions about that.

1. 这真的是最好的解决方案吗?有人可以给我提示以更好的方式吗?

1. Is this really the best solution? Can someone give me a hint to make it in a better way?

如果加载项"是最好的，是否有办法将其仅添加到特定的工作簿(这是我的模板)中?

If Add-In is the best, is there a way to add this only to specific workbooks (which are my template)?

是否可以在有人打开特定工作簿时(使用Workbook_Open)安装加载项，并在工作簿关闭时(使用Workbook_BeforeClose)将其卸载.我一直在网上寻找答案，但是我不清楚这件事.如果可能的话，会影响关闭/打开工作簿的速度吗?

Is it possible to install Add-in when someone opens a specific workbook (using Workbook_Open) and uninstall it when workbooks is closing (using Workbook_BeforeClose). I’ve looked for answers on the web, but this matter is not clear to me. If this is possible, would it affect speed of the closing/opening workbooks?

感谢您的帮助/建议！

推荐答案

要通过共享驱动器与您自己的开发副本分发加载项，我强烈建议您阅读

For distributing an add-in through a shared drive with your own development copy I would highly suggest to read the following link. It has a description of the installation process, including a very important point that braX brought up in his answer - answering No when asked about copying to personal add-in folder.

以下是我用于保存加载项的链接中的调整后代码.它进入外接程序本身的常规模块.它保存一个加载项文件夹并将其属性设置为Read only，以便每当有人使用它时，它都不会被锁定.通过此设置，您可以随时运行此宏来更新文件，而无需追赶用户并使他们关闭Excel.

Below is an adjusted code from the link that I use to save add-ins. It goes into a normal module in the add-in itself. It saves an add-in a folder and sets its attribute to Read only so that whenever it is used by somebody it will not be locked. This setup allows you to update the file by running this macro at any time without needing to chase down users and get them to close Excel.

Private Sub Deploy()
Dim FolderOnSharedDrive as String
FolderOnSharedDrive = "Folder path to store add-in in here"
With ThisWorkbook
    On Error Resume Next
    SetAttr FolderOnSharedDrive  & .Name, vbNormal
    On Error GoTo 0
    .SaveCopyAs FolderOnSharedDrive  & .Name
    SetAttr FolderOnSharedDrive  & .Name, vbReadOnly
    MsgBox "Deploy Completed to " & FolderOnSharedDrive  & .Name
End With
End Sub

添加对文件路径的检查以及可能的一些错误处理是一个好主意. 关于您提出的其他一些问题:

It is a good idea to add checks for filepath and perhaps some error-handling. As to some other issues you bring up:

• 加载项可以包含不可见的工作表，可用于存储设置，路径等.

• 可以使加载项仅出现在特定的工作簿上，但需要一些努力:

• Add-ins can include worksheets that are not visible that you can use to store settings, paths, etc.

• It is possible to make add-in appear only on specific workbooks but it will take some effort:

1. 包括用于加载项的功能区，该功能区包括用于可见性的回调.只要特定的工作簿(模板)处于活动状态，请将其设置为可见.仍将在任何Excel实例中将其加载到内存中

1. Include a ribbon for an add-in that includes callback for visibility. Set it to visible whenever specific workbook(template) is active. It will still be loaded to memory in any Excel instance

您可以按照建议在打开时以编程方式添加加载项，并在关闭时以删除方式删除加载项，请检查此问题查看一些选项.

You can programatically add add-ins on open and remove on close as you suggested, check this question to see some options.

在关闭或打开状态下添加/删除加载项肯定会影响速度，但是我不能说它是否会引起注意.

Adding/removing add-ins on close or open will definitely affect the speed, but I cannot say if it will be noticeable.

这篇关于将宏作为外接程序分发给其他用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！