Hi,
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)
k=Lbound(arr)
ub=Ubound(arr)
For i=0 To n1
For j=0 To n2
arr2(i,j)=arr(k)
k=k+1
If k>ub Then GoTo ExitLabel
Next j
Next i
ExitLabel:
ArrayTo2D=arr2
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)
rg.setDataArray(a())
End SubDear @Villeroy, for correct coloring of text written in Basic, the forum engine wants to see at least one comment starting with an apostrophe. 
1 Like
I’m satisfied when it does not write curly quotes.