How to create a VBA function to find the max of each row in an array

Question

I want to create a customised function to find the max value of each "row" in an array. For example, I have a sample 5x3 dimensions array as below:

[ 0     0      0
  0     1      1
  1     0      1
  1     1      1
  0     0      0]

And I want to get the max value of each row which should come in the form of: [0 1 1 1 0]

Please note that this is just one example. I'm facing quite a number of matrices which require this task. I did some research online but couldn't find an easy solution for this. There is one which I found requires output into the spreadsheet before finding the max values (the outputting into spreadsheet is not preferred though). This code is below:

Function Max_Each_Row(Data_Range As Range) As Variant
    Dim TempArray() As Double, i As Long
    If Data_Range Is Nothing Then Exit Function
    With Data_Range
    ReDim TempArray(1 To .Rows.count)
    For i = 1 To .Rows.count
        TempArray(i) = Application.Max(.Rows(i))
    Next
    End With
    Max_Each_Row = TempArray
End Function

Could someone point me to the right direction? i.e. creating a function () to find the desired array with the max value in each row, without outputting anything into the spreadsheet.

Thanks for your help!

How are the matrices entered in Excel before you have to output them to a spreadsheet? — BruceWayne
– BruceWayne, Commented Nov 12, 2017 at 16:51
Are you intending this function to be a UDF which will be called by Excel (and that writes output to the spreadsheet cells from which it was called) or just a function which will be called only from other functions/subroutines within VBA code? — YowE3K
– YowE3K, Commented Nov 12, 2017 at 16:53
The matrices are intended to be mmult functions of a few other matrices/arrays. But since I have too many calculations like that, outputting them all would not be efficient. The sample matrix shown above is not intended to be shown in the spreadsheet anyway. Yes the function is intended to be a UDF or function to be called by other VBA code. If that is not possible, I would need to resort to subroutines. — Dennis Le
– Dennis Le, Commented Nov 12, 2017 at 17:10

Scott Craner · Accepted Answer · 2017-11-12 17:25:11Z

2

Change the input from a range to an array:

Function Max_Each_Row(Data_Range() As Variant) As Variant()
    Dim TempArray() As Variant, i As Long
    ReDim TempArray(LBound(Data_Range, 1) To UBound(Data_Range, 1))
    For i = LBound(Data_Range, 1) To UBound(Data_Range, 1)
        TempArray(i) = Application.Max(Application.Index(Data_Range, i + 1, 0))
    Next

    Max_Each_Row = TempArray
End Function

Then you can call it directly from a sub like this.

Sub mxrow()
Dim arr(4, 2) As Variant
Dim outArr() As Variant
arr(0, 0) = 0
arr(0, 1) = 0
arr(0, 2) = 0
arr(1, 0) = 0
arr(1, 1) = 1
arr(1, 2) = 1
arr(2, 0) = 1
arr(2, 1) = 0
arr(2, 2) = 1
arr(3, 0) = 1
arr(3, 1) = 1
arr(3, 2) = 1
arr(4, 0) = 0
arr(4, 1) = 0
arr(4, 2) = 0

outArr = Max_Each_Row(arr)
Dim i As Long
For i = LBound(outArr) To UBound(outArr)
    Debug.Print outArr(i)
Next i

End Sub

edited Nov 12, 2017 at 17:25

answered Nov 12, 2017 at 17:18

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dennis Le Over a year ago

Thanks for your help. However, I'm gonna have around 20,000 matrices like this and writing them one by one wouldn't be really practical.

Scott Craner Over a year ago

You did not ask how to fill an array, you asked how to find the max without using the spreadsheet, the second code is just an example. how you fill the matrices into arrays is up to you.

Dennis Le Over a year ago

Oh sorry I misunderstood your response. I have tested the code. It works well as a function to be used in another subroutine but could not be used as UDF. When I tested it out with Max_Each_Row(ArrayA*ArrayB), the answer doesn't seem quite right.

YowE3K Over a year ago

@DennisLe If you use that code (or any similar code) as a UDF, you need to ensure you enter it as an array formula in a range that has as many columns as you expect to have values in the result array. So, for your example, you would need to enter the formula in 5 columns (one for each row's maximum). If you want to return it to multiple rows, instead of multiple columns, you would need to use {=TRANSPOSE(Max_Each_Row(ArrayA*ArrayB))}

Dennis Le Over a year ago

Yeah that’s what I did but it came out as an array of [0 0 0 0 0] or [1 1 1 1 1] only instead of [0 1 1 1 0]. I ran my subroutine and see correct results, but the same calculation in the spreadsheet would not give me the same answer

Moreno · Accepted Answer · 2017-11-13 16:25:13Z

0

Try this it will work fine

 Option Explicit
    Option Base 1
    'by [email protected]
    Public Function MAX_IN_EACH_ROW(Rango As Object) As Variant
     Dim n%, i%, j%, MR() As Variant
      n = Rango.Rows.Count
       ReDim MR(n)
       For i = 1 To n Step 1
            MR(i) = Application.Max(Application.Index(Rango, i, 0)) '#In here I am slicing the original range into rows
       Next i
         MAX_IN_EACH_ROW = MR
    End Function

Hope it helps

answered Nov 13, 2017 at 16:25

Moreno

6382 gold badges11 silver badges26 bronze badges

1 Comment

Dennis Le Over a year ago

Thanks for sharing. Somehow this doesn't work. I guess Scott's answer above is the best I could get

Collectives™ on Stack Overflow

How to create a VBA function to find the max of each row in an array

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related