Improve the efficiency of Excel in VBA

Microsoft Office office suite as a wide range of applications, and continuously upgrade the software version, features continuous improvement platform office suite in the Office of the VBA to develop more and more applications, and VBA is a macro language, the operating speed There are very limited. VBA programming method thus directly related to the efficiency of VBA program is running, the paper cited a number of VBA procedures to improve operating efficiency.


Method 1: Try to use the original VBA properties, methods, and the Worksheet Functions


As many as hundred or more Excel objects, object properties, methods, events and more numerous, for starters, they may not all understand, which often resulted in the preparation of programming and Excel object properties, methods, functions the same VBA code, and the operating efficiency of the code is clear and Excel object properties, methods, task speed vary considerably. For example, with the Range property to return CurrentRegion Range object that represents the current area. (Refer to the current area any blank lines and blank columns on the composition of the border area). VBA code for the same functions to be dozens of lines. Therefore, before programming Excel objects as much as possible to understand the properties and methods.


Worksheet function is to increase take full advantage of the speed run extremely effective method. If the average wage demand example:


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 example code much faster than the above procedures:

AverageValue = Application.WorksheetFunction.Average (Worksheets (1). Range ("A1: A1000"))

Other functions such as Count, Counta, Countif, Match, Lookup, etc., can replace the VBA code the same functionality to improve the running speed.


Method 2: minimize the use of object references, especially in the loop


The properties of each Excel object, method calls through the OLE interface requires one or more calls, the OLE calls takes time, reduce the use of object references to speed up VBA code to run. For example,


1. Use 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 the object variable.

If you find an object reference to be used multiple times, then you can use this object to set the object variable 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 loop to minimize access to the object.

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 object's activation and selection


If you have to learn VBA by recording a macro, then your VBA program in a certain object activation and full of options, such as Workbooks (XXX). Activate, Sheets (XXX). Select, Range (XXX). Select and so on, but In fact in most cases these operations are not required. For example,


Sheets ("Sheet3"). Select

Range ("A1"). Value = 100

Range ("A2"). Value = 200

Can be changed to:

With Sheets ("Sheet3")

. Range ("A1"). Value = 100

. Range ("A2"). Value = 200

End With


Method 4: Turn off screen updates


If your VBA procedures done in front of three relatively poor, then close the VBA program is to increase the screen update speed of the most effective way to shorten the running time of 2 / 3. Way off the screen updates:

Application.ScreenUpdate = False

Please do not forget the end of VBA and then run back to set the value:

Application.ScreenUpdate = True


VBA is to improve the operating efficiency over the more effective of several methods.