Hướng dẫn cách đo tốc độ tính toán của Excel bằng VBA

Đã bao giờ bạn tò mò rằng file Excel của mình đang tính toán với tốc độ như thế nào chưa? Hãy cùng xem hướng dẫn chi tiết kèm theo code mẫu VBA để đo tốc độ tính toán của Excel cực hay. Hãy tham khảo ngay để biết thêm cách làm việc chủ động với Excel các bạn nhé.

Thông thường, khi chúng ta nhập các hàm trong Excel dùng để tính toán thì sẽ thấy thời gian tính diễn ra rất nhanh. Với các bảng tính nhỏ đôi khi chỉ mất chưa để 1 giây là Excel đã tính toán xong. Tuy nhiên, khi chuyển sang các bảng tính lớn, chứa tới hàng ngàn dòng dữ liệu thì các bạn sẽ thấy Excel tính toán chậm hơn. Vậy rốt cuộc phần mềm Excel của chúng ta đang tính toán với tộc độ cụ thể như thế nào? Hãy cùng chúng mình dùng thử cách đo tốc độ tính toán của Excel ngay dưới đây nhé.

Cách đo tốc độ tính toán của Excel bằng VBA

Cách tạo công cụ đo thời gian thực hiện công thức trong Excel

Chúng ta có 3 đoạn code mẫu để sử dụng trong VBA nhằm đo đạc tốc độ thực hiện công thức của Excel là bao nhiêu milisecond.

Đoạn code thứ nhất

#1: Nếu dùng VBA phiên bản 7 thì copy đoạn code sau:    Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _        "QueryPerformanceFrequency" (cyFrequency As Currency) As Long    Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _         "QueryPerformanceCounter" (cyTickCount As Currency) As Long #2: Nếu dùng VBA các phiên bản khác thì copy đoạn code sau:    Private Declare Function getFrequency Lib "kernel32" Alias _        "QueryPerformanceFrequency" (cyFrequency As Currency) As Long    Private Declare Function getTickCount Lib "kernel32" Alias _     "QueryPerformanceCounter" (cyTickCount As Currency) As Long
 

Vậy cách dùng đoạn code mẫu VBA này như thế nào? Đoạn code này yêu cầu người dùng là chúng ta phải chọn được nội dung phù hợp theo phiên bản VBA trong máy tính của mình. Chúng ta có thể kiểm tra phiên bản VBA phù hợp bằng cách làm như sau:

• Bước 1: Chọn thẻ Help trong VBA.
• Bước 2: Nhấp chuột vào mục About Microsoft Visual Basic for Applications trong thẻ Help của VBA.\
• Bước 3: Kiểm tra phiên bản của VBA ở phần đầu cửa sổ hiện ra. Các bạn xem hình ảnh dưới đây sẽ thấy vùng chúng mình khoanh màu tím là tên phiên bản VBA.

 

Sau khi biết được phiên bản VBA, nếu các bạn thấy bản mình đang dùng có số 7 ở trước thì hãy sao chép được code #1 ở trên. Nếu phiên bản của bạn không có số 7 ở trước thì hãy dùng đoạn code #2 ở trên nhé. Cách thêm đoạn code vào VBA rất đơn giản với 3 bước như sau:

• Bước 1: Mở cửa sổ VBA
• Bước 2: Tạo mới một module cho VBA
• Bước 3: Copy đoạn code phù hợp với phiên bản VBA mà máy tính của bạn đang chạy rồi paste vào trong module vừa tạo ở bước trước.

 

Xem thêm: Tổng hợp phương pháp chạy code VBA trong Excel nhanh và đẹp

Đoạn code thứ hai

Function MicroTimer() As Double   'Xac dinh thoi gian bang milisecond
' Returns seconds.
    Dim cyTicks1 As Currency
    Static cyFrequency As Currency
    MicroTimer = 0
' Get frequency.
    If cyFrequency = 0 Then getFrequency cyFrequency
' Get ticks.
    getTickCount cyTicks1
