Passing null to a function

[Guest]

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

 

[Chip Pearson]

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

 

[Guest]

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

 

[Chip Pearson]

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

 

[Chip Pearson]

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

 

[Guest]

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