Giúp giải thích khai báo biến array và Range

Văn Toàn 1996 · 11/9/23

Chào tất cả mọi người. Mình có test 1 đoạn code bên dưới với 2 kiểu khai báo biến như sau
Dim sArr() và Dim sArr
Mình thấy 2 kết quả lọc đều giống nhau, nhưng khi lọc dữ liệu lớn thì Dim sArr có tốc độ nhanh hơn
Vậy khi nào dùng Dim sArr() và khi nào nên dùng Dim sArr. em xin chân thành cảm ơn ạ

Mã:

Sub TESTLOC()
Dim sArr(), dArr(), Dk1 As String, I As Long, K As Long, R As Long, Col As Long, a As Long
sArr = Range("A1:D1000").Value
R = UBound(sArr)
ReDim dArr(1 To R, 1 To 4)
For I = 1 To R
    If sArr(I, 1) = 1 Then
        K = K + 1
        For Col = 1 To 4
            dArr(K, Col) = sArr(I, Col)
        Next Col
    End If
Next I
Range("E1:H1000").ClearContents
Range("E1").Resize(K, 4) = dArr
End Sub

VetMini · 12/9/23

Gãi trúng chỗ ngứa. Điểm mà tôi thích nói chuyện nhất là cấu trúc dữ liệu (Array là một loại cấu trúc) và kiểu dữ liệu (Variant là một kiểu dữ liệu có thể thay đổi kiểu)

Dim a1 ' a1 là một variant. Lúc được gán mới biết nó là cái gì
Dim a2() ' xác định a2 là môt mảng
Dim a3() As Long ' xác định a3 là một mảng chứa kiểu dữ liệu Long

a1 = 1 ' chả sao cả, này typename sẽ cho biết hiện tại nó là Integer
a2 = 1 ' lỗi compile error: cant assign to array

a1 = [a1:a3] ' a1 sẽ là mảng
a2 = [a1:a3] ' a2 vốn là mảng. Các phần tử do là variant cho nen chúng sẽ theo kiểu của các ô a1:a3
a3 = [a1:a3] ' a2 là mảng. Các phần do là Long cho nên sẽ tùy vào a1:a3; nếu tất cả đều là số thì khoogn có lỗi, nếu chỉ một trong 3 là chuỗi thì sẽ bị run time error: type mismatch
Tôi hy vọng ở trình độ code này, bạn đã biết khác nhau giữa compile error và run time error. Không biết thì bạn đa học dốt giai đoạn, cần học lại.

Về câu hỏi nên dùng cái nào thì ở trình độ của bạn nên dùng chách khai báo trơn không có dấu ngoặc hơn.
Chỉ khi nào bạn rành debug và hiểu rõ phương pháp và lý do bảo vệ kiểu dữ liệu của một biến thì mới nên tính đến cách khai báo khác.

Về việc cách nào chạy nhanh hơn thì tôi không buồn tra cứu. Tôi là dân chuyên C/C++ và CSDL, lúc cần hiệu quả tốc độ code, tôi gầy dựng dữ liệu tét nghiêm chỉnh theo luật tét code, không chạy bừa như mấy tay lục lục thường tài.

ptm0412 · 12/9/23

Bổ sung:
Khai báo sẵn là 1 mảng thì không thể gán giá trị của 1 ô, đó là trường hợp gán giá trị của 1 vùng dữ liệu động chưa biết kích thước. Kiểu tìm LastRow mà LastRow lại bằng với dòng đầu tiên và chỉ lấy 1 cột.

befaint · 12/9/23

Cái này từng có chủ đề óp ti mai code "toé lửa" đó.

HieuCD · 12/9/23

Mình thường khai báo arr() vì code chạy nhanh hơn

VetMini · 12/9/23

ptm0412 đã viết:
Bổ sung:
Khai báo sẵn là 1 mảng thì không thể gán giá trị của 1 ô, đó là trường hợp gán giá trị của 1 vùng dữ liệu động chưa biết kích thước. Kiểu tìm LastRow mà LastRow lại bằng với dòng đầu tiên và chỉ lấy 1 cột.

Hàm lấy trị của Range hoạt động có phần hơi không thống nhất.
- Nếu truy bằng lệnh Set (lệnh gán chính của Object) thì nó đưa địa chỉ Object Range cho bên được gán
- Nếu truy bằng lệnh Let (chính thức lệnh gán trị của Basic, về sau này người ta hiểu ngầm Let) thì nó trả về Value của Range. Điểm không đồng nhất là nếu Range chỉ gồm 1 ô thì nó trả về luôn giá trị của ô đó (số hoặc chuỗi), nếu range có ít nhất 2 ô thì nó trả về một mảng 2 chiều là rị của các ô trong tange, nếu range là một union (nhiều vùng khác nhau) thì nó trả về cũng theo luật trên nhưng chỉ sử dụng vùng đâu tiên trong union, bỏ qua các vùng còn lại.

