Matrix VBA

[Cloclo1990]

Dear All,

I have been trying to work on this VBA question:

1. You must now create a user-defined function that will take two parameters of type range (two matrices mat1 and mat2). The function returns a matrix C in which each element is equal to the max value with the same coordinates in mat1 and mat2: ??,? = ???(???1?,?, ???2?,?)
   The function must be used as an array-based formula in Excel. Its exact declaration must be:
   Function matrixofMax(mat1 as Range, mat2 as Range) as Variant
   If mat1 and mat2 do not have the same size, an explicit error message must be returned.

I have tried to start the coding but I am stuck on finding if mat1 and mat2 have the same size or not? And choosing the highest value...

Thank you for your time ! Have a great day



Option Explicit
Option Base 1


Function MatrixOfMax(Mat1 As Range, Mat2 As Range) As Variant


'Declare variables
Dim Mat1() As Variant, Mat2() As Variant
Dim i As Integer, j As Integer


'Ask User to fill up matrix 1 and 2
MsgBox "Hello. Please make sure you filled up Matrix 1 and Matrix 2"


'Setting the size of the matrix 1 and 2 to the user input
ReDim Mat1(size, size)
ReDim Mat2(size, size)


'Setting the value of matrix 1
For i = 1 To size
For j = 1 To size
Mat1(i, j) = i + j
Next j
Next i


createMatrix = Mat1


'Setting the value of matrix 2
For i = 1 To size
For j = 1 To size
Mat1(i, j) = i + j
Next j
Next i


createMatrix = Mat2


'Verify the two matrix are of the same size




'Choose highest value of each matrix




'Show the new matrixin cells I2:M5 of excel file




End Function

 

[BlueAure]

Hello,
You could try using arrays instead of ranges and use UBound to get the uppermost index of the arrays. You can get the upper bound of each dimension of the array by using something like this:

UBound(Mat1, 1) for the X and UBound(Mat1, 2) for the Y

You could then compare those values to see if one is larger than the other.

if UBound(Mat1, 1) > UBound(Mat1, 1) Then
'Code Here
End If


You could also loop through your range and count the values.

 

[Cloclo1990]

Thanks BlueAure for the information. However, the exercise imposes me to use Ranges...

 

[BlueAure]

Sorry, I didn't that. You could try looping through each cell in your range and checking if it has a value while adding to a count. That could get you the dimensions. Probably not the most elegant solution, but it would probably work.

 

[Fazza]

One question at a time.
For the size check.

Please write in words how you would test the two matrix are of the same size

With the plan to then translate that to VBA

 

[mikerickson]

I note that the OP code is working with square matrices, while the question does not make that assumption.

I also note that SOME Excel functions, like SUMIF, that take two (supposedly) same sized ranges. In practice the first range is the only one whose size matters. Excel resizes the second range argument to match.

e.g. =SUMIF(A1:A10, "x", B1:B10) returns the same as =SUMIF(A1:A10, "x", B1:B2)

So, your UDF could take that approach and still be consistant with Excel function practice.

(NB. There are other Excel functions where the two range arguments must be the same size. "Size doesn't matter" is not always true in Excel, nor is it always false.)

 

[Rick Rothstein]

I have tried to start the coding but I am stuck on finding if mat1 and mat2 have the same size or not?

Since your impose function header declares both "matrices" to be ranges, all you need to do to see if they are the same size is to see if both ranges have the same number of rows AND the same number of columns.