Excel User Defined Function With Optional Parameters

2015 VBA user defined function with optional parameters

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:

2015 VBA user defined function with optional parameters

Very simple indeed! Give it a try!

Also read some of the additional information at:

IsMissing Function

Advertisements

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

2 thoughts on “Excel User Defined Function With Optional Parameters”

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s