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
Function_One = 3.2
'exit the function before doing this
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
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.
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.
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext