Tăng tốc VBA

vbano1

SMod
Thành viên BQT
Không cập nhật màn hình, tắt chế độ tự động tính toán sẽ cho tốc độ tốt hơn
Trong VBA, việc đình chỉ cập nhật màn hình sẽ cho tốc độ rất nhanh. Khi chúng ta xử lý dữ liệu trên excel, chúng ta thấy màn hình cứ giật giật.
Việc thiết định hủy cập nhật màn hình sẽ tăng tốc xử lý của excel.
Mã:
Application.ScreenUpdating = False
'đoạn code xử lý dữ liệu
Application.ScreenUpdating = True
Tuy nhiên nó cũng có nhược điểm, người dùng không nhìn thấy tiến trình xử lý của VBA (vì màn hình đứng yên) thì có vẻ sẽ sốt ruột.
Do đó chúng ta cân nhắc, nếu việc thực thi quá mất thời gian, thì vẫn nên dừng cập nhật màn hình để tăng tốc xử lý.

Ngoài ra chúng ta còn có kìm chế sự kiện.
Khi macro xử lý dữ liệu, thay đổi giá trị trên cells, thì worksheet sẽ phải phát hiện sự kiện đó và xử lý. Việc kiềm chế sự kiện tức là không cho worksheet bắt sự kiện, sẽ làm cho macro thực thi nhanh hơn. Tuy nhiên cũng có vấn đề là, trong khi macro thực hiện xử lý dữ liệu thì các sự kiện nếu phát sinh sẽ không được xử lý. Nhưng thông thường, khi xử lý dữ liệu, chúng ta không mấy quan tâm tới việc này. Đây chỉ là điều chú ý nho nhỏ cho các bạn biết mà thôi.
Mã:
Application.EnableEvents = False
'đoạn code xử lý dữ liệu
Application.EnableEvents = True
Tiếp theo chúng ta nói tới việc tự động tính toán. Chẳng hạn như trên bảng tính có chứa các công thức chẳng hạn. Nhưng chú ý rằng, dù bảng tính không có chứa công thức, thì tính năng tự động tính toán vẫn hoạt động dò tìm xem có công thức nào hay không. Do đó, để tăng tốc độ xử lý của macro, chúng ta cũng nên tắt nó đi.
Mã:
Application.Calculation = xlCalculationManual
'đoạn code xử lý dữ liệu
Application.Calculation = xlCalculationAutomatic
Như vậy để tăng tốc độ xử lý của macro, ta sẽ xây dựng một hàm, gọi là Focus (nghĩa là tập trung), ý nghĩa là để cho macro tập trung làm công việc của mình mà không bị gây phiền hà bởi những sự kiện không cần thiết.
Mã:
Sub Focus(ByVal Flag As Boolean)
    With Application
        .EnableEvents = Not Flag
        .ScreenUpdating = Not Flag
        .Calculation = IIf(Flag, xlCalculationManual, xlCalculationAutomatic)
    End With
End Sub
Sau đây chúng ta sẽ cùng nhau kiểm chứng:
Mã:
Sub tangtocdo()
  
    Call Focus(True)
    t = Timer()
    For i = 1 To 255
        For j = 1 To 255
            With Sheet1.Cells(i, j)
                .Value = i * j
                .Interior.Color = RGB(i, j, Int(i + j / 2))
            End With
    Next j, i
    Debug.Print Timer() - t
    Call Focus(False)
End Sub
Chú ý là copy cả code của thủ tục Focus vào Module của các bạn nữa nhé.
Bây giờ tôi sẽ thực thi thủ tục tangtocdo với các tình huống như sau:
EnableEventsX
ScreenUpdatingX
CalculationX
Time7.31258.875448.882
Trong đó ○ nghĩa là tôi giữ nguyên nó trong thủ tục Focus. X nghĩa là tôi bỏ nó ra khỏi thủ tục Focus.
Các bạn thấy sự khác biệt về thời gian hoàn thành chưa?
Kết quả của code trên:
Bạn cần đăng nhập để thấy hình ảnh

Nguồn:
 

tuhocvba

Administrator
Thành viên BQT
Không để lại những thứ vô ích, đừng nghĩ rằng nguyên nhân code chậm là dừng màn hình hay chưa?

Tôi biết rất nhiều bạn sử dụng chức năng ghi code tự động, tuy nhiên cứ thế giữ nguyên code mà sử dụng thì sẽ làm cho tốc độ macro chậm đi đấy. Macro được ghi lại từ thao tác bằng tay không bao giờ cho tốc độ nhanh, do đó chúng ta cần phải bỏ đi những thứ không cần thiết.

Tôi ví dụ:

Mã:
○○.Select
Selection.××
Vậy thì tại sao chúng ta không viết trực tiếp là:

Mã:
 ○○.××
Tất nhiên sẽ có những người không hiểu gì và phản biện lại rằng, không được, cái này là ghi macro tạo ra, bây giờ ghi trực tiếp như trên, nếu xảy ra lỗi gì thì sao? Hầu hết trường hợp phát sinh lỗi không phải là do chúng ta xóa bỏ những thứ 「Select~Selection」. Nó là một nguyên nhân khác.

Chúng ta sẽ viết lại, và hãy lý giải nó thật rõ ràng ngay ở đây. Chúng ta biết rằng lệnh dừng màn hình sẽ cho tốc độ xử lý macro nhanh hơn. Tuy nhiên nếu như quá trình xử lý không thay đổi cập nhật màn hình, thì việc dừng màn hình cũng chẳng có ý nghĩa gì cả. Ngay bây giờ chúng ta sẽ kiểm chứng điều này.

Chúng ta thực hiện lệnh copy từ sheet 1 samg sheet 2. Ở Test1 tôi không sử dụng lệnh dừng màn hình. Ở Test2 tôi cho dừng cập nhật màn hình.

Mã:
Sub test1()

    Dim i As Long

    For i = 1 To 100

        Cells(i, 1).Select

        Selection.Copy

        Sheets("Sheet2").Select

        Cells(i, 1).Select

        ActiveSheet.Paste

        Sheets("Sheet1").Select

    Next i

End Sub
Mã:
Sub test2()

    Dim i As Long

    Application.ScreenUpdating = False

    For i = 1 To 100

        Cells(i, 1).Select

        Selection.Copy

        Sheets("Sheet2").Select

        Cells(i, 1).Select

        ActiveSheet.Paste

        Sheets("Sheet1").Select

    Next i

    Application.ScreenUpdating = True

End Sub
Và đây là kết quả chạy 10 lần thử nghiệm.

Bảng 1:
Lầntest1test2% (test2/test1)
110.1873.20331.40%
29.8754.70447.60%
310.1414.7546.80%
410.3434.76546.10%
511.5794.79741.40%
610.6714.79745.00%
79.7924.65647.50%
810.7425.14147.90%
911.0315.10946.30%
1010.5414.70344.60%
Trung bình10.494.66344.50%
Quả nhiên là tốc độ macro có sự khác biệt khi cho dừng cập nhật màn hình. Nhưng cứ mỗi lần chúng ta Select thì thật buồn cười. Nhìn vào kết quả này, đừng nghĩ rằng việc dừng màn hình cho tốc độ nhanh, chúng ta sẽ sang bước kiểm chứng tiếp theo.

