In a previous post (long time ago) we talked about creating a simple user defined function (UDF) called Half:
Function Half(value)
Half = value / 2
End Function
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:
Dave 
I’m not sure if my question fits in here: cell A1 accepts temperature in deg C, B1 accepts temperature in deg F.
B1 shows the deg F temperature when deg C value is entered in cell A1.
A1 shows the deg C temperature when deg F value is entered in cell B1.
That is good for as long as the operation is in the same direction, but the formula being over-written by the input value (deg C or deg F), it won’t work if it is attempted in the other direction.
I want to keep it working either way around without re-entering the formula after every direction change, i.e. the conversion must be kept working.
Hope you can help
Regards,
Klaus
It is an interesting challenge. As far as I can tell, using simple Excel expressions one can not create such calculation as it creates bi-directional dependency…however, not all hope is lost, by writing some VBA macro and event handler this is possible. Please read the following post http://wp.me/pW1Iy-p9
Note that you can only use the IsMissing() function on arguments with a type of Variant. With primitive types like Integer or Double, you have to either check for a value of 0 (If divideBy = 0 Then…) or include the default value in the function’s declaration like this: “Optional divideBy As Integer = 1”.