In a previous post (long time ago) we talked about creating a simple user defined function (UDF) called Half:
Half = value / 2
In this post I would like to talk about UDF that accepts optional arguments.
Here is a simple example for that case. Requirement to build a function named “Div”:
- The function accepts as one argument named “Value”.
- The function also accepts a second argument named “divideBy”.
- As you can guess, “Div” calculates a division of “Value” by “divideBy”.
- But “divideBy” is optional. If “divideBy” is not present then the function assumes a constant value of 1. I.e. Divide “Value” by 1.
To meet these requirements we need two things:
The first thing we will need is the ability to define an argument as optional: VBA provides a simple way to declare arguments as optional by simply prefixing the argument with the text “Optional”.
The second thing we will need is the ability to know if a given argument is present or not: For that VBA provides another very simple function named “IsMissing”. The function accepts an argument and returns a boolean. Value of “True” means that the argument is not present.
With that we have everything we need to write the function Div:
Very simple indeed! Give it a try!
Also read some of the additional information at: