VBA Excel Initialize Entire Array without Looping

Question

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

User · Answer

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

Dim myArray(10) As Integer
Dim 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 Integer
Dim 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) = 1
myArray(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) = 1
myArray(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 i
End 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 i
End 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 i
End 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 i
End Sub

User · Answer

This function works with variables for size and initial value it combines tbur  amp   Filipe responses   Function ArrayIniValue iSize As Integer  iValue As Integer  Dim sIndex As String sIndex    INDEX Row 1    amp  iSize  amp        ArrayIniValue   Evaluate   Transpose    amp  sIndex  amp   -   amp  sIndex  amp       amp  iValue  amp       End Function   Called this way   myArray   ArrayIniValue 350  13

User · Answer

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     amp HFF  gives -1     FillMemory i 0   5   LenB i 0     amp H80  gives -2139062144     FillMemory i 0   5   LenB i 0     amp H7F  gives 2139062143      FillMemory j 0   5   LenB j 0     amp HFF  gives -1      FillMemory u 0   5   LenB u 0     amp HFF  gives -0 0001      FillMemory f 0   5   LenB f 0     amp HFF  gives -1  QNAN     FillMemory f 0   5   LenB f 0     amp H80  gives -1 18e-38     FillMemory f 0   5   LenB f 0     amp H7F  gives 3 40e 38      FillMemory g 0   5   LenB g 0     amp 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

User · Answer

Fancy way to put  rdhs answer in a function   Function arrayZero size As Integer    arrayZero   Evaluate   IF ISERROR Transpose A1 A   amp  size  amp       0  0    End Function   And use like this   myArray   arrayZero 15

User · Answer

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 it in 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

User · Answer

For VBA you need to initialise in two lines   Sub TestArray    Dim myArray myArray   Array 1  2  4  8   End Sub

[arrays] VBA (Excel) Initialize Entire Array without Looping

Examples related to arrays

Examples related to vba

Examples related to excel

Examples related to initialization