Lượng Nguyễn
Lượng Nguyễn
Thảo luận 21 thảo luận
Vỗ tay 0 vỗ tay
Lượt xem 227 lượt xem

em vừa viết được đoạn code sau mà chạy chậm quá Phiền kiểm tra

Chào Thầy, em vừa viết được đoạn code sau, mà chạy chậm quá. Phiền thầy kiểm tra và tư vấn thêm giúp em.
Cảm ơn Thầy.

Sub Update_LayDon()

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    Application.DisplayStatusBar = False

'Visible sheet

    Sheets("LocDon").Visible = True

    

'Xac dinh dong cuoi cot A sheet LayDon

    Dim ilaydon As Long

    ilaydon = Sheets("LayDon").Range("A" & Rows.Count).End(xlUp).Row

    

'Xoa du lieu cu tren sheet Laydon

    Sheets("Laydon").Range("A3:K" & ilaydon).ClearContents

    

'Refresh bang LocDon

    Sheets("Locdon").Select

    Range("A3").Select

    Application.CutCopyMode = False

    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

    

'Xac dinh dong cuoi cot A sheet Loc Don

    Dim i As Long

    i = Sheets("LocDon").Range("A" & Rows.Count).End(xlUp).Row

          

'Update vung Cong thuc trong sheet LocDon

    'Xac dinh dong cuoi cua vung cong thuc

    Dim ii As Long

    ii = Sheets("LocDon").Range("M" & Rows.Count).End(xlUp).Row


    'Xoa du lieu cu trong vung cong thuc

    Sheets("LocDon").Range("L4:O" & ii).ClearContents

    

    'Cong thuc xac dinh loai kien

    Sheets("Locdon").Range("L4:L" & i).FormulaR1C1 = "=RC[-1]&RC[-2]"


    

    'Cong thuc xac dinh ID

    Sheets("Locdon").Range("O4:O" & i).FormulaR1C1 = "=RC[-7]&RC[-6]"

    

    

    'Cong thuc xac dinh Ghi Chu

    Sheets("Locdon").Range("M4:M" & i).FormulaR1C1 = "=IF(RC[-5]=""B?c Ninh"",""Noi Tinh"",""Ngoai Tinh"")"

 

 

    'Cong thuc xac dinh Diem Cuoi

    Sheets("Locdon").Range("N4:N" & i).FormulaR1C1 = _

        "=INDEX(Local!C[-10],MATCH(LocDon!RC[1],Local!C[-13],0))"


    'Copy Paste value vung cong thuc

    Sheets("Locdon").Range("L4:O" & i) = Sheets("Locdon").Range("L4:O" & i).Value


'Copy tu LocDon sang LayDon

    Sheets("LocDon").Range("A3:C" & i).Copy Destination:=Sheets("LayDon").Range("A3")

    

    Sheets("LocDon").Range("N3:N" & i).Copy Destination:=Sheets("LayDon").Range("D3")


    Sheets("LocDon").Range("N3:N" & i).Copy Destination:=Sheets("LayDon").Range("E3")

    Sheets("LocDon").Range("D3:G" & i).Copy Destination:=Sheets("LayDon").Range("F3")


    Sheets("LocDon").Range("L3:M" & i).Copy Destination:=Sheets("LayDon").Range("J3")

   

'Xoa rac DonGui

    Sheets("DonGui").Cells.EntireColumn.Hidden = False

    

    Sheets("DonGui").Range("F:G,I:I,L:U,AA:AE,AI:AI,AK:AK").ClearContents

 

    Sheets("DonGui").Range("F1:G1,I1,L1:U1,AA1:AE1,AI1,AK1").Value = "abc"

   

    Sheets("DonGui").Range("F:G,I:I,L:U,AA:AE,AI:AI,AK:AK").EntireColumn.Hidden = True

        

    Sheets("LocDon").Visible = False


    Application.DisplayStatusBar = True

    Application.Calculation = xlCalculationAutomatic

    Application.ScreenUpdating = True

End Sub


Thảo luận 21 câu trả lời
Lượt xem 227 lượt xem
Vỗ tay vỗ tay
Lượng Nguyễn 09:12 - Dec 21, 2020

Chào bạn,

việc sử dụng .Formula để gán công thức vào trong 1 ô (hay vùng ô) sẽ khiến quá trình làm việc bị chậm (do excel phải gán công thức -> tính toán công thức -> nhận kết quả).

Sau đó câu lệnh .Copy Destination cũng làm chậm quá trình xử lý.

Các code liên quan tới copy, paste đều khiến quá trình chạy code diễn ra chậm hơn do tốn tài nguyên bộ nhớ nhiều hơn thông thường, đồng thời phân đoạn quá trình xử lý.

