Defined Function vs Formula Array, tiện lợi vs tốc độ

[adam_tran]

Topic này tớ lập ra để cùng nhau trao đổi về những cách sử dụng phối hợp các công thức của Excel và VBA sao cho CSDL đơn giản và tối ưu tốc độ.
Mấy ngày nay tôi tập coding các hàm để thay thế các Formula Array đang dùng như sau:
Các Formula Array và Defined Function:
{=SUM(IF(MaVL=$H2;IF(maKH=$G$1;IF(Ngay>=$G$2;IF(Ngay<=$G$3;SoL;0);0);0);0))}

Function Tonghop(xMaVL As Range, xMaKH As Range, xNgay As Range, xSo As Range, maVL As String, maKH As String, Ngay1 As Date, Ngay2 As Date)
Dim i As Long
Tonghop = 0
For i = 1 To xMaVL.Rows.Count
If xMaVL(i) = "" Then Exit Function
If (xMaVL(i) = maVL) And (xMaKH(i) = maKH) And (xNgay(i) >= Ngay1) And (xNgay(i) <= Ngay2) _
Then Tonghop = Tonghop + xSo(i)
Next i
End Function
Nếu các Name MaSL, MaKH mở đến 3000 dòng ... trong khi CSDL của tớ có dưới 300 dòng (records), hàm Tonghop chạy nhanh hơn Formula Array. (Nhờ thủ tục Exit Function nên vòng lặp kết thúc khi đến dòng cuối cùng không có dữ liệu). Nhưng nếu dữ liệu lên đến 2000 records thì hàm Tonghop chạy chậm như rùa (máy P4 1.5Gb, RAM 128MB)
Như vậy, theo tớ đánh giá:
- Formula Array chạy nhanh hơn dùng Defined Function trên vòng lặp for đến 10 lần (với cấu hình máy như trên, cùng số dòng).
- Nếu các Name apply sẳn với nhiều dòng trống, Formula Arry sẽ chạy chậm, do đó nên có 1 macro tự động apply các Name trên một vùng có dữ liệu, tránh mở xuống dưới quá nhiều dòng trống không cần thiết.
- Có giải thuật nào tăng tốc cho vòng lặp for trên không? So sánh For với Select? Lệnh Exit For là lệnh kết thúc cả vòng lặp, nhưng nếu muốn bỏ qua vòng hiện tại, và tiếp tục những lần tiếp theo thì dùng lệnh gì?

 

[Tuanktcdcn]

Topic này tớ lập ra để cùng nhau trao đổi về những cách sử dụng phối hợp các công thức của Excel và VBA sao cho CSDL đơn giản và tối ưu tốc độ.
Mấy ngày nay tôi tập coding các hàm để thay thế các Formula Array đang dùng như sau:
Các Formula Array và Defined Function:
{=SUM(IF(MaVL=$H2;IF(maKH=$G$1;IF(Ngay>=$G$2;IF(Ngay<=$G$3;SoL;0);0);0);0))}

Function Tonghop(xMaVL As Range, xMaKH As Range, xNgay As Range, xSo As Range, maVL As String, maKH As String, Ngay1 As Date, Ngay2 As Date)
Dim i As Long
Tonghop = 0
For i = 1 To xMaVL.Rows.Count
If xMaVL(i) = "" Then Exit Function
If (xMaVL(i) = maVL) And (xMaKH(i) = maKH) And (xNgay(i) >= Ngay1) And (xNgay(i) <= Ngay2) _
Then Tonghop = Tonghop + xSo(i)
Next i
End Function
Nếu các Name MaSL, MaKH mở đến 3000 dòng ... trong khi CSDL của tớ có dưới 300 dòng (records), hàm Tonghop chạy nhanh hơn Formula Array. (Nhờ thủ tục Exit Function nên vòng lặp kết thúc khi đến dòng cuối cùng không có dữ liệu). Nhưng nếu dữ liệu lên đến 2000 records thì hàm Tonghop chạy chậm như rùa (máy P4 1.5Gb, RAM 128MB)

