VBA - Create User Funtion

TheNoocH

Well-known Member
Joined
Oct 6, 2004
Messages
3,482
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

Code:
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...
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
L

Legacy 98055

Guest
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="filter:progid: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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Perhaps, this, it will allow an array of numbers or a range as parameters.

Code:
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

Well-known Member
Joined
Oct 6, 2004
Messages
3,482
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...
 

Forum statistics

Threads
1,136,268
Messages
5,674,732
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top