编码Excel VBA宏的十个技巧

亦凉 2022-12-06 15:40 179阅读 0赞

Ten commonsense suggestions to make coding Excel VBA faster and easier. These tips are based on Excel 2010 (but they work in nearly all versions) and many were inspired by the O'Reilly book "Excel 2010 - The Missing Manual" by Matthew MacDonald.

十个常识性建议，可以使编写Excel VBA更快，更轻松。 这些技巧基于Excel 2010(但它们几乎可以在所有版本中使用)，并且许多技巧都是由Matthew MacDonald撰写的O'Reilly书“ Excel 2010-The Missing Manual”所启发。

**1 -** Always test your macros in a throwaway test spreadsheet, usually a copy of one that it's designed to work with. Undo doesn't work with macros, so if you code a macro that folds, spindles, and mutilates your spreadsheet, you're outta luck unless you have followed this tip.

**1-**始终在一次性测试电子表格中测试宏，通常是设计用来处理的宏的副本。 撤消不适用于宏，因此，如果您编写了一个折叠，旋转和破坏电子表格的宏，除非您遵循了此技巧，否则您将失去运气。

**2 -** Using shortcut keys can be dangerous because Excel doesn’t warn you if you choose a shortcut key that Excel is already using. If this happens, Excel uses the shortcut key for the macro, not the built-in shortcut key. Think about how surprised your boss will be when he loads your macro and then Ctrl-C adds a random [number][] to half the cells in his spreadsheet.

**2-**使用快捷键可能很危险，因为如果您选择Excel已经使用的快捷键，Excel不会警告您。 如果发生这种情况，Excel将使用宏的快捷键，而不是内置的快捷键。 想一想您的老板在加载宏后，然后按Ctrl-C将随机[数][number]添加到电子表格中一半的单元格时会感到多么惊讶。

Matthew MacDonald makes this suggestion in "Excel 2010 - The Missing Manual."

Matthew MacDonald在“ Excel 2010-The Missing Manual”中提出了这一建议。

Here are some common key combinations that you should never assign to macro shortcuts because people use them too frequently:

以下是一些您不应该分配给宏快捷方式的常用组合键，因为人们经常使用它们：

*  Ctrl+S (Save)
    
    Ctrl + S(保存)
 *  Ctrl+P (Print)
    
    Ctrl + P(打印)
 *  Ctrl+O (Open)
    
    Ctrl + O(打开)
 *  Ctrl+N (New)
    
    Ctrl + N(新)
 *  Ctrl+X (Exit)
    
    Ctrl + X(退出)
 *  Ctrl+Z (Undo)
    
    Ctrl + Z(撤消)
 *  Ctrl+Y (Redo/Repeat)
    
    Ctrl + Y(重做/重复)
 *  Ctrl+C (Copy)
    
    Ctrl + C(复制)
 *  Ctrl+X (Cut)
    
    Ctrl + X(剪切)
 *  Ctrl+V (Paste)
    
    Ctrl + V(粘贴)

To avoid problems, always use Ctrl+Shift+letter macro key combinations, because these combinations are much less common than the Ctrl+letter shortcut keys. And if you’re in doubt, don’t assign a shortcut key when you create a new, untested macro.

为了避免出现问题，请始终使用Ctrl + Shift +字母宏组合键，因为这些组合比Ctrl +字母快捷键少见。 如果您有疑问，请在创建新的未经测试的宏时不要分配快捷键。

**3 -** Can't remember Alt-F8 (the default macro shortcut)? Do the names mean nothing to you? Since Excel will make macros in any opened workbook available to every other workbook that’s currently open, the easy way is to build your own macro library with all of your macros in a separate workbook. Open that workbook along with your other spreadsheets. As Matthew puts it, "Imagine you’re editing a workbook named SalesReport.xlsx, and you open another workbook named MyMacroCollection.xlsm, which contains a few useful macros. You can use the macros contained in MyMacroCollection.xlsm with SalesReport.xlsx without a hitch." Matthew says this design makes it easy to share and reuse macros across workbooks (and between different people).

**3-**不记得Alt-F8(默认的宏快捷方式)了吗？ 这些名字对您没有任何意义吗？ 由于Excel将使任何打开的工作簿中的宏都可用于当前打开的其他每个工作簿，因此简单的方法是使用所有单独的工作簿中的宏来构建自己的宏库。 与其他电子表格一起打开该工作簿。 正如Matthew所说：“假设您正在编辑一个名为SalesReport.xlsx的工作簿，然后打开另一个名为MyMacroCollection.xlsm的工作簿，其中包含一些有用的宏。您可以将MyMacroCollection.xlsm中包含的宏与SalesReport.xlsx一起使用，而无需一会儿。” Matthew说，这种设计使跨工作簿(以及不同人员之间)共享和重用宏变得容易。

**4 -** And consider adding buttons to link to the macros in the worksheet that contains your macro library. You can arrange the buttons in any functional groupings that make sense to you and add text to the worksheet to explain what they do. You'll never wonder what a cryptically named macro actually does again.

