assign excel range to matrix 2

[voodoojon]

in my module I have a matrix
dim probMatrix(26,27)
and I want to assign to it the values in a 27 by 29 block of excel cells. I've never tried using vba with excel before, so I don't yet know to assign a variable to a single excel cell.

 

[mattdrinks]

Hi voodoojon,

This should read the values in the cells into the array

Sub AssignArray()

Dim probMatrix(26, 27)
Dim i As Single
Dim ii As Single

For i = 0 To UBound(probMatrix, 1)
    For ii = 0 To UBound(probMatrix, 2)
        probMatrix(i, ii) = Cells(i + 1, ii + 1)
    Next
Next
End Sub

This assumes that the cells with the data start at A1
If they do not then change the Line:
probMatrix(i, ii) = Cells(i + 1, ii + 1)
to
probMatrix(i, ii) = Cells(i + 2, ii + 2)
(eg. The data starts in B2)

The values in the array are not linked to values in the cells,
so if the values in the cells are changed you will have to read the values into the array again.

To write the values from the array back to the sheet:

Sub OutputArray()

Dim probMatrix(26, 27)
Dim i As Single
Dim ii As Single

For i = 0 To UBound(probMatrix, 1)
    For ii = 0 To UBound(probMatrix, 2)
        Cells(i + 1, ii + 1) = probMatrix(i, ii)
    Next
Next
End Sub

If you want the values in your Array to be linked to the values in the cell,
then I would use a range object instead of an array:

Sub probRange()

Dim probMatrix As Range
Dim c1 As Range

Set probMatrix = Range(Cells(1, 1), Cells(29, 27))

'Loop through all cells in range
For Each c1 In probMatrix
    c1.Value = "Hello"
Next
End Sub

Matt

 

[TonyJollans]

Hi voodoojon,

You have a problem if your matrix has different dimensions from your array of cells, but assuming that is a typo ..

Dim probMatrix
probMatrix = Range("A1:AA29")

.. will cast probMatrix as a 27 * 29 (1-based) array filled with data from cells A1:AA29. Obviously you use your own range.

Is that what you want?

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[voodoojon]

cheers, that not only answers my question but a whole load of others that I hadn't got round to asking!

 

[voodoojon]

but how come matt's solution is so much longer? is there any disadvantage to the short method?

 

[mattdrinks]

Not that I know of, it's just that Tony is better at this than me.
I never realized that you could do it like that!

Thanks
Matt

 

[TonyJollans]

Thanks for those kind words, Matt.

There is no disadvantage that I know to the 'short' method either. In fact, looping through cells is a relatively slow process and it is much more efficient to assign to an array in a single step. Further, for processing large ranges, it is actually more efficient to assign the cells to an array, process the array, and assign it back to the cells afterwards, than it is to just step through the cells.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.