Tăng tốc VBA bằng mảng: Use 1 Array, Use 2 Arrays, Use 3 Arrays

[Cá ngừ F1]

Xin chào các anh/chị trên GPE,

- Căn cứ Topic "Hội thảo VBA trình độ 2: cho người đã học cơ bản"
- Theo đoạn Video tại bài #6 của sư phụ PTM

[video=youtube;aQtQiL2zVnE]https://www.youtube.com/watch?v=aQtQiL2zVnE&feature=youtu.be[/video]​

Em thấy có Use 1 Array, Use 2 Arrays, Use 3 Arrays: trong bài toán tính Doanh thu.
Quả thật là em chưa rõ về khái niệm này lắm: Dùng 1 mảng, 2 mảng, 3 mảng.
Em có làm 1 file tương tự, có sử dụng Code:

Sub DoanhThu()


Dim i&, Arr(), KQ(), T As Double


T = Timer


Arr = Range(Sheet1.[B2], Sheet1.[C1000000].End(3))


    ReDim KQ(1 To UBound(Arr), 1 To 1)


    For i = 1 To UBound(Arr)


        KQ(i, 1) = Arr(i, 1) * Arr(i, 2)


    Next i


    Sheet1.[D2].Resize(i - 1, 1) = KQ


    MsgBox "SoDong " & i & " ThoiGian: " & Timer - T




End Sub

Em cũng ko biết code đó: Dùng 1 mảng, 2 mảng, 3 mảng...???

Vậy, mong anh/chị trên GPE chia sẻ những bài toán thực tế có thể áp dụng & kinh nghiệm về Dùng 1 mảng, 2 mảng, 3 mảng... để tăng tốc độ code

Em xin cảm ơn!

 

[doveandrose]

em nghĩ rằng bản chất của vòng lặp kiểu <To> trong Visual Basic nó chỉ tính toán biểu thức sau <To> 1 lần duy nhất để làm đích đến . các vòng sau cứ nhắm cái đích này mà hướng đến chứ không tính toán lại cái đích đó nữa . Bởi đơn giản con số đằng sau <to> không được coi là 1 biểu thức điều kiện . mà chỉ được coi là 1 con số được chọn làm đích đến
không giống như bản chất vòng lặp While cứ mỗi vòng là phải tính toán biểu thức điều kiệu sau While trả về True mới cho lặp tiếp
đây cũng là bản chất của vòng lặp For nhưng là trong ngôn ngữ C
nếu bài #26 được viết lại trong môi trường C

private void button1_Click(object sender, EventArgs e)
        {
            int i = 0;
            for (i = 1; i < return10();i++ )
            {


            }
        }
        private int return10()
        {
            MessageBox.Show("hello");
            return 10;
        }

thì hàm return10() sẽ được gọi 10 lần vì vòng For trong môi trường C phải tính toán lại biểu thức điều kiện giữa 2 dấu ;;
cho mỗi lần lặp
nhưng vb thì khác . kể cả là viết lại bài #26 trong môi trường VB.net thì hàm return10 () cũng chỉ được gọi 1 lần duy nhất mà thôi

 

[ptm0412]

bài #34 giải thích quá đúng về lý do 2 sub của thầy PTM có tốc độ khác nhau .

Bạn biết code trong bài 34 của bạn nói nó tính End() bao nhiêu lần không?
Cả 2 thủ tục đều tính xlToLeft 10 triệu lần. Thủ tục Calc4 tính xlUp 100 ngàn lần, thụ tục Calc6 chỉ tính 1 lần cho biến k. 10 triệu so với 10 triệu 100 ngàn nên không thấy sự khác biệt lớn là phải rồi.

Ghi chú:
Theo lập luận nhất quán của tôi từ đầu đến cuối, mỗi lần next thì biến đếm tăng step, và so sánh với <to>. Nếu <to> cần phải tính thì sẽ tính. Vậy Next vòng lặp trong được gọi bao nhiêu lần? 10 triệu lần. Next vòng lặp ngoài được gọi 100 ngàn lần.

