Validate That String Only Contains Zeroes

btorrean

Board Regular
Joined
Dec 2, 2011
Messages
76
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!

Code:
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
You could use a loop for this:
Code:
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
 
Last edited:
Upvote 0
UDF?

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td]000[/td][td="bgcolor:#E5E5E5"]
TRUE​
[/td][td]B1: =AllZeros(A1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td]0010[/td][td="bgcolor:#E5E5E5"]
FALSE​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td]0000[/td][td="bgcolor:#E5E5E5"]
TRUE​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td]0[/td][td="bgcolor:#E5E5E5"]
TRUE​
[/td][td][/td][/tr]
[/table]


Code:
Function AllZeros(sInp As String) As Boolean
  AllZeros = Len(Replace(sInp, "0", "")) = 0
End Function
 
Upvote 0
Here is another function that you can consider using...
Code:
Function AllZeros(S As String) As Boolean
  AllZeros = Not S Like "*[!0]*"
End Function
 
Last edited:
Upvote 0
@Rick: You might be curious to know that your routine faster than mine:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
H​
[/td][td="bgcolor:#C0C0C0"]
I​
[/td][td="bgcolor:#C0C0C0"]
J​
[/td][td="bgcolor:#C0C0C0"]
K​
[/td][td="bgcolor:#C0C0C0"]
L​
[/td][td="bgcolor:#C0C0C0"]
M​
[/td][td="bgcolor:#C0C0C0"]
N​
[/td][td="bgcolor:#C0C0C0"]
O​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td="bgcolor:#F3F3F3"]
Range
[/td][td="bgcolor:#F3F3F3"]
Formula
[/td][td="bgcolor:#F3F3F3"]
Cells
[/td][td="bgcolor:#F3F3F3"]
Iterations
[/td][td="bgcolor:#F3F3F3"]
TotalCalcs
[/td][td="bgcolor:#F3F3F3"]
Time
[/td][td="bgcolor:#F3F3F3"]
Time/Calc
[/td][td="bgcolor:#F3F3F3"]
RelSpeed
[/td][/tr]
[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]
[/table]
 
Upvote 0
@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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,182
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top