Mã:
Sub test3()

    Dim i As Long

    For i = 1 To 100

        Sheets("Sheet1").Cells(i, 1).Copy Sheets("Sheet2").Cells(i, 1)

    Next i

End Sub
Mã:
Sub test4()

    Dim i As Long

    Application.ScreenUpdating = False

    For i = 1 To 100

        Sheets("Sheet1").Cells(i, 1).Copy Sheets("Sheet2").Cells(i, 1)

    Next i

    Application.ScreenUpdating = True

End Sub
Dưới đây là kết quả so sánh hai code trên.

Bảng 2:
Lầntest3test4%
12.5471.95376.70%
21.4221.922135.20%
31.8591.37574.00%
42.3131.65671.60%
52.251.12550.00%
62.2181.1451.40%
72.2351.12550.30%
82.251.64172.90%
92.2341.78179.70%
102.2342.12595.10%
Trung bình2.1561.58475.70%
Nhìn vào kết quả này, đừng có nghĩ “Quả nhiên là dừng cập nhật màn hình cho tốc độ nhanh hơn 25%”. Ban đầu Test1 cho kết quả trung bình là 10.490s đấy thôi. Và khi dừng màn hình, kết quả trung bình là 4.663s đấy thôi. Tuy nhiên, với mỗi code đó (Test1 và Test2) khi bỏ Select, chỉ cần thế thôi, kết quả trung bình là 2.156s. Như vậy điều quan trọng ở đây chính là nó.
Ta mới copy có 100 cells, khi copy 1000 lần thì kết quả sẽ như thế nào nhỉ?

Bảng 3:
Lầntest3test4%
119.7516.67284.40%
218.7520.578109.70%
32120.28196.60%
422.29719.10985.70%
519.17219.61102.30%
622.2520.15690.60%
722.43819.04784.90%
818.51619.75106.70%
922.34420.53291.90%
1021.62519.01587.90%
Trung bình20.81419.47594.10%
Đó, thấy chưa nào, kết quả hầu như không thay đổi mấy. Tóm lại, nếu như không bỏ những dòng code thừa như là Select, thì dù cập nhật màn hình hay không cập nhật màn hình, thì cũng chẳng có ý nghĩa gì cả.
Nguon bai viet:
 

Euler

Administrator
Thành viên BQT
Không dùng Select
Chúng ta nhập số vào tuần tự ác cells A1:A5, chúng ta ghi lại thao tác bằng tay ấy để được code macro, như dưới đây:

Mã:
Sub Macro1()

    Range("A1").Select

    ActiveCell.FormulaR1C1 = "1"

    Range("A2").Select

    ActiveCell.FormulaR1C1 = "2"

    Range("A3").Select

    ActiveCell.FormulaR1C1 = "3"

    Range("A4").Select

    ActiveCell.FormulaR1C1 = "4"

    Range("A5").Select

    ActiveCell.FormulaR1C1 = "5"

    Range("A6").Select

End Sub
Các cell lần lượt được select, và số được nhập vào các cell active. Code như trên là không sai. Tuy nhiên, không thể nói đó là code tốt.

Việc ghi macro sinh ra code bằng cách ghi lại từng bước thao tác bằng tay. Đối với thao tác nhập số 1 vào cells A1, đầu tiên cells A1 sẽ được select, rồi ta nhập 1 vào và ấn Enter. Khi đó cells được kích hoạt (active), số 1 chính thức được nhập vào.

Code như vậy không chỉ ảnh hưởng tới tốc độ, mà bảo trì sửa chữa code sau này cũng sẽ rất vất vả.

Bây giờ chúng ta sẽ kiểm chứng:

Mã:
Sub test2()

    Dim i As Long

    Application.ScreenUpdating = False

    For i = 1 To 100

        Cells(i, 1).Select

        Selection.Copy

        Sheets("Sheet2").Select

        Cells(i, 1).Select

        ActiveSheet.Paste

        Sheets("Sheet1").Select

    Next i

    Application.ScreenUpdating = True

End Sub
Ở bài trước, chúng ta đã biết việc dừng màn hình trong trường hợp như thế này chẳng có ý nghĩa gì. Thay vì thế, chúng ta không sử dụng Select.

Mã:
Sub test3()

    Dim i As Long

    For i = 1 To 100

        Sheets("Sheet1").Cells(i, 1).Copy Sheets("Sheet2").Cells(i, 1)

    Next i

End Sub
Bảng so sánh kết quả:
Lầntest2test3%
13.2031.95361.00%
24.7041.92240.90%
34.751.37528.90%
44.7651.65634.80%
54.7971.12523.50%
64.7971.1423.80%
74.6561.12524.20%
85.1411.64131.90%
95.1091.78134.90%
104.7032.12545.20%
Trung bình4.6631.58434.90%
Nếu chúng ta không hiểu, chúng ta dùng chức năng ghi macro để sinh ra code là đương nhiên. Tuy nhiên, đó chỉ là tính năng tìm kiếm điều mình cần. Nếu chúng ta giữ nguyên như thế mà sử dụng, code vừa dài, tốc độ lại chậm. Chúng ta nên xóa đi những thứ vô bổ không cần thiết.

Do đó:

Ghi macro



Thu được code



Giữ nguyên code như thế và sử dụng

Quy trình như trên là không đúng. Thay vào đó, nên là:

Ghi macro



Thu được code



Đọc code đó



Chỗ nào không hiểu thì tự mình tìm hiểu (internet)



Chỉ giữ lại đoạn code mà bản thân mình cần.

Không thể nói là tôi không đọc được code mà chức năng ghi macro đã tạo ra. Chính là phải tìm hiểu về nó, cho tới khi đọc hiểu nó, là việc học cơ bản, nền móng của VBA. Thật tiếc là các sách vở tài liệu về VBA của Việt Nam, ngay đến cả nền móng như vậy cũng viết rất hạn chế. Vì vậy khả năng ngoại ngữ để tra cứu internet là cần thiết. Chính trong lúc tra cứu tìm hiểu, là bạn đang học.

Đừng nghĩ rằng ngôn ngữ VBA là đơn giản. Có thể các bạn sẽ nói mình biết ngôn ngữ C, nhưng ngôn ngữ C không có worksheet, không có cells. Chủ đích của VBA Excel là làm việc với cells và worksheet. Ngoài ra, nếu chỉ đơn giản là copy code từ internet để hòan thành chương trình của mình, thì việc đó cũng không nâng cao trình độ VBA của bạn lên được.

Các bạn có thể dùng các website dịch để chuyển tiếng anh về tiếng việt.

Nói tóm lại, việc tìm hiểu, hiểu rõ code là điều cần thiết phải làm. Đó cũng là lý do mà tuhocvba.net được lập ra.

Nguồn:
 

giaiphapvba

