VBA - Create User Funtion

[TheNoocH]

I'm trying to learn how to create user functions...did some before but can't find any of my examples...

what i'd like to create is the equivalent of MAX but without using Range in the function...

so essentially if i had 3 numbers i think it would be something like this

Function MAXX(num1, num2, num3) as long
maxx=0
if num1>num2 then maxx=num1
if num2>num3 then 
  maxx=num2
else 
  maxx=num3
end if
End Function

i think that's on the right track...but i'm thinking how can i set it up for any number of arguments? and i'm assuming looping through the numbers would be better than IF, THEN, ELSE...so any help on how to clean this up....thanks...

 

[Legacy 98055]

A most basic example. You will find it mandatory to use at least one or more decision statements...

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Sub</font> Example()
       <font color="#0000A0">Dim</font> MyValues
       MyValues = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
       MsgBox MAXX(MyValues)
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Function</font> MAXX(Values)
       <font color="#0000A0">Dim</font> x <font color="#0000A0">As</font> <font color="#0000A0">Double</font>
       <font color="#0000A0">For</font> x = LBound(Values) <font color="#0000A0">To</font> UBound(Values)
           <font color="#0000A0">If</font> Values(x) > MAXX <font color="#0000A0">Then</font> MAXX = Values(x)
       <font color="#0000A0">Next</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Function</font>
</FONT></td></tr></table><button onclick='document.all("1042006172544531").value=document.all("1042006172544531").value.replace(/<br \/>\s\s/g,"");document.all("1042006172544531").value=document.all("1042006172544531").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("1042006172544531").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="1042006172544531" wrap="virtual">
Sub Example()
Dim MyValues
MyValues = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
MsgBox MAXX(MyValues)
End Sub

Function MAXX(Values)
Dim x As Double
For x = LBound(Values) To UBound(Values)
If Values(x) > MAXX Then MAXX = Values(x)
Next
End Function</textarea>

 

[Scott Huish]

Perhaps, this, it will allow an array of numbers or a range as parameters.

Function Max2(r)
Dim i, j
For Each i In r
    If IsEmpty(j) Then j = i
    If IsNumeric(i) And i <> "" Then
        If i > j Then j = i
    End If
Next
Max2 = j
End Function

 

[TheNoocH]

guys thanks...as soon as i left work i realized i needed to get it into an array...just wasn't exactly sure how i had to do that....

both examples worked great...