vba booklet pagination series formula code example

Example: vba booklet pagination series formula

' VBA Excel Custom Formula to create a booklet pagination series
Function PAGINATE(intItemNo As Integer, intNumItems As Integer, Optional intPerPage As Integer = 8)
    If intItemNo > intNumItems Then PAGINATE = 0: Exit Function
    ' This creates booklet series formula
    Dim nP As Integer, pP As Integer, nI As Integer, i As Integer
    Dim ppEffect As Integer, npInc As Integer, npEffect As Integer
    nP = RoundUp(intNumItems / intPerPage)
    nI = intNumItems: pP = intPerPage: i = intItemNo
    ' Assume 12 total items having 3 items per page
    ' 4 Total Pages => [1,5,9] [2,6,10] [3,7,11], [4,8,12]
    ' Paginate => 1  4  7  10  2  6  8 11  3  6  9 12
    ' Item No  => 1  2  3   4  5  7  7  8  9 10 11 12
    '+PP Effect=> 1  4  7  10 13 16 19 22 25 28 31 34
    '-nP Effect=> 0  0  0   0 11 11 11 11 22 22 22 22 (with npInc=11)
    
    ppEffect = (i - 1) * nP + 1
    npInc = nP * pP - 1
    npEffect = ((i - 1) \ pP) * npInc
    PAGINATE = ppEffect - npEffect
End Function