**4-**考虑添加按钮以链接到包含您的宏库的工作表中的宏。 您可以将按钮按您认为合适的任何功能分组，并在工作表中添加文本以说明它们的作用。 您永远都不会怀疑隐式命名的宏又会做什么。

**5 -** Microsoft's new macro security architecture has been improved a lot, but it's even more convenient to tell Excel to trust the files in certain folders on your computer (or on other computers). Pick a specific folder on your hard drive as a trusted location. If you open a workbook stored in this location, it’s automatically trusted.

**5-** Microsoft的新宏安全体系结构已得到很大改进，但告诉Excel信任计算机(或其他计算机)上某些文件夹中的文件更加方便。 在硬盘驱动器上选择一个特定的文件夹作为受信任的位置。 如果打开存储在此位置的工作簿，则该工作簿将被自动信任。

**6 -** When you're coding a macro, don't try to build cell selection into the macro. Instead, assume that the cells that the macro will use have been pre-selected. It's easy for you to drag the mouse over the cells to select them. Coding a macro that is flexible enough to do the same thing is likely to be full of bugs and hard to program. If you want to program anything, try to figure out how to write validation code to check whether an appropriate selection has been made in the macro instead.

**6-**在对宏进行编码时，请勿尝试在宏中构建单元格选择。 而是假定宏将使用的单元格已预先选择。 您很容易将鼠标拖到单元格上以选择它们。 编码足够灵活以执行同一操作的宏可能充满了错误并且难以编程。 如果要编写任何程序，请尝试弄清楚如何编写验证代码，以检查是否已在宏中进行了适当的选择。

**7 -** You might think that Excel runs a macro against the workbook that contains the macro code, but this isn’t always true. Excel runs the macro in the *active workbook*. That's the workbook that you looked at most recently. As Matthew explains it, "If you have two workbooks open and you use the Windows taskbar to switch to the second workbook, and then back to the Visual Basic editor, Excel runs the macro on the second workbook."

**7-**您可能会认为Excel针对包含宏代码的工作簿运行了一个宏，但这并不总是正确的。 Excel在*活动工作簿中*运行宏。 那是您最近查看的工作簿。 正如Matthew解释的那样：“如果您打开了两个工作簿，并且使用Windows任务栏切换到第二个工作簿，然后又回到Visual Basic编辑器，则Excel将在第二个工作簿上运行宏。”

**8 -** Matthew suggests that, "For easier macro coding, try to arrange your windows so you can see the Excel window and the Visual Basic editor window at the same time, side-by-side." But Excel won't do it, (Arrange All on the View menu only arranges the Workbooks. Visual Basic is considered a different application window by Excel.) But Windows will. In Vista, close all but the two you want to arrange and right-click the Taskbar; select "Show Windows Side by Side". In Windows 7, use the "Snap" feature. (Search online for "Windows 7 features Snap" for instructions.)

**8-**马修建议：“为简化宏编码，请尝试排列窗口，以便可以同时并排查看Excel窗口和Visual Basic编辑器窗口。” 但是Excel不会这样做((“视图”菜单上的“全部排列”仅是安排工作簿。VisualBasic被Excel认为是不同的应用程序窗口。)但是Windows可以。 在Vista中，关闭除要排列的两个以外的所有内容，然后右键单击任务栏； 选择“并排显示Windows”。 在Windows 7中，使用“捕捉”功能。 (在线搜索“ Windows 7功能快照”以获取说明。)

**9 -** Matthew's top tip: "Many programmers find long walks on the beach or guzzling a jug of Mountain Dew a helpful way to clear their heads."

**9-**马修(Matthew)的最高提示：“许多程序员发现在沙滩上长途跋涉或向一罐Mountain Dew饮酒是清理头部的有用方法。”

*And of course, the mother of all VBA tips:*

*当然，所有VBA技巧之母：*

**10 -** The first thing to try when you can't think of the statements or keywords you need in your program code is to turn on the [macro recorder][] and do a bunch of operations that seem to be similar. Then examine the generated code. It won't always point you to the right thing, but it often does. At a minimum, it will give you a place to start looking.

**10-**当您无法想到程序代码中需要的语句或关键字时，要尝试的第一件事是打开[宏记录器，][macro recorder]并执行许多看起来相似的操作。 然后检查生成的代码。 它不会总是将您指向正确的事物，但是它经常可以。 至少，它将给您一个开始看的地方。

## 资源 **(** Source **)** ##

MacDonald, Matthew. "Excel 2010: The Missing Manual." 1 edition, O'Reilly Media, July 4, 2010.

麦克唐纳(Matthew)。 “ Excel 2010：缺少的手册。” 1版，O'Reilly Media，2010年7月4日。

> 翻译自: [https://www.thoughtco.com/tips-for-coding-excel-vba-macros-3424201][https_www.thoughtco.com_tips-for-coding-excel-vba-macros-3424201]

[number]: https://www.thoughtco.com/convert-text-to-number-in-excel-3424223
[macro recorder]: https://www.thoughtco.com/learn-about-vba-3423992
[https_www.thoughtco.com_tips-for-coding-excel-vba-macros-3424201]: https://www.thoughtco.com/tips-for-coding-excel-vba-macros-3424201