Macro (SVB) Programs Example - Inserting an Array into a Spreadsheet

This example demonstrates creating an array, resizing it to the size of a 10x10 STATISTICA Spreadsheet, filling it with numbers, and copying its contents to a new spreadsheet:

Sub main

'Create a dynamic array
Dim
Matrix() As Double

'Resize matrix to the size of a 10*10 spreadsheet

ReDim
Matrix(10,10)

'fill the matrix

'the first loop handles going through the rows (cases)

For
i = 1 To 10

'this loop handles going through the columns (variables)
For
j = 1 To 10

Matrix(i,j) = i

Next j

Next i
'create a spreadsheet

Dim
s As New Spreadsheet

'fill spreadsheet with the matrix

'the first loop handles going through the rows (cases)

For
i = 1 To 10

'this loop handles going through the columns (variables)
For
j = 1 To 10

'Each cells has the corresponding matrix
'(2D array) value placed into it

s.Cells(i,j) = Matrix(i,j)

Next j

Next i
s.Visible = True

End Sub

In this example, two different For loops are used together to fill both the two-dimensional array and later the spreadsheet. The first loop is responsible for moving down the rows (cases) of the spreadsheet, whereas the second loop moves across the columns (variables). How this works is that the first loop will begin at one and the loop inside of it will run ten times (filling each cell in the current case with the number of the case). Once that is complete, the outer loop will move to the next case down and the inner loop will again move across and fill the cells. This process is repeated until the outer loop ends, and all of the cases have had their respective cells filled.