Administrator
Thành viên BQT
Không làm việc dư thừa

Khi chúng ta ghi macro thao tác bằng tay, chuyển font về Times New Roman, ta được code như sau:

Mã:
Sub Macro1()

With Selection.Font

.Name = " Times New Roman "

.Size = 11

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ColorIndex = xlAutomatic

.TintAndShade = 0

.ThemeFont = xlThemeFontNone

End With

End Sub
Bạn cần đăng nhập để thấy hình ảnh

Ra là vậy, đây là code để chuyển đổi font. Tuy nhiên chúng ta không cứ thế mà bê nguyên code này vào chương trình của chúng ta. Chúng ta phải đọc xem chúng là cái gì. Tất nhiên, loại font được thiết định là Times New Roman nhưng ngoài ra nó còn viết những cái gì nữa thế kia? Cỡ chữ 11, cái này chúng ta không quan tâm, nên có thể bỏ đi. Chúng ta cũng không cần thiết định đường gạch ngang chữ, không cần thiết định độ bóng cho chữ… Như vậy ngoài thao tác bằng tay của chúng ta, việc ghi macro đã tạo ra rất nhiều thứ dư thừa. Những thứ này cũng ảnh hưởng tới tốc độ của Macro.

Bạn không tin ư? Cùng so sánh với code sau nhé:

Mã:
Sub Test2()

Dim i As Long

For i = 1 To 5000

Cells(i, 1).Font.Name = " Times New Roman "

Next i

End Sub
Bảng so sánh:

Lầntest1test2%
14.6560.73415.80%
25.5630.73413.20%
34.50.71916.00%
44.6880.71915.30%
54.4530.73416.50%
64.4530.71916.10%
74.4370.71916.20%
84.9370.73414.90%
94.5320.79717.60%
104.50.78117.40%
Trung bình4.6720.73915.90%
Như vậy chúng ta đã nhìn thấy sự khác biệt. Có thể nói rằng, nếu chúng ta tỉ mỉ xem xét chỗ nào không cần thiết thì bỏ đi, tốc độ macro sẽ được cải thiện đấy.
Nguồn: .
 

Euler

Administrator
Thành viên BQT
Đừng lặp đi lặp lại những công việc giống nhau.

Cùng nội dung, giữa việc xử lý 10 dòng với xử lý 1000 dòng, thông thường tốc độ xử lý 10 dòng sẽ nhanh hơn. Nhưng mà vấn đề không phải là ở số dòng dữ liệu ít. Mà là số dòng lệnh mà Excel phải thực thi là ít mới tốt. Cách nói này có thể gây hỗn loạn tâm can bạn đọc. Thuộc tính và phương thức được sử dụng càng ít thì tốc độ macro càng nhanh. Để hiểu vấn đề, thôi thì nhìn vào code cho nhanh hiểu.

Mã:
Sub Test1()

    Dim i As Long

    For i = 1 To 5000

        Workbooks("Sample.xlsx").Sheets("Sheet1").Cells(i, 1) = _

            Workbooks("Sample.xlsx").Sheets("Sheet1").Cells(i, 2) + _

            Workbooks("Sample.xlsx").Sheets("Sheet1").Cells(i, 3)

    Next i

End Sub
Và chương trình sau:

Mã:
Sub Test2()

    Dim i As Long

    With Workbooks("Sample.xlsx").Sheets("Sheet1")

        For i = 1 To 5000

            .Cells(i, 1) = .Cells(i, 2) + .Cells(i, 3)

        Next i

    End With

End Sub
Bảng so sánh tốc độ:
Lầntest1test2%
12.892.32980.60%
22.9072.31279.50%
32.9222.29778.60%
42.9062.65691.40%
53.0943.359108.60%
62.8752.31380.50%
72.8752.28179.30%
82.8752.32881.00%
92.8752.29779.90%
103.1722.32873.40%
Trung bình2.9392.4583.40%
Ủa, cái gì thế này, tốc độ chả nhanh hơn tí nào à?
Ngày xưa, khi thí nghiệm code này với chiếc máy tính đời cũ thì có chút khác biệt. Tuy nhiên các bạn cần biết khi chúng ta dùng With để buộc chặt đối tượng, VBA sẽ biên dịch các tham số bên trong With. Để chắc ăn, bây giờ ta thí nghiệm thêm với code sau nhé:

Mã:
Sub Test3()

    Dim i As Long, S As Worksheet

    Set S = Workbooks("Sample.xlsx").Sheets("Sheet1")

    For i = 1 To 5000

        S.Cells(i, 1) = S.Cells(i, 2) + S.Cells(i, 3)

    Next i

End Sub
Bảng so sánh kết quả:
Lầntest1test3%
12.891.87564.90%
22.9071.85963.90%
32.9221.87564.20%
42.9061.87564.50%
53.0941.8660.10%
62.8752.15675.00%
72.8751.87565.20%
82.8751.90666.30%
92.8751.85964.70%
103.1721.87559.10%
Trung bình2.9391.90264.70%
Ồ kết quả đã có sự khác biệt rất lớn rồi đấy. Để làm rõ hơn ta so sánh test2 với test3 nhé:
LầnWith (test2)Object (test3)%
12.3291.87580.50%
22.3121.85980.40%
32.2971.87581.60%
42.6561.87570.60%
53.3591.8655.40%
62.3132.15693.20%
72.2811.87582.20%
82.3281.90681.90%
92.2971.85980.90%
102.3281.87580.50%
Trung bình2.451.90277.60%
Không có sự khác biệt quá lớn. Tuy nhiên việc dùng Object thì code không chỉ nhanh mà còn dễ đọc nữa.
Nguồn:
 

Euler

Administrator
Thành viên BQT
Không gọi cá biệt

Ngày xưa, có câu chuyện so sánh về tốc độ giữa For NextFor Each.

Vào năm 1995 khi Microsoft công bố chính thúc For Each trên VBA, nhiều người cho rằng lý do là bởi For Each cho tốc độ tốt nên được phát hành. Kết quả điều tra thời đó, hầu hết đều cho rằng For Each cho tốc độ nhanh. Thế nhưng thời đại đã thay đổi. Tính năng cũng như tốc độ tính toán của máy tính đã phát triển nhanh chóng, dù tính logic nhanh đi nữa, nhưng cảm giác về tốc độ cần dựa trên kết quả đo lường thực tế sẽ như thế nào đây nhỉ?

Nào, đầu tiên hãy thử so sánh For Next với For Each.

Mã:
Sub Test1()

    Dim i As Long

    For i = 1 To 10000

        Cells(i, 1) = 100

    Next i

End Sub
Mã:
Sub Test2()

    Dim c As Range

    For Each c In Range("A1:A10000")

        c = 100

    Next c

