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
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.
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!
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âng,
Em cảm ơn Thầy,
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.
Cảm ơn bạn Trần Văn Đức nhiều
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?
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)))
Chào bạn bạn đẩy giúp gitiho file mẫu lên nhé
Nhờ bạn kiểm tra giúp và hướng dẫn cách làm
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
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
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
Ở đâ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.
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.
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.
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.
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
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
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