Matrix VBA

Cloclo1990

New Member
Joined
May 21, 2019
Messages
4
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.)
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top