Nhược bằng thầy PTM nhất định rằng mỗi vòng lặp nó sẽ tính toán lại biểu thức để so sánh biến lặp thì mời thầy dự đoán vòng lặp này sẽ chạy bao nhiêu lần khi cột A hoàn toàn trống trơn . thầy muốn ví dụ về Range thì em cũng xin dùng Range luôn

Public Sub hello()
Dim r As Integer
With Sheet3
    For r = 1 To .[A1000000].End(xlUp).Row Step 1
        .Range("A" & .[A1000000].End(xlUp).Row + 1).Value = r
    Next
End With
End Sub

Bạn nghĩ rằng tôi không biết for i = 1 to 1 step 1 thì chạy bao nhiêu lần à?

 

[doveandrose]

Bạn biết code trong bài 34 của bạn nói nó tính End() bao nhiêu lần không?
Cả 2 thủ tục đều tính xlToLeft 10 triệu lần. Thủ tục Calc4 tính xlUp 100 ngàn lần, thụ tục Calc6 chỉ tính 1 lần cho biến k. 10 triệu so với 10 triệu 100 ngàn nên không thấy sự khác biệt lớn là phải rồi.

Ghi chú:
Theo lập luận nhất quán của tôi từ đầu đến cuối, mỗi lần next thì biến đếm tăng step, và so sánh với <to>. Nếu <to> cần phải tính thì sẽ tính. Vậy Next vòng lặp trong được gọi bao nhiêu lần? 10 triệu lần. Next vòng lặp ngoài được gọi 100 ngàn lần.


Bạn nghĩ rằng tôi không biết for i = 1 to 1 step 1 thì chạy bao nhiêu lần à?

thầy ghi for i = 1 to 1 step 1 có 2 khả năng xảy ra :
1 là thầy thừa nhận sau To đã là 1 hằng số không đổi và không cần tính lại trong suốt vòng lặp For
2 là thầy không hiểu câu lệnh này có nghĩa là gì
.Range("A" & .[A1000000].End(xlUp).Row + 1).Value = r
thầy muốn em nghĩ thầy trong trường hợp nào
em nhỏ tuổi lắm thầy ạ . ngày mà thầy lừng danh trên diễn đàn này chắc em còn đang bú mẹ
nhưng khổ cái tính em lỳ như trâu . người khác nói mà không có lý luận không thuyết phục thì em không tin cho dù người đó có nổi tiếng đến đâu .

 

[ptm0412]

thầy ghi for i = 1 to 1 step 1 có 2 khả năng xảy ra :
1 là thầy thừa nhận sau To đã là 1 hằng số không đổi và không cần tính lại trong suốt vòng lặp For

Tôi không thừa nhận <to> là 1 số không đổi. Câu tôi viết có nghĩa rằng sau khi tính toán 1 lần đầu, <to> = 1.

Thế là for chạy vòng lặp đầu tiên. Ngay khi chạy Next lần 1, r tăng 1 thành 2, nó lại so sánh với [A1000000].End(xlUp).Row thấy lớn hơn và thoát luôn.

2 là thầy không hiểu câu lệnh này có nghĩa là gì
.Range("A" & .[A1000000].End(xlUp).Row + 1).Value = r

Tôi đã đọc và sợ rằng đọc sai nên đã chạy thử. Tôi chạy bằng F8 chứ không phải F5 nên tôi càng khẳng định vì câu lệnh đó chỉ được tô vàng có 1 lần. Sau đó sau khi gặp Next, r = 2 và thoát ra luôn.

 

[doveandrose]

Tôi không thừa nhận <to> là 1 số không đổi. Câu tôi viết có nghĩa rằng sau khi tính toán 1 lần đầu, <to> = 1.

Thế là for chạy vòng lặp đầu tiên. Ngay khi chạy Next lần 1, r tăng 1 thành 2, nó lại so sánh với [A1000000].End(xlUp).Row thấy lớn hơn và thoát luôn.


Tôi đã đọc và sợ rằng đọc sai nên đã chạy thử. Tôi chạy bằng F8 chứ không phải F5 nên tôi càng khẳng định vì câu lệnh đó chỉ được tô vàng có 1 lần. Sau đó sau khi gặp Next, r = 2 và thoát ra luôn.

