Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

function - load series of dates in to an array

Status
Not open for further replies.

y2k1981

Programmer
Aug 2, 2002
773
IE
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:
Code:
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
 
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.

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top