Passing null to a function
 

Hi,
I am trying to create a function to deal with nulls, but I'm enconntering an
error (invalid use of null) by simply passing null to a function.

The following code creates the issue

Sub go()
Dim sreturn As String
sreturn = nulls("hello")
MsgBox "return is :" & sreturn

sreturn = nulls(Null)
MsgBox "return is :" & sreturn
End Sub

Function nulls(instring As String) As String
If IsNull(instring) Then
nulls = ""
Else
nulls = instring
End If
End Function

Does anybody have any ideas?

Many thanks in advance
Andy

Passing null to a function
 

Andy,

Null is a special subtype of Variant type variables. An empty
string is NOT a null. IsNull tests only Variant type variables.
To test for an empty string, use code like

If S = "" Then
' or
If Len(S) = 0 Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Andy" wrote in message
...
Hi,
I am trying to create a function to deal with nulls, but I'm
enconntering an
error (invalid use of null) by simply passing null to a
function.

The following code creates the issue

Sub go()
Dim sreturn As String
sreturn = nulls("hello")
MsgBox "return is :" & sreturn

sreturn = nulls(Null)
MsgBox "return is :" & sreturn
End Sub

Function nulls(instring As String) As String
If IsNull(instring) Then
nulls = ""
Else
nulls = instring
End If
End Function

Does anybody have any ideas?

Many thanks in advance
Andy

Passing null to a function
 

Many thanks Chip.
I obviously need to use variants.
Regards,
Andy

"Chip Pearson" wrote:

Andy,

Null is a special subtype of Variant type variables. An empty
string is NOT a null. IsNull tests only Variant type variables.
To test for an empty string, use code like

If S = "" Then
' or
If Len(S) = 0 Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Andy" wrote in message
...
Hi,
I am trying to create a function to deal with nulls, but I'm
enconntering an
error (invalid use of null) by simply passing null to a
function.

The following code creates the issue

Sub go()
Dim sreturn As String
sreturn = nulls("hello")
MsgBox "return is :" & sreturn

sreturn = nulls(Null)
MsgBox "return is :" & sreturn
End Sub

Function nulls(instring As String) As String
If IsNull(instring) Then
nulls = ""
Else
nulls = instring
End If
End Function

Does anybody have any ideas?

Many thanks in advance
Andy

Passing null to a function
 

I obviously need to use variants.

An uninitialized Variant is not Null, it is Empty, which you can
test with the IsEmpty function. E.g.,

Dim V As Variant
Debug.Print "IsEmpty: " & IsEmpty(V)
Debug.Print "IsNull: " & IsNull(V)

A Variant is Null only when you specifically assign Null to it,
and a Variant set to Null is NOT Empty.

Dim V As Variant
V = Null
Debug.Print "IsEmpty: " & IsEmpty(V)
Debug.Print "IsNull: " & IsNull(V)

I think you need to rethink your usage of Null as opposed to
Empty.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Andy" wrote in message
...
Many thanks Chip.
I obviously need to use variants.
Regards,
Andy

"Chip Pearson" wrote:

Andy,

Null is a special subtype of Variant type variables. An empty
string is NOT a null. IsNull tests only Variant type
variables.
To test for an empty string, use code like

If S = "" Then
' or
If Len(S) = 0 Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Andy" wrote in message
...
Hi,
I am trying to create a function to deal with nulls, but I'm
enconntering an
error (invalid use of null) by simply passing null to a
function.

The following code creates the issue

Sub go()
Dim sreturn As String
sreturn = nulls("hello")
MsgBox "return is :" & sreturn

sreturn = nulls(Null)
MsgBox "return is :" & sreturn
End Sub

Function nulls(instring As String) As String
If IsNull(instring) Then
nulls = ""
Else
nulls = instring
End If
End Function

Does anybody have any ideas?

Many thanks in advance
Andy

Passing null to a function
 

I should have added that a Variant containing an empty string is
neither Null nor Empty.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Chip Pearson" wrote in message
...
I obviously need to use variants.

An uninitialized Variant is not Null, it is Empty, which you
can test with the IsEmpty function. E.g.,

Dim V As Variant
Debug.Print "IsEmpty: " & IsEmpty(V)
Debug.Print "IsNull: " & IsNull(V)

A Variant is Null only when you specifically assign Null to it,
and a Variant set to Null is NOT Empty.

Dim V As Variant
V = Null
Debug.Print "IsEmpty: " & IsEmpty(V)
Debug.Print "IsNull: " & IsNull(V)

I think you need to rethink your usage of Null as opposed to
Empty.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Andy" wrote in message
...
Many thanks Chip.
I obviously need to use variants.
Regards,
Andy

"Chip Pearson" wrote:

Andy,

Null is a special subtype of Variant type variables. An empty
string is NOT a null. IsNull tests only Variant type
variables.
To test for an empty string, use code like

If S = "" Then
' or
If Len(S) = 0 Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Andy" wrote in message
...
Hi,
I am trying to create a function to deal with nulls, but
I'm
enconntering an
error (invalid use of null) by simply passing null to a
function.

The following code creates the issue

Sub go()
Dim sreturn As String
sreturn = nulls("hello")
MsgBox "return is :" & sreturn

sreturn = nulls(Null)
MsgBox "return is :" & sreturn
End Sub

Function nulls(instring As String) As String
If IsNull(instring) Then
nulls = ""
Else
nulls = instring
End If
End Function

Does anybody have any ideas?

Many thanks in advance
Andy

Passing null to a function
 

Many thanks Chip.
In my instance the items in question can be nulls, so variants would seem to
be the way to go.
Regards,
Andy

"Chip Pearson" wrote:

I should have added that a Variant containing an empty string is
neither Null nor Empty.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Chip Pearson" wrote in message
...
I obviously need to use variants.

An uninitialized Variant is not Null, it is Empty, which you
can test with the IsEmpty function. E.g.,

Dim V As Variant
Debug.Print "IsEmpty: " & IsEmpty(V)
Debug.Print "IsNull: " & IsNull(V)

A Variant is Null only when you specifically assign Null to it,
and a Variant set to Null is NOT Empty.

Dim V As Variant
V = Null
Debug.Print "IsEmpty: " & IsEmpty(V)
Debug.Print "IsNull: " & IsNull(V)

I think you need to rethink your usage of Null as opposed to
Empty.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Andy" wrote in message
...
Many thanks Chip.
I obviously need to use variants.
Regards,
Andy

"Chip Pearson" wrote:

Andy,

Null is a special subtype of Variant type variables. An empty
string is NOT a null. IsNull tests only Variant type
variables.
To test for an empty string, use code like

If S = "" Then
' or
If Len(S) = 0 Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Andy" wrote in message
...
Hi,
I am trying to create a function to deal with nulls, but
I'm
enconntering an
error (invalid use of null) by simply passing null to a
function.

The following code creates the issue

Sub go()
Dim sreturn As String
sreturn = nulls("hello")
MsgBox "return is :" & sreturn

sreturn = nulls(Null)
MsgBox "return is :" & sreturn
End Sub

Function nulls(instring As String) As String
If IsNull(instring) Then
nulls = ""
Else
nulls = instring
End If
End Function

Does anybody have any ideas?

Many thanks in advance
Andy