In Microsoft Word’s print dialog, there’s a textbox called Pages. In it, you can specify which pages you want to print by separating individual pages and page ranges by a comma.
I have a small app wherein I enter a starting serial number and an ending serial number. The output is a string of serial numbers in between. I wanted to be able to deal with nonconsecutive serial numbers a little more elegantly, so I set out to replicate Word’s Pages textbox.
‘returns a 1-dim array of all pages to print
Dim vaCommaSplit As Variant
Dim vaHyphSplit As Variant
Dim i As Long, j As Long
Dim lLower As Long, lUpper As Long
Dim aResult() As Long
Dim lResCnt As Long
vaCommaSplit = Split(sInput, “,”)
For i = 0 To UBound(vaCommaSplit)
‘if there is no hyphen, vaCommaSplit(i) is returned
vaHyphSplit = Split(vaCommaSplit(i), “-“)
‘no hyphen means lLower = lUpper
lLower = Val(vaHyphSplit(0))
lUpper = Val(vaHyphSplit(UBound(vaHyphSplit)))
For j = lLower To lUpper
lResCnt = lResCnt + 1
ReDim Preserve aResult(1 To lResCnt)
aResult(lResCnt) = j
Next j
Next i
PagesToPrint = aResult
End Function
Sub Test()
Dim sInput As String
Dim i As Long
Dim vaPages As Variant
sInput = “1,3-5,10”
vaPages = PagesToPrint(sInput)
For i = LBound(vaPages) To UBound(vaPages)
Debug.Print vaPages(i)
Next i
End Sub
The Split function returns a zero-based array of elements after it splits a string by the delimiter. If the delimiter doesn’t exist, Split return a single element array with the original string. Because of this, I don’t have to test if there’s a hyphen (meaning a range) or not (just a single number). If it’s just a single number, UBound(vaHyphSplit) will be zero and lUpper and lLower will be the same. That means the inner loop will execute exactly once and put lLower in the result array.
Error checking needed: I put a Val() in there to help with non-numerical stuff, but that needs to be beefed up. I also need to make sure the number before the hyphen is less than the one after. Sounds like version 2 “features”.
Some years back I downloaded code from J-Walk that presented a user form to select sheets to print. I believe the first version was Excel 2K and I have used it since. I searched J-Walk site to see if I could find the original to no avail.
http://spreadsheetpage.com/index.php/tip/displaying_a_menu_of_worksheets_to_print/
This was similar except the original gave a list box with multi-select option.
Whether it makes sense or not, Word supports hyphenated page ranges in reverse order, e.g., 9-4. Your function doesn’t.
Also, any text parsing task is best done using regular expressions.
Dim re As New RegExp, mc As MatchCollection, m As Match
Dim d As New Dictionary
Dim i As Long, j As Long, k As Long, p As Long
‘remove spaces to eliminate that distraction
ps = Replace$(ps, ” “, “”)
‘validity checking: comma-separated list of nums or hyphenated num ranges
re.Global = True
re.Pattern = “((d+-d+|d+),)*(d+-d+|d+)”
If Not re.Test(ps) Then Exit Function
re.Pattern = “d+-d+|d+”
Set mc = re.Execute(ps)
For Each m In mc
p = InStr(1, m.Value, “-“)
If p > 0 Then
i = CLng(Left$(m.Value, p – 1))
j = CLng(Mid$(m.Value, p + 1))
k = IIf(i > j, -1, 1)
Do
d.Add Key:=d.Count, Item:=i
i = i + k
Loop Until Sgn(i – j) = k
Else
d.Add Key:=d.Count, Item:=CLng(m.Value)
End If
Next m
foo = d.Items
End Function
I thought the readers of this blog would appreciate seeing a different way to form the output array (note that the extra length of the code is due to the error checking routines that I added to its functionality)…
Function PagesToPrint(sInput As String) As Variant
Dim X As Long, Z As Long, Temp As String, sNumbers() As String, sRange() As String
If sInput Like “*[!0-9,-]*” Or sInput Like “*,-*” Or sInput Like “*-,*” _
Or sInput Like “*,,*” Or sInput Like “**” Then GoTo Bad
sNumbers = Split(sInput, “,”)
For X = 0 To UBound(sNumbers)
If sNumbers(X) Like “*-*” Then
If sNumbers(X) Like “*-*-*” Then GoTo Bad
sRange = Split(sNumbers(X), “-“)
If sRange(0) <= sRange(1) Then
sNumbers(X) = “”
For Z = sRange(0) To sRange(1)
sNumbers(X) = sNumbers(X) & “,” & Z
Next
sNumbers(X) = Mid(sNumbers(X), 2)
Else
GoTo Bad
End If
End If
Next
PagesToPrint = Split(Join(sNumbers, “,”), “,”)
Exit Function
Bad:
PagesToPrint = Array()
MsgBox “””” & sInput & “””” & vbLf & vbLf & “The range of values you specified is incorrectly formed!”, vbCritical
End Function
Damn! I forgot the code tags. Let’s try that again…
I thought your and the readers of this blog would appreciate seeing a totally different way to form the output array (note that the extra length of the code is due to the error checking routines that I added to its functionality)…
Dim X As Long, Z As Long, Temp As String, sNumbers() As String, sRange() As String
If sInput Like “*[!0-9,-]*” Or sInput Like “*,-*” Or sInput Like “*-,*” _
Or sInput Like “*,,*” Or sInput Like “*–*” Then GoTo Bad
sNumbers = Split(sInput, “,”)
For X = 0 To UBound(sNumbers)
If sNumbers(X) Like “*-*” Then
If sNumbers(X) Like “*-*-*” Then GoTo Bad
sRange = Split(sNumbers(X), “-“)
If sRange(0) <= sRange(1) Then
sNumbers(X) = “”
For Z = sRange(0) To sRange(1)
sNumbers(X) = sNumbers(X) & “,” & Z
Next
sNumbers(X) = Mid(sNumbers(X), 2)
Else
GoTo Bad
End If
End If
Next
PagesToPrint = Split(Join(sNumbers, “,”), “,”)
Exit Function
Bad:
PagesToPrint = Array()
MsgBox “”“” & sInput & “”“” & vbLf & vbLf & “The range of values you specified is incorrectly formed!”, vbCritical
End Function
@Rick – your function dies on an untrapped error when passed “-” and returns {“”,””} when passed “,”.
Nothing beats regular expressions for text parsing and lexical validity testing. ‘Course it helps to get the regex right. My first re.Pattern statement should have been
You CAN check the validity of the input string without regular expressions by using a formal state machine. Not pretty, but a lot more robust than screwing around trying to use Like to match and exhaustive set of compound tokens.
Const PATsep As String * 1 = “,”, STsep As Long = 1
Const PATnum As String = “[0-9]”, STnum As Long = 2
Const PATrng As String * 1 = “-“, STrng As Long = 4, STend As Long = STnum + STrng
Dim ch As String * 1, k As Long, n As Long, st As Long
ps = Replace(ps, ” “, “”)
n = Len(ps)
st = STsep
For k = 1 To n
ch = Mid$(ps, k, 1)
Select Case st
Case STsep:
If ch Like PATnum Then st = STnum Else st = 0
Case STnum:
If ch Like PATsep Then
st = STsep
ElseIf ch Like PATrng Then
st = STrng
ElseIf Not ch Like PATnum Then
st = 0
End If
Case STrng:
If ch Like PATnum Then st = STend Else st = 0
Case STend:
If ch Like PATsep Then
st = STsep
ElseIf Not ch Like PATnum Then
st = 0
End If
Case Else:
st = 0
End Select
If st = 0 Then Exit For
Next k
PGRNGvalchk = (st And STnum)
End Function
@ fzz
>> your function dies on an untrapped error when passed “-” and returns {“”,””} when passed “,”.
I agree about regular expressions, but the last time I worked with them was on a UNIX system in the late 1980s, so RegEx pattern construction capabilities are a little rusty. Beside, I would guess most people following threads on this formum have little or no experience with them, so my code offers them a wholly native VB solution for them to digest. As for the state machine… too much extra code for my tastes. I think the above oversights on my part are easily corrected along with an error I had in my originally posted code (where I did a string rather than numeric comparison in the 2nd “If” statement inside the loop (which screwed up comparing a range like 9-11). Here is my modified code…
Dim X As Long, Z As Long, Temp As String, sNumbers() As String, sRange() As String
If sInput Like “*[!0-9,-]*” Or sInput Like “*,-*” Or sInput Like “*-,*” Or sInput Like “*,,*” _
Or sInput Like “*–*” Or sInput Like “-“ Or sInput Like “,” Then GoTo Bad
sNumbers = Split(sInput, “,”)
For X = 0 To UBound(sNumbers)
If sNumbers(X) Like “*-*” Then
If sNumbers(X) Like “*-*-*” Then GoTo Bad
sRange = Split(sNumbers(X), “-“)
If CLng(sRange(0)) <= CLng(sRange(1)) Then
sNumbers(X) = “”
For Z = sRange(0) To sRange(1)
sNumbers(X) = sNumbers(X) & “,” & Z
Next
sNumbers(X) = Mid(sNumbers(X), 2)
Else
GoTo Bad
End If
End If
Next
PagesToPrint = Split(Join(sNumbers, “,”), “,”)
Exit Function
Bad:
PagesToPrint = Array()
MsgBox “”“” & sInput & “”“” & vbLf & vbLf & “The range of values you specified is incorrectly formed!”, vbCritical
End Function
http://www.regular-expressions.info/
@Rick – regular expressions have been discussed before in this blog, and the blog is meant for more sophisticated users and developers, you know, those who aren’t afraid to do a bit of self-study.
You still have errors. Pass your latest function “1,2,” and it returns {“1?,”2?,””}. Your validity checking fails when SInput begins or ends with a comma.
If you really insist on using Like patterns, then at least use them optimally.
Dim i As Long, j As Long, k As Long, n As Long, p As Long, t As String
ps = Replace(ps, ” “, “”) ‘why not allow whitespace like Word does?
If ps Like “*[!0-9,-]*” _
Or ps Like “[!0-9]*” _
Or ps Like “*[!0-9]” _
Or ps Like “*[,-][,-]*” _
Or ps Like “*-[0-9]-*” _
Then ps = “”
p = InStrRev(ps, “-“) ‘right to left!!
Do While p > 0
i = InStrRev(ps, “,”, p – 1) + 1
j = CLng(Mid$(ps, i, p – i))
i = InStr(p + 1, ps, “,”) – 1
If i = -1 Then k = CLng(Mid$(ps, p + 1)) Else k = CLng(Mid$(ps, p + 1, i – p))
If j = k Then ‘allow trivial ranges like 3-3, but not much to do
ps = Left$(ps, p – 1) & Mid$(ps, i + 1)
Else
n = Sgn(k – j) ‘allow reverse order ranges like 9-4 in addition to forward ranges
t = “”
For i = j + n To k – n Step n ‘only need the interior of range
t = t & “,” & CStr(i)
Next i
ps = Left$(ps, p – 1) & t & “,” & Mid$(ps, p + 1) ‘replace hyphen with interior of range
End If
p = InStrRev(ps, “-“, p – 1) ‘continue right to left!!
Loop
foobar = Split(ps, “,”)
End Function
The 1st If check in prose:
– can contain only decimal numerals, hyphens and commas
– must start with a numeral
– must end with a numeral
– can’t have substrings of 2 or more hyphens or commas
– can’t have 2 or more hyphens between commas
OK, one screw-up. Can’t use a Like pattern to test exhaustively for multiple hyphens between commas, but my test should be expanded as follows.
Or ps Like “*-[0-9]-*” _
Or ps Like “*-[0-9][0-9]-*” _
Or ps Like “*-[0-9][0-9][0-9]-*” _
Or ps Like “*-[0-9][0-9][0-9][0-9]-*” _
Or ps Like “*-[0-9][0-9][0-9][0-9][0-9]-*” _
Or ps Like “*-[0-9][0-9][0-9][0-9][0-9][0-9]-*” _
Then ps = “”
One more reason to prefer regular expressions.
@fzz,
Okay, I corrected the leading trailing problem that you pointed out and I also modified the code to allow blank spaces everywhere except between digits and I also modified the code to allow hyphenated ranges in reverse order (the reverse ordering is preserved when the values are assigned into the returned array from the function)…
Function PagesToPrint(sInput As String) As Variant
Dim X As Long, Z As Long, Temp As String, sNumbers() As String, sRange() As String
If sInput Like “*# #*” Then GoTo Bad
sInput = Replace(sInput, ” “, “”)
If sInput Like “*[!0-9,-]*” Or sInput Like “*,-*” Or sInput Like “*-,*” Or _
sInput Like “*,,*” Or sInput Like “**” Or sInput Like “-” Or _
sInput Like “,” Or sInput Like “,*” Or sInput Like “*,” Then GoTo Bad
sNumbers = Split(sInput, “,”)
For X = 0 To UBound(sNumbers)
If sNumbers(X) Like “*-*” Then
If sNumbers(X) Like “*-*-*” Then GoTo Bad
sRange = Split(sNumbers(X), “-“)
sNumbers(X) = “”
For Z = sRange(0) To sRange(1) Step Sgn(sRange(1) – sRange(0) + 0.1)
sNumbers(X) = sNumbers(X) & “,” & Z
Next
sNumbers(X) = Mid(sNumbers(X), 2)
End If
Next
PagesToPrint = Split(Join(sNumbers, “,”), “,”)
Exit Function
Bad:
PagesToPrint = Array()
MsgBox “””” & sInput & “””” & vbLf & vbLf & “The range of values you specified is incorrectly formed!”, vbCritical
End Function
Other than NIH, any good reason to use the 4 patterns “*,-*”, “*-,*”, “*,,*” and “**” rather than the 1 pattern “*[,-][,-]*” or the 4 patters “-“, “,”, “,*” and “*,” (shouldn’t there be 6 patterns including “*-” and “-*”?) rather than the 2 patterns “#*” and “*#”?
Fair point about spaces between numerals. They should be errors. But that makes the validation rules just
– no spaces between numerals, and after this check all spaces may be removed at which point
– no chars other than numerals, commas and hyphens
– no chars other than numerals in first and last positions
– no sequences of 2 or more non-numerals
– no more than 1 hyphen between commas
>> Other than NIH, any good reason to use the 4 patterns “*,-*”, “*-,*”, “*,,*”
>> and “**” rather than the 1 pattern “*[,-][,-]*”
Nope, just simple inattention on my part.
>> or the 4 patters “-“, “,”, “,*” and “*,” (shouldn’t there be 6 patterns
>> including “*-” and “-*”?) rather than the 2 patterns “#*” and “*#”?
Same excuse, although you missed a simplification yourself… only one pattern test is needed… “#*#”
>> Fair point about spaces between numerals. They should be errors. But that makes the validation rules just
>> – no spaces between numerals, and after this check all spaces may be removed at which point
>> – no chars other than numerals, commas and hyphens
>> – no chars other than numerals in first and last positions
>> – no sequences of 2 or more non-numerals
>> – no more than 1 hyphen between commas
Charting it like this before starting to write any code (instead of writing code as I thought of each condition) is definitely the much, much better way to approach this problem… the code to handle the conditions when laid out like this is much easier to see. Because you can’t make Like searches “ungreedy”, the last item in the list has to be handled the way I currently do it… inside the loop after sInput is Split apart. Before finishing up, I just wanted to thank you for slapping-me-upside-of-my-head with all this, it was much appreciated. Okay, so here is the modified (and I think final[grin]) code for my approach to the original problem…
Dim X As Long, Z As Long, Temp As String, sNumbers() As String, sRange() As String
If sInput Like “*# #*” Then GoTo Bad
sInput = Replace(sInput, ” “, “”)
If sInput Like “*[!0-9,-]*” Or sInput Like “*[,-][,-]*” Or Not sInput Like “#*#” Then GoTo Bad
sNumbers = Split(sInput, “,”)
For X = 0 To UBound(sNumbers)
If sNumbers(X) Like “*-*” Then
If sNumbers(X) Like “*-*-*” Then GoTo Bad
sRange = Split(sNumbers(X), “-“)
sNumbers(X) = “”
For Z = sRange(0) To sRange(1) Step Sgn(sRange(1) – sRange(0) + 0.1)
sNumbers(X) = sNumbers(X) & “,” & Z
Next
sNumbers(X) = Mid(sNumbers(X), 2)
End If
Next
PagesToPrint = Split(Join(sNumbers, “,”), “,”)
Exit Function
Bad:
PagesToPrint = Array()
MsgBox “”“” & sInput & “”“” & vbLf & vbLf & “The range of values you specified is incorrectly formed!”, vbCritical
End Function
@Rick – testing for numbers at beginning and end while allowing single page ranges for pages 1 through 9 requires 2 tests. The “#*#” test fails for “1?, “2?, etc. It should fail for “0?, so maybe the beginning test should be “[1-9]*”.
>> @Rick – testing for numbers at beginning and end while allowing single page
>> ranges for pages 1 through 9 requires 2 tests. The “#*#” test fails for
>> “1?, “2?, etc. It should fail for “0?, so maybe the beginning test should be “[1-9]*”.
Doh! I got so caught up in the range “thing” that I completely forgot about single non-range numbers. And yes, the leading number test should be for non-zero numbers, but in order to protect against someone entering 04-09 as a range, I made the Like test for leading numbers against Val(sInput) rather than against sInput directly (letting VB handle the behind-the-scenes conversion of the resulting number back to a String value). I also added an Else statement in my If..Then block to handle the stripping away of leading zeroes for single pages that might start with zero. And now, the final(?) code[grin]…
Dim X As Long, Z As Long, Temp As String, sNumbers() As String, sRange() As String
If sInput Like “*# #*” Then GoTo Bad
sInput = Replace(sInput, ” “, “”)
If sInput Like “*[!0-9,-]*” Or sInput Like “*[,-][,-]*” Or Not sInput Like “*#” Or Not Val(sInput) Like “[1-9]*” Then GoTo Bad
sNumbers = Split(sInput, “,”)
For X = 0 To UBound(sNumbers)
If sNumbers(X) Like “*-*” Then
If sNumbers(X) Like “*-*-*” Then GoTo Bad
sRange = Split(sNumbers(X), “-“)
sNumbers(X) = “”
For Z = sRange(0) To sRange(1) Step Sgn(sRange(1) – sRange(0) + 0.1)
sNumbers(X) = sNumbers(X) & “,” & Z
Next
sNumbers(X) = Mid(sNumbers(X), 2)
Else
sNumbers(X) = Val(sNumbers(X))
End If
Next
PagesToPrint = Split(Join(sNumbers, “,”), “,”)
Exit Function
Bad:
PagesToPrint = Array()
MsgBox “”“” & sInput & “”“” & vbLf & vbLf & “The range of values you specified is incorrectly formed!”, vbCritical
End Function