Does VBA allow null parameters?

[djmc]

Does VBA allow null parameters? Because when I passed in a null parameter it says "invalid usage of null" right at the function call (it did not even go into the function body code). However when I called it using vbNullString it works but if i pass in lets say a textbox variable name which is blank, it does not work. Can anyone tell me why? The only way I can resolve this is to check the textbox and if its null i pass in vbNullstring instead of the textbox variable. It seems kind of redundant, so i was wondering how to fix this is a better way. TIA

 

[AccessAce]

Can you run the code if the parameter is declared as Optional?

 

[Norris68]

Declare the parameter as a Variant. This will allow you to pass Nulls in. Unfortunately there is no mid-ground to enable you have a strongly-typed parameter that allows Nulls.

Convert Null values before you pass in the parameter and you can keep your strongly-typed parameters e.g. by using the NZ() function. If not you will have to use weakly-typed Variant parameters and run the risk of passing strings where you want numbers, etc. If you use Variant parameters, the first thing you should do in your sub/function is to check the parameter is either Null or of the base-type you want and raise an error if not.

 

[TonyJollans]

Hi djmc,

It all depends on how you type your parameters and whether you pass a Control or its contents.

If you have a textbox on a form and your code is something like this ..

Call mySub(myTextBox)

Then if MySub is defined like this ..

Sub mySub(myTextBox as String)

.. you will get the error you describe because you are trying to pass the String which is the content of the Textbox and it is null.

If, on the other hand, you define your sub like this ..

Sub mySub(myTextBox as Textbox)

.. then the textbox control is passed and you won't get the error. Inside your sub you can work with the contents of the textbox (or any other properties of it you want).

Enjoy,
Tony