Passing null to a function

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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
 
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
 
Back
Top