VBA (Excel) Initialize Entire Array without Looping

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, 13End 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.#QNANEnd 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.

You can initialize the array by specifying the dimensions. For example

Dim myArray(10) As IntegerDim myArray(1 to 10) As Integer

If you are working with arrays and if this is your first time then I would recommend visiting Chip Pearson's WEBSITE.

What does this initialize to? For example, what if I want to initialize the entire array to 13?

When you want to initailize the array of 13 elements then you can do it in two ways

Dim myArray(12) As IntegerDim myArray(1 to 13) As Integer

In the first the lower bound of the array would start with 0 so you can store 13 elements in array. For example

myArray(0) = 1myArray(1) = 2'''myArray(12) = 13

In the second example you have specified the lower bounds as 1 so your array starts with 1 and can again store 13 values

myArray(1) = 1myArray(2) = 2'''myArray(13) = 13

Wnen you initialize an array using any of the above methods, the value of each element in the array is equal to 0. To check that try this code.

Sub Sample()    Dim myArray(12) As Integer    Dim i As Integer    For i = LBound(myArray) To UBound(myArray)        Debug.Print myArray(i)    Next iEnd Sub

or

Sub Sample()    Dim myArray(1 to 13) As Integer    Dim i As Integer    For i = LBound(myArray) To UBound(myArray)        Debug.Print myArray(i)    Next iEnd Sub

FOLLOWUP FROM COMMENTS

So, in this example every value would be 13. So if I had an array Dim myArray(300) As Integer, all 300 elements would hold the value 13

Like I mentioned, AFAIK, there is no direct way of achieving what you want. Having said that here is one way which uses worksheet function Rept to create a repetitive string of 13's. Once we have that string, we can use SPLIT using "," as a delimiter. But note this creates a variant array but can be used in calculations.

Note also, that in the following examples myArray will actually hold 301 values of which the last one is empty - you would have to account for that by additionally initializing this value or removing the last "," from sNum before the Split operation.

Sub Sample()    Dim sNum As String    Dim i As Integer    Dim myArray    '~~> Create a string with 13 three hundred times separated by comma    '~~> 13,13,13,13...13,13 (300 times)    sNum = WorksheetFunction.Rept("13,", 300)    sNum = Left(sNum, Len(sNum) - 1)    myArray = Split(sNum, ",")    For i = LBound(myArray) To UBound(myArray)        Debug.Print myArray(i)    Next iEnd Sub

Using the variant array in calculations

Sub Sample()    Dim sNum As String    Dim i As Integer    Dim myArray    '~~> Create a string with 13 three hundred times separated by comma    sNum = WorksheetFunction.Rept("13,", 300)    sNum = Left(sNum, Len(sNum) - 1)    myArray = Split(sNum, ",")    For i = LBound(myArray) To UBound(myArray)        Debug.Print Val(myArray(i)) + Val(myArray(i))    Next iEnd Sub

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).

Can anyone explain how to do this, without looping? I'd like to do itin one statement if possible.

What do I win?

Sub SuperTest()   Dim myArray   myArray = Application.Transpose([index(Row(1:300),)-index(Row(1:300),)+13])End Sub