VBA体系结构技巧-宏打包 [英] VBA Architecture Tips - Macro packaging

问题描述

我拼凑了一个Excel的概念证明，以从数据库中获取数据，并且需要将其打包以便可以将其分发给我们的客户.我的第一个尝试只是将我的所有代码都放入一个代码模块中，但是随后在Excel中，当我真的只想要列表中的主要模块时，便可以看到宏列表中的所有模块.我猜想我需要将不直接运行的子例程移到类模块中，以便将它们隐藏起来.还有其他关于如何打包宏代码的提示，以便更轻松，更安全地提供给客户吗?

I hacked together a proof of concept for Excel to get data from a database and need to package this so that it can be distributed to our customers. My first attempt was just to throw all my code into a code module, but then in Excel I can see all the modules in the macro list, when I really only want the main one in the list. I'm guessing that I need to move the subroutines that are not to be run directly into class modules so that they're hidden. Are there any other tips on how to package the macro code so it's simpler and safer to give to customers?

推荐答案

在宏列表中是否隐藏了Sub，这取决于它的范围(例如public，private).如果您将Sub设为私有，则它不会在宏"列表中列出.

Whether or not a Sub is hidden in the macro list is based on it's scope (e.g. public, private). If you make a Sub private, it will not be listed in the Macros list.

'This sub is public
Public Sub myPublicSub()
    MsgBox "hello world"
End Sub

'This sub is also public
Sub mySub()
    MsgBox "hello world"
End Sub

'This sub is private
Private Sub myPrivateSub()
    MsgBox "hello world"
End Sub

类模块用于创建类，这些类对于您的解决方案可能没有用或没有必要(如果代码按照您现在想要的方式运行).

Class modules are used to create classes, which are likely not useful or necessary for your solution (if the code operates the way you want it to now).

如果这是给客户的，我强烈建议打包为.xlam(Excel 2007+加载项)，并学习如何自定义功能区以包含用于宏的按钮.宏列表不是特别有用.(请参见 http://openxmldeveloper.org/archive/2006/05/25/CustomUIeditor.aspx 以获得免费的功能区XML编辑器附件"，请参见 http://www.rondebruin.nl/ribbon.htm (有关某些教程)

If this is for a customer I would strongly recommend packaging as a .xlam (Excel 2007+ Add-In) and learning how to customize the Ribbon to include a button for your macros. The macro list is not particularly usable. (see http://openxmldeveloper.org/archive/2006/05/25/CustomUIeditor.aspx for a free ribbon xml editor "Attachment(s)" at bottom of article. see http://www.rondebruin.nl/ribbon.htm for some tutorials)

为了保护您的代码免于查看/被盗，可以对VBA项目进行密码保护，但这不应被认为是坚如磐石的保护.

For protecting your code from being viewed/stolen, the VBA project can be password protected, but this should not be considered rock solid protection.

这篇关于VBA体系结构技巧-宏打包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！