Populating an array

For the purpose of doing some tests I need an array filled with elements.
Lets say that I need a 3 x 2 array of integers.
So I declare it with “Dim Arr(2, 1) as Integer” and then to populate it with data I do:
Arr(0, 0) = 3
Arr(0, 1) = 64
Arr(1, 0) = 384
Arr(1, 1) = 37
Arr(2, 0) = 58
Arr(2, 1) = 8
What I ask is if there is a more simple way to fill the array like something as:
Arr() = {3, 64, 384, 37, 58, 8}
Many thanks in advance for any suggestion.

A possible way: use array of arrays instead of two-dimensional array; and populate it like

arr() = array(array(3, 64), array(384, 37), array(58, 8))
1 Like

Or you can create a macro.

Option Explicit
' Transform array arr to 2D array. n1, n2 - upper bounds of new array.
Function ArrayTo2D(Byval arr, ByVal n1 As Long, n2 As Long)
  Dim arr2, i As Long, j as Long, ub As Long, k As Long
  ReDim arr2(n1, n2)
  For i=0 To n1
    For j=0 To n2
      If k>ub Then GoTo ExitLabel
    Next j
  Next i
End Function

Sub Test
  Dim arr
  arr=ArrayTo2D(Array(3, 64, 384, 37, 58, 8), 2, 1)
End Sub

Writes to A1:I2 on first sheet:

Sub Main
rows = 12 'Count of rows
cols = 9 'Count of columns
dim a(rows -1)  'array of rows
for i = 0 to rows -1 'for each row
	redim c(cols -1) 'reset array of column values
  	for j = 0 to cols -1 'set column values of current row
		c(j) = i &";"& j 'current column value = current row index & current column index
	next j
	a(i) = c() 'set current row array
next i
REM get equally sized range 
rg = ThisComponent.Sheets(0).getCellRangeByPosition(0, 0, cols -1, rows -1)
End Sub

Dear @Villeroy, for correct coloring of text written in Basic, the forum engine wants to see at least one comment starting with an apostrophe. :slightly_smiling_face:

1 Like

I’m satisfied when it does not write curly quotes.