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.
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
You cannot use an Excel worksheet function in code if VBA provides an equivalent function.

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

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
Again this can be done with or without using the Call keyword.

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

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