excel宏与vba示例_修改示例文件中的Excel VBA代码

excel宏与vba示例

On my website, there are hundreds of Excel sample files that you can download and use. Some of the files contain macros, and you might need to adjust those macros, to use them in your own files.

在我的网站上，可以下载和使用数百个Excel示例文件。 有些文件包含宏，您可能需要调整这些宏，以在自己的文件中使用它们。

There is a page on my Contextures site, that explains how to copy the sample code into your files, and where to paste it. There are written instructions, and a few videos, to help you get started.

我的Contextures网站上有一个页面，该页面说明了如何将示例代码复制到文件中以及将其粘贴到何处。 这里有书面说明和一些视频，可以帮助您入门。

更改目标列或行 (Change the Target Columns or Rows)

On that page, I’ve just added a new section, that shows how you can modify the code, so it runs on specific rows or columns.

在该页面上，我刚刚添加了一个新部分 ，显示了如何修改代码，使其在特定的行或列上运行。

For example, in the screen shot below, you can see some code from one of my Data Validation Multi Select files. There is an If…End If section, marked with red dots at the start and end.

例如，在下面的屏幕快照中，您可以从我的Data Validation Multi Select文件之一中看到一些代码。 有一个If…End If部分，在开始和结束处标有红点。

The If line checks the column number, for the cell where you made the change (the Target cell). If the column number is 3, the code in the If…End If section will run. For any other column, it skips that section of the code.

如果行检查列号，则为您进行更改的单元格(目标单元格)。 如果列号为3，则将运行If…End If部分中的代码。 对于任何其他列，它会跳过代码的该部分。

You can change the code so it runs on a different column, or multiple columns, or any column. There are code samples below.

您可以更改代码，使其在不同的列或多列或任何列上运行。 下面是代码示例。

NOTE: In all of these examples, you could use Row instead of Column, to limit the target to specific rows.

注意：在所有这些示例中，您可以使用Row而不是Column来将目标限制为特定的行。

更改列号 (Change the Column Number)

The simplest change is to use a different number in that line of code. For example, if you want the code to run when a cell in column E is changed, you could change the 3 to a 5.

最简单的更改是在该行代码中使用其他数字。 例如，如果您希望代码在E列中的单元格更改时运行，则可以将3更改为5。

If Target.Column = 5 Then

使用多个列号 (Use Multiple Column Numbers)

Instead of restricting the code to just one column, you can add more columns in the code. For example:

您可以在代码中添加更多列，而不是将代码限制为仅一列。 例如：

If Target.Column = 3 _
    Or Target.Column = 5 _
    Or Target.Column = 6 Then

删除列限制 (Remove the Column Limits)

If you don’t want to limit the code to a specific column, you could delete the two rows (If...End If) that are marked with red circles. In that case, the code will run for a change in every column.

如果您不想将代码限制为特定的列，则可以删除标有红色圆圈的两行(If ... End If)。 在这种情况下，代码将在每一列中运行以进行更改。

Be sure to delete both the If and the End If lines.

请务必同时删除。如果和结束时，如果线路。

排除特定的列号 (Exclude a Specific Column Number)

If you want code to run on any column EXCEPT a specific column, use the Not Equal To operator -- <> -- instead of the equal sign.

如果要让代码在除特定列之外的任何列上运行，请使用不等于运算符-<>-代替等号。

For example, this code will run after a change in any column, except column 2:

例如，此代码将在除第2列之外的任何其他列中发生更改后运行：

If Target.Column <> 2 Then

I hope that helps you get started with customizing code that you copy from sample files! _____________________

我希望这可以帮助您开始自定义从示例文件复制的代码！ _____________________

翻译自: https://contexturesblog.com/archives/2014/05/08/modify-excel-vba-code-in-sample-files/

excel宏与vba示例