View page breaks excel for mac code#
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.
View page breaks excel for mac generator#
As part of an overhaul of a report generator I saw what I believed to be inefficient code.
0 kommentar(er)
