The function below sums up columns of a matrix and the result is a horizontal array. Now I would like to modify that function so that if the calling range is vertical that it returns the sum of the rows. So the code needs to detect whether the spreadsheet range containing the function is horizontal or vertical. Anybody know how to do this? Many thanks in advance.
This is the code:
Function MSum(Mat As Variant)
Dim i As Integer, j As Integer
Dim m As Integer, n As Integer
Dim MatC As Variant 'dynamic array with resulting 'matrix
Dim Mat1 As Variant
On Error GoTo MSum_Error
If TypeName(Mat) = "Range" Then
Mat1 = Mat.Value
Else
Mat1 = Mat
End If
If Not (IsArray(Mat1)) Then
Err.Raise vbObjectError + 11, "MSum", "Please use matrices!"
End If
'Check dimensions (of the input arrays)
'Create resulting matrix
m = UBound(Mat1, 1) 'number of rows
n = UBound(Mat1, 2) 'number of columns
Lm = LBound(Mat1, 1) 'number of rows
Ln = LBound(Mat1, 2)
ReDim MatC(1 To n) 'matrix with m rows and n columns
'Add the two matrices
For j = 1 To n
For i = 1 To m
MatC(j) = MatC(j) + Mat1(i, j)
Next i
Next j
MSum_Exit:
MSum = MatC
Exit Function
MSum_Error:
MatC = "#Value!"
' MsgBox Err.Description
Resume MSum_Exit
End Function
This is the code:
Function MSum(Mat As Variant)
Dim i As Integer, j As Integer
Dim m As Integer, n As Integer
Dim MatC As Variant 'dynamic array with resulting 'matrix
Dim Mat1 As Variant
On Error GoTo MSum_Error
If TypeName(Mat) = "Range" Then
Mat1 = Mat.Value
Else
Mat1 = Mat
End If
If Not (IsArray(Mat1)) Then
Err.Raise vbObjectError + 11, "MSum", "Please use matrices!"
End If
'Check dimensions (of the input arrays)
'Create resulting matrix
m = UBound(Mat1, 1) 'number of rows
n = UBound(Mat1, 2) 'number of columns
Lm = LBound(Mat1, 1) 'number of rows
Ln = LBound(Mat1, 2)
ReDim MatC(1 To n) 'matrix with m rows and n columns
'Add the two matrices
For j = 1 To n
For i = 1 To m
MatC(j) = MatC(j) + Mat1(i, j)
Next i
Next j
MSum_Exit:
MSum = MatC
Exit Function
MSum_Error:
MatC = "#Value!"
' MsgBox Err.Description
Resume MSum_Exit
End Function