Trong hàm trên bạn dùng lệnh "Exit Function" tức là sẽ thoát khỏi hàm "Tonghop" khi có MãVL ở dòng thứ I="" . Như vậy nếu ở dòng I+n mà MãVL<>"" thì sao? Vì thế khai báo "Exit Function" là sai.
I chữa lại nhưa sau:

Function Tonghop(xMaVL As Range, xMaKH As Range, xNgay As Range, xSo As Range, maVL As String, maKH As String, Ngay1 As Date, Ngay2 As Date, Optional Byval DaSapxep as Boolean=False)
Dim i As Long
Dim bTonghop
bTonghop=0
For i = 1 To xMaVL.Rows.Count
If xMaVL(i) = "" Then
If DaSapxep and bTonghop<> 0 then
Exit For
End If
Else
If (xMaVL(i) = maVL) And (xMaKH(i) = maKH) And (xNgay(i) >= Ngay1) And (xNgay(i) <= Ngay2) _
Then bTonghop = bTonghop + xSo(i)
End If
Next i

Tonghop=bTonghop
End Function

- Có giải thuật nào tăng tốc cho vòng lặp for trên không? So sánh For với Select? Lệnh Exit For là lệnh kết thúc cả vòng lặp, nhưng nếu muốn bỏ qua vòng hiện tại, và tiếp tục những lần tiếp theo thì dùng lệnh gì?

Viết một hàm tính như "Tonghop" trong môi trường VBA chỉ đạt được hiệu quả như vậy mà thôi. Người ta thường chỉ viết một hàm khi nó cần cung cấp cho một chỉ tiêu đơn lẻ (VD một mã khách hàng) và nhằm giảm bới khai báo các tham số cho ngắn gọn dễ dùng, còn nếu cần lập chỉ tiêu cho một bảng dữ liệu (n khách hàng) thì việc dùng hàm "Tonghop" để Copy cho nhiều khách hàng là một sai lầm, nên dùng công thức mảng của EXCEL ( I khẳng định dùng công thức mảng tối ưu hơn hàm "Tonghop").
Một giải pháp tối ưu tốt hơn việc viết hàm hay dùng công thức EXCEL là ta viết riêng một thủ tục (Sub):
Giả sử CSDL có 1000 dòng, để tính số liệu 1 KH (khách hàng) bạn phải dùng 1 hàm "Tonghop" EXCEL thực hiện khoảng >=1000 phép tính, bảng số liệu cần tính có Tồn đầu/Nợ|Có; Phát sinh/Nợ|Có Như vậy 1 KH bạn phải dùng 4 hàm "Tonghop" à EXCEL phải thực hiện số phép tính khoảng >=4*1000 tức 4000 phép tính (Nếu dùng công thức mảng thì cũng như vậy thôi). Nếu bạn có 100 KH tức bạn sẽ Copy từ KH thứ nhất->KH thứ 100 vậy là EXCEL bạn sẽ phải dùng 4*100=400 hàm "Tonghop", EXCEL phải thực hiện 400*1000=400 000 phép tính. Với yêu cầu trên bạn muốn máy chạy trong bao lâu? Bây giờ giải pháp chỉ là mua máy tính với cấu hình cao hơn mà thôi!
Giải pháp ở đây là: Chỉ dùng duy nhất một vòng lặp For i=1 to 1000, trong mỗi lần lặp phải tính một lúc cho 4 cột vào đúng mã KH (phải loại trừ KH=""). Với cách phân tích trên thì dù bạn có 1000000000000xxx KH thì bạn cũng chỉ cần một vòng lặp duy nhất (tức khỉ trong khoảng 1000 phép tính). Nếu bạn dùng 1000000000000xxx công thức mảng hay hàm "Tonghop" thì chắc máy tính của bạn đã bốc khói mất rồi.
Trên chỉ là thuật toán I chỉ ra còn việc coding nó bạn tự làm sẽ tốt hơn khi còn đang học VBA hay lập trình CSDL.
Select ...End Select <-> If ...End If
For... Next gần như Do .... While
Bạn nhấn F1 trong môi trường VBA chắc sẽ rõ hơn.

 

