# initailise array with constant values

#### jimrward

##### Well-known Member
looking for a concise VBA method to fill an array with a constant value without using a loop

in fortran you can use data statements or inline declaration for example
Code:
``CHARACTER*3 MYARRAY(10)/10 * "abc"/``

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Norie

##### Well-known Member
What data type is the constant value string, number?

Also, how are you defining/declaring your array?

#### jimrward

##### Well-known Member
either or doesn't matter, if we assume string for this exercise, the size of the array may be variable and the string I will probably grab a cell value

Code:
``dim myarray(10) as string``

#### iliauk

##### Board Regular
Not sure if this any faster:

Code:
``````Dim MyArray As Variant
Range("A1:A100").Value = "ABC"
MyArray = Application.Transpose(Range("A1:A100"))``````

#### Peter_SSs

##### MrExcel MVP, Moderator
For String values, a 1-dimension, zero-based array, try one of these slight variations.

Edit again: <del>Edit: Assumption is that myarrayval does not contains any space characters.</del>

Code:
``````Sub FillArray1()
Dim myarray

Const myarraysize As Long = 10
Const myarrayval As String = "abc"

myarray = Split(Replace(Space(myarraysize - 1), " ", myarrayval & Chr(0)) & myarrayval, Chr(0))
End Sub

Sub FillArray2()
Dim myarray

Const myarraysize As Long = 10
Const myarrayval As String = "abc"

myarray = Split(Replace(Space(myarraysize), " ", myarrayval & Chr(0)), Chr(0))
ReDim Preserve myarray(myarraysize - 1)
End Sub``````

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
.. and if you do want a 1-based array, just change this line in the second code above.
Rich (BB code):
``ReDim Preserve myarray(1 To 10)``

#### Rick Rothstein

##### MrExcel MVP
Here is one more way to create an one dimensional array with lower bound of 1 and upper bound of 10...
Code:
``````Sub FillArray3()

Dim MyArray As Variant

Const Size As Long = 10
Const Value As String = "abc"

MyArray = Evaluate("TRANSPOSE(IF(ROW(),MID(REPT(""" & Value & """," & Size & ")," & _
Len(Value) & "*(ROW(A1:A" & Size & ")-1)+1,""" & Len(Value) & """)))")

End Sub``````

#### Peter_SSs

##### MrExcel MVP, Moderator
Taking Rick's Transpose(Row(.. idea as a base, I think we can shortcut that somewhat.

For String values
Code:
``````Sub FillArray4()
Dim MyArray As Variant

Const Size As Long = 10
Const Value As String = "abc"

MyArray = Evaluate("IFERROR(TRANSPOSE(ROW(1:" & Size & ")/0),""" & Value & """)")
End Sub``````

And also now for Numerical values
Code:
``````Sub FillArray5()
Dim MyArray As Variant

Const Size As Long = 10
Const Value As Single = 5.2

MyArray = Evaluate("TRANSPOSE(ROW(1:" & Size & ")/ROW(1:" & Size & ")*" & Value & ")")
End Sub``````

Last edited:
• Rick Rothstein

#### mikerickson

##### MrExcel MVP
If a 0 based array is acceptable
Code:
``````Dim myString As String
Dim myArray As Variant
Dim mySize As Long

mySize = 10
myString = "initial Value"

myArray = Split(Mid(Application.Rept(Chr(5) & myString, mySize), 2), Chr(5))``````

#### Rick Rothstein

##### MrExcel MVP
Taking Rick's Transpose(Row(.. idea as a base, I think we can shortcut that somewhat.

For String values
Code:
``````Sub FillArray4()
Dim MyArray As Variant

Const Size As Long = 10
Const Value As String = "abc"

MyArray = Evaluate("IFERROR(TRANSPOSE(ROW(1:" & Size & ")/0),""" & Value & """)")
End Sub``````
What an interesting construction... I like it!

And also now for Numerical values
Code:
``````Sub FillArray5()
Dim MyArray As Variant

Const Size As Long = 10
Const Value As Single = 5.2

MyArray = Evaluate("TRANSPOSE(ROW(1:" & Size & ")/ROW(1:" & Size & ")*" & Value & ")")
End Sub``````
But why didn't you use the first construction for numbers also? The following seems to work for me (am I missing something?)
Code:
``````Sub FillArray4()
Dim MyArray As Variant

Const Size As Long = 10
Const Value As Single = 5.2

MyArray = Evaluate("IFERROR(TRANSPOSE(ROW(1:" & Size & ")/0),""" & Value & """)")
End Sub``````

Replies
1
Views
69
Replies
5
Views
126
Replies
1
Views
98
Replies
1
Views
122
Replies
4
Views
199

### Forum statistics

1,191,719
Messages
5,988,287
Members
440,148
Latest member
sandy123 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back