Optional variables in subroutines

  • Thread starter Thread starter robert d via AccessMonster.com
  • Start date Start date
R

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.
 
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!)
 
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.
 
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 = "")
 
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
 
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!)



Dale Fye said:
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
Click to expand...
 
Yeah, got in too big of a hurry and missed that.

Douglas J. Steele said:
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)
Click to expand...
 
Back
Top