theo lý luận của thầy (không phải em) thì thầy vui lòng cho biết lý do sao r lại lớn hơn ?
để em diễn giải vòng lặp theo kịch bản của thầy
khi r = 1 sau <to> là biểu thức cần tính => .[A1000000].End(xlUp).Row = 1 hợp lệ => thực hiện lệnh bên trong
.Range("A" & .[A1000000].End(xlUp).Row + 1).Value = r
mà .[A1000000].End(xlUp).Row + 1 lúc này bằng 2 => .[A2] = 1 hết 1 vòng nhảy step 1 => r = 2

khi r = 2 sau <to> là biểu thức cần tính vì .[A2] = 1 => .[A1000000].End(xlUp).Row phải = 2
vậy xin thầy cho biết lý do tại sao r = 2 và .[A1000000].End(xlUp).Row = 2 mà phải Break vòng lặp ?

 

[ptm0412]

thầy muốn em nghĩ thầy trong trường hợp nào
em nhỏ tuổi lắm thầy ạ . ngày mà thầy lừng danh trên diễn đàn này chắc em còn đang bú mẹ
nhưng khổ cái tính em lỳ như trâu . người khác nói mà không có lý luận không thuyết phục thì em không tin cho dù người đó có nổi tiếng đến đâu .

Có khi bạn phản biện đúng và chỉ ra chỗ sai của tôi cũng nên. Từ đầu đến giờ, HTN phản biện dở ẹt nên không phản bác được. Để tôi tóm tắt thế này:
- Tôi nói nếu không dùng biến ghi nhận UBound (trong trường hợp cụ thể đang dùng UBound() làm <to>), thì sẽ bị tính nhiều lần (tỷ lần, có phóng đại lên)
- HuuThangbd nói chỉ tính 1 lần và cho 1 thủ tục minh họa
- Tôi chỉ ra rằng câu lệnh Debug.Prict của Thắng chỉ cho biết giá trị của i trước khi gặp Next là bao nhiêu, chứ không chứng tỏ việc <to> có được tính hay không.
- HTN đưa 2 thủ tục chạy gần như nhau
- Tôi cho rằng UBound tính nhanh quá nên không thấy khác biệt, tôi đưa 2 thủ tục tính <to> bằng End(), sự khác biệt là rất lớn
- HTN phản biện rằng đã lấy UBound() so sánh với tính trên Range
- Tôi chấp nhận và sửa code cả 2 thủ tục đều tính trên Range, kết quả vẫn khác biệt rất lớn
- Bài 34, HTN đưa 2 thủ tục tính trên Range chạy như nhau, và cho rằng vòng lặp ngoài tính 1 lần, vòng lặp trong tính nhiều lần. Như vậy là không nhất quán với các bài ở trên, vì bài trên HTN khẳng định chỉ tính 1 lần.
- Bài 42, tôi tính lại cho thấy tại sao 2 thủ tục của Nghĩa chạy gần bằng nhau: Số lần tính của 2 thủ tục chỉ chênh nhau 1%.
- Bài 41, bạn đưa ra 1 thủ tục để hỏi (hay đố) mà không đưa ra lập luận gì sau khi chạy thủ tục của bạn.
- Bài 43 bạn cũng không có lập luận gì phản biện, chỉ tự xưng là lì. Tôi cũng lì lắm, tôi cũng tuổi trâu đây. Bạn cứ phản biện hết mình, có khi tôi sai thì sao? Có điều các phản biện từ đầu đến giờ chưa khiến tôi tự thấy sai. Các thành viên trên GPE đều biết tôi sẵn sàng nhận sai.

Nói cho rõ hơn ý của tôi để bạn phản biện đúng chỗ:
- Tôi nói rằng cần dùng 1 biến để tính số cần đưa vào <to> để khỏi tính lại nhiều lần. Dù cho tính nhanh hay chậm, nó cũng tính nhiều lần.
- Tôi đề ra nguyên tắc nên dùng biến trong tất cả các trường hợp (nhanh hay chậm), và khuyến cáo những người mới học nên làm theo.
- Tôi không nói gì đến vòng lặp ngoài hay trong, 1 vòng lặp hay 2 vòng lặp, tôi chỉ nói chung chung.

 

