CODEWITHSUNDEEP
arraysvbafunction
gjoeyjoe
gjoeyjoe

Reputation: 25

Calling a Double Array Function

I have a question regarding my function to return the largest number in a double array. This is what my current code looks like

Function return_largest()
last_row = Range("A1").End(xlDown).Row 
Dim array_1()
ReDim array_1(last_row - 2, 5)    

For i = 0 To last_row - 2

    array_1(i, 0) = Range("A" & i + 2)
    array_1(i, 1) = Range("B" & i + 2)
    array_1(i, 2) = Range("C" & i + 2)
    array_1(i, 3) = Range("D" & i + 2)
    array_1(i, 4) = Range("E" & i + 2)

Next

MsgBox Application.WorksheetFunction.Max(array_1)

End Function

This function works for a 5x5 array. How should I change this code if I wish to make it a function usable for an array of dimensions (i,k)?

In other words, how could I write a function that finds the largest element for an array which can be called in a subprocedure for an array of size (x,y) (Whatever dimensions the array in the subprocedure has), and what is the syntax for that sub-procedure?

Upvotes: 1

Views: 119

Answers (1)

shg
shg

Reputation: 333

If you are calling this from the worksheet and don't pass the range, Excel sees no dependency, and will not recalculate when the range changes. 

Function return_largest(r As Range) As Double
  return_largest = WorksheetFunction.Max(r.Value2)
End Function

Then you can call it using a dynamic or static range:

=return_largest(A1:K5)

Upvotes: 1

Related Questions