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"/

 

[Peter_SSs]

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

A few comments.

1. This is quite similar to my suggestion in post #5 but I do like the idea of putting the delimiter before the given string and then using Mid.

2. I still don't see a way to make this work to fill the array with numerical values.

3. I don't know how big an array the OP might be wanting to fill, but using Application.Rept is more restrictive in that regard than the slightly longer construct I used to produce the string to split. For example, with your existing code, try changing mySize to 3000

So, adopting part of Mike's idea with part of mine from post #5, another way to fill a zero-based 1-dimensional array of string values:

Sub FillArray1a()
  Dim myArray As Variant

  Const Size As Long = 10000
  Const Value As String = "initial value"

  myArray = Split(Mid(Replace(Space(Size), " ", Chr(0) & Value), 2), Chr(0))
'  ReDim Preserve myArray(1 To Size) '<- To convert to a 1-based array
End Sub

Again, if a 1-based array is required, it can be converted by using the commented line

 

[Peter_SSs]

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 & """)")
  MsgBox TypeName(MyArray(1))
End Sub

Rick, that fills each element of the array with the String "5.2" rather than the Number 5.2 per FillArray5


Edit: But removing the extra quote marks does appear to work - though I was sure I tried this yesterday and couldn't get it to work.

Sub FillArray4a()
  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

 

[Rick Rothstein]

Edit:

I just called your message up to respond to it and the Edit note appeared... it was not in the message itself, so you must have just changed it.

But removing the extra quote marks does appear to work

That is what I was just going to post.

though I was sure I tried this yesterday and couldn't get it to work.

Oh good... then I am not the only one who has this happen to them.

 

[jimrward]

thank you all for some interesting takes, I will try a few to see how I get on, I have in the meantime put together a version of what I need using temp work areas and cells, as execution speed is not an issue