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?

`:` is continuation character if you want it on one line for readability. Try this `Dim myArray : myArray = Array(1, 2, 4, 8)` — Santosh, Oct 12 '13 at 18:13
Related [microsoft-visual-basic-how-to-initialize-an-array-variable](http://stackoverflow.com/questions/18828139/microsoft-visual-basic-how-to-initialize-an-array-variable) — nawfal, Sep 22 '14 at 19:34

score 29 · Accepted Answer · edited May 23 '17 at 12:25

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.

The `Evaluate` way can be shortened to `v = [Column(A:KN)*0+13]` or even `v = [A1:KN1+13]` if you use blank range — Slai, Jul 25 '16 at 15:10

score 12 · Answer 2 · edited May 08 '18 at 21:48

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

What does this initialize to? For example, what if I want to initialize the entire array to 13? — user1205577, Oct 12 '13 at 17:25
I appreciate the clarification, but perhaps I wasn't clear about the question. By `what does this initialize to`, I meant the initialization value. 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. — user1205577, Oct 12 '13 at 17:35
I am not aware if you can do that in one line. Unless for example you have 300 cells in Excel with a value of 13, then you can use MyArray = Range("A1:A300").Value which will create a 2D array. — Siddharth Rout, Oct 12 '13 at 17:42
Wait there is one more method I just thought of ;) One moment updating the post — Siddharth Rout, Oct 12 '13 at 17:45
If you are going to be initializing the arrays often, one way for easier initialization COULD be to write the data to a temp sheet once and then read data from the sheet each time in the future. But I suspect this is complete and utter overkill. — enderland, Oct 12 '13 at 18:11
"sNum = WorksheetFunction.Rept("13,", 300)" : This is the only solution proposed that does not required accessing cells or rows in the worksheet, hence probably the fastest. Hence best solution from my standpoint. — serge, Nov 28 '15 at 11:24
Am I reading this right "Dim x (12) as integer" is a 13 item array? — Mark Walsh, Jan 03 '20 at 20:22
@MarkWalsh: Yes. When you do not specify a lower bound then it is taken as `0`. So `x(0)` to `x(12)` are 13 items. `Dim X(1 to 12)` will have only 12 items. You can always check the bounds of the array using `LBound(x)` and `UBound(x)` — Siddharth Rout, Jan 04 '20 at 04:01

score 4 · Answer 3 · answered Oct 12 '13 at 17:21

4

For VBA you need to initialise in two lines.

Sub TestArray()

Dim myArray
myArray = Array(1, 2, 4, 8)

End Sub

answered Oct 12 '13 at 17:21

Santosh

12,175
4
41
72

Thanks for the comment, but this does what I mentioned in the question - I'm looking to initialize the `entire` array to a single value in one shot. – user1205577 Oct 12 '13 at 17:26
1

@user1205577 `Dim myArray = New Integer() {1, 2, 4, 8}` is a valid syntax for vb.net but not for vba. For vba you have to do it the way i suggested if you do not wanna loop. – Santosh Oct 12 '13 at 17:29
+1 for differentiating between VBA and VB.NET, but that still means you have to write the initialization value for every single element. The heart of the question is how to do it without having to write a value out, say, 100 times. – user1205577 Oct 12 '13 at 17:32
@user1205577 When ever a variable is defined as integer it has a default values as 0. Similarly if we define array ...as per @sid answer `Dim myArray(1 to 10) As Integer` all the elements inside array will have value as 0. But i would term it as array declaration and i believe you are looking for declaration and intialisation without looping. – Santosh Oct 12 '13 at 17:40
Right, but what if we don't want the initial value to be zero? What if we want it to be 13? Or 50, or whatever. That initialization is the question being asked. – user1205577 Oct 12 '13 at 17:42
1

@user1205577 I believe its not possible in vba (array declaration and assigning it some other value at once without looping). – Santosh Oct 12 '13 at 17:46

score 4 · Answer 4 · edited Jun 20 '20 at 09:12

4

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 25 '14 at 19:48

tbur

2,384
1
13
12

nice answer. i think this one is even a little better though `myArray = [A1:KN1+13]` – luckyguy73 Jun 25 '18 at 18:14

