initailise array with constant values

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,873
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
What data type is the constant value string, number?

Also, how are you defining/declaring your array?
 
Upvote 0
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
 
Upvote 0
Not sure if this any faster:

Code:
Dim MyArray As Variant
Range("A1:A100").Value = "ABC"
MyArray = Application.Transpose(Range("A1:A100"))
 
Upvote 0
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:
Upvote 0
.. 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)
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top