End sub
LầnFor NextFor Each%
13.7033.703100.00%
23.6413.687101.30%
34.2183.71988.20%
43.7194.297115.50%
53.6874.031109.30%
63.6883.62598.30%
73.6564.422121.00%
84.2193.95393.70%
93.8913.68894.80%
1044.25106.30%
Trung bình3.8423.938102.50%
Tuy nhiên, nếu thử suy nghĩ, chúng ta sẽ thấy rằng cách sử dụng cũng như cách nghĩ của For Next và For Each là khác nhau. Đây không phải là câu chuyện cái nào nhanh hơn. Này nhé, For Next sử dụng biến đếm thông thường là số nguyên kiểu Long, trong khi đó For Each sử dụng biến điều khiển là kiểu đối tượng (Object) hoặc là kiểu Variant. Ngay đầu vào input của chúng đã khác nhau rồi, cho nên việc so sánh khiên cưỡng như trên thật là khó chấp nhận.

Tuy nhiên, dù thế nào đi nữa, chúng ta cũng kiểm chứng thêm một chút nữa. Ở macro trên, ta sử dụng chỉ 1 cells. Giả sử như, trường hợp trong cùng một hàng mà sử dụng nhiều cells thì sẽ ra sao?

Mã:
Sub Test3()

    Dim i As Long

    For i = 1 To 10000

        Cells(i, 1) = Cells(i, 2) + Cells(i, 3)

    Next i

End Sub
Ta cũng có code tương tự cho for each.

Mã:
 Sub Test4()

    Dim c As Range

    For Each c In Range("A1:A10000")

        c = c.Offset(0, 1) + c.Offset(0, 2)

    Next c

End Sub
LầnFor NextFor Each%
14.1253.85993.60%
24.5633.87584.90%
33.7663.828101.60%
43.8433.79798.80%
53.7973.76599.20%
63.7663.781100.40%
73.753.781100.80%
83.7814.532119.90%
93.7664.485119.10%
104.5473.7582.50%
Trung bình3.973.94599.40%
Kết quả hầu như không có thay đổi mấy. Thậm chí nếu ta sử dụng vòng lặp Do Loop thì kết quả cũng không có sự khác nhau nhiều.

Mã:
Sub Test5()

    Dim i As Long

    Do While i <= 10000

        i = i + 1

        Cells(i, 1) = Cells(i, 2) + Cells(i, 3)

    Loop

End Sub
Tóm lại, máy tính bây giờ, thì For Next hay For Each, hay Do Loop thì chúng gần như không có khác biệt về tốc độ.
Tham khảo và dịch từ:
 

tuhocvba

Administrator
Thành viên BQT
Chỉ định kiểu
Nhiều khi ta vẫn nghe những câu như là, hãy chỉ định rõ ràng kiểu biến là Long, đừng có mập mờ Variant nữa. Nếu cứ chỉ định Variant như thế, thì chương trình sẽ chậm đấy. Thật ra đó là câu chuyện của ngày xưa.
Ta hãy so sánh hai đoạn code dưới đây:
Mã:
Sub Test1()
    Dim i As Long
    For i = 1 To 10000
        Cells(i, 1) = 100
    Next i
End Sub
Mã:
Sub Test2()
    Dim i As Variant
    For i = 1 To 10000
        Cells(i, 1) = 100
    Next i
End Sub
LầnLongVariant%
1​
4.39​
3.672​
83.60%​
2​
3.594​
3.609​
100.40%​
3​
3.656​
3.61​
98.70%​
4​
3.734​
3.61​
96.70%​
5​
3.625​
3.578​
98.70%​
6​
3.641​
3.828​
105.10%​
7​
3.672​
3.64​
99.10%​
8​
3.594​
4.204​
117.00%​
9​
4.25​
3.578​
84.20%​
10​
3.594​
3.875​
107.80%​
Trung bình
3.775​
3.72​
98.60%​
Chúng ta thấy là hầu như không có khác biệt gì.
Ta thử sử dụng nhiều biến Variant hơn xem sao nhé.
Mã:
Sub Test3()
    Dim i As Long, A As Long, B  As Long, C As String
    For i = 1 To 10000
        A = 100
        B = 2
        C = "tuhocvba.net"
        Cells(i, 1) = C & A * 2
    Next i
End Sub
Mã:
Sub Test4()
    Dim i As Variant, A As Variant, B As Variant, C As Variant
    For i = 1 To 10000
        A = 100
        B = 2
        C = "tuhocvba.net"
        Cells(i, 1) = C & A * 2
    Next i
End Sub
Ở trên ta viết Cells(i, 1) = C & A * 2 , đó thật ra không phải là cách viết tốt. Nào bây giờ hãy xem kết quả so sánh.
LầnLongVariant%
1​
3.844​
3.89​
101.20%​
2​
3.813​
3.891​
102.00%​
3​
4.078​
4.188​
102.70%​
4​
4.468​
4.235​
94.80%​
5​
3.844​
3.921​
102.00%​
6​
4.141​
3.813​
92.10%​
7​
3.843​
3.984​
103.70%​
8​
3.829​
4.25​
111.00%​
9​
3.797​
3.797​
100.00%​
10​
3.797​
3.875​
102.10%​
Trung bình
3.945​
3.984​
101.00%​
Ta thấy rằng, tốc độ chẳng có khác biệt gì lớn phải không nào?
Nói rằng kiểu biến Variant làm cho chương trình chậm thì đó là câu chuyện của những chiếc máy tính cổ thời xưa. Hiện tại, máy tính bây giờ thì đó chẳng còn là vấn đề nữa, không cần thiết phải quan tâm tới điều ấy. Tuy nhiên, vẫn có nhiều người cứ nói rằng kiểu biến Variant làm cho chương trình chạy chậm. Đó chắc chắn là họ chưa bao giờ tự mình thử nghiệm, phải không các bạn!
Cái gì thế này. Sao kỳ cục vậy. Tự mình chưa thử nghiệm thì lấy cái gì làm nguyên cớ để nói kiểu biến Variant làm chương trình chạy chậm nhỉ!
Nguyên cớ chỉ có thể là "thấy trên internet mọi người bảo thế".
Mặc dù họ nói là kiểu biến Variant làm chương trình chạy chậm, nhưng chúng ta thỉnh thoảng vẫn thấy chính họ ghi code như thế này:
Mã:
Dim A, B, C As Long
Nếu ghi như thế này thì chỉ có C là Long, còn A,B vì bị giản lược khai báo biến nên mặc định là Variant đấy.
Bài viết được dịch từ:
//Tôi nhớ là trên diễn đàn đã có vài bài viết nói cái này cái kia chậm hay chưa tốt mà tôi phải xóa vì không đưa ra được dẫn chứng. Trên tuhocvba.net, nếu không có dẫn chứng thì đừng nói gì cả.
 

Euler

Administrator
Thành viên BQT
Không gọi tên
Khi chỉ định sheet, ta có hai cách:
Sheets(1)
Sheets("Sheet1")
Đối với cách gọi tên sheet như thế này, Sheets("Sheet1"), người ta nói rằng sẽ làm việc chậm hơn so với gọi chỉ mục như thế này Sheets(1) . Đây là câu chuyện của ngày xưa, hiện tại như thế nào, thì bây giờ chúng ta sẽ cùng nhau kiểm chứng.