HieuCD đã viết:
Mình thường khai báo arr() vì code chạy nhanh hơn

Theo lô gic tôi nghĩ thì đáng lẽ khai báo trước như vây, như bạn nói VBA sẽ chạy nhanh hơn vì nó không cần phải chuyển kiểu của biến, và lúc truy cập cũng nhanh hơn vì không cần qua trung gian Variant.
Nhưng vì thớt nói khai kiểu kia nhanh hơn cho nên tôi mới dè dặt phản ánh lại là tùy theo test bằng cách nào, trên dữ liệu nào. Té ra cách tét của thớt là dỏm!

Văn Toàn 1996 · 12/9/23

HieuCD đã viết:
Mình thường khai báo arr() vì code chạy nhanh hơn

Em thử code này thấy sArr () là 2.4 giây, mà sArr là 1.2 giây

Mã:

Sub testloc()
Dim sArr, dArr(), Dk1 As String, I As Long, K As Long, R As Long, Col As Long, a As Long, Tmr As Double
Tmr = Timer()
a = 800000
sArr = Range("A1:S" & a).Value
R = UBound(sArr)
ReDim dArr(1 To R, 1 To 19)
For I = 1 To R
    If sArr(I, 6) = 3 Then
        K = K + 1
        For Col = 1 To 19
            dArr(K, Col) = sArr(I, Col)
        Next Col
    End If
Next I
Range("V1:AN1000000").ClearContents
Range("V1").Resize(K, 19) = dArr
MsgBox Timer() - Tmr
End Sub

File Text

VetMini · 12/9/23

Văn Toàn 1996 đã viết:
Em thử code này thấy sArr () là 2.4 giây, mà sArr là 1.2 giây
...

Tét như vậy là lười biếng.
Code làm nhiều chuyện như vậy thì phải xét giờ từng giai đoạn
Tối thiểu: Cứ mỗi lần lấy dữ liệu, chạy xong một vòng lặp, gán dữ liệu thì phải cho biết mõi giai đoạn ấy là bao lâu.

Văn Toàn 1996 · 12/9/23

VetMini đã viết:
Tét như vậy là lười biếng.
Code làm nhiều chuyện như vậy thì phải xét giờ từng giai đoạn
Tối thiểu: Cứ mỗi lần lấy dữ liệu, chạy xong một vòng lặp, gán dữ liệu thì phải cho biết mõi giai đoạn ấy là bao lâu.

Đã Test 10 lần gửi kết quả anh xem xét

VetMini · 12/9/23

Văn Toàn 1996 đã viết:
Đã Test 10 lần gửi kết quả anh xem xét
...

Khong cần xem. Bạn mới cần xem lại. Giai đoạn khác với lượt.

HieuCD · 12/9/23

Văn Toàn 1996 đã viết:

Em thử code này thấy sArr () là 2.4 giây, mà sArr là 1.2 giây

Mã:

Sub testloc()
Dim sArr, dArr(), Dk1 As String, I As Long, K As Long, R As Long, Col As Long, a As Long, Tmr As Double
Tmr = Timer()
a = 800000
sArr = Range("A1:S" & a).Value
R = UBound(sArr)
ReDim dArr(1 To R, 1 To 19)
For I = 1 To R
    If sArr(I, 6) = 3 Then
        K = K + 1
        For Col = 1 To 19
            dArr(K, Col) = sArr(I, Col)
        Next Col
    End If
Next I
Range("V1:AN1000000").ClearContents
Range("V1").Resize(K, 19) = dArr
MsgBox Timer() - Tmr
End Sub

File Text

Máy mình báo không đủ bộ nhớ, cần chia nhỏ dữ liệu để xử lý nhiều lần

ptm0412 · 12/9/23

Máy tôi chạy được (1 triệu cũng được) nên test như sau:
Copy thêm 1 sheet y vậy
Chạy vòng lặp chạy 2 sheet, mỗi lần tính thời gian riêng

kết quả

VetMini · 12/9/23

ptm0412 đã viết:
Máy tôi chạy được (1 triệu cũng được) nên test như sau:
Copy thêm 1 sheet y vậy
Chạy vòng lặp chạy 2 sheet, mỗi lần tính thời gian riêng

kết quả

View attachment 294796

Excel coi dữ liệu sheet là mảng hai chiều.
Có lẽ hàm copy mặc định của sheet có sẵn cách dựng mảng riêng của nó và chỉ trao qua đổi lại với Variant mới hiệu quả. Trao đổi với mảng dựng sẵn có thể nó phải copy memory (mà memcopy đúng ra cóp rất nhanh, có thể còn điều kiện gì nữa tôi chưa tìm ra).

Bạn thử định giờ truy vấn dữ liệu trong mảng từ hai loại kiểu xem sao.

Lưu ý là máy xịn không cần swap pages nhưng máy ì ạch có thể swap page vài lần.

Văn Toàn 1996 · 12/9/23

HieuCD đã viết:
Máy mình báo không đủ bộ nhớ, cần chia nhỏ dữ liệu để xử lý nhiều lần

ptm0412 đã viết:
Máy tôi chạy được (1 triệu cũng được) nên test như sau:
Copy thêm 1 sheet y vậy
Chạy vòng lặp chạy 2 sheet, mỗi lần tính thời gian riêng

kết quả

View attachment 294796

em ĐÃ test 1 triệu dòng thì Sarr nó nhanh hơn gấp đôi Sarr()

VetMini · 12/9/23

Bạn hỏi nên dùng cái nào. Tôi đã trả lời rồi, và có nói rõ là tôi không muốn dính líu đến chuyện tốc độ.

Tôi đã chỉ cho cách tét, và có lẽ bạn không hề màng tới.
Nếu mục đích của bạn để xem cái nào chạy nhanh thì cứ việc chọn nó. Không cần phải suy nghĩ nhiều.
Ở bài trước đây tôi có nói thẳng là trình độ của bạn nên dùng biến Variant. Biến Array là chuyện của dân chuyên nghiệp dùng để bảo vệ kiểu của biến trong trường hợp Project có nhiều Modules. Bạn chỉ chú ý tốc độ thì kiểu khai Variant quá hợp, không cần phải thắc mắc gì hơn.

Văn Toàn 1996 · 12/9/23

VetMini đã viết:
Bạn hỏi nên dùng cái nào. Tôi đã trả lời rồi, và có nói rõ là tôi không muốn dính líu đến chuyện tốc độ.

Tôi đã chỉ cho cách tét, và có lẽ bạn không hề màng tới.
Nếu mục đích của bạn để xem cái nào chạy nhanh thì cứ việc chọn nó. Không cần phải suy nghĩ nhiều.
Ở bài trước đây tôi có nói thẳng là trình độ của bạn nên dùng biến Variant. Biến Array là chuyện của dân chuyên nghiệp dùng để bảo vệ kiểu của biến trong trường hợp Project có nhiều Modules. Bạn chỉ chú ý tốc độ thì kiểu khai Variant quá hợp, không cần phải thắc mắc gì hơn.

Cảm ơn anh đã giúp đỡ . Vậy túm lại cú pháp Viết Code Dim sArr As Variant hoặc Dim sArr nó giống nhau hoàn toàn phải không anh

ptm0412 · 12/9/23

VetMini đã viết:
Bạn thử định giờ truy vấn dữ liệu trong mảng từ hai loại kiểu xem sao.

Tôi cũng định test với từng đoạn code, chia ra:
- Giai đoạn từ khi khai báo mảng đến khi lấy xong dữ liệu lên mảng
- Giai đoạn chạy vòng lặp điều kiện lấy kết quả
- giai đoạn gán xuống sheet

Nhưng đang bận chút
Code:

Mã:

Sub testloc()
Dim dArr(), I As Long, K As Long, R As Long, Tmr As Double
a = 800000
Set sh = Sheet1
Dim sArr
    Tmr = Timer()
    K = 0
    sArr = sh.Range("A1:S" & a).Value
    Sheet1.[v2].Value = Timer - Tmr
    Tmr = Timer
    R = UBound(sArr)
    ReDim dArr(1 To R, 1 To 19)
    For I = 1 To R
        If sArr(I, 6) = 3 Then
            K = K + 1
            For Col = 1 To 19
                dArr(K, Col) = sArr(I, Col)
            Next Col
        End If
    Next I
    Sheet1.[v3].Value = Timer - Tmr
    Tmr = Timer
    sh.Range("X1:AO1000000").ClearContents
    sh.Range("X1").Resize(K, 19) = dArr
    Sheet1.[v4].Value = Timer - Tmr
End Sub

Kết quả test 3 giai đoạn

Tôi không dám đưa ra kết luận gì về lý do tại sao lấy dữ liệu nhanh hơn. Hai mục còn lại quá ít không thể nói gì.

HieuCD · 12/9/23

ptm0412 đã viết:
Tôi cũng định test với từng đoạn code, chia ra:
- Giai đoạn từ khi khai báo mảng đến khi lấy xong dữ liệu lên mảng
- Giai đoạn chạy vòng lặp điều kiện lấy kết quả
- giai đoạn gán xuống sheet

Nhưng đang bận chút
Code:

Mã:

Sub testloc() Dim dArr(), I As Long, K As Long, R As Long, Tmr As Double a = 800000 Set sh = Sheet1 Dim sArr Tmr = Timer() K = 0 sArr = sh.Range("A1:S" & a).Value Sheet1.[v2].Value = Timer - Tmr Tmr = Timer R = UBound(sArr) ReDim dArr(1 To R, 1 To 19) For I = 1 To R If sArr(I, 6) = 3 Then K = K + 1 For Col = 1 To 19 dArr(K, Col) = sArr(I, Col) Next Col End If Next I Sheet1.[v3].Value = Timer - Tmr Tmr = Timer sh.Range("X1:AO1000000").ClearContents sh.Range("X1").Resize(K, 19) = dArr Sheet1.[v4].Value = Timer - Tmr End Sub

Kết quả test 3 giai đoạn

View attachment 294808

Tôi không dám đưa ra kết luận gì về lý do tại sao lấy dữ liệu nhanh hơn. Hai mục còn lại quá ít không thể nói gì.

Như vậy lấy dữ liệu và ghi dữ liệu thì dim arr nhanh hơn, xử lý dữ liệu thì dim arr() nhanh hơn
Code minh họa xử lý đơn giản chỉ gán dữ liệu 2 mảng (khai báo dArr() là mảng) nếu xử lý phức tạp chạy vòng for nhiều lần lúc đó tốc độ sẽ khác

HeSanbi · 13/9/23

Ngắn gọn:

1. Khai báo kiểu Variant - Dim var: Trong VBA Variant là kiểu khai báo rộng nhất, bộ nhớ biến chứa tất cả trừ kiểu Type, đồng thời là khai báo tốn kém bộ nhớ nhất.

Để biết var phải là mảng hay không, ta dùng bẫy lỗi, gọi Ubound(var).

2. Khai báo mảng chưa có kích thước Dim arr(): là khai báo mảng kiểu Variant, không cần dùng bẫy lỗi để biết kích thước chỉ cần toán tử Not Not arr.

Mã của bạn nhanh hay chậm là ở Thuộc tính Value của đối tượng range trả về có thể là giá trị hoặc mảng giá trị, Value sẽ được gán vào bộ nhớ biến arr hoặc arr(), arr có thể nhận cả 2 kiểu giá trị, arr() chỉ nhận giá trị mảng. Để hiểu phần này phải đi sâu vào việc cấp phát bộ nhớ. Cấp phát bộ nhớ ở hai kiểu arr và arr() là khác nhau hoàn toàn.

Nếu bạn xử lý dữ liệu không gồm ngày tháng và kiểu Currency nên dùng value2 sẽ nhanh hơn.

ptm0412 · 13/9/23

HieuCD đã viết:
nếu xử lý phức tạp chạy vòng for nhiều lần lúc đó tốc độ sẽ khác

Tôi thử lặp 20 lần giai đoạn lọc dữ liệu vào dArr

Mã:

    For j = 1 To 20
        ReDim dArr(1 To R, 1 To 19)
        For I = 1 To R
            If sArr(I, 6) = 3 Then
                K = K + 1
                For Col = 1 To 19
                    dArr(K, Col) = sArr(I, Col)
                Next Col
            End If
        Next I
    Next j

Kết quả

Có nhanh hơn nhưng không đáng kể.

Giúp giải thích khai báo biến array và Range

Thành viên hoạt động

Ăn cùng góc phố

Bad Excel Member

|||||||||||||

Chuyên gia GPE

Ăn cùng góc phố

Thành viên hoạt động

File đính kèm

Ăn cùng góc phố

Thành viên hoạt động

Ăn cùng góc phố

Chuyên gia GPE

Bad Excel Member

Ăn cùng góc phố

Thành viên hoạt động

Ăn cùng góc phố

Thành viên hoạt động

Bad Excel Member

Chuyên gia GPE

Nam Nhân✨Hiếu Lễ Nghĩa Trí Tín✨

Bad Excel Member