excel-vba User Defined Functions (UDFs)
Syntax
-
Function functionName(argumentVariable As dataType, argumentVariable2 As dataType, Optional argumentVariable3 As dataType) As functionReturnDataType
Basic declaration of a function. Every function needs a name, but it does not have to take any arguments. It may take 0 arguments, or it may take a given number of arguments. You may also declare an argument as optional (meaning it does not matter if you supply it when calling the function). It is best practice to supply the variable type for each argument, and likewise, to return what data type the function itself is going to return. -
functionName = theVariableOrValueBeingReturned
If you're coming from other programming languages, you may be used to theReturnkeyword. This is not used in VBA - instead, we use the function name. You can set it to the contents of a variable or to some directly supplied value. Note that if you did set a data type for the function's return, the variable or data you are supplying this time must be of that data type. -
End Function
Mandatory. Signifies the end of theFunctioncodeblock and must thusly be at the end. The VBE usually supplies this automatically when you create a new function.
Remarks
A User Defined Function (aka UDF) refers to a task specific function that has been created by the user. It can be called as a worksheet function (ex: =SUM(...)) or used to return a value to a running process in a Sub procedure. A UDF returns a value, typically from information passed into it as one or more parameters.
It can be created by :
- using VBA .
- using Excel C API - By creating an XLL that exports compiled functions to Excel.
- using the COM interface.
