Sulprobil
Search…
sbVector
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.
Please read my Disclaimer.
1
Function sbVector( _
2
Optional dStart As Double = 1#, _
3
Optional dInc As Double = 1#, _
4
Optional lCount As Long) As Variant
5
'sbVector creates a vector constant with lCcount cells
6
'(if called from a worksheet range this parameter can
7
'be omitted, it will be set implicitly), starting with
8
'value dStart and incremented by dInc. This function is
9
'intended to replace the old but inefficient (volatile)
10
'worksheet function "trick" =ROW(INDIRECT("1:3"))
11
'by the non-volatile =sbVector(3), for example.
12
'Reverse("moc.LiborPlus.www") PB 13-Jan-2013 V0.1
13
Dim vR As Variant
14
Dim i As Long, j As Long, rc(1 To 2) As Long
15
Dim dCurr As Double
16
With Application.Caller
17
If TypeName(Application.Caller) = "Range" Then
18
rc(2) = .Columns.Count
19
rc(1) = .Rows.Count
20
Else
21
If lCount < 1 Then
22
sbVector = CVErr(xlErrValue)
23
Exit Function
24
End If
25
rc(1) = lCount
26
rc(2) = 1
27
End If
28
ReDim vR(1 To rc(1), 1 To rc(2))
29
dCurr = dStart - dInc
30
For i = 1 To rc(1)
31
For j = 1 To rc(2)
32
vR(i, j) = dCurr + dInc
33
dCurr = dCurr + dInc
34
Next j
35
Next i
36
sbVector = vR
37
End With
38
End Function
39
40
Sub test()
41
Sheets("Sheet1").Range("A12:A14").FormulaArray = sbVector(1, 3, 3)
42
End Sub
Copied!
sbVector.xlsm
15KB
Binary
sbVector.xlsm
Last modified 1yr ago
Copy link