[adam_tran]

Trên chỉ là thuật toán I chỉ ra còn việc coding nó bạn tự làm sẽ tốt hơn khi còn đang học VBA hay lập trình CSDL.
.

Đúng rồi, phải coding và thực tế nhiều mới có kinh nghiệm. Để tăng tốc cho Excel, tớ cần chú ý đến 2 vấn đề sau:
- Các thuật toán trong các hàm chuẩn của Excel là DLL viết bằng ngôn ngữ nhị phân nào đó... có tốc độ nhanh hơn khoảng 10 lần so với hàm tương tự viết bằng code VBA.
- Tính các phép tính trong thuật toán của mình khi coding để suy ra Code của mình nhanh hay chậm.
Cám ơn bạn nhiều!

 

[Tran Chau]

Có tác giả nước ngoài lại khuyên không nên vội vả nhảy vào vòng lặp, i đã đọc nhưng chỉ hiểu được 1 chút chút, post các bạn thâm cứu
Excel VBA Using Loops in Excel
Don't get caught in the Loop:
Đừng dùng vội trong Loop
To put it bluntly I very often avoid Loops, they are far too sloooow in many cases. A common mistake we all make when first
Don't get caught in the Loop:

To put it bluntly I very often avoid Loops, they are far too sloooow in many cases. A common mistake we all make when first learning VBA is to use Loops when we really shouldn't..Take the simple example below for instance. It Loops through a range and places the word "Blank" in each blank cell within a used range, i.e it assumes the last occupied cell is D500


Sub WrongWay()
Dim Bcell As Range
For Each Bcell In Range("A1500")
If IsEmpty(Bcell) Then Bcell = "Blank"
Next Bcell
End Sub

Now compare the above code to this one:
Sub RightWay()
If WorksheetFunction.CountA(Range("A1500")) = 0 Then
MsgBox "All cells are empty", vbOKOnly, "OzGrid.com"
Exit Sub
End If
On Error Resume Next
Range("A1500").SpecialCells(xlCellTypeBlanks) = "Blank"
On Error GoTo 0
End Sub

Not only is it much easier to type, but it will run much much quicker.

Next time you have a VBA task, don't rush in with a Loop. Step back and give some serious thought to Excels built in functions. Some of my favourites to use are: SpecialCells, AutoFilter, Find, AdvancedFilter and Subtotals to name but a few.Once you start to make use of these you will always think twice before using a Loop of any kind.

Instead of a Loop, try inserting a Column and placing a formula in the required range that makes the check on the cells. Use a number for a Yes and text for a No, then use SpecialCells to do the rest. I can promise you there is nearly always a built in feature that will execute at least 100 times quicker than a Loop. You just need to step outside the box!

Here is another comparison to stress my point!:
Place the text "Find Me" into cell IV65536 and run this code:


Sub NoLoop()
If WorksheetFunction.CountIf(Cells, "Find Me") = 0 Then
MsgBox "You didn't type 'Find Me'", vbOKOnly, "OzGrid.com"
Exit Sub
End If

