| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Zero and Null Sum Function | Excel Worksheet Functions | |||
| Passing a row to a function | Excel Worksheet Functions | |||
| Passing a WorkSheet from a Function??? | Excel Worksheet Functions | |||
| VBA - Passing a FUNCTION as an Argument | Excel Programming | |||
| Passing array to a function | Excel Programming | |||
Linear Mode