' Seconds
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency
End Function

Đây là đoạn code VBA có thể giúp chúng ta đo tốc độ tính toán của Excel tính theo đơn vị mili giây (milisecond). Các bạn cần lưu ý là chúng ta chỉ dùng được đoạn code này khi đã có đoạn code thứ nhất trong VBA thôi nhé. Cách đưa đoạn code này vào VBA cũng rất đơn giản thực hiện thao tác copy & paste vào module thôi nhé. Các bạn chỉ cần chú ý là đặt nó nằm bên dưới đoạn code thứ nhất là được.

Xem thêm: Cách sử dụng Immediate để kiểm tra code trong VBA Excel cho Macbook

Đoạn code thứ ba

Sub RangeTimer()
    DoCalcTimer 1
End Sub
Sub SheetTimer()
    DoCalcTimer 2
End Sub
Sub RecalcTimer()
    DoCalcTimer 3
End Sub
Sub FullcalcTimer()
    DoCalcTimer 4
End Sub
Sub DoCalcTimer(jMethod As Long)
    Dim dTime As Double
    Dim dOvhd As Double
    Dim oRng As Range
    Dim oCell As Range
    Dim oArrRange As Range
    Dim sCalcType As String
    Dim lCalcSave As Long
    Dim bIterSave As Boolean
    '
    On Error GoTo Errhandl
' Initialize
    dTime = MicroTimer
    ' Save calculation settings.
    lCalcSave = Application.Calculation
    bIterSave = Application.Iteration
    If Application.Calculation <> xlCalculationManual Then
        Application.Calculation = xlCalculationManual
    End If
    Select Case jMethod
    Case 1
        ' Switch off iteration.
        If Application.Iteration <> False Then
            Application.Iteration = False
        End If
        
        ' Max is used range.
        If Selection.Count > 1000 Then
            Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
        Else
            Set oRng = Selection
        End If
        ' Include array cells outside selection.
        For Each oCell In oRng
            If oCell.HasArray Then
                If oArrRange Is Nothing Then
                    Set oArrRange = oCell.CurrentArray
                End If
                If Intersect(oCell, oArrRange) Is Nothing Then
                    Set oArrRange = oCell.CurrentArray
                    Set oRng = Union(oRng, oArrRange)
                End If
            End If
        Next oCell
        sCalcType = "Calculate " & CStr(oRng.Count) & _
            " Cell(s) in Selected Range: "
    Case 2
        sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": "
    Case 3
        sCalcType = "Recalculate open workbooks: "
    Case 4
        sCalcType = "Full Calculate open workbooks: "
    End Select
' Get start time.
    dTime = MicroTimer
    Select Case jMethod
    Case 1
        If Val(Application.Version) >= 12 Then
            oRng.CalculateRowMajorOrder
        Else
            oRng.Calculate
        End If
    Case 2
        ActiveSheet.Calculate
    Case 3
        Application.Calculate
    Case 4
        Application.CalculateFull
    End Select
' Calculate duration.
    dTime = MicroTimer - dTime
    On Error GoTo 0
    dTime = Round(dTime, 5)
    MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _
        vbOKOnly + vbInformation, "CalcTimer"
Finish:
    ' Restore calculation settings.
    If Application.Calculation <> lCalcSave Then
         Application.Calculation = lCalcSave
    End If
    If Application.Iteration <> bIterSave Then
         Application.Iteration = bIterSave
    End If
    Exit Sub
Errhandl:
    On Error GoTo 0
    MsgBox "Unable to Calculate " & sCalcType, _
        vbOKOnly + vbCritical, "CalcTimer"
    GoTo Finish
End Sub

Tương tự như 2 đoạn code ở trên, các bạn chỉ cần copy & paste nó vào Module trong VBA là được nhé. Sau khi đã có đủ 3 đoạn code trong VBA, các bạn quay trở lại file Excel của mình và nhấn tổ hợp phím tắt Alt + F8 để xem kết quả nhé.

 

