- 帖子
- 1425
- 主題
- 40
- 精華
- 0
- 積分
- 1449
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2024-4-16
|
VBA中的陣列用法, 要早點了解,
最好也理解字典用法,
字典+陣列, 可處理很多種資料的處理及統計運算需求,
...
准提部林 發表於 2020-8-24 17:19 
謝謝前輩指導
後學駑鈍! 經過了2年才學到陣列語字典的一點點皮毛
勤能補拙!,永不放棄!
下列程式碼是今日學習到的字典與陣列技巧
再請前輩撥空再指導- Option Explicit
- Sub TEST_20220920_3()
- Dim Brr, R&, C&, i&, j&, k%, T$, TT, Y, Z, W, P, Q
- Dim Crr, V, xR, n
- TT = Timer
- Set Y = CreateObject("Scripting.Dictionary")
- Set Z = CreateObject("Scripting.Dictionary")
- Set V = CreateObject("Scripting.Dictionary")
- Set Z = ActiveSheet.Cells
- For i = 1 To 4
- Set V(i) = CreateObject("Scripting.Dictionary")
- Next
- R = Z(Rows.Count, "D").End(xlUp).Row '最後一行
- W = Z(12, Columns.Count).End(xlToLeft).Column '最後一欄
- Crr = Z(12, 33).Resize(R - 11, W - 32)
- Z = Z(1, 1).Resize(R, W) '定義資料範圍--A1至整個區
- For i = 1 To 13 Step 4
- Y.Add Mid(Z(11, i + 12), 2, 2), (i + 3) / 4
- Next
- For i = 33 To W Step 4
- P = Right(Split(Z(11, i), "]")(0), 2)
- V(Y(P)).Add V(Y(P)).Count, i
- Next
- ReDim Brr(1 To R - 12, 1 To 20) '設空陣列
- For i = 13 To R
- For Each xR In V(1)
- If Z(i, V(1)(xR) + 1) > Brr(i - 12, 2) Then
- Brr(i - 12, 1) = Z(i, V(1)(xR))
- Brr(i - 12, 2) = Z(i, V(1)(xR) + 1)
- End If
- If Z(i, V(1)(xR) + 1) - Z(i, V(1)(xR)) > Brr(i - 12, 3) Then
- Brr(i - 12, 3) = Z(i, V(1)(xR) + 1) - Z(i, V(1)(xR))
- End If
- Next
- For n = 1 To 3
- For Each xR In V(n + 1)
- Brr(i - 12, n * 4 + 1) = Brr(i - 12, n * 4 + 1) + Z(i, V(n + 1)(xR))
- Brr(i - 12, n * 4 + 2) = Brr(i - 12, n * 4 + 2) + Z(i, V(n + 1)(xR) + 1)
- Brr(i - 12, n * 4 + 3) = Brr(i - 12, n * 4 + 2) - Brr(i - 12, n * 4 + 1)
- Next
- Next
- For n = 1 To 3
- For j = 1 To 3
- Brr(i - 12, 16 + n) = Brr(i - 12, 16 + n) + Brr(i - 12, j * 4 + n)
- Next
- Next
- Next i
- [M13].Resize(UBound(Brr), UBound(Brr, 2)) = Brr
- MsgBox Timer - TT
- End Sub
|
|
發表於 2022-9-20 16:41
| 