I am fairly new to VBA, so this may be a simple question but here goes.
I would like to initialize an entire array myArray
, say of integers, in VBA. I know that I can do this by a simple initialization like so:
Dim myArray
myArray = Array(1, 2, 4, 8)
But if the array is large this is cumbersome, and I'd like to initialize all of the elements to the same value. Ideally it would be something like this:
myArray(:) = 0
I tried that but the compiler complained. Then I tried myArray() = 0
and it complained about that, too.
Can anyone explain how to do this, without looping? I'd like to do it in one statement if possible.
Clarification:
I want to initialize every single element of the array to some initial value.
So if I have an array Dim myArray(300) As Integer
of 300 integers, for example, all 300 elements would hold the same initial value (say, the number 13).
More Clarification
I found this answer that states that you can do this with a variable like so:
Dim x As Double: x = 0
Perhaps there is a way to update the syntax slightly to make it applicable to arrays?
This is easy, at least if you want a 1-based, 1D or 2D variant array:
Sub StuffVArr()
Dim v() As Variant
Dim q() As Variant
v = Evaluate("=IF(ISERROR(A1:K1), 13, 13)")
q = Evaluate("=IF(ISERROR(A1:G48), 13, 13)")
End Sub
Byte arrays also aren't too bad:
Private Declare Sub FillMemory Lib "kernel32" Alias "RtlFillMemory" _
(dest As Any, ByVal size As Long, ByVal fill As Byte)
Sub StuffBArr()
Dim i(0 To 39) As Byte
Dim j(1 To 2, 5 To 29, 2 To 6) As Byte
FillMemory i(0), 40, 13
FillMemory j(1, 5, 2), 2 * 25 * 5, 13
End Sub
You can use the same method to fill arrays of other numeric data types, but you're limited to only values which can be represented with a single repeating byte:
Sub StuffNArrs()
Dim i(0 To 4) As Long
Dim j(0 To 4) As Integer
Dim u(0 To 4) As Currency
Dim f(0 To 4) As Single
Dim g(0 To 4) As Double
FillMemory i(0), 5 * LenB(i(0)), &HFF 'gives -1
FillMemory i(0), 5 * LenB(i(0)), &H80 'gives -2139062144
FillMemory i(0), 5 * LenB(i(0)), &H7F 'gives 2139062143
FillMemory j(0), 5 * LenB(j(0)), &HFF 'gives -1
FillMemory u(0), 5 * LenB(u(0)), &HFF 'gives -0.0001
FillMemory f(0), 5 * LenB(f(0)), &HFF 'gives -1.#QNAN
FillMemory f(0), 5 * LenB(f(0)), &H80 'gives -1.18e-38
FillMemory f(0), 5 * LenB(f(0)), &H7F 'gives 3.40e+38
FillMemory g(0), 5 * LenB(g(0)), &HFF 'gives -1.#QNAN
End Sub
If you want to avoid a loop in other situations, it gets even hairier. Not really worth it unless your array is 50K entries or larger. Just set each value in a loop and you'll be fast enough, as I talked about in an earlier answer.