Determine how many (optional) arguments that are actually passed to the VBA function?

Question

I would like to define a function in Visual Basic that calculates the income tax in this bracket. The contribution must be income, marginal tax rate, the lower limit of the brackets and - optionally - the upper limit of the bracket. (There is no upper bound for the upper bracket).

Here is how I did it. First, I define the ramp function as follows:

Public Function ramp(x) ramp = (x + Abs(x)) / 2 End Function 

which is basically the same as IF (x <0,0, x). Then I define the function (in Dutch) for tax as

 Public Function schijfbelasting(inkomen, ondergrens, bovengrens, tarief) schijfbelasting = ramp(tarief * (inkomen - ondergrens)) - ramp(tarief * (inkomen - bovengrens)) End Function 

Here, "inkomen" = income, "ondergrens" = lower bracket, "bovengrens" = upper bracket, "tarief" = tax margin, and "schijfbelasting" = tax in that bracket.

All this works fine, except that I would like to make "bovengrens" (the top border of the bracket) optional, using

 Optional bovengrens 

In Matlab, I used the "nargin" function (number of arguments) to do something like the following:

 Public Function schijfbelasting(inkomen, ondergrens, Optional bovengrens, tarief) If nargin==4 schijfbelasting = ramp(tarief * (inkomen - ondergrens)) - ramp(tarief * (inkomen - bovengrens)) Elseif nargin==3 schijfbelasting = ramp(tarief*(inkomen-ondergrens)) End If End Function 

However, I do not know about a function similar to "nargin" in Visual Basic. It could also be something like "if the argument" bovengrens "is defined." Does anyone know how to approach this problem? Thanks in advance.

PS I know that I can make the code "work" by filling in a very large number for the border "border" in the upper bracket, but I do not consider this elegant coding.

Answer 1

You can use the VBA IsMissing function to check additional parameters. Here is an example:

 Public Sub OptionalArg(arg1, Optional arg2) Debug.Print arg1; IIf(IsMissing(arg2), "missing", arg2) End Sub 

Check it out as follows:

 Sub Test() OptionalArg 1 OptionalArg 1, 2 End Sub 

Answer 2

Just check the arguments with the IsMissing function:

 If IsMissing(bovengrens) Then ...