Cách khắc phục:

1. Với các công thức IF, index, match bạn hoàn toàn có thể thay thế bằng các hàm trong VBA để lấy kết quả trực tiếp được.

2. Thay vì sử dụng việc copy, bạn dùng:

Sheets("LocDon").Range("N3:N" & i).Value = Sheets("LocDon").Range("N3:N" & i).Value 

Khi đó kết quả nhận được sẽ là giá trị của công thức giống như copy paste (nhưng sẽ chỉ lấy phần giá trị thôi, không bao gồm định dạng) => tránh bị phân đoạn quá trình xử lý code.

3. Bạn có thể tìm hiểu thêm về mảng trong VBA. Khi xử lý qua mảng sẽ nhanh hơn so với xử lý trực tiếp trên Excel. Sau khi có kết quả trên mảng sẽ đưa trở lại vào Excel để lấy kết quả cuối cùng.

Vỗ tay vỗ tay
Lượng Nguyễn 23:12 - Dec 22, 2020

Chào Thầy,

Em có chia sẻ 1 file cho nhiều bạn cùng làm việc trên google driver.

File chạy rất mượt trên máy tính của em. Nhưng khi chạy trên máy tính của các bạn khác thì báo lỗi.

Phiền thầy kiểm tra giúp em xem file có vấn đề gì không? (Link file bên dưới)

Chia sẻ file có chứa VBA cho nhiều người cùng sử dụng trên GG Driver hoặc OneDriver có cần lưu ý gì không?

Cảm ơn thầy!

File chia sẻ

Vỗ tay vỗ tay
Lượng Nguyễn 08:12 - Dec 23, 2020

Chào bạn,

hiện link bạn gửi bị lỗi không mở được.

Việc chia sẻ sang máy khác thì sẽ có 1 vài điểm cần lưu ý:

- Với code trong phạm vi khóa học: chỉ cần máy tính cho phép chạy VBA (như trong hướng dẫn ở chương 1) là sử dụng được bình thường. Chú ý máy tính phải cài đặt office bản đầy đủ (thường từ office 2010 trở đi), còn bộ cài office 2007 thường bị giản lược, bỏ qua thư viện VBA khi cài.

- Với code ngoài phạm vi khóa học: sẽ có những bộ thư viện chỉ có trên 1 số phiên bản nhất định, do đó sang phiên bản khác, máy tính khác (32bit với 64 bit) sẽ gặp lỗi. Thường những trường hợp này sẽ có gắn kèm code xử lý luôn, khi dùng các file đó cần giữ nguyên code mẫu để tránh lỗi.

Vỗ tay vỗ tay
Lượng Nguyễn 09:12 - Dec 23, 2020

Vâng,

Em cảm ơn Thầy,

Vỗ tay vỗ tay
Lượng Nguyễn 09:07 - Jul 12, 2021

Xin phép hỏi ké hướng dẫn này: 1. Với các công thức IF, index, match bạn hoàn toàn có thể thay thế bằng các hàm trong VBA để lấy kết quả trực tiếp được.

Ví dụ cho biểu thức A17 =IF(ISNUMBER(E17),INDEX(E12:G17,3,MATCH(E17,E12:E17,0)))
Tks
Vỗ tay vỗ tay
Lượng Nguyễn 11:07 - Jul 12, 2021

Cảm ơn bạn Trần Văn Đức nhiều

Vỗ tay vỗ tay
Lượng Nguyễn 12:07 - Jul 12, 2021
Mình đang nhớ hướng dẫn giúp mà
Vỗ tay vỗ tay
Lượng Nguyễn 14:07 - Jul 12, 2021

Chào bạn nghĩa là bạn muốn dùng vba lấy bảng hay dữ liệu như thế nào nhỉ, bạn có thể mô tả kỹ hơn giúp gitiho không?

Vỗ tay vỗ tay
Lượng Nguyễn 14:07 - Jul 12, 2021

trên các bạn có hướng dẫn chỗ này

1. Với các công thức IF, index, match bạn hoàn toàn có thể thay thế bằng các hàm trong VBA để lấy kết quả trực tiếp được.

mình xem mà không hiểu, nên nhớ các bạn hướng dẫn chi tiết giúp cho ví dụ cụ thể như sau thì phải sử dụng hàm trong VBA như thế nào:

A17 =IF(ISNUMBER(E17),INDEX(E12:G17,3,MATCH(E17,E12:E17,0)))
Vỗ tay vỗ tay
Lượng Nguyễn 15:07 - Jul 12, 2021

