Techniques for writing VBA code

Method 1: Use VBA's original properties, methods, and worksheet functions as much as possible

Because the Excel object is up to odd, the object's properties, methods, and events are numerous and may not be fully understood by beginners, which results in a code snippet that programmers often write about the same functions as the properties and methods of Excel objects. The efficiency of these snippets is obviously different from the properties of the Excel object, and how quickly the method accomplishes the task. For example, use the property currentregion of range to return a Range object that represents the current area. (the current area refers to an area that is bounded by any blank row and a combination of blank columns.) The same functionality requires dozens of lines of VBA code. Therefore, you should learn as much as you can about the properties and methods of Excel objects before you program.

Making full use of the worksheet function is an extremely effective way to improve the running speed of the program. Examples of average wage:

For each C in worksheet (1). Range (″a1:a1000″)

Totalvalue = Totalvalue + c.value

Next

Averagevalue = Totalvalue/worksheet (1). Range (″a1:a1000″). Rows.Count

The following code program is much faster than the example above:

Averagevalue=application.worksheetfunction.average (Worksheets (1). Range (″a1:a1000″))

Other functions such as count,counta,countif,match,lookup and so on, can replace the same function of VBA program code, improve the running speed of the program.

Method 2: Minimize the use of object references, especially in loops

The properties of each Excel object, the invocation of the method, requires one or more calls through the OLE interface, all of which take time, and reducing the use of object references can speed up the operation of VBA code.

For example 1. Use the WITH statement.

Workbooks (1). Sheets (1). Range (″a1:a1000″). Font.name=″pay″

Workbooks (1). Sheets (1). Range (″a1:a1000″). Font.fontstyle=″bold″ ...

The following statement is faster than the above

With workbooks (1). Sheets (1). Range (″a1:a1000″). Font

Name =″pay″

. FontStyle =″bold″ ...

End With

2. Using Object variables

If you find that an object reference is used more than once, you can set this object to an object variable with set to reduce access to the object. Such as:

Workbooks (1). Sheets (1). Range (″a1″). Value = 100

Workbooks (1). Sheets (1). Range (″a2″). Value = 200

The following code is faster than the above:

Set mysheet = Workbooks (1). Sheets (1)

Mysheet.range (″a1″). Value = 100

Mysheet.range (″a2″). Value = 200

3. In the cycle to minimize the access to objects.

For k = 1 to 1000

Sheets (″sheet1″). Select

Cells (k,1). Value = Cells (1,1). Value

Next K

The following code is faster than the above:

Set thevalue = Cells (1,1). Value

Sheets (″sheet1″). Select

For k = 1 to 1000

Cells (k,1). Value = Thevalue

Next K

Method 3: Reduce the activation and selection of objects

If you are recording macros to learn VBA, your VBA program must be full of object activation and selection, such as

Workbooks (XXX). Activate, Sheets (XXX). Select, Range (XXX). Select, but in fact these operations are not required in most cases. For example

Sheets (″sheet3″). Select

Range (″a1″). Value = 100

Range (″a2″). Value = 200

Could read:

With Sheets (″sheet3″)

. Range (″a1″). Value = 100

. Range (″a2″). Value = 200

End With

Method 4: Turn off screen updates

If your VBA program is worse than the previous three, turn off screen update is the most efficient way to improve the speed of the VBA program to shorten the running time of about 2/3. How to turn off screen updates:

Application.screenupdate = False

Do not forget to set this value back when the VBA program is run at the end:

Application.screenupdate = True