Incorporating optional function argument

[BruceM]

After much searching online I have found some information in VBA Help about
using optional arguments in a user-defined function, but I cannot find
anything to find why adding an optional argument would make the function
stop working when I call it from the event property. Here are some details,
in case my terminology is unclear.

The function builds a filter string that is used to filter the recordset.
It works as it should. However, in one instance I need a modified approach
when making a selection from one of the combo boxes that is used to select
the elements of the filter. Here is the function title (or whatever it's
called):

Public Function SelFilter(lngID As Long) As String

On the Property Sheet for a combo box I have the following for the After
Update event:
=SelFilter([cboFind])

In other cases the function is called in code (because the event has to do
several things).

My idea was to add an optional boolean to the function:
Public Function SelFilter(lngID As Long, Optional blnSel as Boolean) As
String
or
Public Function SelFilter(lngID As Long, Optional blnSel as Boolean = False)
As String

From what I can tell, this works only if I am calling the function from
code. That is, in an event procedure I can have:

SelFilter(Me.cboFind, False)
or
SelFilter(Me.cboFind)

Either one works.

However, on the property sheet, this doesn't even call the function:
=SelFilter([cboFind])
I put a break point at the function title, but the code never ran. However,
when I did this:
=SelFilter([cboFind], False)
The function worked.

What I am getting from this is that optional arguments cannot be left blank
when calling the function from the property sheet. In other words, all
arguments, even optional ones, are required when calling the function from
the property sheet. If this is documented I cannot find it.

I am using Access 2003.

 

[JString]

That's pretty strange that you were able to get it to work at all. I tried
reproducing your scenario with this code:

Public Function test(strMsg As String, Optional blnVal As Boolean = False)
test = strMsg & blnVal
End Function

I then assigned the OnClick property of a textbox to :

=test("this is a test ", False)

And nothing happened. However, other variations gave me an error stating
that a macro by this name couldn't be found.

The VBA help file for the OnClick property states that it can be used to
programmatically change the behavior of a control object from an event
procedure to a macro or vice versa. So perhaps it's a syntax issue, if I'm
understanding you correctly.

 

[BruceM]

I don't see that the code you used calls for anything to happen that the
user can see or otherwise verify. If, for instance, you had MsgBox =Test or
Debug.Print Test there would be something for the user to observe.

Adding a message box to your code, and removing blnVal from the value
returned by the function:
Public Function test(strMsg As String, Optional blnVal As Boolean = False)
test = strMsg
MsgBox test
End Function

If I put this into the event property (After Update for a combo box),
nothing happens:
=test("this is a test ")

The function does not even start. A break point at the Public Function line
does not result in the code breaking. However, this works:
=test("this is a test ", False)

In VBA code, a function call works whether or not there is an optional
argument. Both of these work if I put them into After Update code:

test("this is a test ")
test("this is a test ", False)

Nothing changes if I add something for blnVal to do:
Public Function test(strMsg As String, Optional blnVal As Boolean = False)
test = strMsg
MsgBox test
Me.SomeControl.Visible = blnVal
End Function

If it is syntax, the syntax for a function call from an event property is
different from the syntax for a function call in code, but in a way that
seems not to be documented.

That's pretty strange that you were able to get it to work at all. I
tried
reproducing your scenario with this code:

Public Function test(strMsg As String, Optional blnVal As Boolean = False)
test = strMsg & blnVal
End Function

I then assigned the OnClick property of a textbox to :

=test("this is a test ", False)

And nothing happened. However, other variations gave me an error stating
that a macro by this name couldn't be found.

The VBA help file for the OnClick property states that it can be used to
programmatically change the behavior of a control object from an event
procedure to a macro or vice versa. So perhaps it's a syntax issue, if
I'm
understanding you correctly.

After much searching online I have found some information in VBA Help
about
using optional arguments in a user-defined function, but I cannot find
anything to find why adding an optional argument would make the function
stop working when I call it from the event property. Here are some
details,
in case my terminology is unclear.

The function builds a filter string that is used to filter the recordset.
It works as it should. However, in one instance I need a modified
approach
when making a selection from one of the combo boxes that is used to
select
the elements of the filter. Here is the function title (or whatever it's
called):

