Tăng tốc cho code VBA của bạn

[levanduyet]

1. Tắt các chế độ cập nhật màn hình và các chế độ khác để tăng tốc thực hiện code

Option Explicit 
Public glb_origCalculationMode As Integer 
Sub [B][COLOR="Red"]SpeedOn[/COLOR][/B](Optional StatusBarMsg As String = "Running macro...") 
' [U]Chú ý[/U]: [COLOR="blue"]ở đây các bạn có thể thay đổi thông báo [/COLOR]
'           [COLOR="blue"]bằng cách truyền vào chuổi UNICODE vào biến StatusBarMsg[/COLOR]
   glb_origCalculationMode = Application.Calculation 
   With Application 
      .Calculation = xlCalculationManual 
      .ScreenUpdating = False 
      .EnableEvents = False 
      .DisplayAlerts = False 
      .Cursor = xlWait 
      .StatusBar = StatusBarMsg 
      .EnableCancelKey = xlErrorHandler 
   End With 
End Sub 
Sub [B][COLOR="Red"]SpeedOff[/COLOR][/B]() 
   With Application 
      .Calculation = glb_origCalculationMode 
      .ScreenUpdating = True 
      .EnableEvents = True 
      .DisplayAlerts = True 
      .CalculateBeforeSave = True 
      .Cursor = xlDefault 
      .StatusBar = False 
      .EnableCancelKey = xlInterrupt 
   End With 
End Sub 
Sub [COLOR="Blue"][B]FillSlow[/B][/COLOR]() 
   Dim c As Range, r As Range, startTime, EndTime 
   Set r = Range("A1:C1000") 
   r.ClearContents 
   startTime = Timer 
   For Each c In r 
      c.Select 
      c.Formula = "=Row()*Column()" 
   Next c 
   DoEvents 
   EndTime = Timer 
   MsgBox "Total Time: " & EndTime - startTime 
   [A1].Select 
End Sub 
Sub [B][COLOR="Blue"]FillFast[/COLOR][/B]() 
   Dim c As Range, r As Range, startTime, EndTime 
   Set r = Range("A1:C1000") 
   r.ClearContents 
   startTime = Timer 
   On Error Goto ResetSpeed 
   SpeedOn 
   For Each c In r 
      c.Select 
      c.Formula = "=Row()*Column()" 
   Next c 
   DoEvents 
   EndTime = Timer 
   MsgBox "Total Time: " & EndTime - startTime 
   [A1].Select 
ResetSpeed: 
   SpeedOff 
End Sub

Nguồn từ đây.

Cách dùng:
Xin xem ví dụ FillSlow và FillFast ở trên.
Bạn sẽ gọi thủ tục để tăng tốc khi thực hiện các đoạn mã bằng cách gọi thủ tục SpeedOn và gọi lại thủ tục SpeedOff mỗi khi bạn đã hoàn tất.

Chú ý:
Nếu các đoạn mã của bạn có sử dụng kết quả tính toán của một công thức, thì bạn nên xem xét hoặc chỉnh sửa các thủ tục ở trên cho phù hợp với công việc của mình.

Lê Văn Duyệt

 

[levanduyet]

2. Truy cập đến một ô trong vùng - Accessing Cells In A Range

Giả sử tôi có dữ liệu tại Sheet1, vùng

A1:B10

, và tôi đặt tên là "MyRange", tôi muốn tham chiếu đến ô B5:

Làm thế nào để tham chiếu đến các ô trong (mang tính tương đối, sẽ giải thích ở phần sau) vùng dữ liệu A1:B10 (tên MyRange) là nhanh nhất?

Câu trả lời:
Thay vì tham chiếu

Range("MyRange").Cells(5,2)

ta dùng

Range("MyRange")(5,2)

Ngoài ra khi tham chiếu đến ô A1, các bạn có thể dùng code
Cách 1:

Range("A1")

hoặc
Cách 2:

[A1]

Trong hai cách ở trên, cách 2 ngắn gọn hơn nhưng sẽ chậm hơn cách 1.


