polarohio.blogg.se - Page break preview in excel for mac

#PAGE BREAK PREVIEW IN EXCEL FOR MAC GENERATOR#
#PAGE BREAK PREVIEW IN EXCEL FOR MAC CODE#

If ActiveCell.FormulaR1C1 = "Site ID" Then The same for the Next B4 line.ĭo While ActiveCell.Row <=

#PAGE BREAK PREVIEW IN EXCEL FOR MAC CODE#

So, for the first appearance of Select in your code Range("A3").Select, you would 'Set' it as Set rngCurrentCell = Range("A3"). I would recommend using a Range as a reference and do all the tests "in memory".ĭim a range for tracking ( dim rngCurrentCell as range) and use that instead of the selecting the cells. Excel actually selects the cells as your code is running, making the code run slower (as you've noticed). The easy answer is that you use ActiveCell and Select and Activate. Private Sub LockInterface(ByVal interfaceOff As Boolean) PctProgress.ProgressText = "Set break point " & (CStr(passes) & "." & CStr(i)) wstWorksheet.Cells(lngRow, lngColSiteIDSecondary_c)

If LCase$(wstWorksheet.Cells(lngRow, lngColSiteIDSecondary_c).value) = "site id" Then If LenB(wstWorksheet.Cells(lngRow, lngColSiteIDSecondary_c).Formula) = 0& Then 'Checking the LenB is faster than a string check. 'Move the intended break point up to the first blank sectionįor lngRow = p.Location.Row - lngOffset_c To 1& Step -1& PctProgress.ProgressText = ("Row " & CStr(lngRow)) & (" of " & CStr(lngRowBtm)) WstWorksheet.Cells(lngRow, lngColSiteID_c).PageBreak = xlPageBreakManual If LCase$(wstWorksheet.Cells(lngRow, lngColSiteID_c).value) = "site id" Then 'LCase is to make comparison case insensitive. Public Sub PageBreak(ByRef wstWorksheet As Excel.Worksheet, ByVal pctProgress As ProgressCtl.ProgressContro)Ĭonst lngColSiteIDSecondary_c As Long = 1& Without knowing your data layout, it's hard to be sure if this code is 100% valid, so I would double check it for logic errors. I refactored the original code to give you an example of some of these ideas. If you know for a fact that it will be Cased that way you can of course remove the calls to LCase$ that I added.

Finally, I noticed that you are making assumptions about the case of "Site ID", unless there is no possible way it could be cased otherwise, it's best to do a case insensitive comparison.

Make sure you have the Excel interface locked, as running events and screen-updating etc, can slow your code down a lot.

If the loop must always execute once, then you want a post-test loop.

When looping, structure your loop to have to perform as few tests as possible.

Don't use "Activate" or "Select" to move around the ActiveCell, just access the range directly.

Value = ""), instead use the LenB function to check for emptiness, it will execute faster as it's just reading the length of the string header instead of launching into a byte by byte string comparison.

Don't do text comparisons if you can avoid it (Ex.

Don't access properties that are implemented slowly, like more than once(particularly inside a loop) unless you think they may have changes.

I see room for improvement in a couple spots in your code: So my questions are: Why is my newer code so much slower than the original despite mine looking faster and what can I do to speed up the slowness of the code? But how much quicker is it? On the small test is takes 54 seconds compared to the original 45 seconds, and on the larger test my code is slower again at 153 to 130 seconds. So why does the original run through multiple times? We can improve that too (the boiler plate outside the loops is the same). Lets look at the next criteria.Įach logical group is split by a blank row, so the easiest way is to find the next page break, step back until you find the next blank line and insert the new break. These have 6 rows respectively.ġ second isn't so bad. Using the lame Now() style of rough timing this is 1 second slower on my short 4 site report and the more challenging 15 site report. I used these named ranges to meet the first requirement: ' add breaks after each siteįor Each RangeName In ActiveWorkbook.NamesĪctiveCell.Offset(Range(RangeName).Rows.Count - 1, 0).SelectĪll Ranges are prefixed with P_ (for parent). So I added checkboxes on another page and copied the checked items across. As one of the new requirements the people wanting the report were manually removing pages prior to printing. Seeing room for improvement I set about modifying this.

PctProgress.ProgressText = "Set break point " & CStr(passes) & "." & CStr(i) 'Defaults the print area to be the entire sheet

'If this isn't performed beforehand, then the HPageBreaks object isn't available ' Marks that no rows/columns are to be repeated on each page This is the original code (sorry for the length): Public Sub PageBreak(ByRef wstWorksheet As Excel.Worksheet, ByVal pctProgress As ProgressCtl.ProgressControl) The code follows the above format: 2 loops doing those jobs. Group's aren't allowed to broken across pages.This part of the code runs after the main report is generated to set the page breaks in logical positions.

#PAGE BREAK PREVIEW IN EXCEL FOR MAC GENERATOR#

As part of an overhaul of a report generator I saw what I believed to be inefficient code.