PDA

View Full Version : Solved: Need help for adjustment of code



tomjoe
02-18-2008, 01:39 PM
Anyone !

I am very new to VBA and I need help for the code under that someone else have written.
This is a part of a bigger code but I just need help for this part.
I need only to get this code adjusted to accept the form A.2.1 (two periods).
I will not use the form A.2 but A.2.1 or A.22.1

What do I have to change in this code :

Private Function ValidateLocIDFormat(entryCell As Range) As Boolean
Const validAlphas = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Const validNumerics = "0123456789"
Const sepChar = "."
Dim tmpString1 As String
Dim alphaPortion As String
Dim numericPortion As String
Dim LC As Integer ' loop counter
'make assumption that it is invalid!
ValidateLocIDFormat = False
'remove leading/trailing spaces and convert all alphas to UPPERCASE
tmpString1 = UCase(Trim(entryCell.Value))
If Len(tmpString1) = 0 Then
Exit Function
End If
If InStr(tmpString1, sepChar) < 2 Or InStr(tmpString1, sepChar) =
Len(tmpString1) Then
'either no "." in it, or "." is 1st or last character: Invalid
Exit Function
End If
alphaPortion = Left(tmpString1, InStr(tmpString1, sepChar) - 1)
For LC = 1 To Len(alphaPortion)
If InStr(validAlphas, Mid(alphaPortion, LC, 1)) = 0 Then
Exit Function ' invalid: has something other than ABC... in left portion
End If
Next
numericPortion = Right(tmpString1, Len(tmpString1) - InStr(tmpString1,
sepChar))
For LC = 1 To Len(numericPortion)
If InStr(validNumerics, Mid(numericPortion, LC, 1)) = 0 Then
Exit Function ' invalid: has something other than a number in right
portion
End If
Next

Bob Phillips
02-18-2008, 02:49 PM
Private Function ValidateLocIDFormat(entryCell As Range) As Boolean
Const validAlphas = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Const validNumerics = "0123456789"
Const sepChar = "."
Dim tmpString1 As String
Dim tmpArray As Variant
Dim alphaPortion As String
Dim numericPortion As String
Dim LC As Integer ' loop counter
'make assumption that it is invalid!
ValidateLocIDFormat = False

'remove leading/trailing spaces and convert all alphas to UPPERCASE
tmpString1 = UCase(Trim(entryCell.Value))
If Len(tmpString1) = 0 Then Exit Function

tmpArray = Split(tmpString1, sepChar)
If UBound(tmpArray) - LBound(tmpArray) + 1 <> 3 Then Exit Function

'either no "." in it, or "." is 1st or last character: Invalid
If Len(entryCell.Value) - Len(Replace(entryCell.Value, ".", "")) <> 2 Then Exit Function

alphaPortion = tmpArray(LBound(tmpArray))
' invalid: has something other than ABC... in left portion
For LC = 1 To Len(alphaPortion)
If InStr(validAlphas, Mid(alphaPortion, LC, 1)) = 0 Then Exit Function
Next

numericPortion = tmpArray(LBound(tmpArray) + 1)
' invalid: has something other than a number in middle portion
If Not IsNumeric(numericPortion) Then Exit Function

numericPortion = tmpArray(LBound(tmpArray) + 2)
' invalid: has something other than a number in right portion
If Not IsNumeric(numericPortion) Then Exit Function

ValidateLocIDFormat = True
End Function