問題：我想使用 VBA 陣列的一個子集，使用變數來控制子集
我的測驗程式是
Sub test2()
Dim MyArray() As Double
Dim c As Double
Dim d As Double
ReDim MyArray(5, 2)
c = 3
d = 5
MyArray(0, 0) = 1
MyArray(1, 0) = 2
MyArray(2, 0) = 3
MyArray(3, 0) = 4
MyArray(4, 0) = 5
MyArray(0, 1) = 10
MyArray(1, 1) = 20
MyArray(2, 1) = 30
MyArray(3, 1) = 40
MyArray(4, 1) = 50
a = Application.Average(Application.Index(MyArray, [ROW(3:5)], 2))
b = Application.Average(Application.Index(MyArray, [ROW(c:d)], 2))
End Sub
我想計算第二行中單元格 3-5 的平均值。對于變數 a，結果是正確的。因為我想用兩個變數（c=3 和 d=5）動態定義陣列的邊界，所以我試圖簡單地替換它們。變數 b 的結果是 "Error 2023" 。
如何解決？
我已經嘗試使用像 (ROW("c":"d") 或 Row("c:d") 這樣的“”。結果：只有錯誤型別發生變化
uj5u.com熱心網友回復：
您可以使用：
b = Application.Average(MyArray(c - 1, 1), MyArray(d - 1, 1))
但這并不方便。試試這個：放在 vba 頁面的頂部：
Option Base 1
然后你可以使用：
Sub test2()
Dim MyArray() As Double
Dim b as double, c As Integer, d As Integer
c = 3: d = 5
ReDim MyArray(5, 2)
MyArray(1, 1) = 1
MyArray(2, 1) = 2
MyArray(3, 1) = 3
MyArray(4, 1) = 4
MyArray(5, 1) = 5
MyArray(1, 2) = 10
MyArray(2, 2) = 20
MyArray(3, 2) = 30
MyArray(4, 2) = 40
MyArray(5, 2) = 50
b = Application.Average(MyArray(c, 2), MyArray(d, 2))
MsgBox b
End Sub
uj5u.com熱心網友回復：
因為我想用兩個變數（c=3 和 d=5）動態定義陣列的邊界，所以我試圖簡單地替換它們。變數 b 的結果是 "Error 2023" 。- 如何解決？
要回答您的問題：問題是您不能僅通過插入 VBA 變數來使用表格 []-括號評估；通過連接器Evaluate構造所需的字串輸入“ROW(3:5)”來使用&：
b = Application.Average(Application.Index(MyArray, Evaluate("ROW(" & c & ":" & d & ")"), 2))
旁注：不要忘記宣告所有需要的變數并Option Explicit在代碼模塊之上設定一個變數。
uj5u.com熱心網友回復：
@VBasic2008，你是對的。@caz68，像這樣更改宏：
Sub test3()
Dim MyArray() As Double
Dim a As Integer, b As Double, c As Integer, d As Integer
c = 3: d = 5
ReDim MyArray(5, 2)
MyArray(1, 1) = 1
MyArray(2, 1) = 2
MyArray(3, 1) = 3
MyArray(4, 1) = 4
MyArray(5, 1) = 5
MyArray(1, 2) = 10
MyArray(2, 2) = 20
MyArray(3, 2) = 20
MyArray(4, 2) = 40
MyArray(5, 2) = 50
Sum = 0: a = c
Do Until a > d
Sum = Sum Application.Sum(MyArray(a, 2))
a = a 1
Loop
MsgBox "Average = " & Round(Sum / (d - c 1), 2)
End Sub
不要忘記：vba 頁面頂部的 Option Base 1！
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/534678.html
標籤：数组擅长VBA子集