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 · Accepted 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

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

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

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

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

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          gt  Create a string with 13 three hundred times separated by comma         gt  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          gt  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

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

Examples related to arrays

Examples related to vba

Examples related to excel

Examples related to initialization