以下是一个比较灵活的函数:
Function BuildArray(ParamArray params()) As Variant
Dim A As Variant, v As Variant
Dim i As Long, j As Long, k As Long, n As Long, m As Long, b As Long
n = UBound(params)
If n Mod 2 = 0 Then
b = params(n)
n = n - 1
End If
For i = 1 To n Step 2
m = m + params(i)
Next i
ReDim A(b To b + m - 1)
k = b
For i = 0 To n - 1 Step 2
v = params(i)
For j = 1 To params(i + 1)
A(k) = v
k = k + 1
Next j
Next i
BuildArray = A
End Function
它可以接受任意数量的参数。如果有偶数个参数,则将它们分成连续的一对,形式为
v,i
,其中
v
是值,
i
是重复该值的次数,返回第一个索引为0的结果数组。如果传递的参数数量为奇数,则最后一个参数被解释为数组的基础。例如:
Sub test()
Dim A As Variant
A = BuildArray(1, 3, 2, 4) 'creates 0-based array [1,1,1,2,2,2,2]
'verify:
Debug.Print "A = " & LBound(A) & " to " & UBound(A)
Debug.Print "Items: " & Join(A, ",")
A = BuildArray(1, 3, 2, 4, 1) 'creates 1-based array [1,1,1,2,2,2,2]
'verify:
Debug.Print "A = " & LBound(A) & " to " & UBound(A)
Debug.Print "Items: " & Join(A, ",")
End Sub
输出:
A = 0 to 6
Items: 1,1,1,2,2,2,2
A = 1 to 7
Items: 1,1,1,2,2,2,2
Application.WorksheetFunction.Rept()
? - Brian