optional arguments type

[b1ll]

Hi - 'VBA Made Easy' says that optional arguments for both Functions and Subroutines should not be given a type as they are Variants. eg

Function Test (optional Counter) and not Function Test (optional Counter as integer).

I am using Excel 97, not specifying type and am getting 'type mismatch' errors.
What are the rules please.
TKIA

[Richard Schollar]

Hi

You get the Type Mismatch error because if not specified the argument returns 'Missing'. This only happens with Variant arguments. Your book is incorrect - you can specify the type of arguments. One reason why you may not want to do so is to specifically test whether the function has been called with that argument (in which case the argument must be a variant allowing you to use the IsMissing VBA function on the argument which returns True if argument omitted or False if not).

[shg]

optional arguments for both Functions and Subroutines should not be given a type as they are Variants

That's an amazingly misinformed statement for a book.

There are a few reasons to use Variants. One is if the data may be of varying type (string, number, boolean) and your code needs to know which. Another is if you specifically need to know whether the argument was passed using IsMissing().

Otherwise, for performance and practice, you should assuredly specify the data type. You can specify defaults:

Please Login or Register  to view this content.

[b1ll]

Hi - thanks for that. I will go through and put the types in using the shg method and also remove all my ismissing statements.