CODEWITHSUNDEEP
arraysexcelvba
Dennis Le
Dennis Le

Reputation: 13

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

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!

Upvotes: 0

Views: 2780

Answers (2)

Moreno
Moreno

Reputation: 638

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

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

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

Upvotes: 2

Related Questions