[ptm0412]

theo lý luận của thầy (không phải em) thì thầy vui lòng cho biết lý do sao r lại lớn hơn ?
để em diễn giải vòng lặp theo kịch bản của thầy
khi r = 1 sau <to> là biểu thức cần tính => .[A1000000].End(xlUp).Row = 1 hợp lệ => thực hiện lệnh bên trong
.Range("A" & .[A1000000].End(xlUp).Row + 1).Value = r
mà .[A1000000].End(xlUp).Row + 1 lúc này bằng 2 => .[A2] = 1 hết 1 vòng nhảy step 1 => r = 2

khi r = 2 sau <to> là biểu thức cần tính vì .[A2] = 1 => .[A1000000].End(xlUp).Row phải = 2
vậy xin thầy cho biết lý do tại sao r = 2 và .[A1000000].End(xlUp).Row = 2 mà phải Break vòng lặp ?

Cái này hay nè, để tôi nghiên cứu thêm. Tôi chưa sai vì rõ ràng do phải tính toán nên thủ tục 2 của tôi chạy chậm hơn thủ tục 1 nhiều lần.

 

[doveandrose]

Cái này hay nè, để tôi nghiên cứu thêm. Tôi chưa sai vì rõ ràng do phải tính toán nên thủ tục 2 của tôi chạy chậm hơn thủ tục 1 nhiều lần.

thầy cứ trả lời #45 cho hợp lý đã rồi ta mới bàn tiếp được thầy ạ

 

[doveandrose]

khi nào thầy quay lại thì mời thầy ghé qua đây
http://stackoverflow.com/questions/...arp-execute-math-sqrt-more-slowly-than-vb-net
và để ý chỗ này trong đó

The C# implementation is recalculating Math.Sqrt(suspectPrime) each time through the loop, while VB only calculates it at the beginning of the loop. This is just due to the nature of the control structure. In C#, for is just a fancy while loop, while in VB it's a separate construct.

không biết ai sao chứ em rất có cảm tình với những người quyền cao chức lớn mà sẵn sàng thừa nhận sai lầm . bởi nó là tiền đề cho xã hội phát triển . tuổi em chỉ phát biểu được nhiêu đó thôi .

 

[le tin]

Tôi thử code này :
Sub Test()
Dim i, j, arr()
ReDim arr(1 To 20)
For i = 1 To UBound(arr) Step 1
j = j + 1
ReDim arr(1 To 10)
Next
MsgBox j
MsgBox UBound(arr)
End Sub
Kết quả : j=20 ; UBound(arr)=10 . Vậy nó đâu có tính lại .

 

[huuthang_bd]

Tôi không nghĩ vấn đề này lại cần phải tranh luận nhiều đến vậy. Vì mọi chuyện quá rõ ràng.
Như tôi đã nói. Dòng lệnh For... to... chỉ được thực thi 1 lần. Không thể đặt dòng lệnh này trong một vòng lặp khác rồi nói rằng nó không phải được thực thi 1 lần được. Vì mọi lệnh trong vòng lặp đều được lặp lại, đó là nguyên tắc cơ bản.

Ở bài 29, thủ tục Calc2 chậm hơn thủ tục Calc1 là do [XX2].End(xlToLeft).Column được tính lại theo số lần lặp của vòng lặp mẹ (vòng lặp For i...) chứ không phải được tính lại cho bản thân nó (vòng lặp For j...). Việc so sánh 2 thủ tục này với nhau là không công bằng. Nó giống như so sánh hai thủ tục sau:

Sub Sub1()
EndR = [A1048576].End(xlUp).Row
For i = 1 To 1000000
    Debug.Print EndR
Next
End Sub

Sub sub2()
For i = 1 To 1000000
    Debug.Print [A1048576].End(xlUp).Row
Next
End Sub

Nếu muốn so sánh, để so sánh công bằng thì phải so sánh như vầy:

Sub Calc1()
Dim arr(1 To 100000, 1 To 100)
t = Timer
k = ([A150000].End(xlUp).Row - 1)': [COLOR=#ff0000]m = [XX2].End(xlToLeft).Column[/COLOR]
For i = 1 To k
    [COLOR=#0000cd][B]m = [XX2].End(xlToLeft).Column[/B][/COLOR]
    For j = 1 To m
        arr(i, j) = 0
    Next
Next
[A1] = Timer - t
End Sub

Sub Calc2()
Dim arr(1 To 100000, 1 To 100)
t = Timer
For i = 1 To ([A150000].End(xlUp).Row - 1)
    For j = 1 To [XX2].End(xlToLeft).Column
        arr(i, j) = 0
    Next
Next
[B1] = Timer - t
End Sub

Ngoài ra, khi Run Step (F8) thì dòng lệnh nào được Highlight là dòng lệnh sắp thực thi. Nếu Run Step 1 vòng lặp For... to... sẽ thấy dòng For... to... chỉ Highlight 1 lần, sau dòng Next sẽ nhảy đến dòng dưới dòng For... to...

 

[Hoàng Trọng Nghĩa]

khi nào thầy quay lại thì mời thầy ghé qua đâyhttp://stackoverflow.com/questions/...arp-execute-math-sqrt-more-slowly-than-vb-netvà để ý chỗ này trong đókhông biết ai sao chứ em rất có cảm tình với những người quyền cao chức lớn mà sẵn sàng thừa nhận sai lầm . bởi nó là tiền đề cho xã hội phát triển . tuổi em chỉ phát biểu được nhiêu đó thôi .

Đúng vậy, trong chừng mực cho phép thì bảo vệ quan điểm và giữ lập trường của mình là hợp lý, nhưng nếu quá bảo thủ, không thừa nhận sự thật thì không tốt lắm!

 

[ptm0412]

khi nào thầy quay lại thì mời thầy ghé qua đây
http://stackoverflow.com/questions/...arp-execute-math-sqrt-more-slowly-than-vb-net
và để ý chỗ này trong đó

Hai hôm nay tôi quá bận không online lâu được nên bây giờ tôi mới viết trả lời.
Tôi đã nhận ra tôi sai 1 phần từ bài 45 của bạn mà không cần xem dẫn chứng. Tôi nói nhận sai 1 phần vì còn phân vân tại sao 2 thủ tục calc1 và calc2 của tôi ở bài 29, sửa lại ở bài 33 vẫn có sự khác biệt lớn. Lúc đó tôi còn thắc mắc:
Nếu chỉ tính 1 lần ở đầu vòng lặp, tại sao vòng lặp bên trong không tính 1 lần? Sau đó tôi không rảnh nên chưa tạo thêm các trường hợp để test.
Đến bài số 51 của huuthangbd, chỉ cần đọc 1 câu được tô đậm của anh ta, tôi thấy ngay vấn đề. Như vậy tôi đã sai khi tuyên bố luôn luôn tính lại. Xin cám ơn bạn doveandrose và bạn huuthang.

Nhân tiện, tôi cũng sai ở chỗ này: Tôi đã tính ra rằng calc2 tính lại 10 triệu lần, thực ra chỉ tính 100.000 lần, đó là số lần lặp của vòng for ngoài.

Dù sao, tôi cũng giữ nguyên ý kiến rằng, trong mọi trường hợp, người mới học nên đặt 1 biến để ghi lại giá trị <to>, dù 1 vòng lặp hay nhiều vòng lặp, để thống nhất cách viết code, sau này phải dùng 2 vòng lặp trở lên không bị chậm.

TB:
Tôi cũng lỳ vì tôi tuổi trâu, nên những phản biện cần phải có lập luận kèm theo, giải thích đúng và đúng chỗ tôi lập luận sai, tôi mới chịu. Chứ nếu chỉ đưa code chứng minh cái của mình mà không chỉ ra chỗ sai của code tôi viết thì tôi chưa chịu. Đó là tôi nói về bài 45 của bạn dove và bài 51 của huuthangbd, chỉ cần 1 câu đúng và đúng chỗ là tôi thấy ngay chỗ sai của mình. Kể cả bài 41 của bạn dove cũng chưa thuyết phục được tôi.

Đúng vậy, trong chừng mực cho phép thì bảo vệ quan điểm và giữ lập trường của mình là hợp lý, nhưng nếu quá bảo thủ, không thừa nhận sự thật thì không tốt lắm!

Tôi không bảo thủ. Tôi chỉ tâm phục khẩu phục những lập luận chính xác chỉ ra được chỗ sai trong lập luận của tôi. Những bài viết của nghĩa không có giá trị lý luận, chỉ viết code đưa lên chứng minh chính mình. Trong bài tôi tính số lần lặp của calc4 và calc6, nếu là người lý luận tốt như huuthangbd, nghĩa chỉ cần nói 1 câu như huuthangbd thôi, tôi đã nhận sai. Bản than bài 34 Nghĩa đưa lên 2 thủ tục calc4 và calc6 cũng viết mơ hồ, lúc thì trong ngoài, lúc thì đầu cuối (không biết đầu cuối trong cùng thủ tục hay thủ tục đầu, thủ tục cuối). Lại còn nói tôi tưởng 100 ngàn. Lúc đó tôi tưởng 10 triệu 100 ngàn đấy chứ?

 

[VetMini]

Thấy có bạn so sánh cách hoạt động vủa vòng lặp theo C nên tôi góp thêm về nguyên tắc của vòng lặp FOR:

Mỗi ngôn ngữ có quy luật riêng về cách đếm sô lượt trong vòng lặp FOR:

- Ngôn ngữ cổ đại như FORTRAN 2: vòng lặp for luôn luôn chạy ít nhất 1 lần, không cần biết biến điều khiển có lớn hơn trị cuối hay không
- Ngôn ngữ Algol 60 (và Pascal cổ): biến điều khiển đếm được tính theo đầu vòng lặp, trong thân vòng lặp không thể thay đổi.
- Ngôn ngữ C (và các loại cùng họ): biến điều khiển và giới hạn vòng lặp hoàn toàn cơ động, muốn thay đổi ra sao cũng được. Trên thực tế, vòng lặp For trong C có thế có từ 0 đến n biến điều khiển. vd: for (; { code ở đây } và for (int i1 = 1, i2 = 10; i1 <= 10 && i2 >= 1; i1++, i2--) { code ở đây } đều làm được cả, code thứ nhất không có biến điều khiển và code thứ 2 có 2 biến điều khiển. Điều kiện giới hạn cuối cũng cơ động. Tức là cái biểu thức nằm giữa 2 dấu chấm phẩy của dòng for là một biểu thức trọn vẹn, luôn luôn được tính nghiêm chỉnh. (*)

Riêng VBA, biến điều khiển có thể thay đổi trong thân vòng lặp, và giới hạn cuốí chỉ tính 1 lần, ở đầu vòng lặp.

(*) chú thích: theo đúng lý thuyết thì vòng lặp C khôg thể đem so sánh với các ngôn ngữ khác, bởi vì C định nghĩa vòng lặp C theo nghĩa rất rộng
lệnh for được tiếp theo bởi mệnh đề điều khiển, nằm trong 2 dấu ngoặc, mệnh đề điều khiển gồm 3 phần, mỗi phần là 1 lệnh. Phân thứ nhất được xử lý trước khi bắt đầu vòng lặp, và chỉ xử lý 1 lần duy nhất. Phần thứ 2 được xử lý trước khi bước vào thân vòng lặp, và sẽ lặp lại sau phần 3. Phần thứ 3 được xử lý sau khi chạy xong dòng cuối cùng của vòng lặp. Như vậy, thoe lý thuyết, vòng lặp for hoàn toàn không cần có số đếm gì cả - tuy rằng viết code như vậy hơi kỳ quặc.

Trái hẳn với C, VBA xác định vòng lặp rất quy củ, lệnh for là một lệnh có biến đếm nghiêm chỉnh. Biến đếm được đặt một trị đầu và định một giới hạn cuối, hệ số tăng giảm cũng đươc định ngay trong dòng lệnh for. Cách thay đổi duy nhất là thay đổi biến ngay bên trong vòng lặp.