How to assign an Excel Range to a 2D array?

Question

Could you please say- how a Excel Range("G2:AA1000") can be assigned to a 2D array? If possible how to return back that 2D array to the same range after performing some operation on that 2D array?After assignment a Range to an 2D array,How each row will be identified from that 2D matrix?

Thanks,

Answer 1

There is an easy way to make changes to an area using an array, and write it out to the same place, or somewhere else.

This example code will copy data from one area to another, using an array:

Sub example()
Dim testdata()
testdata = Range("A1:B13")
Range("D1:E13") = testdata ' simple copy
Range("G1") = testdata ' copy only 1 cell
Range("I1:K22") = testdata 'try to copy too much
End Sub

The testdata array starts from 1, and will extend to the number of columns and rows specified in the range. In this case, testdata(1,1) refers to the data obtained from A1, testdata(1,2) refers to B1, finishing up with testdata(13,1) referring to A13, and testdata(13,2) referring to B13.

Setting the range equal to the array in the next line copies the array into the specified location.

• If the area is smaller than the original array, it will copy only enough of the array to fill that space, so Range("D1")=testdata will only place one cell on the sheet.
• If you specify a larger area, then #N/A will fill the area that is not in the space covered by array elements, so Range("A1:A3")=testdata will fill A1 and A2 with data from the array, but A3 will have #N/A

Result of example program:
Note: A1:B13 is the original data, which gets copied with the subsequent range(??)=testdata

Answer 2

Here's a worked-out example of reading a range of data from a worksheet, operating on the array, and then writing it back out to the same worksheet.

    Sub RangeArray()

    Dim Rng As Range
    Dim Arr()
    Dim ArrItem
    Dim i As Long, j As Long
    Dim rUB as Long, cUB as Long

     Set Rng = Worksheets("Sheet1").Range("A1:G19")
    rUB = Rng.Rows.Count    'Row upper bound
    cUB = Rng.Columns.Count  ' Column upper bound

    ReDim Arr(1 To rUB, 1 To cUB)

   'Read worksheet range into array
    For i = 1 To rUB
       For j = 1 to cUB
          Arr(i, j) = Rng.Cells(i, j).Value
       Next
    Next

   'Do something to array 
    For i = 1 To rUB
       For j = 1 To cUB
          If i <> j Then
             Arr(i, j) = Arr(i, j) / (i * j)
          End If
       Next
    Next

   'Write array back to worksheet
    Set Rng = Worksheets("Sheet1").Range("I1")
    For i = 1 To rUB
       For j = 1 To cUB
          Rng.Offset(i - 1, j - 1).Value = Arr(i, j)
       Next
    Next

    End Sub

Answer 3

Yes, an Excel range can be assigned to a 2D array in one single assignment. In C++/CLI it looks like this:

cli::array<Object^, 2>^ arrData = safe_cast<cli::array<Object^, 2>^>(rg->Value[Excel::XlRangeValueDataType::xlRangeValueDefault]); In c# or visual basic it would look considerably simpler (see here for example https://www.automateexcel.com/vba/assign-range-to-array/, btw in dotnet the object is now playing the role of the variant data type). Note that it must be a two dimensional array and the returned array has a one-based indexing, and not a zero based indexing.

For large data sets this method is a lot faster than the looping. The looping generates lots of COM objects. I compared the two methods with a 33000 row Excel range and the data import into the array was almost instantaneous while with the looping it took very long and it heated up the CPU.

Answer 4

One way to loop through a range is to use the For...Next loop with the Cells property. Using the Cells property, you can substitute the loop counter (or other variables or expressions) for the cell index numbers. In the following example, the variable counter is substituted for the row index. The procedure loops through the range C1:C20, setting to 0 (zero) any number whose absolute value is less than 0.01.

Sub RoundToZero1()
    For Counter = 1 To 20
        Set curCell = Worksheets("Sheet1").Cells(Counter, 3)
        If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
    Next Counter
End Sub