score 2 · Answer 5 · answered May 12 '15 at 17:02

This function works with variables for size and initial value it combines tbur & Filipe responses.

Function ArrayIniValue(iSize As Integer, iValue As Integer)
Dim sIndex As String
sIndex = "INDEX(Row(1:" & iSize & "),)"
ArrayIniValue = Evaluate("=Transpose(" & sIndex & "-" & sIndex & "+" & iValue & ")")
End Function

Called this way:

myArray = ArrayIniValue(350, 13)

score 1 · Answer 6 · answered Apr 14 '15 at 21:34

1

Fancy way to put @rdhs answer in a function:

Function arrayZero(size As Integer)
  arrayZero = Evaluate("=IF(ISERROR(Transpose(A1:A" & size & ")), 0, 0)")
End Function

And use like this:

myArray = arrayZero(15)

answered Apr 14 '15 at 21:34

Filipe

91
8

score 0 · Answer 7 · answered Jun 17 '21 at 09:59

Thanks for the answers. I rebuilt some default functions from Matlab to initialize arrays.

colon operator as function
linspace
ones
zeros

hope that helps in any application

Function colon( _
    ByVal LB As Integer, _
    ByVal UB As Integer, _
    Optional ByVal Step As Integer = 1, _
    Optional ByRef ColonStr As String = "")
'generates array from LB to UB with stepwidth Step
'equal to Matlab operator "a:b" or "a:n:b"

Dim sIndex As String
Cnt = WorksheetFunction.RoundUp((UB - LB + 1) / Step, 0)
sIndex = "INDEX(Row(1:" & Cnt & "),)"

ColonStr = "((Transpose(" & sIndex & ")-1)*" & Step & "+" & LB & ")"
colon = Evaluate("=" & ColonStr)
End Function


Function linspace( _
    ByVal LB As Integer, _
    ByVal UB As Integer, _
    Optional ByVal Count As Integer = 100)
'requirements: colon()
'generates linearly spaced array
'equal to Matlab function linspace()

Dim str As String
Count0 = Count - 1
temp = colon(0, Count0, 1, str)
y = Evaluate("=" & LB & "+" & str & "*" & ((UB - LB) / Count0))
linspace = "=" & LB & "+" & str & "*" & ((UB - LB) / Count0)
End Function


Function ones( _
    ByVal vert As Integer, _
    Optional ByVal horz As Integer = 1)
'generate array of all ones
'horz (multidimension) not implemented yet

Dim sIndex As String
sIndex = "INDEX(Row(1:" & vert & "),)"
ones = Evaluate("=Transpose(" & sIndex & ")*0+1")
End Function


Function zeros( _
    ByVal vert As Integer, _
    Optional ByVal horz As Integer = 1)
'generate array of all zeros
'horz (multidimension) not implemented yet

Dim sIndex As String
sIndex = "INDEX(Row(1:" & vert & "),)"
zeros = Evaluate("=Transpose(" & sIndex & ")*0")
End Function

score -1 · Answer 8 · answered Jun 17 '21 at 20:27

One liner via newer dynamic function Sequence()

If you dispose of the new dynamic SEQUENCE() function (vs. Excel/MS 365) you can create a 1-based 2-dimensional array with indicated dimension indices (e.g. 5 rows, 4 columns) which gets filled with a list of sequential numbers.

As OP requires to initialize the entire array by a fixed numeric value (3rd argument of e.g. 13), you simply have to set the incremental steps (last argument) to 0:

Excel Function The following formula entered into any wanted worksheet cell would display the array results in a spill range:

=SEQUENCE(5,4,13,0)

Use within VBA procedures

It's also possible to assign either

a) the evaluation result or
b) the worksheetfunction itself

to a predeclared array:

    Dim myArray()           ' declare variant array

a) Evaluation

    myArray = [Sequence(5,4,13,0)]

    myArray = Evaluate("Sequence(5,4,13,0)")

b) Functional approach

    myArray = Worksheetfunction.Sequence(5,4,13,0)  ' or even: Application.Sequence

VBA (Excel) Initialize Entire Array without Looping

8 Answers8

Linked

Related