Functions always return a single value.
In order to return a value from a function we must assign the function's name to a return value.
A function is identical to a subroutine but also has the ability to return a value.
You can exit a function at any time using the 'Exit Function' statement.

Public Sub Function_One() As Double 
'do something
   Function_One = 3.2
   Exit Function
'exit the function before doing this
End Function

Never write functions that already exist as built-in functions. You will never be able to match the speed of a built-in function
When you create a function that you only want to use in a VBA procedure and not as a worksheet function you should declare it as Private
Calling it from another procedure
0) Not using the Call Keyword
1) Using the Call Keyword
2) Using Application.Run

Because functions return values they cannot be called directly from the (Tools > Macro > Macros) dialog box.
If you want to be able to call a function you will have to wrap it inside a subroutine.

You must declare the datatypes not only for all the parameters but also for the return type.
To list all the built-in VBA functions that are available use Application.VBA.
Each function that is declared at the same level of scope must have a unique name

If you do not define the return value of a Boolean function, it will return False.

Not using the Call Keyword

To use the value that is returned from a function just assign a value to the function name.

Dim iSum As Integer 
   iSum = AddTwoNumbers(20, 30)

Public Function AddTwoNumbers(ByVal iNumber1 As Integer, _ 
                              ByVal iNumber2 As Integer) As Integer
   AddTwoNumbers = iNumber1 + iNumber2
End Function

You should always explicitly declare the data type that is returned by your functions. Otherwise these will be interpreted as being Variants

To return a collection from a function you must use the set keyword "set fn = col"

Ignoring the returned value

There will be occasions when we want to call a function but we are not interested in the result.
In this case we can call the function in an identical way to calling a normal procedure

Dim iSum As Integer 
   Call AddTwoNumbers(20, 30)

You cannot use an Excel worksheet function in code if VBA provides an equivalent function.

Using Application.Run

For more information refer to the dedicated Application.Run page.


Always avoid keywords in your function and procedure names
You should try and avoid calling a function without assigning the return value to a variable.
You should always declare the datatypes of each parameter and also of the return type.
Any parameters passed to a function must be enclosed in parentheses.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext