Các câu hỏi về mảng trong VBA (Array)

[viehoai]

Xin các anh chị giúp đỡ Code Gán các giá trị của một Range là các phần tử của Mãng
Ví dụ: Tôi có các giá trị của Range("A1:A10"). Tôi muốn viết code để gán giá trị của các cells từ A1:A10 là các phần tử của Mãng Arr chẳn hạn.
Xin cảm ơn các anh chị

 

[Gió Đông]

Viết kiểu này cho đơn giản (vì không biết dữ liệu Arr chắc chắn là dạng sô hay text?)

if (Arr(J, 7) = "" And Arr(J, 8) = "" And Arr(J, 9) = "") or (Arr(J, 7) <> "" And Arr(J, 8) <> "" And Arr(J, 9) <> "") then 
   dArr(J, 2) = ""
else:   dArr(J, 2) = 1   : End If

còn nhanh hay chóng thì quan trọng gì khi dữ liệu có vài ngàn dòng, khi nào dữ liệu cực lớn (hàng chục triệu) thì mới quan tâm nhanh chậm, thêm nữa nhanh chậm vẫn quan trọng thuật toán tổng thể gốc , há chi mấy con kiến nhỏ này

 

[VetMini]

Vậy thì chắc không có cách viết nào khác mà xử lý dữ liệu nhanh hơn cách viết này mọi người nhỉ:

 If Arr(J, 7) = "" And Arr(J, 8) = "" And Arr(J, 9) = "" Then
        dArr(J, 2) = ""
 ElseIf Arr(J, 7) <> "" And Arr(J, 8) <> "" And Arr(J, 9) <> "" Then
        dArr(J, 2) = ""
 Else
        dArr(J, 2) = 1
 End If

Code trên để check sự tồn tại dữ liệu ở 3 ô. Nếu tất cả đều có dữ liệu hoặc không có dữ liệu thì ra giá trị ""
Còn lại là giá trị 1

Code trước khi bạn sửa nhanh hơn, tuy nó trông rườm rà hơn.
VBA khong có tính chất xét biểu thức một cách thong minh. Khi gặp If (biểu thức 1) And (biểu thức 2) And (biểu thức 3) thì nó phải tính đủ cả 3 biểu thức. Khác với nhiều ngôn ngữ sau này (PHP chẳng hạn) xét theo kiểu thong minh, nếu (biểu thức 1) không thoả thì nó tự động biết không xét đến (biểu thức 2)
Vì vậy trường hợp nhiều điều kiện đặt lồng vào nhau chạy nhanh hơn dùng And

 

[tueyennhi]

Code trước khi bạn sửa nhanh hơn, tuy nó trông rườm rà hơn.
VBA khong có tính chất xét biểu thức một cách thong minh. Khi gặp If (biểu thức 1) And (biểu thức 2) And (biểu thức 3) thì nó phải tính đủ cả 3 biểu thức. Khác với nhiều ngôn ngữ sau này (PHP chẳng hạn) xét theo kiểu thong minh, nếu (biểu thức 1) không thoả thì nó tự động biết không xét đến (biểu thức 2)
Vì vậy trường hợp nhiều điều kiện đặt lồng vào nhau chạy nhanh hơn dùng And

Ừm nhưng không hiểu sao nó lại sai không đúng bằng cái này.

 

[tueyennhi]

Viết kiểu này cho đơn giản (vì không biết dữ liệu Arr chắc chắn là dạng sô hay text?)

if (Arr(J, 7) = "" And Arr(J, 8) = "" And Arr(J, 9) = "") or (Arr(J, 7) <> "" And Arr(J, 8) <> "" And Arr(J, 9) <> "") then 
   dArr(J, 2) = ""
else:   dArr(J, 2) = 1   : End If

còn nhanh hay chóng thì quan trọng gì khi dữ liệu có vài ngàn dòng, khi nào dữ liệu cực lớn (hàng chục triệu) thì mới quan tâm nhanh chậm, thêm nữa nhanh chậm vẫn quan trọng thuật toán tổng thể gốc , há chi mấy con kiến nhỏ này

. Tại thấy các anh trên này toàn tốc độ 0,0..mấy giây mà mình thì toàn mất gần 1s (. Nên ham hố

 

[huuthang_bd]

Vậy thì chắc không có cách viết nào khác mà xử lý dữ liệu nhanh hơn cách viết này mọi người nhỉ:

 If Arr(J, 7) = "" And Arr(J, 8) = "" And Arr(J, 9) = "" Then
        dArr(J, 2) = ""
 ElseIf Arr(J, 7) <> "" And Arr(J, 8) <> "" And Arr(J, 9) <> "" Then
        dArr(J, 2) = ""
 Else
        dArr(J, 2) = 1
 End If

Code trên để check sự tồn tại dữ liệu ở 3 ô. Nếu tất cả đều có dữ liệu hoặc không có dữ liệu thì ra giá trị ""
Còn lại là giá trị 1

Vầy có lẽ nhanh hơn

If Arr(J, 7) = "" Xor Arr(J, 8) = "" Then
    dArr(J, 2) = 1
ElseIf Arr(J, 7) = "" Xor Arr(J, 9) = "" Then
    dArr(J, 2) = 1
Else
    dArr(J, 2) = ""
End If

Có thể chỗ cẫn tối ưu không phải là chỗ này

 

[VetMini]

Ừm nhưng không hiểu sao nó lại sai không đúng bằng cái này.

Bạn viết code không có chú thích (comments). Tôi đâu có biết bạn muón làm gì đâu mà nói chuyện đúng sai.
Vì bạn đề cập chuyện "nhanh" cho nên tôi chỉ ra cách cấu trúc dòng code thôi.

 

[tueyennhi]

Vầy có lẽ nhanh hơn

If Arr(J, 7) = "" Xor Arr(J, 8) = "" Then
    dArr(J, 2) = 1
ElseIf Arr(J, 7) = "" Xor Arr(J, 9) = "" Then
    dArr(J, 2) = 1
Else
    dArr(J, 2) = ""
End If

Có thể chỗ cẫn tối ưu không phải là chỗ này

Cảm ơn huuthang_bd , code đáp ứng được yêu cầu. Có nhanh hơn một chút và quan trọng là mình lại học thêm được một vài thứ . Đúng như bạn nói, còn nhiều cái mình biết là có thể làm cho nhanh hơn nhưng code của mình dài, cũng đã nhờ mọi người xem nhưng nhọc quá nên mình vẫn phải hỏi một vài vấn đề một, hi hi.

Ví dụ code này mất thời gian hơn 1/3 tất cả các code khác: Mình phải dùng một dòng phụ để định dạng các cột có chủ định, dựa vào dòng này định dạng cho toàn bộ trang tính.

[A5].Resize(, 45).Copy
[A9].Resize(Rws, 45).PasteSpecial Paste:=xlPasteFormats

Hoặc cái này

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Rất tốn thời gian, nhưng không cho không được vì nếu không nó update cả công thức cũng chết (

 

[tueyennhi]

Bạn viết code không có chú thích (comments). Tôi đâu có biết bạn muón làm gì đâu mà nói chuyện đúng sai.
Vì bạn đề cập chuyện "nhanh" cho nên tôi chỉ ra cách cấu trúc dòng code thôi.

Hi hi, mình có ghi là Code trên để check sự tồn tại dữ liệu ở 3 ô. Nếu tất cả đều có dữ liệu hoặc không có dữ liệu thì ra giá trị ""
Còn lại là giá trị 1. Mà

 

[Gió Đông]

Cảm ơn huuthang_bd , code đáp ứng được yêu cầu. Có nhanh hơn một chút và quan trọng là mình lại học thêm được một vài thứ . Đúng như bạn nói, còn nhiều cái mình biết là có thể làm cho nhanh hơn nhưng code của mình dài, cũng đã nhờ mọi người xem nhưng nhọc quá nên mình vẫn phải hỏi một vài vấn đề một, hi hi.
Ví dụ code này mất thời gian hơn 1/3 tất cả các code khác: Mình phải dùng một dòng phụ để định dạng các cột có chủ định, dựa vào dòng này định dạng cho toàn bộ trang tính.

[A5].Resize(, 45).Copy
[A9].Resize(Rws, 45).PasteSpecial Paste:=xlPasteFormats

Tôi có lần xem cái code đó rui, bạn nên vứt cái dòng phụ đó đi, và xóa bỏ cái đoạn code format đó đi sẽ nhanh thôi, vì

+ Không phải lúc nào cũng format lại bảng tính thế, vì format rui lại format lại ah

+ nên làm 1 sub riêng về format, gắn với button chỉ chạy khi cần, không chạy cùng chương trình tính toán chính: định dạng luôn là luôn format thêm 200 dòng (dòng trắng dưới của bảng) nữa chẳng hạn hoặc cho chọn định dạng toàn bảng và thêm 200 dòng nữa ...

như thế code chính mới nhằm vào mục đích chính là tính toán không phải format, không phải để sẵn 1 dòng thừa như thế (luôn lấy dòng 1 làm chuẩn là đủ). CÒn 3 cái and or này, cứ thử test đi, nhanh chậm không đáng kể, so với format ngoài bảng tính số lượng dòng nhiều.

 

[tueyennhi]

Tôi có lần xem cái code đó rui, bạn nên vứt cái dòng phụ đó đi, và xóa bỏ cái đoạn code format đó đi sẽ nhanh thôi, vì

+ Không phải lúc nào cũng format lại bảng tính thế, vì format rui lại format lại ah

+ nên làm 1 sub riêng về format, gắn với button chỉ chạy khi cần, không chạy cùng chương trình tính toán chính: định dạng luôn là luôn format thêm 200 dòng (dòng trắng dưới của bảng) nữa chẳng hạn hoặc cho chọn định dạng toàn bảng và thêm 200 dòng nữa ...

như thế code chính mới nhằm vào mục đích chính là tính toán không phải format, không phải để sẵn 1 dòng thừa như thế (luôn lấy dòng 1 làm chuẩn là đủ). CÒn 3 cái and or này, cứ thử test đi, nhanh chậm không đáng kể, so với format ngoài bảng tính số lượng dòng nhiều.

Ừm nhỉ, vẫn biết là để chấm công hoàn chỉnh thì phải kiểm tra và chạy đi chạy lại các khâu không cần thiết mà mình không nghĩ ra bỏ phần ấy thành sub riêng. Quả nhiên tốc độ đã tăng lên đáng kể. .

 

[tueyennhi]

Cho em hỏi về code mảng sau:

J = Sheets("100H").Range("F3").Value
With Sheets("OVT")
sArr = .Range("B4", .Range("B4").End(xlDown)).Resize(, 60).Value
    For I = 1 To UBound(sArr)
        If sArr(I, 59) >= J Then
            dArr(I, 1) = sArr(I, 1)
            dArr(I, 2) = sArr(I, 2)
            dArr(I, 3) = sArr(I, 3)
            dArr(I, 4) = sArr(I, 4)
            dArr(I, 5) = sArr(I, 59)
        End If
    Next I
End WithSheets("100H").Range("B5").Resize(I, 5) = dArr

Code với điều kiện lọc lấy giá trị mà lớn hơn giá trị trong F3. Tuy lọc thì được rồi nhưng chạy code em thấy mặc định các giá trị nào không thỏa mãn thì nó vẫn gán vào mảng với giá trị là ô trống. Như vậy dữ lieu sẽ không liền mà bị cách nhau. Có cách nào xử lý không ạ?

 

[tueyennhi]

Thêm 1 biến K nữa. Có nghĩa là:

[COLOR=#007700][FONT=monospace]If [/FONT][/COLOR][COLOR=#0000bb][FONT=monospace]sArr[/FONT][/COLOR][COLOR=#007700][FONT=monospace]([/FONT][/COLOR][COLOR=#0000bb][FONT=monospace]I[/FONT][/COLOR][COLOR=#007700][FONT=monospace], [/FONT][/COLOR][COLOR=#0000bb][FONT=monospace]59[/FONT][/COLOR][COLOR=#007700][FONT=monospace]) >= [/FONT][/COLOR][COLOR=#0000bb][FONT=monospace]J Then
     K= K+1
     dArr(K,1) = sArr(I,1)
     .....
End if
....
[/FONT][/COLOR][COLOR=#0000bb][FONT=monospace][I]Sheets[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]([/I][/FONT][/COLOR][COLOR=#dd0000][FONT=monospace][I]"100H"[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]).[/I][/FONT][/COLOR][COLOR=#0000bb][FONT=monospace][I]Range[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]([/I][/FONT][/COLOR][COLOR=#dd0000][FONT=monospace][I]"B5"[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]).[/I][/FONT][/COLOR][COLOR=#0000bb][FONT=monospace][I]Resize[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]([/I][/FONT][/COLOR][FONT=monospace][I][COLOR=#0000bb]K[/COLOR][/I][/FONT][COLOR=#007700][FONT=monospace][I], [/I][/FONT][/COLOR][COLOR=#0000bb][FONT=monospace][I]5[/I][/FONT][/COLOR][COLOR=#007700][FONT=monospace][I]) = [/I][/FONT][/COLOR][COLOR=#0000bb][FONT=monospace][I]dArr  [/I][/FONT][/COLOR][COLOR=#0000bb][FONT=monospace]
....
[/FONT][/COLOR]

Hay quá, cảm ơn bạn rất nhiều!

 

[tueyennhi]

Cho em hỏi ạ. Bình thường trong Excel ta có hàm cơ bản là vlookup để lấy số liệu mà ta muốn, vậy trong VBA ta làm như nào.
Giả sử:

Ô A1 chứa giá trị A101;
Ô C1 và C2 chứa giá trị tương ứng là A101, A102;
Ô D1 và D2 chứa giá trị tương ứng là 100 và 200;

Vậy viết code nào để giá trị gán vào ô B1 sẽ là 100???

Em thử sử dung Find để chơi nhưng mới chỉ loáng thấy cái gì đó chứ chưa phải cái cần tìm

With Sheets("TT")
sArr = .Range("A5", .Range("A5").End(xlDown)).Resize(, 25).Value
For I = 1 To UBound(sArr)
     dArr(I, 1) = Range("N5:O20").Find(sArr(I, 5))
Next I
End With

Hiện tại nó lấy giá trị ở cột N, em muốn lấy giá trị tương ứng ở cột O thì em phải làm như nào ?

 

[tueyennhi]

À em làm được rồi, đúng là không có cơ bản thì cái đơn giản cũng phải tìm hiểu @@

dArr(I, 1) = Range("N5:O20").Find(sArr(I, 5)).Offset(, 1)

 

[ChanhTQ@]

À em làm được rồi, đúng là không có cơ bản thì cái đơn giản cũng phải tìm hiểu @@

dArr(I, 1) = Range("N5:O20").Find(sArr(I, 5)).Offset(, 1)

Lệnh này tiềm ẩn nguy cơ lớn về lâu dài!
Với phương thức FIND() cần bẩy lỗi bạn à!

 

[tueyennhi]

Lệnh này tiềm ẩn nguy cơ lớn về lâu dài!
Với phương thức FIND() cần bẩy lỗi bạn à!

Em vừa dính lỗi nếu trong vùng tìm kiếm không có giá trị mình tìm xong @@. Em chỉ muốn bẫy lỗi ở dòng dArr(I, 1) = Range("N5:O20").Find(sArr(I, 5)).Offset(, 1) thì để như vậy được chưa ạ?

Em sửa thành như vầy không biết có tiềm ẩn gì nữa không:

With Sheets("TT")
sArr = .Range("A5", .Range("A5").End(xlDown)).Resize(, 25).Value
For I = 1 To UBound(sArr, 1)
On Error Resume Next
    dArr(I, 1) = Range("N5:O20").Find(sArr(I, 5)).Offset(, 1)
Next I

For I = 1 To UBound(sArr, 1)
Tem = sArr(I, 5)
    If Not Dic.Exists(Tem) Then
        Dic.Add Tem, sArr(I, 10)
    Else
        Dic.Item(Tem) = Dic.Item(Tem) + sArr(I, 10)
    End If
Next I

For I = 1 To UBound(sArr, 1)
    pArr(I, 1) = Dic.Item(sArr(I, 5))
Next I

For I = 1 To UBound(sArr, 1)
    tArr(I, 1) = Round(dArr(I, 1) / pArr(I, 1) * sArr(I, 10), 2)
Next I

[K5].Resize(I - 1) = tArr

Set Dic = Nothing
End With

 

[phuyen89]

Code của bạn chưa phải là mảng. Đoạn này If Cells(li, 1).Value = "111250000125" Then vẫn xem như chưa "thoát ly" khỏi Range đâu (vì phải dựa vào Cells(li, 1) ). Vì thế tốc độ vẫn chậm
Tôi sửa lại như sau:

Sub hocmang()
  Dim Arr() As String, sArray, li As Long, lj As Long
  sArray = Range("A1:A808945").Value
  ReDim Arr(1 To UBound(sArray, 1), 1 To UBound(sArray, 2))
  For li = 1 To UBound(sArray, 1)
    If sArray(li, 1) = "111250000125" Then
      lj = lj + 1
      Arr(lj, 1) = sArray(li, 1)
    End If
  Next li
  Range("E1").Resize(lj).Value = Arr
End Sub

- Thứ nhất: Không cần Option Base 1 gì cả, khai báo trực tiếp rằng ReDim Arr(1 To UBound(sArray, 1), 1 To UBound(sArray, 2))
- Thứ hai: Dim Arr() As String sẽ bảo đảm kết quả luôn là chuổi
- Thứ ba: Chổ này arr(UBound(sarr, 1), LBound(sarr, 1)) xem bộ không chuẩn (phải giống cái trên mới đúng)
Thử lại code tôi vừa đưa ở trên xem tốc độ thế nào
---------------------------------------------------------------------------------

Không biết báo lỗi copy với dữ liệu lọc bằng AutoFilter hay với code? ---> Đoán là báo lỗi khi copy dữ liệu sau AutoFilter! Điều này rất bình thường. SpecialCells sẽ có giới hạn với dữ liệu lớn ---> Nếu dùng Filter thì nên chuyển sang Advanced Filter, sẽ hết lỗi liền (vì không phải copy, lọc và đưa kết quả thẳng đến nơi ta cần luôn)

Anh ơi, Em thắc mắc, chỗ khai báo biến.
Dim Arr() As String, sArray, li As Long, lj As Long
- sArray hiện tại có phải nó đang được ngầm định, kiểu dữ liệu là Variant không? Nếu Em đổi lai khai báo sArray() [ có được hiểu là mảng động không]
- Sau khi Em khai báo sArray thì các dữ liệu của range(A1:A808945) vẫn được đưa vào bình thường.
Mong anh giải thích thêm.

 

[phuyen89]

Cách Dim như trên, sArray có thể là bất kỳ giá trị nào, đồng thời có thể là biến đơn hoặc biến mảng. Tuy nhiên, ngay sau khi gán giá trị 1 range vào, nó trở thành biến mảng. Khai báo trước là mảng cũng không sao.
Còn Dim Arr() tức là đã khai báo trước cho nó là mảng rồi. Mục đích khai báo trước nó là mảng, là để có thể ReDim. Biến chưa biết là mảng hay đơn thì không thể ReDim được.

Anh ơi.
Sao em cố tính tạo một biến được lưu dưới dạn Variant, nhưng em vẫn Redim được biến ấy.

Sub Abc()
    Dim Arr
    Dim sArr()
    ReDim Arr(1 To 10, 1 To 1)
    ReDim sArr(1 To 10, 1 To 1)
End Sub

 

[ndu96081631]

Anh ơi, Em thắc mắc, chỗ khai báo biến.
Dim Arr() As String, sArray, li As Long, lj As Long
- sArray hiện tại có phải nó đang được ngầm định, kiểu dữ liệu là Variant không? Nếu Em đổi lai khai báo sArray() [ có được hiểu là mảng động không]
- Sau khi Em khai báo sArray thì các dữ liệu của range(A1:A808945) vẫn được đưa vào bình thường.
Mong anh giải thích thêm.

Khai báo vậy thì sArray là biến Variant, nó có thể là bất cứ thứ gì nên đương nhiên nó cũng có thể là mảng được và khi bạn "nạp" Range("A1:A808945") vào, nó sẽ trở thành mảng (tức là tùy bạn nạp thứ gì vào thì nó sẽ trở thành thứ ấy)
Nếu khai báo sArray() thì nó chỉ là biến mảng với số lượng phần tử chưa biết trước... và đương nhiên nó không thể trở thành "thứ khác" được (chỉ có thể là mảng thôi). Lấy ví dụ:
- Từ A1 đến A10 đang có dữ liệu nào đó
- Giờ ta khai báo Dim arr() đồng thời nạp giá trị A1:A10 vào nó

Dim arr()
arr = Range("A1:A10").Value

- Mọi thứ chạy bình thường không lỗi
- Nhưng nếu bạn sửa lại:

Dim arr()
arr = Range("A1").Value

Thì lỗi ngay lập tức. Bởi 1 cell duy nhất A1 thì không thể là mảng được. Trong khi nếu khai báo Dim arr (không có cặp dấu ngoặc) thì cả 2 code đều không lỗi

 

[nvt22vn]

Thanks các bác nhé... may mà có bài này em mới làm được VBA của em