Declaring an array

[tiredofit]

I prefer to declare arrays by adding the brackets, ie:

Dim MyArray() As Variant

instead of:

Dim MyArray As Variant

because I find adding the brackets makes it clearer.

However, in the following code which attempts to return multiple values from a function. I find that adding the brackets causes the program to crash:

Option Explicit

Sub GetValues()

    Dim Counter As Integer
   
    For Counter = 1 To 3
   
        MsgBox "Value " & Counter & " = " & SomeArray(Counter, 1)
       
    Next Counter

End Sub

Public Function SomeArray() As Variant

    Dim DataArray(1 To 3, 1 To 1) As Variant
   
    DataArray(1, 1) = 10
    DataArray(2, 1) = 20
    DataArray(3, 1) = 30
   
    SomeArray() = DataArray()

End Function

The error message is:

Run-time error '28'

Out of stack space

Stepping into it, when the code hits this line:

SomeArray() = DataArray()

instead of proceeding to End Sub, it calls it again, ie jumps to here:

Public Function SomeArray() As Variant

If instead I wrote:

SomeArray = DataArray()

it's fine.

Why is that?

Thanks

 

[Rick Rothstein]

SomeArray is not an array... it is a function that returns a Variant which, in your case, happens to be an array. When you assign a return value from a function, you assign it to the function's name (SomeArray in your case) not to the function itself which is what SomeArray() is. I'm a little surprised this bothers you but asking for, say, the upper bound of an array (let's use your DataArray for example purposes) wouldn't. ..

Correct: MsgBox UBound(DataArray)

Incorrect: MsgBox UBound(DataArray())

Here UBound requires the array's name, not the array itself... it is kind of the same thing as with the function name versus the function itself.

 

[tiredofit]

SomeArray is not an array... it is a function that returns a Variant which, in your case, happens to be an array. When you assign a return value from a function, you assign it to the function's name (SomeArray in your case) not to the function itself which is what SomeArray() is. I'm a little surprised this bothers you but asking for, say, the upper bound of an array (let's use your DataArray for example purposes) wouldn't. ..

Correct: MsgBox UBound(DataArray)

Incorrect: MsgBox UBound(DataArray())

Here UBound requires the array's name, not the array itself... it is kind of the same thing as with the function name versus the function itself.

Thanks for your explanation re my original query.

I'm a little puzzled though by the UBound part of your comment.

I always write the following:

    Dim DataArray() As Variant
   
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim DataArrayRows As Integer
   
    DataArrayRows = UBound(DataArray(), 1)

and it works as expected (unless Sheet1 only contains a single value in cell A1), in which case, it would be better to declare DataArray without the brackets.

So are you saying I shouldn't write:

    DataArrayRows = UBound(DataArray(), 1)

ie include the brackets?

 

[Rick Rothstein]

Interesting... there is a difference in declaring a Variant variable with and without the parentheses. I never declare them with the parentheses unless I am going to create an actual array of variants. Doing it that way, UBound(DataArray()) raises the error... but if you declare the array with the parentheses, no error is raised. So, this works fine...

Sub Test1()
  Dim DataArray() As Variant
  DataArray = Array(1, 2, 3)
  MsgBox UBound(DataArray(), 1)
End Sub

and this does not...

Sub Test()
  Dim DataArray As Variant
  DataArray = Array(1, 2, 3)
  MsgBox UBound(DataArray(), 1)
End Sub

 

[tiredofit]

Interesting... there is a difference in declaring a Variant variable with and without the parentheses. I never declare them with the parentheses unless I am going to create an actual array of variants. Doing it that way, UBound(DataArray()) raises the error... but if you declare the array with the parentheses, no error is raised. So, this works fine...

Sub Test1()
  Dim DataArray() As Variant
  DataArray = Array(1, 2, 3)
  MsgBox UBound(DataArray(), 1)
End Sub

and this does not...

Sub Test()
  Dim DataArray As Variant
  DataArray = Array(1, 2, 3)
  MsgBox UBound(DataArray(), 1)
End Sub

Thanks for clarifying.