Passing null to a user defined function - Help!!!

[mattcottrell]

Hi,

I am trying to write a function is passed a string and returns a number

It works fine untill I pass it a null value, I get an "Invalid use of Null" Error

Here is the code I have use for the function:

Public Function NumberCorrection(Value1 As String) As Double

Dim Var1 As String

If IsNull(Value1) Then
Var1 = 0
Else: Var1 = Value1
End If

If Right(Var1, 1) = "-" Then
NumberCorrection = CDbl(Left(Var1, (Len(Var1) - 1))) * -1
Else: NumberCorrection = CDbl(Var1)
End If

End Function


Many thanks in advance
Matt Cottrell

 

[chrissie1]

find the very small difference

Public Function NumberCorrection(Value1 As String) As Double

Dim Var1 As string

If IsNull(Value1) Then
Var1 = "0"
Else: Var1 = Value1
End If

If Right(Var1, 1) = "-" Then
NumberCorrection = CDbl(Left(Var1, (Len(Var1) - 1))) * -1
Else: NumberCorrection = CDbl(Var1)
End If

End Function
Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[mattcottrell]

Many thanks for your help Christiaan, but unfortunatly that didn't fix the problem.

I did manage to fix the problem myself however by declaring Value1 as a Variant rather than a string:

Public Function NumberCorrection(Value1 As Variant) As Double

Dim Var1 As string

If IsNull(Value1) Then
Var1 = "0"
Else: Var1 = Value1
End If

If Right(Var1, 1) = "-" Then
NumberCorrection = CDbl(Left(Var1, (Len(Var1) - 1))) * -1
Else: NumberCorrection = CDbl(Var1)
End If

End Function

Regards
Matt