Nói thêm về tham chiếu kiểu này:

Range("A1:B10")(5,2) ' Tham chiếu đến ô B5
Range("A1:B10")(1,1) ' Tham chiếu đến ô A1
Range("B2:C10")(0, 0) ' Tham chiếu đến ô A1, các bạn chú ý !
Range("C3:D10")(-1, -1) ' Tham chiếu đến ô A1, các bạn chú ý !
Range("A1:B10")(5,2)(5,2) ' Tham chiếu đến ô C9
Range("A1:B10")(5,3) ' Tham chiếu đến ô C5
Range("A1:B10")(12,13) ' Tham chiếu đến ô M12

Range("A1:B10")(1) ' Tham chiếu đến ô A1
Range("A1:B10")(2) ' Tham chiếu đến ô B1
Range("A1:B10")(3) ' Tham chiếu đến ô A2
Range("A1:B10")(4) ' Tham chiếu đến ô B2

Việc tham chiếu khi dùng một số (Range("A1:B10")(3)) sẽ đi từ trái qua phải, sau đó xuống một hàng và lại tiếp tục từ trái qua phải.

Di chuyển theo hàng:

Range("D4")(1) ' Tham chiếu đến ô D4
Range("D4")(2) ' Tham chiếu đến ô D5
Range("D4")(3) ' Tham chiếu đến ô D6

Di chuyển theo cột:

Range("D4").Columns(1) ' Tham chiếu đến ô D4
Range("D4").Columns(2) ' Tham chiếu đến ô E4
Range("D4").Columns(3) ' Tham chiếu đến ô F4

Các bạn có thể đọc thêm bài viết về các kiểu tham chiếu tại đây.

3. Khi tham chiếu đến một sheet - Collection Indexes:

Khi tham chiếu đến một sheet bạn có thể tham chiếu bởi Tên hoặc Số như,
Cách 1:

Worksheets("Sheet1")

Cách 2:

Worksheets(1)

Tham chiếu ở cách 2 sẽ nhanh hơn. Nhưng chú ý, số tham chiếu này có thể sẽ thay đổi, chính vì điều này để an toàn thì tham chiếu cách một vẫn an toàn hơn và dễ thay đổi hơn.

4. Khai báo sớm - Early Binding:

Đôi khi khi lập trình chúng ta sẽ tham chiếu đến một số đối tượng thì việc khai báo sớm (Early Binding: có thể hiểu là tường minh, vì nếu bạn khai báo là Object (đối tượng), thì chương trình phải mất thời gian để dò tìm) sẽ nhanh hơn.
Ví dụ sau tôi tham chiếu đến đối tượng Word:
Thì khai báo sau

Dim WordObj As Word.Application

sẽ giúp thực thi nhanh hơn

Dim WordObj As Object

5. Chú ý khi dùng vòng lập FOR EACH:
Bạn nên dùng vòng lập FOR EACH như sau:

Dim WS as Worksheet
For Each WS In Worksheets
    MsgBox WS.Name
Next WS

thay vì dùng

Dim i as Integer
For i = 1 To Worksheets.Count
    MsgBox Worksheets(i).Name
Next i

6. Tránh sử dụng SELECT:
Giả sử tôi có nhiều shape trên một sheet, tôi muốn đưa chữ "Hello" vào các shape này.

Thay vì sử dụng SELECT

For i = 0 To ActiveSheet.Shapes.Count
       ActiveSheet.Shapes(i).[COLOR="blue"]Select[/COLOR]
       Selection.Text = "Hello"
Next i

thif tôi dùng trực tiếp như sau, sẽ nhanh hơn

For i = 0 To ActiveSheet.Shapes.Count
       ActiveSheet.Shapes(i).TextEffect.Text = "Hello"
Next i

7. Đọc và ghi trên một khối dữ liệu lớn :
Vấn đề này, người viết cũng đã từng gặp, nhất là khi phải làm việc với khối dữ liệu lớn. Ví dụ như: khi viết chương trình lấy dữ liệu từ tập tin Excel từ Visual Basic 6.
Khi viết code, bạn cần chú ý làm giảm thiểu việc truyền dữ liệu từ Excel và code của bạn.
Code 1:

Dim DataRange  As Range
Dim Irow       As Long
Dim Icol       As Integer
Dim MyVar      As Double
Set DataRange = Range("A1:C10000")

For Irow = 1 To 10000
    For Icol = 1 To 3
        MyVar = DataRange(Irow, Icol)                 ' Đọc giá trị từ Excel
        If MyVar > 0 Then
            MyVar = MyVar * MyVar                     ' Thay đổi các giá trị
            DataRange(Irow, Icol) = MyVar             ' Đưa các giá trị ngược vào Excel
        End If
    Next Icol
Next Irow

Code 2:

Dim DataRange  As Variant
Dim Irow       As Long
Dim Icol       As Integer
Dim MyVar      As Double
DataRange = Range("A1:C10000").Value                  ' Đọc tất cả các giá trị từ Excel một lần, và đưa vào mảng

For Irow = 1 To 10000
    For Icol = 1 To 3
        MyVar = DataRange(Irow, Icol)
        If MyVar > 0 Then
            MyVar = MyVar * MyVar                     ' Thay đổi các giá trị trong mảng
            DataRange(Irow, Icol) = MyVar
        End If
    Next Icol
Next Irow
Range("A1:C10000").Value = DataRange                  ' Đưa lại các giá trị từ mảng vào Excel một lần

Code 2 sẽ nhanh hơn. Vậy các bạn chú ý các code sau:

DataRange = Range("A1:C10000").Value

Range("A1:C10000").Value = DataRange

và khai báo biến là kiểu Variant

Dim DataRange  As Variant

sẽ làm cho chương trình của bạn nhanh hơn.

Đôi khi người ta còn dùng hàm sau (cũng chỉ là vấn đề tôi đã nói ở trên)

Function ExcelToVBA(Rng As Range)
    Dim Arr    As Variant
    Arr = Rng.Value
End Function

Ngoài ra, các bạn nên tham khảo topic bàn về cách làm việc với mảng tại đây.

8. Sử dụng các hàm sẵn có của Excel:

Nên dùng các hàm sẵn có của Excel. Ví dụ sau dùng hàm SUM cho vùng A1:A100

MySum = Application.WorksheetFunction.Sum(Range("A1:A100"))

Bạn đừng viết lại code như sau:

For Each C In Range("A1:A100")
    MySum = MySum + C.Value
Next C

9. Tránh sử dụng Application.Volatile khi có thể

10. Tránh sử dụng OFFSET, nên dùng giống như mục 2

Tổng hợp.
Lê Văn Duyệt

 

[levanduyet]

11. Sử dụng các kiểu dữ liệu phù hợp với nhau:
VBA quá dễ giải, chính vì thế mà đôi khi trong các đoạn mã chúng ta lại đi so sánh hai biến không hề giống nhau. Để tránh những lỗi đáng tiếc xãy ra, trước khi truyền biến cho một thủ tục, hay gán một biến cho một biến khác, luôn luôn phải chắc chắn rằng các kiểu biến của chúng phải giống nhau.

12. Thực hiện chuyển đổi trực tiếp, thay vì phải để VBA tự quyết định:
Khi hai biến không cùng kiểu dữ liệu, bạn nên thực hiện việc chuyển đổi kiểu dữ liệu như CStr(), CDbl() vv... Bằng việc thực hiện này, bạn sẽ giúp tiết kiệm thời gian thay vì phải để VBA đưa ra quyết định.

13. Sử dụng Len(chuỗi)=0 thay vì dùng chuỗi ="":
VBA lưu trữ chuổi trong bộ nhớ bằng việc lưu chiều dài của chuổi, được theo sau bởi các ký tự chuỗi này chứa.
Chính vì vậy việc so sánh chiều dài của chuổi như Len(chuỗi)=0 sẽ nhanh hơn.

14. Sử dụng Left$, Right$, Mid$ ... thay vì dùng Left, Right và Mid