excel-vba User Defined Functions (UDFs)

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Syntax

  1. 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.

  2. functionName = theVariableOrValueBeingReturned
    If you're coming from other programming languages, you may be used to the Return keyword. 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.

  3. End Function
    Mandatory. Signifies the end of the Function codeblock 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 :

  1. using VBA .
  2. using Excel C API - By creating an XLL that exports compiled functions to Excel.
  3. using the COM interface.


Got any excel-vba Question?