Using a function to return a populated array

[CompleteNewb]

I have this part of a procedure that gives me all the certain values I'm
looking for, and I use it to determine column headings in some sheets, row
headings in others, and various counts and checks, etc. I have to run it in
every Sub for which I use this array. I experimented with CPearson's page
on using functions to return arrays, but I think I'm at too low a level to
grasp it. Is there a relatively easy way to set up the following so I can
just use the completed MyArray in other subs without having to run this
every time? Like in the sub I'd be able to set a range.value to
FunctionName(MyArray)?

With MySheet.Columns("C")
Set foundit = .Find("MyString", LookIn:=xlValues, LookAt:=xlPart)
If Not foundit Is Nothing Then
FirstAddress = foundit.Address
Do
Startrange =
MySheet.Range(foundit.Address).Offset(3, -2).Address
EndRange = MySheet.Range(Startrange).End(xlDown).Address
For Each cell In MySheet.Range(Startrange,
EndRange)
ReDim Preserve MyArray(0 To i)
MyArray(i) = cell.Value
i = i + 1
Next cell
Set foundit = .FindNext(foundit)
Loop While Not foundit Is Nothing And foundit.Address <>
FirstAddress
i = 0
End If
End With

Thanks for any thoughts and help

 

[Dave Peterson]

I don't quite understand how you're going to use this, but could you declare the
myArray as a public/global variable -- outside any procedure and then populate
it once and use it where ever you want.

 

[minimaster]

I agree with Dave, that should be the way to go.
Here a simple example how to return an array via a function while
using a global variable to hold the array.

'define a global variant for the array
Dim mnth As Variant

Sub MainRoutine()
mnth=MonthlyDateArray(2011)
' now mnth contains an array of 12 date values
End Sub

Function MonthlyDateArray(TheYear As Integer, Optional MonthDay As
Integer) As Variant
' returns variant array with 12 dates, one for each month
Dim i As Integer
Dim m As Variant
m = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11",
"12")
If MonthDay < 1 Or MonthDay > 31 Then MonthDay = 1
For i = 1 To 12
m(i) = m(i) & "/" & MonthDay & "/" & TheYear
Next i
MonthlyDateArray = m
End Function

 

[CompleteNewb]

That IS the best way to do it, and thanks. I should have thought of it
before posting, I was just in "must use a function" mode.

Thanks, Dave