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