Public Function SelFilter(lngID As Long) As String

On the Property Sheet for a combo box I have the following for the After
Update event:
=SelFilter([cboFind])

In other cases the function is called in code (because the event has to
do
several things).

My idea was to add an optional boolean to the function:
Public Function SelFilter(lngID As Long, Optional blnSel as Boolean) As
String
or
Public Function SelFilter(lngID As Long, Optional blnSel as Boolean =
False)
As String

From what I can tell, this works only if I am calling the function from
code. That is, in an event procedure I can have:

SelFilter(Me.cboFind, False)
or
SelFilter(Me.cboFind)

Either one works.

However, on the property sheet, this doesn't even call the function:
=SelFilter([cboFind])
I put a break point at the function title, but the code never ran.
However,
when I did this:
=SelFilter([cboFind], False)
The function worked.

What I am getting from this is that optional arguments cannot be left
blank
when calling the function from the property sheet. In other words, all
arguments, even optional ones, are required when calling the function
from
the property sheet. If this is documented I cannot find it.

I am using Access 2003.

 

[JString]

I can't answer your question as to why this occurs or where it might be
documented, but I may have found a solution for your problem.

I found that if I have the function located in the form's module, all
parameters are required for it to work correctly, including optional ones.
However, after moving the function to a new module, it worked even if the
optional parameter was left blank in the event property.

I don't see that the code you used calls for anything to happen that the
user can see or otherwise verify. If, for instance, you had MsgBox =Test or
Debug.Print Test there would be something for the user to observe.

Adding a message box to your code, and removing blnVal from the value
returned by the function:
Public Function test(strMsg As String, Optional blnVal As Boolean = False)
test = strMsg
MsgBox test
End Function

If I put this into the event property (After Update for a combo box),
nothing happens:
=test("this is a test ")

The function does not even start. A break point at the Public Function line
does not result in the code breaking. However, this works:
=test("this is a test ", False)

In VBA code, a function call works whether or not there is an optional
argument. Both of these work if I put them into After Update code:

test("this is a test ")
test("this is a test ", False)

Nothing changes if I add something for blnVal to do:
Public Function test(strMsg As String, Optional blnVal As Boolean = False)
test = strMsg
MsgBox test
Me.SomeControl.Visible = blnVal
End Function

If it is syntax, the syntax for a function call from an event property is
different from the syntax for a function call in code, but in a way that
seems not to be documented.

That's pretty strange that you were able to get it to work at all. I
tried
reproducing your scenario with this code:

Public Function test(strMsg As String, Optional blnVal As Boolean = False)
test = strMsg & blnVal
End Function

I then assigned the OnClick property of a textbox to :

=test("this is a test ", False)

And nothing happened. However, other variations gave me an error stating
that a macro by this name couldn't be found.

The VBA help file for the OnClick property states that it can be used to
programmatically change the behavior of a control object from an event
procedure to a macro or vice versa. So perhaps it's a syntax issue, if
I'm
understanding you correctly.

After much searching online I have found some information in VBA Help
about
using optional arguments in a user-defined function, but I cannot find
anything to find why adding an optional argument would make the function
stop working when I call it from the event property. Here are some
details,
in case my terminology is unclear.

The function builds a filter string that is used to filter the recordset.
It works as it should. However, in one instance I need a modified
approach
when making a selection from one of the combo boxes that is used to
select
the elements of the filter. Here is the function title (or whatever it's
called):

Public Function SelFilter(lngID As Long) As String

On the Property Sheet for a combo box I have the following for the After
Update event:
=SelFilter([cboFind])

In other cases the function is called in code (because the event has to
do
several things).

My idea was to add an optional boolean to the function:
Public Function SelFilter(lngID As Long, Optional blnSel as Boolean) As
String
or
Public Function SelFilter(lngID As Long, Optional blnSel as Boolean =
False)
As String

From what I can tell, this works only if I am calling the function from
code. That is, in an event procedure I can have:

SelFilter(Me.cboFind, False)
or
SelFilter(Me.cboFind)