Chào bạn bạn đẩy giúp gitiho file mẫu lên nhé

Vỗ tay vỗ tay
Lượng Nguyễn 16:07 - Jul 12, 2021

Nhờ bạn kiểm tra giúp và hướng dẫn cách làm

https://drive.google.com/file/d/1itDN...

Vỗ tay vỗ tay
Lượng Nguyễn 16:07 - Jul 12, 2021

Chào bạn, bạn thử như sau nhé

Sub test1()
    Dim pi As String, sodong As Integer
    If (Sheet4.Range("I9").Value <> "") Then
       sodong = Application.WorksheetFunction.Match(Sheet4.Range("I9"), Sheet4.Range("B4:B12"), 0)
       pi = Application.WorksheetFunction.Index(Sheet4.Range("B4:G12"), sodong, 4)
    End If
    Sheet4.Range("j9") = pi
End Sub
Vỗ tay vỗ tay
Lượng Nguyễn 18:07 - Jul 13, 2021

Theo hướng dẫn trên sử dụng hàm của VBA thì sẽ làm tăng tốc độ chạy code hơn, nhưng ở ví dụ file đình kèm, sử dụng hàm thông thường lại cho thời gian chạy nhanh hơn sử dụng hàm của VBA, nhờ Gitiho hướng dẫn giúp có sai gì không.

Tks

https://drive.google.com/file/d/1BqxM...

Sub hamvba()
Dim Tmr As Double
Tmr = Timer()

Sheet6.Activate
    Dim sodulieu As Integer
        sodulieu = Sheet6.Range("a2") + 7
    Dim bcd8 As String, caodo As Single
        For i = 8 To sodulieu
            If (Range("b" & i) = "ok") Then
                caodo = Application.WorksheetFunction.Index(Sheet10.Range("B1:LK86"), Sheet6.Range("A" & i), Sheet6.Range("d2"))
                If (Application.WorksheetFunction.IsNumber(caodo)) Then
                    Sheet6.Range("d" & i).Value = caodo
                End If
            End If
       Next i