Mã:
Sub Test1()
    Dim i As Long, A As String
    For i = 1 To 1000
        Sheets(1).Name = Sheets(1).Name
    Next i
End Sub
Mã:
Sub Test2()
    Dim i As Long, A As String
    For i = 1 To 1000
        Sheets("Sheet1").Name = Sheets("Sheet1").Name
    Next i
End Sub
LầnIndex (chỉ mục)Name%
1​
2.937​
2.954​
100.60%​
2​
2.828​
3.234​
114.40%​
3​
2.844​
2.859​
100.50%​
4​
2.844​
2.86​
100.60%​
5​
2.844​
2.985​
105.00%​
6​
2.906​
3.437​
118.30%​
7​
3.312​
3.156​
95.30%​
8​
3.047​
3.25​
106.70%​
9​
3​
3.703​
123.40%​
10​
3.219​
6.406​
199.00%​
Trung bình
2.978​
3.484​
117.00%​
Quả nhiên là có sự khác biệt về tốc độ nhưng không phải là quá nhiều. Và cũng còn tùy vào mục đích mà có lúc chúng ta phải làm việc với tên sheet. Tương tự, cũng có lúc chúng ta phải làm việc với thứ tự sheet (index-chỉ mục), vì vậy tốc độ ở đây không phải là điều quyết định chúng ta phải dùng thứ tự sheet, hay tên sheet, phải không nào.
Bài viết được dịch và tham khảo từ nguồn:
 

tuhocvba

Administrator
Thành viên BQT
Thuộc tính tiêu chuẩn (mặc định)
Mã:
Range("A1").Value = "tuhocvba.net"
MsgBox Range("A1").Value
Thuộc tính (property) mặc định của Range là Value. Nó có thể được giản lược, tóm lại code trên có thể viết lại là:
Mã:
Range("A1") = "tuhocvba.net"
MsgBox Range("A1")
Vậy, bây giờ sẽ có câu hỏi đặt ra, viết như thế nào mới là tốt. Về việc này diễn đàn đã từng có một topic tương tự:
Nào, chúng ta cùng kiểm chứng để so sánh tốc độ nhé:
Mã:
Sub Test1()
    Dim i As Long
    For i = 1 To 20000
        Cells(i, 1).Value = Cells(i, 2).Value
    Next i
End Sub
Mã:
Sub Test2()
    Dim i As Long
    For i = 1 To 20000
        Cells(i, 1) = Cells(i, 2)
    Next i
End Sub
LầnCó ValueKhông có Value%
10.8130.73490.30%
20.8280.71986.80%
30.8120.73590.50%
40.8130.73490.30%
50.8280.70384.90%
60.8120.73490.40%
70.8280.73488.60%
80.8280.71986.80%
90.8130.71988.40%
100.9060.71879.20%
Trung bình0.8280.72587.50%
Tốc độ đúng là có khác biệt nhưng không nhiều quá. Vì vậy trong trường hợp này, thì như thế nào cũng được. Bạn viết có value hay không viết value thì cũng không sao hết. Quan trọng hơn tốc độ, đó là nhìn vào code, bạn hiểu đuợc mình đang làm việc với cái gì, mang mục đích và ý nghĩa gì là được.
Nguồn:
 

Euler

Administrator
Thành viên BQT
Nếu có thể thì hãy sử dụng các hàm có sẵn của excel
Đây được coi là việc đỡ tốn công tốn sức khi code, bạn khỏi phải code nữa vì có hàm sẵn rồi, chẳng những thế tốc độ còn được cải thiện đáng kinh ngạc.
Các hàm mà bạn vẫn dùng trên bảng tính Excel ví dụ như SUM, VLOOKUP,... đều có thể được gọi từ VBA.
Với Excel 2016, người ta ước tính có khoảng 480 hàm worksheet (các hàm dùng trên bảng tính), tất nhiên không phải tất cả đều có thể gọi bằng VBA nhưng hầu hết chúng có thể gọi bằng VBA. Hãy chú ý điều này.
Để sử dụng trong VBA, chúng ta sẽ sử dụng từ khóa WorksheetFunction.tên_hàm
Ví dụ: Ta hãy thực hiện tính tổng các ô A1:A100000 bằng hai đoạn code sau:
Mã:
Sub Test1()
    Dim i As Long, A As Long
    A=0
    For i = 1 To 100000
        A = A + Cells(i, 1)
    Next i
End Sub
Mã:
Sub Test2()
    Dim i As Long, A As Long
    A = WorksheetFunction.Sum(Range("A1:A100000"))
End Sub
LầnASUM%
10.54700.00%
20.34400.00%
30.43800.00%
40.35900.00%
50.3600.00%
60.43700.00%
70.34400.00%
80.40600.00%
90.3750.0164.30%
100.34400.00%
Trung bình0.3950.0020.40%
Ái chà, như thế này thì thật khó kiểm chứng rồi. Tốc độ của SUM xấp xỉ là 0, tức là chả mất tí thời gian nào.
Bây giờ ta phải tăng dữ liệu lên.
Bạn cần đăng nhập để thấy đính kèm


Bây giờ ta dùng hai đoạn code sau để so sánh tốc độ:
Code này, ta cho chạy từng dòng để kiểm tra:
Mã:
Sub Test3()
    Dim i As Long, A As Long
    For i = 1 To 1048576
        If Cells(i, 1) = "tuhocvba" Then A = Cells(i, 2)
    Next i
End Sub
Code này tương đương với ta dùng Ctr+F để tìm
Mã:
Sub Test4()
    Dim FC As Range, A As Long
    Set FC = Range("A1:A1048576").Find(What:="tuhocvba", Lookat:=xlWhole)
    If Not FC Is Nothing Then A = FC.Offset(0, 1)
End Sub
Code này ta dùng Vlookup:
Mã:
Sub Test5()
    Dim A As Long
    A = WorksheetFunction.VLookup("tuhocvba", Range("A1:B1048576"), 2, False)
End Sub
LầnFor NextFindVLOOKUP%
10.750.0630.0314.10%
20.9380.0780.0475.00%
30.6560.0790.0477.20%
40.6560.0630.0324.90%
50.6410.0780.0477.30%
60.640.0620.0314.80%
70.6410.0780.0477.30%
80.6870.0630.0476.80%
90.6410.0630.0314.80%
100.6410.0620.0314.80%
Trung bình0.6890.0690.0395.70%

Find thì không phải là tệ nhưng vẫn chậm hơn Vlookup nhỉ.
Các bạn thấy các hàm của Excel đáng sợ chưa nào, code rất nhanh, tốc độ cũng rất tốt.
Để xét tồn tại, tôi hay dùng Countif
Mã:
Sub Test6()
    If WorksheetFunction.CountIf(Range("A:A"), "Euler") > 0 Then
        ''Xử lý gì đó ở đây
    Else
        MsgBox "Euler không tồn tại"
    End If
