Validate an account number
 

Given a general ledger account entered into a cell on the spreadsheet that
looks like: 123.123.1234.123456.123.12.123

Using VBA, I need to be sure it only contains either numbers or periods and
no other stray characters (`,#$!-_=+ A-z ... etc), including blank spaces.
What is the best way to test for this?

Please note that I am already counting the periods to ensure the number of
segments and checking the length of each segment...

Thanks much in advance.

Validate an account number
 

Sub account()
Dim s As String
s = "123.123.1234.123456.123.12.123"
b = IsNumeric(Replace(s, ".", ""))
MsgBox (b)
End Sub

--
Gary''s Student - gsnu200736


"XP" wrote:

Given a general ledger account entered into a cell on the spreadsheet that
looks like: 123.123.1234.123456.123.12.123

Using VBA, I need to be sure it only contains either numbers or periods and
no other stray characters (`,#$!-_=+ A-z ... etc), including blank spaces.
What is the best way to test for this?

Please note that I am already counting the periods to ensure the number of
segments and checking the length of each segment...

Thanks much in advance.

Validate an account number
 

On Tue, 7 Aug 2007 06:26:03 -0700, XP wrote:

Given a general ledger account entered into a cell on the spreadsheet that
looks like: 123.123.1234.123456.123.12.123

Using VBA, I need to be sure it only contains either numbers or periods and
no other stray characters (`,#$!-_=+ A-z ... etc), including blank spaces.
What is the best way to test for this?

Please note that I am already counting the periods to ensure the number of
segments and checking the length of each segment...

Thanks much in advance.

To merely check that the account contains only digits and periods, you could
use this:

==========================
Option Explicit
Sub ChkNum()
Dim c As Range
Dim oRegex As Object
Const sPattern As String = "[^0-9\.]+"

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Pattern = sPattern
For Each c In Selection
If oRegex.test(c.Text) = True Or _
Len(c.Text) = 0 Then
Debug.Print c.Text, "Invalid"
Else
Debug.Print c.Text, "Valid"
End If
Next c
End Sub
=============================

If you wanted to check for a valid account number, how to do it would depend on
the rules for a valid account number. But it would merely involve changing
sPattern in the above.

For example, if a valid account number were one formatted as you show, and had
no leading or trailing <spaces, then you could set sPattern =

"^\d{3}\.\d{3}\.\d{4}\.\d{6}\.\d{3}\.\d{2}\.\d{3}$ "

Of course, you'd also have to reverse the "True/False" test, so:

================================================== ======
Option Explicit
Sub ChkNum()
Dim c As Range
Dim oRegex As Object
Dim mcMatchCollection As Object
Const sPattern As String = "^\d{3}\.\d{3}\.\d{4}\.\d{6}\.\d{3}\.\d{2}\.\d{3}$ "

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Pattern = sPattern
For Each c In Selection
If oRegex.test(c.Text) = False Then
Debug.Print c.Text, "Invalid"
Else
Debug.Print c.Text, "Valid"
End If
Next c
End Sub
================================================

If there were more acceptable variability in Account Number, that could
probably be included in sPattern also.

--ron

Validate an account number
 

On Tue, 7 Aug 2007 07:14:09 -0700, Gary''s Student
wrote:

Sub account()
Dim s As String
s = "123.123.1234.123456.123.12.123"
b = IsNumeric(Replace(s, ".", ""))
MsgBox (b)
End Sub

Your routine does not filter out all extraneous characters, as specified by the
OP.

For example:

s = "+123.123.1234.123456.123.12.123"
s = "-123.123.1234.123456.123.12.123"
s = "123.123.1234.123456.123.12.123-"
s = "123.123.1234.123456.123.12.123+"
s = "(123.123.1234.123456.123.12.123)"

will all return TRUE, even though they include characters the OP specifically
listed to be excluded.
--ron

Validate an account number
 

On Tue, 07 Aug 2007 10:16:16 -0400, Ron Rosenfeld
wrote:

================================================= =======
Option Explicit
Sub ChkNum()
Dim c As Range
Dim oRegex As Object
Dim mcMatchCollection As Object
Const sPattern As String = "^\d{3}\.\d{3}\.\d{4}\.\d{6}\.\d{3}\.\d{2}\.\d{3}$ "

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Pattern = sPattern
For Each c In Selection
If oRegex.test(c.Text) = False Then
Debug.Print c.Text, "Invalid"
Else
Debug.Print c.Text, "Valid"
End If
Next c
End Sub
=============================================== =

Removing an extraneous line:

================================================== ======
Option Explicit
Sub ChkNum()
Dim c As Range
Dim oRegex As Object
Const sPattern As String = "^\d{3}\.\d{3}\.\d{4}\.\d{6}\.\d{3}\.\d{2}\.\d{3}$ "

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Pattern = sPattern
For Each c In Selection
If oRegex.test(c.Text) = False Then
Debug.Print c.Text, "Invalid"
Else
Debug.Print c.Text, "Valid"
End If
Next c
End Sub
================================================
--ron

Validate an account number
 

If sPattern is well defined (as shown in the example), you don't need to use
a Regular Expression to test it... VBA's Like operator is enough...

Sub ChkNum()
Dim c As Range
Dim oRegex As Object
Const sPattern As String = "###.###.####.######.###.##.###"
For Each c In Selection
If c.Text Like sPattern Then
Debug.Print c.Text, "Valid"
Else
Debug.Print c.Text, "Invalid"
End If
Next c
End Sub

Rick


"Ron Rosenfeld" wrote in message
...
On Tue, 07 Aug 2007 10:16:16 -0400, Ron Rosenfeld

wrote:

================================================ ========
Option Explicit
Sub ChkNum()
Dim c As Range
Dim oRegex As Object
Dim mcMatchCollection As Object
Const sPattern As String =
"^\d{3}\.\d{3}\.\d{4}\.\d{6}\.\d{3}\.\d{2}\.\d{3 }$"

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Pattern = sPattern
For Each c In Selection
If oRegex.test(c.Text) = False Then
Debug.Print c.Text, "Invalid"
Else
Debug.Print c.Text, "Valid"
End If
Next c
End Sub
============================================== ==

Removing an extraneous line:

================================================== ======
Option Explicit
Sub ChkNum()
Dim c As Range
Dim oRegex As Object
Const sPattern As String =
"^\d{3}\.\d{3}\.\d{4}\.\d{6}\.\d{3}\.\d{2}\.\d{3}$ "

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Pattern = sPattern
For Each c In Selection
If oRegex.test(c.Text) = False Then
Debug.Print c.Text, "Invalid"
Else
Debug.Print c.Text, "Valid"
End If
Next c
End Sub
================================================
--ron

Validate an account number
 

On Tue, 7 Aug 2007 12:28:27 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

If sPattern is well defined (as shown in the example), you don't need to use
a Regular Expression to test it... VBA's Like operator is enough...

Sub ChkNum()
Dim c As Range
Dim oRegex As Object
Const sPattern As String = "###.###.####.######.###.##.###"
For Each c In Selection
If c.Text Like sPattern Then
Debug.Print c.Text, "Valid"
Else
Debug.Print c.Text, "Invalid"
End If
Next c
End Sub

That's true.

I was thinking along the lines that if there is some variability in the numbers
of digits allowed between each 'dot', then constructing a regex might be
simpler.
--ron

Validate an account number
 

If sPattern is well defined (as shown in the example), you don't need to
use
a Regular Expression to test it... VBA's Like operator is enough...

Sub ChkNum()
Dim c As Range
Dim oRegex As Object
Const sPattern As String = "###.###.####.######.###.##.###"
For Each c In Selection
If c.Text Like sPattern Then
Debug.Print c.Text, "Valid"
Else
Debug.Print c.Text, "Invalid"
End If
Next c
End Sub

That's true.

I was thinking along the lines that if there is some variability in the
numbers
of digits allowed between each 'dot', then constructing a regex might be
simpler.

If the OP needed flexibility in setting the pattern, I would probably add a
Module to the VBA project and use a function something like this...

Function CHECKNUM(Cell As Range, ParamArray FieldCount())
Dim X As Long
Dim sPattern As String
If Cell.Count < 1 Then
CHECKNUM = "#CELL REF ERROR!"
Else
For X = LBound(FieldCount) To UBound(FieldCount)
sPattern = sPattern & String$(FieldCount(X), "#")
If X < UBound(FieldCount) Then sPattern = sPattern & "."
Next
CHECKNUM = Cell.Text Like sPattern
End If
End Function

That way, the user could specify how many number go in each section and test
that against a specified cell value. For example, if the value being checked
were in A1 and the pattern was as originally posted, then the formula to use
on the spreadsheet would be...

=CHECKNUM(A1,3,3,4,6,3,2,3)

If the value in, say, A2 needed to be checked against a pattern that looked
like this 12.123.1234, then this would be the formula to use...

=CHECKNUM(A2,2,3,4)

I wasn't sure of the best way to report back an error if the specified range
that wasn't a single cell, so I made up what I show in the code.

Rick

Validate an account number
 

On Tue, 7 Aug 2007 13:39:38 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

That way, the user could specify how many number go in each section and test
that against a specified cell value. For example, if the value being checked
were in A1 and the pattern was as originally posted, then the formula to use
on the spreadsheet would be...

=CHECKNUM(A1,3,3,4,6,3,2,3)

If the value in, say, A2 needed to be checked against a pattern that looked
like this 12.123.1234, then this would be the formula to use...

=CHECKNUM(A2,2,3,4)

I wasn't sure of the best way to report back an error if the specified range
that wasn't a single cell, so I made up what I show in the code.

There are many ways of solving a problem. But since we may not have all the
parameters, it is difficult to argue with any particular approach.

I was especially thinking of allowing a variable number of digits in each
section.

--ron