By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

Optional arguaments and IsMissing function

P: n/a

Hi All,

Im trying to use thie combination but have not had success. Below is the
function
It tried the following

myriskLevel(2,2)
myrisklevel(0,0,2)

and the ismissing(Three) alwasy returns false.?

Am is missing something here?

Thanks,

----------------------------------------------------------------------------
----------------
Public Function myFunctionl(One As String, Two As String, Optional Three As
String) As String
Dim RiskNO As Integer
If IsMissing(Three) Then
RiskNO = Val(One) * Val(Two)
else
RiskNO = Val(three)
End If
do something here
End function
----------------------------------------------------------------------------
--------------------
Gerry Abbott
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Aug 07 2004, 08:26 am, "Gerry Abbott" <pl****@ask.ie> wrote in
news:DT******************@news.indigo.ie:

Hi All,

Im trying to use thie combination but have not had success. Below is
the function
It tried the following

myriskLevel(2,2)
myrisklevel(0,0,2)

and the ismissing(Three) alwasy returns false.?

Am is missing something here?

Thanks,

-----------------------------------------------------------------------
----- ----------------
Public Function myFunctionl(One As String, Two As String, Optional
Three As String) As String

<snip>

IsMissing() function only works for optional arguments declared as Variant.
So you may want to change your function signature as

Public Function myFunctionl(One As String, Two As String, Optional
Three As Variant) As String

If the argument is declared as a string, test for its equality to
vbNullString instead of using IsMissing().

--
remove a 9 to reply by email
Nov 13 '05 #2

P: n/a
Thanks Dimitri,
vbNullString worked fine
I had tried Isnull(arguament name) but that did not work

Can someone explain whats the difference betweeen the Isnull function and
the vbnullstring test. ?

Regards,
Gerry

"Dimitri Furman" <df*****@cloud99.net> wrote in message
news:Xn****************************@127.0.0.1...
On Aug 07 2004, 08:26 am, "Gerry Abbott" <pl****@ask.ie> wrote in
news:DT******************@news.indigo.ie:

Hi All,

Im trying to use thie combination but have not had success. Below is
the function
It tried the following

myriskLevel(2,2)
myrisklevel(0,0,2)

and the ismissing(Three) alwasy returns false.?

Am is missing something here?

Thanks,

-----------------------------------------------------------------------
----- ----------------
Public Function myFunctionl(One As String, Two As String, Optional
Three As String) As String <snip>

IsMissing() function only works for optional arguments declared as

Variant. So you may want to change your function signature as

Public Function myFunctionl(One As String, Two As String, Optional
Three As Variant) As String

If the argument is declared as a string, test for its equality to
vbNullString instead of using IsMissing().

--
remove a 9 to reply by email

Nov 13 '05 #3

P: n/a
The number of types of nothing in VB/VBA can be really confusing, and is tied
up with the various data types.

Null is equivalent to a Null value in a database field. Null is not equal to
anything else including Null, and most operations performed on Null with
another value result in Null - the exception being the explicit string
concatenation operator & which treats Null the same as a blank string. Null
also acts like its own data type with only one possible value, Null.

1 + Null gives Null
"A" + Null gives Null
"A" & Null gives "A"
(Null = Null) gives Null
(0 = Null) gives Null
If 0 = Null Then ... raises invalid use of Null error
If IsNull(Null) ... executes the code in the If block
IsNull(Empty) gives False
Len(Null) gives Null
Empty is the default value for a Variant variable, and is interchageable for
various other default values. Used in arithmetic, it acts as a zero. Used in
string operations, it acts as a blank string, etc. Like Null, Empty also acts
like a data type with one possible value, Empty. This is compatable with the
behavior of BASIC for an uninitialized value.

1 + Empty gives 1
"A" + Empty gives "A"
"A" & Empty gives "A"
(Empty = Empty) gives True
(0 = Empty) gives True
("" = Empty) gives True
If 0 = Empty Then ... executes the code in the If block
IsEmpty(Empty) gives True
IsEmpty(Null) gives False
Len(Empty) gives 0
vbNullString is actually a string that points to address zero, and it was
created for the purpose of passing a null string pointer to a DLL call. Folks
have discovered, though, that VB/VBA treats this as equivalent to the empty
string "", but processes it faster in some cases.

(vbNullString = "") gives True
IsNull(vbNullString) gives False
IsEmpty(vbNullString) gives False
Len(vbNullString) gives 0

One difference between vbNullString and Null is that vbNullString (or "") is a
string type. String variables can only hold string data, so a string variable
can hold these values, but it cannot hold Null or Empty because these are not
string type data.
Missing is a special value/type a Variant can have indicating that an optional
parameter was not supplied. There's very little you can to with a Missing
other than check if it is missing using IsMissing without generating an error,
however, you can assign it to another variant, and you can pass it to an
optional argument of another procedure. Since strings can contain only string
type data, they cannot have a value of Missing.
Nothing is an object reference that's not referencing any object. Since a
Variant can contain any object reference, it can contain Nothing, meaning it
has an object reference, but that reference is not to any existing object.
Any object variable (such as dbs As DAO.Database) can contain either a
reference to an object of its specific type or Nothing, meaning it has not
been initialized or it has been cleared by setting it back to Nothing.

Object references are not values, and if you try to treat them as values, VB
will try to find a default value property of the object to use as its value.
Since Nothing does not point to an object at all, it can never be treated as a
Value, but you can do pretty much anything you want with it as a refrence
including set some other Variant or object variable to its reference (which is
Nothing). The "Is" operator is used to compare 2 references to each other,
and using "Is" to compare 2 Nothing references gives True.

(Nothing Is Nothing) gives True
(Nothing = 0) raises "Invalid use of object) error
(New VBA.Collection Is Nothing) gives False
On Sat, 7 Aug 2004 19:37:15 +0100, "Gerry Abbott" <pl****@ask.ie> wrote:
Thanks Dimitri,
vbNullString worked fine
I had tried Isnull(arguament name) but that did not work

Can someone explain whats the difference betweeen the Isnull function and
the vbnullstring test. ?

Regards,
Gerry

"Dimitri Furman" <df*****@cloud99.net> wrote in message
news:Xn****************************@127.0.0.1.. .
On Aug 07 2004, 08:26 am, "Gerry Abbott" <pl****@ask.ie> wrote in
news:DT******************@news.indigo.ie:
>
> Hi All,
>
> Im trying to use thie combination but have not had success. Below is
> the function
> It tried the following
>
> myriskLevel(2,2)
> myrisklevel(0,0,2)
>
> and the ismissing(Three) alwasy returns false.?
>
> Am is missing something here?
>
> Thanks,
>
>
>
> -----------------------------------------------------------------------
> ----- ----------------
> Public Function myFunctionl(One As String, Two As String, Optional
> Three As String) As String

<snip>

IsMissing() function only works for optional arguments declared as

Variant.
So you may want to change your function signature as

Public Function myFunctionl(One As String, Two As String, Optional
Three As Variant) As String

If the argument is declared as a string, test for its equality to
vbNullString instead of using IsMissing().

--
remove a 9 to reply by email


Nov 13 '05 #4

P: n/a
Many Thanks Steve,

That is an excellent description of several issues which were confusing to
me. I have printed this and pinned it to my notice board of reference
information.
Why us microsoft help not like this!
Regards,

Gerry
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.