Excel lacks a worksheet function "VECTOR(start,increment)" which creates a constant vector like 1, 3, 5, 7 (if start = 1 and increment = 2).
All worksheet function "tricks" like =ROW(INDIRECT("1:3")) have severe drawbacks of being volatile (they are recalculated each time you hit F9) and of depending on all cells in the referred rows.
A user-defined function like sbVector can help here. Please notice that it needs to be entered as an array formula (with CTRL + SHIFT + ENTER, not only with ENTER) into the desired target range.
Function sbVector( _
Optional dStart As Double = 1#, _
Optional dInc As Double = 1#, _
Optional lCount As Long) As Variant
'sbVector creates a vector constant with lCcount cells
'(if called from a worksheet range this parameter can
'be omitted, it will be set implicitly), starting with
'value dStart and incremented by dInc. This function is
'intended to replace the old but inefficient (volatile)
'worksheet function "trick" =ROW(INDIRECT("1:3"))
'by the non-volatile =sbVector(3), for example.
'Reverse("moc.LiborPlus.www") PB 13-Jan-2013 V0.1
Dim vR As Variant
Dim i As Long, j As Long, rc(1 To 2) As Long
Dim dCurr As Double
With Application.Caller
If TypeName(Application.Caller) = "Range" Then
rc(2) = .Columns.Count
rc(1) = .Rows.Count
Else
If lCount < 1 Then
sbVector = CVErr(xlErrValue)
Exit Function
End If
rc(1) = lCount
rc(2) = 1
End If
ReDim vR(1 To rc(1), 1 To rc(2))
dCurr = dStart - dInc
For i = 1 To rc(1)
For j = 1 To rc(2)
vR(i, j) = dCurr + dInc
dCurr = dCurr + dInc
Next j
Next i
sbVector = vR
End With
End Function
Sub test()
Sheets("Sheet1").Range("A12:A14").FormulaArray = sbVector(1, 3, 3)
End Sub
