subscript out of range
-
hi, please see code below...i keep on get in a "subscript out of range" error for the array v(n), my option base is 0 even if i reset the array to v(n-1) it does not accept. is the something i am missing Public Function datevec(d1 As Variant, d2 As Variant, freq As Variant) Dim v() Dim n As Long Dim c1, c2 As Date ReDim v(n) As Variant If IsDate(d1) = True And IsDate(d2) = True Then c1 = DateSerial(Year(d1), Month(d1), Day(d1)) c2 = DateSerial(Year(d2), Month(d2), Day(d2)) n = Application.Round(VBA.DateDiff("d", c1, c2) / 365 * freq, 0) Select Case freq Case 1 For Each i In v(n) v(i) = VBA.DateAdd("yyyy", i + 1, c1) Next i Case 2 For Each i In v(n) v(i) = VBA.DateAdd("q", 2 * (i + 1), c1) Next i Case 4 For Each i In v(n) v(i) = VBA.DateAdd("q", i + 1, c1) Next i Case 12 For Each i In v(n) v(i) = VBA.DateAdd("M", i + 1, c1) Next i End Select datevec = v End If End Function
-
hi, please see code below...i keep on get in a "subscript out of range" error for the array v(n), my option base is 0 even if i reset the array to v(n-1) it does not accept. is the something i am missing Public Function datevec(d1 As Variant, d2 As Variant, freq As Variant) Dim v() Dim n As Long Dim c1, c2 As Date ReDim v(n) As Variant If IsDate(d1) = True And IsDate(d2) = True Then c1 = DateSerial(Year(d1), Month(d1), Day(d1)) c2 = DateSerial(Year(d2), Month(d2), Day(d2)) n = Application.Round(VBA.DateDiff("d", c1, c2) / 365 * freq, 0) Select Case freq Case 1 For Each i In v(n) v(i) = VBA.DateAdd("yyyy", i + 1, c1) Next i Case 2 For Each i In v(n) v(i) = VBA.DateAdd("q", 2 * (i + 1), c1) Next i Case 4 For Each i In v(n) v(i) = VBA.DateAdd("q", i + 1, c1) Next i Case 12 For Each i In v(n) v(i) = VBA.DateAdd("M", i + 1, c1) Next i End Select datevec = v End If End Function
Try moving your ReDim statement bellow the assignment of n like this: n = Application.Round(VBA.DateDiff("d", c1, c2) / 365 * freq, 0) ReDim v(n) As Variant