Either one works.

However, on the property sheet, this doesn't even call the function:
=SelFilter([cboFind])
I put a break point at the function title, but the code never ran.
However,
when I did this:
=SelFilter([cboFind], False)
The function worked.

What I am getting from this is that optional arguments cannot be left
blank
when calling the function from the property sheet. In other words, all
arguments, even optional ones, are required when calling the function
from
the property sheet. If this is documented I cannot find it.

I am using Access 2003.

 

[BruceM]

Thanks for looking into it. It's something to try in the future if needed,
I suppose, but I wish I had a better understanding of why an "optional"
argument is requireed in some cases. For now I have added the optional
argument to the event property function calls.

I can't answer your question as to why this occurs or where it might be
documented, but I may have found a solution for your problem.

I found that if I have the function located in the form's module, all
parameters are required for it to work correctly, including optional ones.
However, after moving the function to a new module, it worked even if the
optional parameter was left blank in the event property.

I don't see that the code you used calls for anything to happen that the
user can see or otherwise verify. If, for instance, you had MsgBox =Test
or
Debug.Print Test there would be something for the user to observe.

Adding a message box to your code, and removing blnVal from the value
returned by the function:
Public Function test(strMsg As String, Optional blnVal As Boolean =
False)
test = strMsg
MsgBox test
End Function

If I put this into the event property (After Update for a combo box),
nothing happens:
=test("this is a test ")

The function does not even start. A break point at the Public Function
line
does not result in the code breaking. However, this works:
=test("this is a test ", False)

In VBA code, a function call works whether or not there is an optional
argument. Both of these work if I put them into After Update code:

test("this is a test ")
test("this is a test ", False)

Nothing changes if I add something for blnVal to do:
Public Function test(strMsg As String, Optional blnVal As Boolean =
False)
test = strMsg
MsgBox test
Me.SomeControl.Visible = blnVal
End Function

If it is syntax, the syntax for a function call from an event property is
different from the syntax for a function call in code, but in a way that
seems not to be documented.

That's pretty strange that you were able to get it to work at all. I
tried
reproducing your scenario with this code:

Public Function test(strMsg As String, Optional blnVal As Boolean =
False)
test = strMsg & blnVal
End Function

I then assigned the OnClick property of a textbox to :

=test("this is a test ", False)

And nothing happened. However, other variations gave me an error
stating
that a macro by this name couldn't be found.

The VBA help file for the OnClick property states that it can be used
to
programmatically change the behavior of a control object from an event
procedure to a macro or vice versa. So perhaps it's a syntax issue, if
I'm
understanding you correctly.



:

After much searching online I have found some information in VBA Help
about
using optional arguments in a user-defined function, but I cannot find
anything to find why adding an optional argument would make the
function
stop working when I call it from the event property. Here are some
details,
in case my terminology is unclear.

The function builds a filter string that is used to filter the
recordset.
It works as it should. However, in one instance I need a modified
approach
when making a selection from one of the combo boxes that is used to
select
the elements of the filter. Here is the function title (or whatever
it's
called):

Public Function SelFilter(lngID As Long) As String

On the Property Sheet for a combo box I have the following for the
After
Update event:
=SelFilter([cboFind])

In other cases the function is called in code (because the event has
to
do
several things).

My idea was to add an optional boolean to the function:
Public Function SelFilter(lngID As Long, Optional blnSel as Boolean)
As
String
or
Public Function SelFilter(lngID As Long, Optional blnSel as Boolean =
False)
As String

From what I can tell, this works only if I am calling the function
from
code. That is, in an event procedure I can have:

SelFilter(Me.cboFind, False)
or
SelFilter(Me.cboFind)

Either one works.

However, on the property sheet, this doesn't even call the function:
=SelFilter([cboFind])
I put a break point at the function title, but the code never ran.
However,
when I did this:
=SelFilter([cboFind], False)
The function worked.

What I am getting from this is that optional arguments cannot be left
blank
when calling the function from the property sheet. In other words,
all
arguments, even optional ones, are required when calling the function
from
the property sheet. If this is documented I cannot find it.

I am using Access 2003.