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