Why do I get "error2023" in VBA when subsetting an array dynamically-CodePudding

Problem: I want to use a subset of a VBA array using variables to steer the subset

My test program is

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

I want to calc the average of cells 3-5 in row two. For the variable a the result is correct. Since I want to have the boundaries of the array defiend dynamically with two variables (c=3 and d=5) I tried to simply replace them. The result for variable b is "Error 2023" .

How can this be solved?

I already tried to use "" like (ROW("c":"d") or Row("c:d"). Result: only the Error type changes

CodePudding user response：

You can use:

b = Application.Average(MyArray(c - 1, 1), MyArray(d - 1, 1))

But that's not convenient. Try this: Place at the top of the vba-page:

Option Base 1

Then you can use:

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

CodePudding user response：

Since I want to have the boundaries of the array defined dynamically with two variables (c=3 and d=5) I tried to simply replace them. The result for variable b is "Error 2023" . - How can this be solved?

To answer your question: the issue is that you cannot just use the tabular []-bracket evaluation by inserting VBA variables; use Evaluate by constructing the needed string input "ROW(3:5)" via & connectors:

b = Application.Average(Application.Index(MyArray, Evaluate("ROW(" & c & ":" & d & ")"), 2))

Side note: don't forget to declare all needed variables and to have an Option Explicit set on top of your code modules.