Trong hình ảnh trên bạn có thể thấy được 4 macro VBA mà chúng ta thu được nhờ 3 đoạn code. Với các macro này chúng ta có để dễ dàng sử dụng để đo tốc độ tính toán của Excel.

Xem thêm: Hướng dẫn cách dùng các toán tử logic AND, OR, NOT trong VBA

Cách sử dụng các Macro đo tốc độ tính toán Excel

Để sử dụng được các VBA macro đã tạo ở trên thì chúng ta sẽ tạo các nút bấm cho nó. Cách làm rất đơn giản alf vẽ ra các hình khối bằng công cụ Shape rồi gán macro vào đó là được.

Ví dụ: Các bạn có thể vẽ ra 4 hình chữ nhật, đổ màu khác nhau trong từng hình và đặt trên trùng với tên của các Macro. Sau đó các bạn nhấp chuột phải vào từng hình => chọn AssignMacro và gán macro tương ứng cho từng hình.

 

Bây giờ chúng ta sẽ cùng tìm hiểu ý nghĩa của từng macro nhé:

• FullCalcTimer: Đo tốc độ tính của toàn bộ các công thức trong toàn bộ các sheet thuộc file Excel mà bạn đang mở.
• RangeTimer: Đo tốc độ tính của 1 vùng nhất định trong 1 sheet
• ReCalcTimer: Thực hiện tính toán lại. Macro này phù hợp để kiểm tra kết quả tính của một nội dung nhiều lần.
• SheetTimer: Đo tốc độ tính của các công thức có trong sheet đang được chọn mà không bao gồm các công thức có trong sheet khác.

Để hiểu rõ hơn thì các bạn theo dõi ví dụ cụ thể sau đây:

Tạo 1 file Excel có chứa 2 sheet với cấu trúc giống nhau bao gồm:

• Từ ô A1:E10 áp dụng công thức =RANDBETWEEN(1,1000000) tức là tạo ra trong mỗi ô 1 số ngẫu nhiên từ 1 tới 1 triệu
• Tại vùng A11:E11 đặt công thức tính tổng của cột tương ứng, từ dòng 1 tới dòng 10

 

Các bạn chọn vào vùng ô A1:A11 rồi bấm vào nút RangeTimer đã gán macro tương ứng là có thể kiểm tra được tốc độ tính toán của Excel:

Trong hình ảnh trên chúng ta có thể thấy rằng việc tính toán cho 11 ô công thức trong Excel sẽ mất khoảng 0,00296 giây. Tốc độ tính toán này sẽ thay đổi khi các giá trị tại vùng dữ liệu này thay đổi. Số càng lớn thì việc tính toán càng mất nhiều thời gian hơn.

Nếu các bạn cần file mẫu của bài viết này thì hãy tải ở trong mục "Tài liệu đính kèm" nhé.

Xem thêm: Hướng dẫn các phép toán trong VBA: Phép nhân, phép chia, phép cộng, mô đun

Kết luận

Qua bài viết này chúng ta có thể thấy VBA có ứng dụng rất thú vị trong Excel. Nếu các bạn muốn tìm hiểu kỹ hơn về VBA nhằm tự động hóa các thao tác trong Excel thì hãy đăng ký khóa học sau:

VBAG01 - Tuyệt đỉnh VBA - Viết code trong tầm tay

Đây là khóa học được xây dựng bởi các chuyên gia tin học văn phòng đã có nhiều năm kinh nghiệm giảng dạy cho nhân viên của các tập đoàn lớn. Trong khóa học, bạn sẽ được cung cấp kiến thức từ cơ bản đến nâng cao, vừa học lý thuyết vừa làm bài tập thực hành để dễ dàng tiếp thu kiến thức. Nếu bạn có thắc mắc gì chưa hiểu về bài học, hãy để lại câu hỏi trong mục Hỏi - Đáp ngay bên dưới mỗi video bài giảng. Chuyên gia VBA Excel của Gitiho sẽ phản hồi các bạn trong vòng 24h. Chúc các bạn học tập hiệu quả!