MsgBox Timer() - Tmr
End Sub
Sub hamthongthuong()
Dim Tmr As Double
Tmr = Timer()

    Dim bcd8 As String
        bcd8 = "=IF(ISERROR(VALUE(IF($B8=""ok"",INDEX(kqxl,$A8,D$2),""""))),"""",IF($B8=""ok"",INDEX(kqxl,$A8,D$2),""""))"
        Sheet6.Range("d8").Value = bcd8
        Sheet6.Range("d8").Copy Sheet6.Range("d9:d27")
        Sheet6.Range("d8:d27").Value = Sheet6.Range("d8:d27").Value
 MsgBox Timer() - Tmr
End Sub

Vỗ tay vỗ tay
Lượng Nguyễn 23:07 - Jul 13, 2021

Chào bạn phần này bạn muốn nhanh phải sử dụng mảng đưa và xuất một loạt mới nhanh được

https://by.com.vn/

Vỗ tay vỗ tay
Lượng Nguyễn 23:07 - Jul 13, 2021

Ở đây đang phản biện lại hướng dẫn của Gitiho, sử dụng hàm VBA sẽ nhanh hơn, nhưng ở ví dụ trên lại thấy chậm hơn, nên không biết áp dụng hàm VBA sai chỗ nào hay không. Còn so sánh với ứng dụng quản lý khác thì không bàn rồi.

Vỗ tay vỗ tay
Lượng Nguyễn 09:07 - Jul 14, 2021

Chào bạn Trần Văn Đức,

mình giải thích như sau nhé:

1. Tốc độ hàm trong VBA (sub test thứ 1) chậm hơn là do xét thêm vòng lặp và logic IF chứ ko hẳn chỉ là tốc độ của hàm (việc gán công thức ở sub test thứ 2 không có vòng lặp)

2. Việc dùng hàm trong VBA (worksheetfunction) mục đích chính là để không có công thức động trong Sheet, để kết quả cuối cùng là giống nhau, nhưng bước thực hiện thì trực tiếp hơn, thay vì gán hàm => tính toán => lấy giá trị

Bởi nếu file có nhiều công thức ở những sheet khác => sẽ ảnh hưởng tới tốc độ tính toán của code (đây là không phải là vấn đề trực tiếp trong câu hỏi, nhưng là vấn đề thường gặp khi dùng phương pháp gán công thức)

Ngoài ra câu này có ý hướng tới đoạn code:

.FormulaR1C1 = "=RC[-1]&RC[-2]"

bởi những đoạn này có thể viết trực tiếp trong VBA mà không qua phương thức FormulaR1C1

chỉ công thức phức tạp, khó viết theo dạng VBA thì mới viết trực tiếp trong Sheet (như hàm INDEX MATCH bạn nói)

Câu này cũng mang tính gợi ý thôi, ko phải bắt buộc.

Vỗ tay vỗ tay
Lượng Nguyễn 10:07 - Jul 14, 2021

Chào bạn bạn thử nhé với mảng và thuật toán sử lý

Sub SpeedOptional(Optional ByVal TrueOrFalse As Boolean = True) ' tang toc code
    On Error Resume Next
    With Application
        If TrueOrFalse = True Then
            .Calculation = xlCalculationManual
            .EnableCancelKey = xlErrorHandler
        ElseIf TrueOrFalse = False Then
            .Calculation = xlCalculationAutomatic
            .CalculateBeforeSave = True
            .EnableCancelKey = xlInterrupt
        End If
        .ScreenUpdating = TrueOrFalse
        .EnableEvents = TrueOrFalse
        .DisplayAlerts = TrueOrFalse
        .PrintCommunication = TrueOrFalse
    End With
End Sub
Sub Chaymang()
    SpeedOptional True
    Dim Tmr As Double
    Tmr = Timer()
    Dim Arr As Variant, i As Long, dc As Long, k As Long, j As Long
    Arr = Sheet10.Range("B6:LK86").Value
    Dim ars As Variant
    ars = Sheet6.Range("B2:B28").Value
    ReDim kq(LBound(Arr, 1) To UBound(Arr, 1), LBound(Arr, 2) To UBound(Arr, 2))
        For j = LBound(ars, 1) To 1
            If (ars(j, 1)) = "ok" Then
                For i = LBound(Arr, 1) To UBound(Arr, 1)
                    k = k + 1
                    kq(k, 1) = Arr(i, 1)
                 Next i
           End If
        Next j
    Sheet6.Range("E8").Resize(k, 1).Value = kq
    MsgBox Timer() - Tmr
    SpeedOptional False
End Sub

Tại sao file chạy thuần excel lại nhanh do thuật toán của excel mặc định đã được tối ưu nên chạy rất nhanh dù bạn viết ngôn ngữ nào đi chăng nữa không nhanh bằng, ở đây bạn chạy vòng lặp và để rất nhiều công thức excel nên khi chạy nó phải chạy cả công thức dẫn đến tốc độ châm.

Ở đây mình không biết phần ok bạn tính kiểu gì nên mượn chạy luôn dùng 2 vòng lặp nếu tính toán luôn trong code tốc độ sẽ nhanh hơn rất nhiều, do mình tính toán trong bảng rồi mới ghi ra chứ không phải là tính đến đâu ghi đến đấy > nhanh hơn range.

Vỗ tay vỗ tay
Lượng Nguyễn 11:07 - Jul 14, 2021

Lý mình tham gia khóa học là để học hỏi bài bản và tối ưu những đoạn code mình đã viết, nhìn code của bạn thì mới biết được kiến thức của mình còn rất nhiều hạn chế cần học hỏi.

Mình tham gia học, hy vọng tìm được cách tối ưu code đã viết, nên nhiều khi mình mong muốn tìm bản chất của vấn đề. Một số kiến thức học online dc, mình cũng áp dụng cho mấy code mình viết trước đó, hiện tại thời gian đã giảm dc khoảng 20%, mình tin còn giảm xuống dc nữa sau khi xong khóa học. 

Vỗ tay vỗ tay
Lượng Nguyễn 11:07 - Jul 14, 2021

Chào bạn vậy mình mới nói ở trên là bạn cần tham dự khoá như vbag02 trên đó nói về mảng, dic và sql những công cụ tuyệt nhất để sử lý dữ liệu bằng vba, đảm bảo khi bạn hiểu bản chất rồi khéo sẽ không dùng mấy đến kiểu công thức rồi value xuống đâu

Vỗ tay vỗ tay
Lượng Nguyễn 11:07 - Jul 14, 2021

Ok bạn. Kiến thức trong khóa học này là nền tảng để tìm hiểu vbag02

Qua đoạn code bạn gửi, mình cũng học thêm được một số kiến thức để áp dụng sửa code cho file mình đang sử dụng trong công việc.

Tks

Vỗ tay vỗ tay
Lượng Nguyễn 11:07 - Jul 14, 2021

Chúc bạn học tốt cùng gitiho, khi bạn code quen rồi sẽ thấy dùng funtion hoặc sub(dulieu) sẽ rất tuyệt vời

Vỗ tay vỗ tay
Câu hỏi liên quan
@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội