Results 1 to 4 of 4
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    IsPageBreak Function (Excel97)

    I was trying to create a function to determine if a particular cell had a page break. The goal was to possibly use this function in a conditional formatting "formula" to change the format of the LAST row on a page.

    The function I have attached, does this,
    BUT it is ungodly SLOOOOOOOOOOOOOOOOOOOOOOOOOOOOOW.
    WARNING: If you test it, try it only with 2 or 3 cells to get an idea of the slowness. I found it very painful to wait for too many more.

    I presume that EACH calling of the hpagebreak collection forces excel to relook at the page to calculate. It is worse than calling pagesetup multiple times!

    Does anyone have any ideas on how to speed it up or even a different technique for determining whether a cell has a page break?

    Thanks,
    Steve


    <pre>Function IsPageBreak(rcell As Range) As Boolean
    Dim hpb As HPageBreak
    Dim ws As Worksheet
    Set ws = Sheets(rcell.Parent.Name)
    IsPageBreak = False
    For Each hpb In ws.HPageBreaks
    If rcell.Row = hpb.Location Then
    IsPageBreak = True
    End If
    Next
    End Function
    </pre>


  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsPageBreak Function (Excel97)

    Steve
    Yes - it IS slow
    I made one minor change to add an Exit For statement within the loop
    I found I also had to code Location.Row to get it to work for me in 2002

    I think there is another approach - though it depends on your needs.

    You're using it as a function which implies it's replicated a lot on a single sheet.
    Each execution is reworking the analysis which is unnecesary as it is hardly likely that the page breaks are moving.

    An alternative is to
    Identify the Hpagebreaks Once and create an array (Public variable) of row numberss
    Have your function work on the array - rather than Hpagebreaks
    The Hpagebreaks.locations.rows could be put in the array in an event procedure - e.g. BeforePrint or on Sheet activation / deactivation spring to mind

    Alternatively or additionally,
    a variable could be used to count and force an update every so often.
    an alternate function (only once per sheet) could set it

    Hope that's something to go on with

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsPageBreak Function (Excel97)

    Hi Steve,

    Is this any better:

    Sub GetPgbrks()
    Dim vpgbrks As Variant
    Dim lCount As Long
    ActiveWorkbook.Names.Add "zzTemp", "=get.document(64)"
    vpgbrks = (ActiveWorkbook.ActiveSheet.[zzTemp])
    For lCount = 1 To UBound(vpgbrks)
    MsgBox vpgbrks(lCount)
    Next
    ActiveWorkbook.Names("zzTemp").Delete
    End Sub

    Maybe this one is just SLOOOOW, not SLOOOOOOOOOOOOOOOOOOW <g>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: IsPageBreak Function (Excel97)

    Jan and Andrew,
    Thanks for your thoughts. I think I will have to try alternate approaches.

    Andrew you are correct, I wanted a function so it would be used multiple times. I wanted to use it in conditional formatting: It could have hundreds of calls on a single page and multiple pages!

    The page breaks are not stagnant. as the user enters data into a worksheet the number of pages could change. Formatting is done for each entry: as data is entered into a row, the row gets formatting. When a page break occurs I wanted a line at the bottom of the last row on the page.

    It could be done before the print event, but this is also a problem, since if it is added as a cell format, it will still be overridden by the conditional formatting already on the cell.

    I think doing the formatting before the print is the way to go and I will have to try to get it to work that way. The function does not seem to be the way to go at all due to the number of calcs involved and all the calls to the repaging function in excel.

    Again, thanks for your thoughts,
    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •