Optional variables in subroutines

[robert d via AccessMonster.com]

I have a subroutine as follows:

MySub(Var1 As String, Var2 As String, Optional Var3 As String)

The subroutine builds an SQL statement based on the variables passed to it. I
want to make sure that I'm using good practices in dealing with the optional
variable.

So if no Var3 is to be passed, then do not pass a value to Var3.

Call MySub(strVar1, strVar2)

In this case Var3, in the subroutine, will equal "" (no spaces). So in code
that builds the SQL statement, I would have the following:

If Var3 <> "" Then
strWHERE = strWHERE & "Additional Where clause"
End If

I just want to make sure that there won't be any unintended or unforseen
consequences of the way I'm handling this.

Thanks.

 

[Douglas J. Steele]

What you've got is pretty standard for dealing with optional parameters.

If you're a pants-and-suspenders type, you could define the default value:

MySub(Var1 As String, Var2 As String, Optional Var3 As String =
vbNullString)

or

MySub(Var1 As String, Var2 As String, Optional Var3 As String = "")

As well,

If Len(Var3) = 0 Then

is actually a little more efficient (although I doubt you'll be able to
notice any difference!)

 

[Rob Oldfield]

That looks fine to me. Having said that, I generally provide default
values -

Sub MySub(Var1 As String, Var2 As String, Optional Var3 As
String="NotProvided")

but I don't think it really matters in this case.

 

[John Nurick]

Hi Robert,

That seems fine, though I prefer to explicitly declare the default
values of optional arguments, e.g.

Sub MySub(Var1 As String, Var2 As String, _
Optional Var3 As String = "")

 

[Dale Fye]

Robert,

I also like to set default values for my optional variables, in this case I
think I would use NULL instead of using a null string ("").

MySub(Var1 As String, Var2 As String, Optional Var3 As String = Null)

With this technique, you don't have to test the value at all, just write
your SQL as below. This may take a while to understand the first couple of
times you look at it, but it becomes second nature after a while.

strWhere = strWHERE & (" AND " + var3)

By using the + symbol to concatenate the " AND " to var3, Access will
interpret this as a Null if var3 is Null. When you concatenate strWhere to
NULL using the & symbol, it returns the value that is stored in strWhere.

Dale
Happy New Year

 

[Douglas J. Steele]

If you want to be able to use Null as the default value, then you can't
declare the parameter as a string: strings can't be null (only variants can)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Robert,

I also like to set default values for my optional variables, in this case
I think I would use NULL instead of using a null string ("").

MySub(Var1 As String, Var2 As String, Optional Var3 As String = Null)

With this technique, you don't have to test the value at all, just write
your SQL as below. This may take a while to understand the first couple
of times you look at it, but it becomes second nature after a while.

strWhere = strWHERE & (" AND " + var3)

By using the + symbol to concatenate the " AND " to var3, Access will
interpret this as a Null if var3 is Null. When you concatenate strWhere
to NULL using the & symbol, it returns the value that is stored in
strWhere.

Dale
Happy New Year

 

[Dale Fye]

Yeah, got in too big of a hurry and missed that.

If you want to be able to use Null as the default value, then you can't
declare the parameter as a string: strings can't be null (only variants
can)