Populating an array from within a calledSub.

[sts023]

Hi guys, it's that thick old Newbie again.

I'd like to achieve the following (syntactically incorrect) task of populating an array in a subroutine. I'm not bothered about the "invente" task, but I'm going a bit weird trying to find the syntax to pass an array definition to a subroutine, so that the subroutine can populate it.
Can anyone PLEASE help?
Sample (pointless) code below.

Sub TopLevel()
Dim ReturnedArray() As String
Dim EntryCount As Long
PopulateIt(ReturnedArray, EntryCount)
End Sub

PopulateIt(ReceivedString(), NumberOfEntries As Long)
Dim i As Integer
For i = 1 to 5
ReceivedString(i) = i * 3
Next I
NumberOfEntries = i
End Sub


Thanks in advance for taking the time to instruct a novice!

 

[MarkK]

Check out the VBA.Split() function to immediately create an array from a delimited string. Syntax would look like ...

dim var as variant
var = split("1 2 3 5 78 99 100 testing")

And if you want to return a value from a subroutine in VBA I recommend using a function, rather than changing the value of a parameter that was passed to a sub. Consider ...

Sub testing
  dim tmp as single
[COLOR="Green"]  'note that the function itself returns the value, not an update to an argument[/COLOR]
  tmp = AddNumbers(2, 2)
  debug.print tmp
end sub

Function AddNumbers(op1 as single, op2 as single) As Single
  AddNumbers = op1 + op2
End Function

 

[sts023]

Thanks lagbolt - all info is worth having, but in this case I don't think it answers my problem.

I wanted a Sub rather than a Function, 'cos what I'm trying to do from the calling ("TopLevel"") Sub is call another Sub ("PopulateIt") in order to populate an array defined in the calling Sub.

Although my original example is just dummy code, what actually happens in "PopulateIt" (eventually!) is a parameter driven SQL statement recovers matching records in order to put them into the array. The idea of a using a Function is therefore not really appropriate (unless a Function could return an array???).

I suspect I could achieve my aims by "Dim"ming the array outside of the Subs, to make it "universal" - the problem is I'm a bit perverse , so I'm trying to do it without resorting to universal declarations.

Whilst researching this problem, I caught a whiff of the "By Ref" operand, and I suspect that may be involved in a possible solution. However, what I've read implies that the "By Ref"d variable has to be the last in a calling parameter list. So what happens if I wanted a subroutine to populate TWO arrays??? (Unless "last parameter" means "last set of parameters")

As always, thanks for patiently reading so far - any further comments would be welcomed!

 

[MarkK]

Yes a function can return an array. Copy this code into a standard module and try it ...

Sub TestMyArrayFunction()
   Dim var
   For Each var In GetStringArray("this is a test of the emergancy braodcast system")
      Debug.Print var
   Next
End Sub

Function GetStringArray(text As String) As [B][COLOR="DarkRed"]String()[/COLOR][/B]
   GetStringArray = Split(text)
End Function

Note the function GetStringArray, above, returns a value of String(), not String, which will be an array. Also, a function might return an array of arrays, a collection, a user-defined type, or a class. Returning two arrays doesn't stress the system in any way.
Cheers,