End Sub
Tiện lợi và lợi hại chưa nào, một cách khác nhé:
Mã:
Sub Test7()
    Dim FC As Range
    Set FC = Range("A:A").Find(What:="Euler")
    If FC Is Nothing Then
        MsgBox "Euler không tồn tại"
    Else
        ''Xử lý gì đó ở đây
    End If
End Sub
Viết cho gọn hơn nữa nhé:
Mã:
Sub Test7()
    If Range("A:A").Find(What:="Euler") Is Nothing Then
        MsgBox "Euler không tồn tại"
    Else
        ''Xử lý gì đó ở đây
    End If
End Sub
Mặc dù các hàm của excel được sử dụng thông qua Worksheetfunction cho tốc độ rất tốt trong VBA nhưng tôi cảm giác rằng ít người sử dụng nó. Là bởi vì, người dùng VBA có khi không giỏi sử dụng hàm trong Excel, cho nên họ sẽ code theo logic trong đầu họ nghĩ.
Tương tự vậy, người giỏi hàm trong Excel thì có khi lại không giỏi logic để code VBA.
Vì vậy, nếu như nắm được logic để code VBA mà lại biết sử dụng hàm trong Excel, khi đó bạn vận dụng vào code VBA sẽ cho hiệu quả đáng kinh ngạc đấy.
Nguồn:
 

tuhocvba

Administrator
Thành viên BQT
Phương pháp chỉ định cells.

Có thể nói đơn giản, dùng cells và dùng Range thì khác nhau như thế nào? Sau đây ta sẽ đi vào code để so sánh tốc độ nhé.
Mã:
Sub Test1()
    Dim i As Long, A As Long
    For i = 1 To 50000
        A = Range("A1") + Range("B1") + Range("C1")
    Next i
End Sub
Mã:
Sub Test2()
    Dim i As Long, A As Long
    For i = 1 To 50000
        A = Cells(1, 1) + Cells(1, 2) + Cells(1, 3)
    Next i
End Sub
LầnRangeCells%
10.7650.48563.40%
20.7650.46961.30%
30.750.48564.70%
40.7660.565.30%
50.7810.57874.00%
60.8120.48459.60%
70.7660.51667.40%
80.7810.564.00%
90.7660.48463.20%
100.7660.51667.40%
Trung bình0.7720.50265.00%
Chỉ định cells cho tốc độ nhanh thật.
Trên một số diễn đàn, không biết từ khi nào, tôi thấy đại bộ phận đều thường dùng Range.
Thậm chí các bạn còn đặt tên riêng cho Range để làm việc. Tuy nhiên, Toàn bộ các cells được Excel quản lý bằng mảng hai chiều.
Nếu viết như thế này, cells(3,4) thì thật dễ thao tác cho excel. Giả sử như bạn chỉ định thế này, Range("B4"), khi đó Excel sẽ phải tìm xem, cái tên "B4" rút cuộc là nằm ở dòng nào, cột nào? Tất nhiên việc tìm như thế không mất nhiều thời gian, nhưng để nói tốc độ nhanh, thì dùng cells sẽ cho tốc độ nhanh hơn.
Tuy nhiên, như đã nói với các bạn, tốc độ không quyết đinh tất cả, chúng ta dựa trên rất nhiều yếu tốc để lựa chọn, ví dụ như là dễ đọc code dễ hình dung ý đồ-mục đích, dễ sửa chữa nâng cấp code, dễ sử dụng bàn phím- thói quen đối với người gõ code...
Vậy, các bạn hãy tự mình lựa chọn cho bản thân nhé.
Tham khảo:
 

giaiphapvba

Administrator
Thành viên BQT
Nên sử dụng hàm có giá trị trả về là string?
So với hàm Left thì Left$ sẽ cho tốc độ nhanh hơn, đây là câu chuyện mà người ta vẫn nói với nhau đấy. Lý do là giá trị trả về của Left thật ra là variant, còn giá trị trả về của Left$ là string. Vì string thì cho tốc độ nhanh hơn variant cho nên hàm Left bị đánh giá là chậm hơn. Tuy nhiên ta hãy cùng kiểm chứng nhé.
Mã:
Sub Test1()
    Dim i As Long, A As String
    For i = 2 To 100001
        A = Left(Cells(i, 1), 1)
    Next i
End Sub
Mã:
Sub Test2()
    Dim i As Long, A As String
    For i = 2 To 100001
        A = Left$(Cells(i, 1), 1)
    Next i
End Sub
LầnLeftLeft$%
10.3280.31395.40%
20.3280.31295.10%
30.3280.328100.00%
40.3130.328104.80%
50.3130.313100.00%
60.3280.31295.10%
70.3290.32899.70%
80.3120.328105.10%
90.3280.31395.40%
100.2970.328110.40%
trung bình0.320.32100.00%
Chẳng có khác biệt gì mấy phải không nào. Quả nhiên đây là câu chuyện của ngày xưa. Bây giờ các đời máy tính thế hệ ngày nay, tốc độ xử lý hơn ngày xưa rất nhiều cho nên tốc độ xử lý variant và string không còn mấy khác biệt. Vì vậy, bạn hãy quên điều này đi nhé.
Nguồn:
 

vbano1

SMod
Thành viên BQT
Redim Preserve trên VBA có thực sự làm tốc độ chậm hay không?
Để reset một mảng, ta có lệnh Redim. Tuy nhiên để lưu trữ giá trị vốn có của mảng khi cần thêm một phần tử mới vào mảng, khai báo lại kích thước mảng, chúng ta có .
Tôi nghe người ta nói nếu dùng Redim Preserve sẽ làm tốc độ chương trình bị chậm. Vì vậy hãy hạn chế sử dụng nó, nếu sử dụng thì dùng càng ít càng tốt. Là bởi vì khi thực hiện lệnh này, nó sẽ copy dữ liệu cũ cho sang khu vực mới để lưu trữ. Quá trình này sẽ gây mất thời gian.
Vậy tôi sẽ làm rõ với các bạn là nó có thực sự làm chậm hay không nhé.

1. Tốc độ của Redim
Mã:
Sub RedimTest()
    Dim ar()
    Dim iCount
    Dim i
    Dim a, b
   
    a = Timer
   
    iCount = 1000000
    ReDim ar(iCount)
   
    For i = 0 To iCount
        ar(i) = "aaaaaaaaaaaaaaa"
    Next
   
    b = Timer
   
    Debug.Print b - a & " giay"
End Sub
Kết quả:
Mã:
0.1914063 giay
Lệnh Redim ở trên tạo ra vùng nhớ cho 1 triệu phần tử. Thời gian thực thi chỉ mất khoảng 0.1 giây.

2. Redim Preserve sẽ chậm nếu tạo vùng nhớ cho 10 vạn phần tử?
Tùy vào hiệu năng của máy tính mà tốc độ có thể sẽ khác nhau. Tuy nhiên với số lượng phần tử đủ lớn, chúng ta thực hiện thí nghiệm sẽ cho kết quả đủ để chúng ta hình dung tốc độ ra sao.
Sau khi thực thi lệnh Redim, chúng ta thực thi lệnh ReDim Preserve liên tục thông qua vòng lặp For.
Mã:
Sub RedimPreserveTest()
    Dim ar()
    Dim iCount
    Dim i
    Dim a, b
   
    a = Timer
   
    ReDim ar(0)
   
    iCount = 10000
    For i = 0 To iCount
        ReDim Preserve ar(i)
        ar(i) = "aaaaaaaaaaaaaaa"
    Next
   
    b = Timer
   
    Debug.Print b - a & " giay"
