Validate That String Only Contains Zeroes

[btorrean]

Dear Forum Members,

I apologize if I'm missing something really simple here, but I'm trying to use VBA to test whether a string contains only zeroes. The string can be many zeroes, or one zero, but must only contain zeroes. Here's the code I've been trying. It always tells me that the string has zeroes, even if some of the characters are not zeroes. If the string contains no zeroes, it will tell me that it does not contain zeroes, however. I think that my problem is with the pattern. I've not used RegEx before, so I'm not super-familiar with how to write the patterns. Can anyone help? Thanks!

Option Explicit
Option Compare Text

Public Sub TestRegEx()


Dim myNumStr As String
Dim regEx As VBScript_RegExp_55.RegExp
Dim strPattern As String


myNumStr = "0100"  ' This does NOT produce an error, and it should.
Set regEx = New VBScript_RegExp_55.RegExp


With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = False
    .Pattern = "[0]+"
End With


If regEx.test(myNumStr) Then
    MsgBox "The string '" & myNumStr & "' IS all zeroes!!  Congrats!!", vbInformation
Else
    MsgBox "The string '" & myNumStr & "' is NOT all zeroes.  Bummer dude!", vbCritical
End If


Set regEx = Nothing


End Sub

Brian

 

[Max1616]

You could use a loop for this:

Sub ZeroCheck()
Dim MyString As String
Dim length As Integer
Dim check As Integer
Dim i As Integer

MyString = "00100"
length = Len(MyString)
check = 0

For i = 1 To length
    If Mid(MyString, i, 1) <> 0 Then check = 1
Next i
If check = 1 Then MsgBox "Not all zeros"
If check = 0 Then MsgBox "All zeros"

check = 0

End Sub

 

[shg]

UDF?

	A​	B​	C​
1​	000	TRUE​	B1: =AllZeros(A1)
2​	0010	FALSE​
3​	0000	TRUE​
4​	0	TRUE​

Function AllZeros(sInp As String) As Boolean
  AllZeros = Len(Replace(sInp, "0", "")) = 0
End Function

 

[btorrean]

Thanks, both of you! I appreciate it much!

Brian

 

[Rick Rothstein]

Here is another function that you can consider using...

Function AllZeros(S As String) As Boolean
  AllZeros = Not S Like "*[!0]*"
End Function

 

[btorrean]

Thanks, Rick!

Brian

 

[shg]

@Rick: You might be curious to know that your routine faster than mine:

	H​	I​	J​	K​	L​	M​	N​	O​
1​	Range​	Formula​	Cells​	Iterations​	TotalCalcs​	Time​

[td="bgcolor:#F3F3F3"]

Time/Calc​

[/td][td="bgcolor:#F3F3F3"]

RelSpeed​

[/td]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]C1:C1000[/td][td] =AllZerosShg($A1)[/td][td]

1,000​

[/td][td]

256​

[/td][td]

256,000​

[/td][td]

2.496​

[/td][td="bgcolor:#E5E5E5"]

0.000 009 750​

[/td][td="bgcolor:#E5E5E5"]

1.0​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]E1:E1000[/td][td] =AllZerosrr($A1)[/td][td]

1,000​

[/td][td]

512​

[/td][td]

512,000​

[/td][td]

3.371​

[/td][td="bgcolor:#E5E5E5"]

0.000 006 584​

[/td][td="bgcolor:#E5E5E5"]

1.5​

[/td][/tr]

 

[Rick Rothstein]

@Rick: You might be curious to know that your routine faster than mine:

I kind of thought it would be... and I almost posted that my gut feeling was that it would be... but I had done that a couple of times in the past only to have Peter_SSs check them and find that I was wrong, so I have become a little bit gun-shy of late making those kinds of claims. Now that you have confirmed it, though, I can tell you why I thought my code would be faster. Your code makes two function calls whereas my code makes none. And while the Len function is probably the fastest VB string function, Replace is not, probably because it has to write its solution out to a new memory location (because the resulting string might be either shorter or longer) and then put that result back into the memory location assigned to the variable. I will say, though, that even though my code uses no function calls, the Like operator is not anywhere near the fastest logical operator given the work it has to do for its pattern matching, but I guess in the end that is still faster than what Replace has to do. Anyway, thanks for performing the test as it is always nice to know what's what for future coding efforts.