VBA passing vector from spreadsheet to array

[curious]

Hi all,


What is the proper way (best way) how to pass date vector fro
spreadsheet to a date vector inside VBA?


Ideally I would like to have something like this:



Code
-------------------

Public Function foo(inputVector() As Date)

Dim firstDate As Date
firstDate=inputVector(1)

...

End Function

-------------------



but this does not work.

Thanks

 

[Bob Phillips]

I think that you need to pass the array and then process that for dates.

This is an example of handling an array

Function test(a)
Dim i As Long
Dim maxVal
For i = LBound(a, 1) To UBound(a, 1)
If a(i) > maxVal Then
maxVal = a(i)
End If
Next i
test = maxVal
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Sub Main()
dim arrDate(1 to 10) as Date
i = 0
for each cell in Range("A1:A10)
i = i + 1
arrDate(i) = Cell.Value
Next
Foo arrDate

End Sub

If you want to put values from a worksheet into other than a variant, you
have to do it a cell at a time.

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, doing it a
cell at a time is built in to the Assign function:

Sub Main()
Dim arrDate() As Date
Assign MakeArray(Range("A1:J1"),1), arrDate
End Sub

The MakeArray function converts the input to a 1-dimensional array. If
the input is single-column range, the following also works:

Sub Main()
Dim arrDate() As Date
Assign Application.Transpose(Range("A1:A10")), arrDate
End Sub

Alan Beban