Quote Originally Posted by orange
wedd,

I didn't put a NULL check in the function for sPhone. My understanding is that you would do that before calling the ValidatePhone routine.

Put this in the function for completeness.
It goes before the line With CreateObject
and after On Error GoTo ValidatePhone_Error

[vba]If Len(sPhone & "") = 0 Then
ValidatePhone = "Fail"
Exit Function
End If[/vba]
hansup is correct, a NULL check would be pointless.

I mis-named this check. It's really checking for a zero length string.

I have adjusted the Tester program, so that whatever string you pass, a message will be written to the immediate window.
Here is the adjusted code. Test it.

[vba]
'---------------------------------------------------------------------------------------
' Procedure : ValidatePhone
' Author : Orange
' Created : 4/13/2011
' Purpose : This routine will check an input string against a pattern.
'To be valid the input
' must be all numerics
' must start with 0 and
' must be 11 chars long.
'---------------------------------------------------------------------------------------
' Last Modified: 4/14/2011 ** Check for ZLS in sPhone (hansup VBAX)
'
' Inputs: a string
' Outputs: "Pass" or "Fail"
' Dependency: Requires a reference to Microsoft VBScript Regular Exressions
'------------------------------------------------------------------------------
'
Function ValidatePhone(sPhone As String) As String

On Error GoTo ValidatePhone_Error

Check_for_ZEero_length_String:
If Len(sPhone & "") = 0 Then
ValidatePhone = "Fail"
Exit Function
End If

With CreateObject("vbscript.regexp")
.Pattern = "(^0[0-9]{10})"
.Global = True
If .test(sPhone) Then
ValidatePhone = "Pass"
Else
ValidatePhone = "Fail"
End If
End With

On Error GoTo 0
Exit Function

ValidatePhone_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ValidatePhone of Module AWF_Related"
End Function


Sub testValidatePhone()
'no message if the myphone is valid
'
'Fail if
' ZLS is passed or
' all chars not numeric, or
' first digit is not 0, or
' length is not 11
'
Dim myPhone As String
myPhone = "12978481234" ' change this value, or comment it out
myPhone = Nz(myPhone, "")
If Len(myPhone & "") = 0 Then
Debug.Print "Myphone has no Value"
Else
Debug.Print "Myphone has a Value of " & myPhone
End If

If ValidatePhone(myPhone) <> "Pass" Then
MsgBox myPhone & " is not a valid phone number"
End If
End Sub
[/vba]