initailise array with constant values

[jimrward]

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

CHARACTER*3 MYARRAY(10)/10 * "abc"/

 

[Norie]

What data type is the constant value string, number?

Also, how are you defining/declaring your array?

 

[jimrward]

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

dim myarray(10) as string

 

[iliauk]

Not sure if this any faster:

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

 

[Peter_SSs]

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>

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

 

[Peter_SSs]

.. and if you do want a 1-based array, just change this line in the second code above.

ReDim Preserve myarray(1 To 10)

 

[Rick Rothstein]

Here is one more way to create an one dimensional array with lower bound of 1 and upper bound of 10...

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]

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

For String values

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

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

 

[mikerickson]

If a 0 based array is acceptable

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]

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

For String values

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

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?)

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