function - load series of dates in to an array

[y2k1981]

Hi All,

I'm pretty new to VBA and just discovered how you can use function in excel for different calculations. what I want to know is, suppose I have the following function:

function dateRange(start, end as range)
dim myArray(5)

I have two questions, first, how do I load the dates between start and end in to the array myArray? and second, how can I make the array more dynamic so that the amount of indexes in there is equal to end minus start?

thanks in advance
Martin

 

[TonyJollans]

Hi y2k1981,

To dynamically size an array you must declare it without any dimensions (at compile time) and then redimension it (at run time).

Assuming you have dates in the cells you pass and that the second is greater than or equal to the first, this will do it for you. Note that arrays are by default zero-based so using startdate-enddate gives an array with startdate-enddate+1 elements which is just enough.

Function dr(StartDate As Range, EndDate As Range)
Dim myArray() As Date
ReDim myArray(EndDate - StartDate)
For i = 0 To UBound(myArray)
    myArray(i) = StartDate + i
Next
End Function

Enjoy,
Tony