End Sub
Kết quả:
Mã:
1.171875E-02 giay
Và đây là kết quả:
Số lầnGiâySự khác biệt (giây)
100000.014648440
200000.01464840
300000.0156250.0009766
400000.03076170.0151367
500000.05615230.0253906
600000.065917970.0097657
700000.0781250.012207
800000.11914060.0410156
900000.14111330.0219727
1000000.18701170.0458984
2000000.7170410.5300293
3000001.642090.925049
4000003.0410161.398926
5000004.6791991.638183
6000006.7758792.09668
7000009.1091312.333252
80000011.899172.790039
90000014.970953.07178
100000018.439943.46899
110000022.477054.03711
120000026.906984.42993
130000031.376954.46997
140000036.337894.96094
150000041.877445.53955
160000047.655275.77783
170000053.698736.04346
180000060.473146.77441
190000067.137216.66407
200000074.381847.24463

Bạn cần đăng nhập để thấy đính kèm


Nhìn vào kết quả này ta thấy rằng nếu số lượng phần tử dưới 10 vạn, thì tốc độ xử lý chưa tới 1s. Vượt qua 10 vạn, thời gian xử lý có xu hướng sẽ tăng lên rất nhiều.
Nếu số lượng phần tử lên tới 1 triệu, tốc độ xử lý mất khoảng 20s.

3. Redim Preserve hầu như không ảnh hưởng nhiều tới tốc độ
Từ biểu đồ trên ta thấy, nếu số lượng phần tử ít hơn 10 vạn, tốc độ xử lý rất nhanh, không bị ảnh hưởng mấy.
Nếu bạn có một vòng lặp 10.000 lần, bên trong có lệnh Redim Preserve thì tốc độ hầu như chẳng bị ảnh hưởng gì.
Đối với máy tính của tôi, giới hạn là 10 vạn, tôi nghĩ ở trên các máy tính khác, giới hạn này có thể thay đổi tùy thuộc vào cấu hình máy tính của bạn.
Nguồn tham khảo và dịch:
 
V

vothanhthu

Guest
Hạn chế đọc ghi dữ liệu nhiều lần ra Sheet. Nếu có thể, hãy dùng mảng để xử lý số liệu

Đây là điều rất cơ bản nhưng cũng khá khó đối với các bạn mới làm quen VBA. Khi Code đang được xử lý, việc đọc và ghi dữ liệu nhiều lần giữa biến và Sheet sẽ làm tốc độ xử lý chậm đi rất nhiều. Hãy thử một đoạn Code ghi giá trị đơn giản như sau:

Đây là đoạn Code ghi dữ liệu liên tục ra sheet bằng cách gán giá trị
Mã:
Sub SetValue()
    Dim i
    Dim StartTime, EndTime

    '/BẮT ĐẦU GHI THỜI GIAN
    StartTime = Timer

       'VÒNG LẶP GHI DỮ LIỆU LIÊN TIẾP RA SHEET'
    iCount = 10000
    For i = 1 To iCount
        Sheet1.Cells(i, 1).Value = i
    Next

       'KẾT THÚC THỜI GIAN GHI'
    EndTime = Timer

    Debug.Print EndTime - StartTime
End Sub
Còn đây là đoạn code sử dụng mảng để ghi dữ liệu và chỉ gán một lần vào Sheet
Mã:
Sub SetValueArr()
    Dim arr
    Dim i As Long
    Dim StartTime, EndTime
    '//BẮT ĐẦU THỜI GIAN GHI'
    StartTime = Timer

    'KHAI BÁO MẢNG'
    iCount = 10000
    ReDim arr(1 To iCount)

    'NẠP MẢNG
    For i = 1 To iCount
        arr(i) = i
    Next

    'GHI DỮ LIỆU TỪ MẢNG RA SHEET'
    Sheet1.Range(Cells(LBound(arr, 1), 1), Cells(UBound(arr, 1), 1)).Value = WorksheetFunction.Transpose(arr)

    'KẾT THÚC THỜI GIAN GHI'
    EndTime = Timer

    Debug.Print EndTime - StartTime
End Sub
Và đây là kết quả:
LầnGán liên tục (s)Mảng (s)%
10.593750.046878%
20.531250.0156253%
30.531250.0468759%
40.5156250.0156253%
50.50.0156253%
60.5468750.0156253%
70.5781250.0156253%
80.593750.031255%
90.531250.031256%
100.50.031256%
Trung bình
0.5421875​
0.026562​
5%​

Qua kết quả, ta có thể thấy được rằng, việc dùng mảng cho tốc độ nhanh hơn rất rất nhiều lần so với việc xử lý trực tiếp trên Sheet. Do đó, hãy cố gắng xử lý dữ liệu trên mảng, đừng xử lý trực tiếp trên Sheet bạn nhé !.

Nếu các bạn đang gặp khó khăn về việc dùng mảng. Hiện tại trên diễn đàn, có loạt các bài viết về mảng rất dễ hiểu đang dành cho các bạn, link
 
Sửa lần cuối bởi điều hành viên:

NhanSu

SMod
Thành viên BQT
Chỉ định kiểu
Nhiều khi ta vẫn nghe những câu như là, hãy chỉ định rõ ràng kiểu biến là Long, đừng có mập mờ Variant nữa. Nếu cứ chỉ định Variant như thế, thì chương trình sẽ chậm đấy. Thật ra đó là câu chuyện của ngày xưa.
Ở bài 7 do thời gian chủ yếu của chuơng trình là ghi số liệu ra sheet nên ta thấy ít khác biệt về tỷ lệ % nhưng nếu không tính thời gian ghi ra sheet thì khác biệt sẽ đáng kể. Điều này dễ thấy ví dụ tính toán đối với Long hết 10s, ghi ra sheet hết 1000s, tính toán với variant hết 20s, như vậy khác biệt về tính toán thì Long nhanh gấp đôi nhưng nếu tính cả ghi ra sheet thì nhanh hơn khoảng 1%.
Sau đây mình ví dụ 2 thủ tục tính i/(i-1) với i chạy từ 2 đến 1 tỷ. Kết quả khai báo long chạy nhanh hơn 2 lần variant.
Mã:
Option Explicit
Sub TestLong()
    Dim i&, n As Double, t As Double
    t = Timer
    For i = 2 To 1000000000
        n = i / (i - 1)
    Next
    Debug.Print "Long " & (Timer - t)
End Sub
Sub TestVar()
    Dim i, n, t
    t = Timer
    For i = 2 To 1000000000
        n = i / (i - 1)
    Next
    Debug.Print "Variant " & (Timer - t)