Cells.Find(What:="Find Me", After:=[A1], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate

End Sub

Now if you have at least 5 minutes to spare, try this code that uses a Loop:

Sub WithLoop()
Dim rCell As Range

For Each rCell In Cells
If rCell.Value = "Find Me" Then
rCell.Activate
Exit For
End If
Next rCell
End Sub

To stop the Loop, push Ctrl+Break or Esc. Now that has to at least make you try alternatives for Loops!

Now sometimes a Loop might be the only way or the best way (not too often though). If this is the case we should restrict our range to only the cells we need. The example below will change the font color of all negative value cells to yellow and the backgroud to red for an entire Worksheet. Truth be known I would use conditional formatting! Before it permforms the loop though it restricts the range to only numeric cells.

Sub FastestLoop()
Dim rCcells As Range, rFcells As Range
Dim rAcells As Range, rLoopCells As Range

'Set variable to all used cells
Set rAcells = ActiveSheet.UsedRange
rAcells.Select
On Error Resume Next 'In case of no formula or constants.
'Set variable to all numeric constants
Set rCcells = rAcells.SpecialCells(xlCellTypeConstants, xlNumbers)
'Set variable to all numeric formulas
Set rFcells = rAcells.SpecialCells(xlCellTypeFormulas, xlNumbers)

'Determine which type of numeric data (formulas, constants or none)
If rCcells Is Nothing And rFcells Is Nothing Then
MsgBox "You Worksheet contains no numbers"
End
ElseIf rCcells Is Nothing Then
Set rAcells = rFcells 'formulas
ElseIf rFcells Is Nothing Then
Set rAcells = rCcells 'constants
Else
Set rAcells = Application.Union(rFcells, rCcells) 'Both
End If
On Error GoTo 0

'Loop through needed cells only see if negative
For Each rLoopCells In rAcells
If rLoopCells.Value < 0 Then
With rLoopCells
.Interior.ColorIndex = 6
.Font.ColorIndex = 3
End With
End If
Next rLoopCells
End Sub


--------------------------------------------------------------------------------
Here is another way to speed up a loop that makes use of Excel's COUNTIF function. The code could be modified to suit almost any situation very easily. This particular Procedure Bolds all instances of the "Cat" in Column "A" of the active sheet.
Sub BoldCat()
Dim iLoop As Integer
Dim rNa As Range
Dim i As Integer

iLoop = WorksheetFunction.CountIf(Columns(1), "Cat")
Set rNa = Range("A1")

For i = 1 To iLoop
Set rNa = Columns(1).Find(What:="Cat", After:=rNa, _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True)
rNa.Font.Bold=True

Next i

End Sub

What Loops Are Good For

The examples below here show what loops are good for, in this case it is looping through a text string and passing back the positions of / within the string to an array. The array (in this case) would result in {6,11,19,22}. Each number representing the position of each /
Sub GetIt()
Dim i As Integer
Dim strText As String
Dim iPositions() As Integer
Dim iStart As Integer
Dim iLoop As Integer

strText = "Ihave/four/OfThese/In/Me"

'Find out how many "/" are within the string by subtracting _
Len("Ihave/four/OfThese/In/Me") from Len("IhavefourOfTheseInMe") _
This will result in four. We then take 1 because the first element _
in an Array is always zero, unless told otherwise.
iLoop = Len(strText) - Len _
(Application.Substitute(strText, "/", "")) - 1

'Tell the array how many elements it is to hold.
ReDim iPositions(iLoop) As Integer
iStart = 1

For i = 0 To iLoop 'loop four times
'Parse the position of the nth "/" starting from iStart.
iPositions(i) = InStr(iStart, strText, "/")

'Add one to the found position, for next InStr to start from.
iStart = iPositions(i) + 1

MsgBox "Number " & i + 1 & " '/' is in position " & _
iPositions(i), vbInformation, "OzGrid.com"
Next i

End Sub
........
Tìm tiếp tại http://www.ozgrid.com/VBA/VBALoops.htm

 

[workman]

Site của bạn gửi hay lắm. Ko chỉ có phần lập trình mà các hướng dẫn về function mà mảng cũng cực kỳ dễ hiểu và thiết thực. Cám ơn bạn nhiều.

 

[adam_tran]

Lại có thêm một cao thủ nữa, lần này chóng mặt mà học...
Bác workman lên lịch đi, rồi gọi cho em.

Lập trình thì có nhiểu giải thuật hay, cái hay ở đây là biết dùng những công cụ có sẳn, phối hợp với nhau sao cho bài toán gọn nhẹ và tốc độ xử lý nhanh. Chưa đọc cái này thì biết quái thế nào là SpecialCells, mà biết rồi chưa đọc cái của người khác thì ứng dụng thế quái nào được...
Bởi vậy, không thầy , không sách đố mày làm nên...