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