End Sub
Bạn cần đăng nhập để thấy hình ảnh
 

BKKBG

Yêu THVBA nhất
Ví dụ của bạn @NhanSu rất hay. Nhân đây, mình cũng thảo luận thêm:
Sau đây mình ví dụ 2 thủ tục tính i/(i-1) với i chạy từ 2 đến 1 tỷ. Kết quả khai báo long chạy nhanh hơn 2 lần variant.
Mã:
Option Explicit
Sub TestLong()
    Dim i&, n As Double, t As Double
    t = Timer
    For i = 2 To 2000000
        n = i / (i - 1)
    Next
    Debug.Print "Long " & (Timer - t)
End Sub
Sub TestVar()
    Dim i, n, t
    t = Timer
    For i = 2 To 2000000
        n = i / (i - 1)
    Next
    Debug.Print "Variant " & (Timer - t)
End Sub
Kết quả:
Mã:
Long 0.078125
Variant 0.2109375
Mình sửa lại code ví dụ của bạn @NhanSu , cho chạy tới 2 triệu thôi. Kết quả thì Variant sẽ chậm hơn theo đúng lý thuyết, nhưng thời gian chấp nhận được.
Excel bây giờ (2016) có hơn 1 triệu dòng. Chạy tới 2 triệu là được. Trong thực tế, data nặng lắm cỡ 10.000 dòng. Giả sử bảng tính có 200 cột cũng đã là nhiều. Duyệt mảng chạy theo dòng và cột thì cỡ 2 triệu vòng lặp.
Thời gian dưới 1s là chấp nhận được. (Máy tính core i5, Ram 8Gb, Office 2016 64bit).
Vì vậy kết luận ở bài 7 là hợp lý. Thực tế khi nạp giá trị một sheet vào mảng, chúng ta vẫn khai báo mảng là Variant. Hiếm ai khai báo mảng chỉ định là integer hay Long khi nạp giá trị của sheet vào mảng :
Mã:
Dim arr() as Long
thường khai báo là:
Mã:
Dim arr 'Variant
 

tuhocvba

Administrator
Thành viên BQT
Ví dụ của NhanSu hay hơn bản dịch đấy. Theo lý thuyết thì variant sẽ chậm hơn.
Nhưng thực tế tôi chưa thấy ai chạy tới 2 tỷ.

Ý kiến của BKKBG thực tế hơn.

Cả hai bạn đều đúng. Một người chứng minh lý thuyết. Một người dựa trên cảm nhận thực tế.

Cảm ơn hai bạn.
 

NhanSu

SMod
Thành viên BQT
Để gán giá trị của range vào mảng ta phải khai báo kiểu variant, nếu không sẽ gây lỗi Type mismatch, làm ngược lại thì được. Tuy vậy công việc của ta không chỉ có đọc ghi trên sheet, có thể ta cần phải tính toán nhiều thứ (ví dụ ta mã hóa, giải mã chẳng hạn) lúc này khác biệt về tốc độ là vấn đề cần quan tâm. Một vấn đề đối với variant nữa là nó chiếm nhiều bộ nhớ hơn kiểu long. Điều này thì mình gặp thường xuyên. Mình thường làm việc với khoảng vài trăm nghìn dòng x 50 cột, nạp cả bảng vào array rất dễ thiếu bộ nhớ, vì vậy chỉ phải nạp các cột cần thiết vào các mảng variant, để lấy kết quả tính toán thì mình khai báo mảng có định kiểu.
 

giaiphapvba

Administrator
Thành viên BQT
Mình đã gặp trường hợp như bạn @NhanSu nói. Giải pháp của mình là mình cho dữ liệu Excel vào Access để làm việc. Tốc độ trích lọc nhanh. Nhưng mình lại gặp vấn đề ngược lại, đó là khi Update dữ liệu vào Access thì bị chậm.
 
Dùng Paste sẽ làm chậm tốc độ
Chức năng Copy và Paste trên Excel là thao tác quen thuộc với chúng ta, nhưng đó là thao tác bằng tay. Nếu trong code VBA mà thực hiện thao tác này sẽ làm tốc độ chậm đi đấy.
Hãy xem code dưới đây:
Mã:
Sub Macro1()
    Dim i As Long
    For i = 1 To 500
        Cells(i, 2).Copy
        Cells(i, 3).PasteSpecial xlPasteValues
    Next i
End Sub
Kết quả:
Lần chạy macroThời gian
1​
63.547​
2​
69.281​
3​
68.781​
4​
68.094​
5​
67.188​
6​
65.844​
7​
69.671​
8​
68.703​
9​
69.562​
10​
71.031​
Trung bình
68.17​

Chức năng copy và paste này sẽ khiến cho màn hình cập nhật nội dung liên tục và làm cho tốc độ chương trình bị chậm. Bây giờ ta thử dừng cập nhật màn hình nhé.
Mã:
Sub Macro2()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 1 To 500
        Cells(i, 2).Copy
        Cells(i, 3).PasteSpecial xlPasteValues
    Next i
    Application.ScreenUpdating = True
End Sub
Kết quả:
Lần chạy macroBình thườngTắt chức năng cập nhật màn hình
1​
63.547​
19.812​
2​
69.281​
17.656​
3​
68.781​
21.625​
4​
68.094​
18.422​
5​
67.188​
20.766​
6​
65.844​
17.375​
7​
69.671​
20.328​
8​
68.703​
18.125​
9​
69.562​
18.844​
10​
71.031​
18.437​
Trung bình
68.17​
19.139​

Tốc độ chương trình đã được cải thiện đáng kể. Sau đây ta sẽ cố gắng tránh dùng chức năng Copy và Paste, thay vào đó sử dụng phép gán trực tiếp:
Mã:
Sub Macro3()
    Dim i As Long
    For i = 1 To 500
        Cells(i, 3).Value = Cells(i, 2).Value
    Next i
End Sub
Kết quả:
Lần chạy macroBình thườngDừng cập nhật màn hìnhDùng phép gán thay cho copy & paste
1​
63.547​
19.812​
0.562​
2​
69.281​
17.656​
0.547​
3​
68.781​
21.625​
0.688​
4​
68.094​
18.422​
0.703​
5​
67.188​
20.766​
0.719​
6​
65.844​
17.375​
0.75​
7​
69.671​
20.328​
0.688​
8​
68.703​
18.125​
0.593​
9​
69.562​
18.844​
0.547​
10​
71.031​
18.437​
0.515​
Trung bình
68.17​
19.139​
0.631​
Kết quả đã được cải thiện trông thấy.
Chúng ta biết rằng, nếu thực hiện thì tốc độ còn cải thiện hơn nữa. Vì vậy hãy hạn chế dùng Copy và Paste. Không thể nói là không dùng, bởi trong thực tế, đôi khi cần copy format (định dạng) của một vùng dữ liệu, thì việc copy là cần thiết. Nhưng trong những trường hợp không thực sự cần thiết, thì các bạn hãy hạn chế dùng nhé.
Nguồn tham khảo:
 
Top