Example

The following example lets the user type numbers in the first of two sheets. Selecting the "Copy Array" button copies a block of the numbers to the beginning of the second sheet.

C++

void CAboutDlg::OnButton1()
{
    SAFEARRAYBOUND rgsabound[2];
    VARIANT varg;
    long lg[2];
    int i,j;
    int val;
    VariantClear(&varg);
    VariantInit(&varg);

    // Treat values as longs
    varg.vt = VT_ARRAY | VT_I4;

    // Set up dimensions
    rgsabound[0].lLbound = 0;
    rgsabound[0].cElements = 4;
    rgsabound[1].lLbound = 0;
    rgsabound[1].cElements = 2;
    varg.parray = SafeArrayCreate(VT_I4,2,rgsabound);

    // varg will contain values in cols 1 & 2, rows 1 to 4
    m_Spread.GetArray(1,1,varg);

    // Set array into second sheet
    m_Spread2.SetArray(1,1,varg);
}

Visual Basic

' Dimension variables for arrays
Dim ret As Boolean, cols As Long, rows As Long

Sub Form_Load()
    ' Create label for button
    Command1.Caption = "Copy Array"
    ' Set both sheets to contain number cells
    fpSpread1.Row = -1
    fpSpread1.Col = -1
    fpSpread1.CellType = CellTypeNumber
    fpSpread2.Row = -1
    fpSpread2.Col = -1
    fpSpread2.CellType = CellTypeNumber
End Sub

Sub Command1_Click()
    ' Check for block selection when user clicks button
    If fpSpread1.IsBlockSelected = True Then
        ' Clear second sheet of any previous values
        fpSpread2.Row = -1
        fpSpread2.Col = -1
        fpSpread2.ClearRange 0, 0, -1, -1, True
        ' Calculate dimensions of selected block
        cols = Abs(fpSpread1.SelBlockCol2 - fpSpread1.SelBlockCol)
        rows = Abs(fpSpread1.SelBlockRow2 - fpSpread1.SelBlockRow)
        ' Declare the array to hold the data
        ReDim myarray(rows, cols) As Integer

        ' Retrieve the block of data from the first sheet
        ret = fpSpread1.GetArray(fpSpread1.ActiveCol, fpSpread1.ActiveRow, myarray)
        ' Place the block of data into the second sheet
        ret = fpSpread2.SetArray(1, 1, myarray)
    Else
        MsgBox "Select a block of cells.", , "Select Cells"
    End If
End Sub