Passing an Array created in a Function or Subprocedure back to the calling Subprocedure

  • Thread starter Thread starter Cloudfall
  • Start date Start date
C

Cloudfall

I want to have a Function or Subprocedure that can create an array with
52 specific values in it. I then want to call this Function or
Subprocedure from a main Subprocedure and be able to access the
contents of the array. Everything can be of type variant for the
purposes of this exercise. Can anybody help, please?
 
This worked ok for me:

Option Explicit
Sub testme()
Dim myArr As Variant
Dim iCtr As Long

myArr = BuildArray(myArr)

For iCtr = LBound(myArr) To UBound(myArr)
MsgBox iCtr & ". " & myArr(iCtr)
Next iCtr
End Sub

Function BuildArray(SomeArray As Variant) As Variant
Dim iCtr As Long
ReDim SomeArray(1 To 52)
For iCtr = 1 To 52
SomeArray(iCtr) = iCtr & "--Hi there"
Next iCtr
BuildArray = SomeArray
End Function
 
Or maybe this is closer. (I'm kind of confused over what calls what.)

Option Explicit
Sub testme()
Dim myArr As Variant
Dim iCtr As Long

myArr = BuildArray(myArr)

Call ShowArray(myArr)
End Sub

Function BuildArray(SomeArray As Variant) As Variant
Dim iCtr As Long
ReDim SomeArray(1 To 52)
For iCtr = 1 To 52
SomeArray(iCtr) = iCtr & "--Hithere"
Next iCtr
BuildArray = SomeArray
End Function

Sub ShowArray(SomeArray As Variant)

Dim iCtr As Long
If IsArray(SomeArray) Then
For iCtr = LBound(SomeArray) To UBound(SomeArray)
MsgBox SomeArray(iCtr)
Next iCtr
Else
MsgBox "not an array"
End If
End Sub
 
Hi again Dave,

This worked in my application. I'm trying to figure out how it works. I
think it works as follows:

1. When you declare "myArr" in the calling subprocedure "testme()", its
just a variable and not an array yet.
2. When you call the function "BuildArray" you pass it the variable
"myArr" and "BuildArray" turns the variable "myArr" into a variable
array when it re-declares it as such.
3. You then assign the created array to the function "BuildArray" which
is passed back to the calling variable "myArr".
4. What I don't understand is how the variable "myArr" turns into a
variable array when it hasn't been declared as such!
5. I understand the rest of the above.

(By the way, are you Australian? If not, where are you from?)

Thank you for the trouble you have gone to to help me. I really
appreciate your expertise in matters of VBA.

Regards,

Terry.
 
Hi Dave,

As I said in my reply to your first posting, that worked. No doubt this
would too, but what I wanted was what you initially provided.

Thanks again for your help.

Regards,

Terry.
 
#2. I didn't need to pass it anything. This worked just as well:

Option Explicit
Sub testme()
Dim myArr As Variant
Dim iCtr As Long

myArr = BuildArray
For iCtr = LBound(myArr) To UBound(myArr)
MsgBox iCtr & ". " & myArr(iCtr)
Next iCtr
End Sub

Function BuildArray() As Variant
Dim iCtr As Long
ReDim SomeArray(1 To 52)
For iCtr = 1 To 52
SomeArray(iCtr) = iCtr & "--Hi there"
Next iCtr
BuildArray = SomeArray
End Function

This may make it easier to see how #4 works. Since myArr is a Variant (which
can hold anything) and BuildArray passes back an array), then myArr becomes an
array.

I guess it was more muscle memory than anything to pass it the parm--it wasn't
necessary and shouldn't have been passed.

I'm in the middle of the USA.
 
Thanks again Dave,

Everything is crystal clear.

"Dim SomeArray(1 To 52)" works for me as well.

My Function is funtioning, it's Friday and my wife and I are playing
tournament no-limit Texas Hold'em tonight, and I'll be watching the
football on TV with a bet on my team after that, my wife's sister is
visiting from England (she's Australian married to an Englishman) and
we're having a party tomorrow night, so have a good weekend yourself
and thanks for all the help.

Regards,

Terry.
 
Good luck with that poker tourney.


Thanks again Dave,

Everything is crystal clear.

"Dim SomeArray(1 To 52)" works for me as well.

My Function is funtioning, it's Friday and my wife and I are playing
tournament no-limit Texas Hold'em tonight, and I'll be watching the
football on TV with a bet on my team after that, my wife's sister is
visiting from England (she's Australian married to an Englishman) and
we're having a party tomorrow night, so have a good weekend yourself
and thanks for all the help.

Regards,

Terry.
Click to expand...
 
Back
Top