Simple Matrix Algebra: Ranges Vs Arrays

[hahdawg]

Hi,

I'm new to VBA (although I know Matlab well), and I'm having trouble working with arrays and ranges. In VBA, the application.worksheetfunction matrix algebra functions all seem to work with arrays and ranges. I'd like some of the custom matrix algebra functions I write to do the same, but I can't figure out what to do.

For example, suppose I write a function which doubles every element in a range. I know that it's a dumb function, but it'll show where I'm getting confused.

Function DoubleMatrix(inMatrix1 As Range)
RowCount = inMatrix1.Rows.Count
ColCount = inMatrix1.Columns.Count
Dim tempMatrix() As Double
ReDim tempMatrix(1 To RowCount, 1 To ColCount)
For i = 1 To RowCount
    For j = 1 To ColCount
        tempMatrix(i, j) = 2 * inMatrix1(i, j)
    Next j
Next i
DoubleMatrix = tempMatrix
End Function

This function works fine when I use it on a range in a spreadsheet.

But now suppose I'm writing a sub, and I want to call the DoubleMatrix function.

Sub TryingToCallFunction()
Dim inputMatrix(1 To 4, 1 To 4) as double
For i = 1 To 4
    For j = 1 To 4
        inputMatrix(i, j) = i + j
    Next j
Next i
test = DoubleMatrix(inputMatrix)
End Sub

This sub doesn't work, because inputMatrix isn't a range. I'm not sure what to do here. Do I dim inputMatrix as a range, and populate it with a loop? I tried this, but I couldn't get it to work. Should I change the DoubleMatrix function to work with both ranges and arrays? In this case, I'd have to determine whether the input is an array or a range, and figure out the dimensions of the input using, respectively, Ubound() or count. This seems kind of clunky. I'm really lost, so I'd appreciate any help.

 

[shg]

Welcome to the board.

I'd do it like this:

Sub test()
    With Range("A1:D5")
        .Formula = "=randbetween(1,10)"
        .Value = .Value
        .Offset(, 5).Value = DoubleMatrix(.Value)
    End With
End Sub
 
Function DoubleMatrix(avdInp As Variant) As Double()
    Dim iRow As Long
    Dim iCol As Long
    Dim adOut() As Double
    
    ReDim adOut(1 To UBound(avdInp, 1), 1 To UBound(avdInp, 2))
   
    For iRow = 1 To UBound(avdInp, 1)
        For iCol = 1 To UBound(avdInp, 2)
            adOut(iRow, iCol) = 2 * avdInp(iRow, iCol)
        Next iCol
    Next iRow
    
    DoubleMatrix = adOut
End Function

Doing operations in memory is much faster than interacting with the worksheet.

 

[hahdawg]

Thanks, shg.

With your code, the doubleMatrix function works with array inputs but not range inputs, right? I'd like the doubleMatrix function to work with either type of input.

I want to use the function on spreadsheets, where the input would be a range, and in functions and subs, where the input would often be an array.

Here's what I had in mind:

Function DoubleMatrix(avdInp)
    Dim iRow As Long
    Dim iCol As Long
    Dim adOut() As Double
    Dim nRows As Long
    Dim nCols As Long
 
    If IsObject(avdInp) Then
        nRows = avdInp.Rows.Count
        nCols = avdInp.Columns.Count
    Else
        nRows = UBound(avdInp, 1)
        nCols = UBound(avdInp, 1)
    End If
 
    ReDim adOut(1 To nRows, 1 To nCols)
 
    For iRow = 1 To nRows
        For iCol = 1 To nCols
            adOut(iRow, iCol) = 2 * avdInp(iRow, iCol)
        Next iCol
    Next iRow
 
    DoubleMatrix = adOut
End Function

It seems kind of clunky, but maybe it's the best way to do it?