Calling a Double Array Function

Question

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?

To convert a range to an array use array_1 = Range("A1").Resize(100,5).Value for example. There is no need to use the ReDim function to initialize it first. — John Alexiou
– John Alexiou, Commented Nov 21, 2014 at 1:46

shg · Accepted Answer · 2014-11-21 01:00:24Z

1

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)

answered Nov 21, 2014 at 1:00

shg

3332 silver badges7 bronze badges

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

Collectives™ on Stack Overflow

Calling a Double Array Function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related