Pages to Print

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.

Function PagesToPrint(sInput As String) As Variant
    ‘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”.

Posted in Uncategorized

14 thoughts on “Pages to Print

  1. 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.

    Function foo(ps As String) As Variant
      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

  2. 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

  3. 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)…

    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
  4. @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

    re.Pattern = “^((d+-d+|d+),)*(d+-d+|d+)$”

    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.

    Function PGRNGvalchk(ps As String) As Boolean
      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

  5. @ 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…

    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 “*–*” 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
  6. @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.

    Function foobar(ps As String) As Variant
      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

  7. 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.

  8. @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

  9. 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

  10. >> 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…

    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 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
  11. @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]*”.

  12. >> @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]…

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

Leave a Reply

Your email address will not be published. Required fields are marked *