Fastest VBA MIN code

[Silverjman]

Hello,

I have a grid that is 4814 rows by 40 columns and need to do a simple NON Worksheetfunction MIN function.

From my googling this seems to be the best solution but I can't get the code to work.

max min VBA

How do I get maximum of two numbers in VBA lets say I have var1 = 2 var2 = 30 I want to compare the 2 and get the maximum so maybe something like this. (but i know this doesnt work) result = max(var1, var2)

www.mrexcel.com

Function Min(ParamArray values() As Variant) As Variant
   Dim minValue, Value As Variant
   minValue = values(0)
   For Each Value In values
       If Value < minValue Then minValue = Value
   Next
   Min = minValue
End Function

Any idea whay I'm getting a Type 13 mismatch with this?
If I move the "minValue = Value" down to the next line I get a "Next without For" error?
Any help much appreciated

 

[Fluff]

Do you have any text values or errors in the range of values you're using?

 

[Silverjman]

Just highlighted the ranges and did Go To Special on both Text and Errors and none of either were found. Thanks for the suggestion, may help in the future.

 

[Fluff]

How are you calling the function?

 

[Silverjman]

Else: Max_Recovery_result = Min(arr

  If arrResolution__SubPortfolio(i, 1) = "REO" Then
             Max_Recovery_result = arrResolution__Total_Owned_Lien_Value_Grid(i, j)
             Else: Max_Recovery_result = Min(arrResolution__Total_Owned_Lien_Value_Grid(i, j), arrResolution__Loan_Balance)
             End If

 

[Fluff]

Is "arrResolution__Loan_Balance" an array of values?

 

[Silverjman]

Ugg THANK YOU. I feel silly now.
Min(arrResolution__Total_Owned_Lien_Value_Grid(i, j), arrResolution__Loan_Balance(i, j))

Definitive novice, that's a good lesson that the function or line the debug highlights very well may not be the issue!
That was a tremendous help! Thanks again

 

[Fluff]

You're welcome & thanks for the feedback.
Although I don't see the need for the function, when you are only sending 2 values

 

[Silverjman]

Ahh, yeah maybe I was a bit blinded by just trying to convert a formula that used MIN to VBA.

Are you saying you would use something like the below instead of a MIN?

If Lien > Loan Then
Lien
Else Loan

 

[Fluff]

Something like that yes.