Maximum function

[irki]

Hello all,
I'm new in VBA. I'm trying to code a maximum function but despite all my effort and reading I can figure out why it's no working.
The function is suppose to work with array as input or a any selected range from the spreadsheet. Simply I trying to reproduce the build in Exel "MAX" function. this is my code

Function Maximum(ParamArray inputs() As Variant)
Dim i, j, k As Integer
Dim rng As Range
If Not IsMissing(inputs()) Then
Maximum = inputs(0)
For i = 1 To UBound(inputs())
If inputs(i) > Maximum Then Maximum = inputs(i)
Next i
ElseIf Not IsEmpty(Selection) Then
Set rng = Selection.Value
Maximum = rng.Cells(1, 1)
For j = 1 To rng.Rows.Count
For k = 1 To rng.Columns.Count
If rng.Cells(j, k) > Maximum Then Maximum = rng.Cells(j, k)
Next k
Next j
End If
End Function

Thank for your helps

 

[tusharm]

How does it not work?

Also, it is a very bad idea to work with the 'selection' in a function. Even if Excel doesn't stop you from using the object, the results will be unpredictable.

Just keep in mind that the Excel MAX function does not work with the current selection. You have to provide it with a range / array / cells / values.

 

[Ruddles]

Are you trying to emulate the MAX function as a personal exercise or do you need it for a specific purpose?

If the latter, are you aware that you can use the worksheet function MAX within your VBA code?

MsgBox Application.WorksheetFunction.Max(Range("A1:X99"))

 

[irki]

Guy I need to emulate that function as part of an exercice. I'm not suppose to ue the build in function MAX. @tursharm, How do you convert a selected range on a spreadsheet to a range that can work with the function. actually I manage to have somthing that worked with a string data type like "A2:A10" but what I want is to make it work with a simple and variant selection from the spreadsheet like A2:A10 or B1:B20 at user wish. That's why I have been trying to use the Selection object.

Thanks

 

[Ruddles]

You need to pass the range to the function as an argument. Paste this into a new general code module:-

Option Explicit
 
Public Function IrkiMax(argRange As Range) As Variant
 
  Dim oCell As Range
  
  For Each oCell In argRange
    If oCell.Value > IrkiMax Then IrkiMax = oCell.Value
  Next oCell
 
End Function

Now put some numbers in A1 to A20 of your worksheet and test it by typing =IrkiMax(A1:A20).

Any good?

 

[irki]

@Ruddles, That works just fine! Thanks.
But I don't understand how the code, if you can explain and I'll do some reading on the new notion you'll be mentioning it will really be great. The fact is that I need to generalized it such that it work for IrkiMax("A12:A18") and IrkiMax(1,2,3,4) as well which is not the case at the moment (only works for IrkiMax(A12:A18) where A12:A18 is any range selected on the spreadsheet). So I need to understand the concept you used.
Thanks for the function name

 

[irki]

For instance, how can you choose a Range (oCell) in a Range (argRange) in :
"For Each oCell In argRange"
Then what is the initial value of IrkiMax in the comparison:
"oCell.Value > IrkiMax"?
And what value represent "oCell.Value" if oCell is actually a whole range?
Though the name "oCell" seam to indicate that it is a cell, I don't understand that you declare it as Range!

 

[Ruddles]

For instance, how can you choose a Range (oCell) in a Range (argRange) in :
"For Each oCell In argRange"

VBA does it for you. You tell it to cycle through all the cells in argRange setting oCell to each cell in turn.

Then what is the initial value of IrkiMax in the comparison:
"oCell.Value > IrkiMax"?

Good point: it's zero, so the function won't work when you pass it only negative values. I'll fix that.

And what value represent "oCell.Value" if oCell is actually a whole range?
Though the name "oCell" seam to indicate that it is a cell, I don't understand that you declare it as Range!

A cell is a range with only one cell in it. There is no data type Cell - you have to use Range.

To allow you to pass a range or an array to the function, I shall use a Variant type and test it to see whether it's a Range or an array, then use the appropriate method for finding the value I want:-

Option Explicit
 
Public Function IrkiMax(arg As Variant) As Variant
 
  Dim rTest As Range
  Dim i As Integer
  Dim oCell As Range
 
  IrkiMax = -1E+308 ' set start point to lowest possible value
 
  On Error Resume Next
  Set rTest = arg
  On Error GoTo 0
  If Not rTest Is Nothing Then [COLOR=green]' it's a range, so use For Each[/COLOR]
    For Each oCell In arg
      If oCell.Value > IrkiMax Then IrkiMax = oCell.Value
    Next oCell
    Exit Function
  End If
 
  If IsArray(arg) Then [COLOR=green]' it's an array, so loop from LBound to UBound[/COLOR]
    For i = LBound(arg) To UBound(arg)
      If arg(i) > IrkiMax Then IrkiMax = arg(i)
    Next i
  End If
 
End Function

I you want to pass a range, code =IrkiMax(F9:J50). If you want to pass a list of numbers, pass them as an array: =IrkiMax({-11,-3,-99,-25}).

 

[tusharm]

Check out the ParamArray argument type. It's a very powerful feature that lets one define any number of arguments, with each possibly a different data type.

@Ruddles, That works just fine! Thanks.
But I don't understand how the code, if you can explain and I'll do some reading on the new notion you'll be mentioning it will really be great. The fact is that I need to generalized it such that it work for IrkiMax("A12:A18") and IrkiMax(1,2,3,4) as well which is not the case at the moment (only works for IrkiMax(A12:A18) where A12:A18 is any range selected on the spreadsheet). So I need to understand the concept you used.
Thanks for the function name

 

[irki]

Thank Ruddles,I really appreciate. The function works for selected cells in the spreadsheet and array pass as arguments. More with your explanation I understand what is going on.
Yet I already tried the "variant" argument while I was waiting for your reply and I now try to directly change your last IrkiMax to include an argument of the type "A12:A15" with is String type and those of the form (1,2,3,4,...) which work when I use "ParamArray arg()" as the function parameter.
But If arg is declare only as Variant
1) Is Variant type also take ParamArray (Doesn't seem like);
2)Is there any function to test for ParamArray something lke "IsParamArray"? or can I cast the parameter to ParamArray?
The same question goes for string such that when I type Debug.Print IrkiMax("A12:A15") the immediate window it gives me the max in that range. So
1) is the a "IsString" function or how can I test if an argument is of type String? I know there is a function to cast to String, but testing comes first!

Maybe you want to have a look again to my original post. It works for ParamArray but for selected cells it returns only the value in the first cell

Thanks