I have an array that i would like to reference the count from in another sub. Basically a message box that would include the Ubound value from the other Sub.
If I'm following what you're wanting, your SubB needs to be a Function that returns an Integer, as Subs cannot return values in VBA.
Sub main() ' SubA
MsgBox ("SubB array count = " & SubB())
Function SubB() As Integer
' Your array that you would like the count from for SubA
Dim itemCountArray() As Variant
ReDim itemCountArray(1 To 4)
' Returns the length of the array of interest as an integer.
SubB = UBound(itemCountArray) - LBound(itemCountArray) + 1
When I have an object initialized in the scope of Sub A and I need to reference any of its properties in Sub B, the pattern I like to use is to elevate the object's scope to the "nearest common" scope and pass it to both subs as an argument (or pass the minimum
information that I need from that object into both subs as an argument).
So if Sub A and Sub B were called from Sub C, I would initialize the array in Sub C instead of Sub A.
What are you trying to do?
So following you...I have Sub A(main) and Sub B (called from middle of A). Array is in Sub B. But i would like to have the Count message at the end of Sub A, just before the end.
I don't like using global variables, myself unless it is something like a string constant. If you want to keep the array scoped non-globally but be able to change it in SubB and have access to it in SubA, I normally do the following:
Sub Main() 'SubA Dim an_array() As Integer DoThing an_array 'an_array will be modified by DoThing Debug.Print an_array(0)End Sub Sub DoThing(an_array() As Integer) 'SubB ReDim an_array(0) an_array(0) = 1End Sub
Sub Main() 'SubA
Dim an_array() As Integer
DoThing an_array 'an_array will be modified by DoThing
Sub DoThing(an_array() As Integer) 'SubB
an_array(0) = 1
this is the right answer
An easy solution would be to declare the array at the module level, and not at the procedure level, and then the array would be accessible to any procedure in the module. Passing the array as an argument from one procedure to another is an unnecessary complication.
Retrieving data ...