excel vba代码大全_更改Excel VBA代码以提高速度

excel vba代码大全

In some of my files, macros run automatically when you select a cell on the worksheet. For example, if you've bought a copy of my Data Validation Multi-Select Premium (DVMSP) kit, it shows a pop-up list when you click on a cell with a drop down list.

在我的某些文件中，当您在工作表上选择一个单元格时，宏会自动运行。 例如，如果您购买了我的数据验证多选高级(DVMSP)套件的副本，则在单击带有下拉列表的单元格时，它将显示一个弹出列表。

I'll show you how to quickly change that, so the pop-up appears when you double-click, and you could make a similar change in other worksheet code.

我将向您展示如何快速进行更改，因此，当您双击该弹出窗口时，您可以在其他工作表代码中进行类似的更改。

使用双击 (Use a Double-Click)

If you find that the DVMSP code is slow in a big workbook, or if you don't want to see the pop-up list as you move around the workbook, you can make a simple change to its code.

如果发现大型工作簿中的DVMSP代码运行缓慢，或者在工作簿中移动时不希望看到弹出列表，则可以对其代码进行简单的更改。

Instead of showing the pop-up list when a cell is selected, change the code to run when a cell is double-clicked. That should speed things up a bit, and the pop-up list will only appear when you want it to.

选中单元格时，不用显示弹出列表，而是将代码更改为在双击单元格时运行。 这样可以加快速度，并且仅在您需要时才会显示弹出列表。

更改DVMSP代码 (Change the DVMSP Code)

To make the pop-up list appear on a double-click, follow these steps to make a couple of simple changes to the code:

要使弹出列表双击显示，请按照以下步骤对代码进行几个简单的更改：

• On the sheet where you pasted the code, right-click the sheet tab, and click View Code.
  在粘贴代码的工作表上，右键单击工作表选项卡，然后单击“查看代码”。
• At the top of the code module, click in the blank row above the Private Sub Worksheet_SelectionChange line
  在代码模块的顶部，单击Private Sub Worksheet_SelectionChange行上方的空白行。

• At the top right of the window, click the arrow in the Procedures drop down, and click on BeforeDoubleClick
  在窗口的右上角，单击“过程”下拉菜单中的箭头，然后单击“ BeforeDoubleClick”

• Select from the End Sub line, down to the Private Sub Worksheet_SelectionChange line
  从“结束子”行中选择，然后向下到“私人子工作表_选择更改”行
• Delete those lines
  删除这些行

• Scroll down a bit, to the first "Select Case Target.Column" section
  向下滚动到第一个“ Select Case Target.Column”部分
• Click in the blank line above that section
  单击该部分上方的空白行

• In the blank line, type: Cancel = True (that turns off the default double-click action, such as entering the cell to edit it)

  在空白行中，键入： Cancel = True (这将关闭默认的双击操作，例如输入要对其进行编辑的单元格)

• Then, click the Save button, at the top right of the Visual Basic Editor window.
  然后，单击“ Visual Basic编辑器”窗口右上方的“保存”按钮。

获取DVMSP套件 (Get the DVMSP Kit)

If you don't have a copy of my DVMSP kit, you can learn more about it here. You can add this feature to your workbooks, and then use them yourself, or share them with your co-workers. They'll be impressed!

如果没有我的DVMSP套件的副本，可以在此处了解更多信息 。 您可以将此功能添加到您的工作簿，然后自己使用它们，或与您的同事共享。 他们会留下深刻的印象！

了解有关Excel宏的更多信息 (Learn More About Excel Macros)

Closes Sunday, Feb. 7th – Would you like to learn how to save time with Excel macros? Take a look at the free Mini Course on Macros & VBA -- an excellent video series by Jon Acampora, from Excel Campus. Jon’s simple step-by-step approach makes it easy to follow along and learn, so get started now!

2月7日星期日关闭 –您想学习如何使用Excel宏节省时间吗？ 观看免费的Macros＆VBA迷你课程 -Excel Campus的Jon Acampora的精彩视频系列。 乔恩(Jon)的简单循序渐进方法使后续操作和学习变得容易，因此立即开始！

翻译自: https://contexturesblog.com/archives/2016/02/04/change-excel-vba-code-to-improve-speed/

excel vba代码大全