search

OfficeDev / VBA-content

All content in this repository has been migrated to a new repository (https://github.com/MicrosoftDocs/VBA-Docs). Please do not open issues or pull requests here. We are no longer monitoring this content.
https://github.com/MicrosoftDocs/VBA-Docs
Creative Commons Attribution 4.0 International
564 stars 434 forks source link

Mistake in the VBA-Excel documentation #230

Closed BehindTheMath closed 8 years ago

BehindTheMath commented 8 years ago

I am trying to change the position of existing page breaks in Excel with VBA. According to the documentation (VPageBreak.Location, HPageBreak.Location), this would be achieved with the following code:

Worksheets(1).VPageBreaks(1).Location = Worksheets(1).Range("e5")
Worksheets(1).HPageBreaks(1).Location = Worksheets(1).Range("e5")

However, when I tried that syntax, I got no results. No errors, but no change either.

After some Googling, it seems I'm not the only one having this issue:
http://www.pcreview.co.uk/threads/recorded-macro-to-set-page-breaks-generates-error.998729/ https://groups.google.com/forum/#!topic/microsoft.public.excel.programming/M7jSrjlvtT8 http://www.xtremevbtalk.com/archive/index.php/t-240387.html http://www.excelforum.com/excel-programming-vba-macros/473696-moving-horizontal-pagebreak.html https://www.mathworks.com/matlabcentral/newsreader/view_thread/299034?requestedDomain=www.mathworks.com

I did see a suggestion here to use Set, while in Page Break Preview mode:

Set Worksheets(1).HPageBreaks(1).Location = Worksheets(1).Range("e5")

That worked for HPageBreak. However, with VPageBreak, I got the following error:

Run-time error '1004': Application-defined or object-defined error

If you record a macro and change a horizontal page break, VBA generates a macro using Set ActiveSheet.HPageBreaks(1).Location = Range("e5"), whereas if you change a vertical page break, it uses ActiveSheet.VPageBreaks(1).DragOff.

In summary:
It seems to me that VPageBreak.Location is read-only, and the documentation, along with its example, is incorrect. The only way to change the location of a VPageBreak is by using VPageBreak.DragOff. HPageBreak.Location can be written to, however, only while in Page Break Preview mode, and only with Set.

laschultz commented 8 years ago

BehindTheMath, Thank you for bringing this to our attention. Your contribution helps create more technically accurate documentation for the VBA reference. I'm made the code example changes to reflect your suggestions. Office Scale Team

BehindTheMath commented 8 years ago

Perhaps I'm missing something, but I don't see any changes to the above referenced pages.

laschultz commented 8 years ago

They publish to our MSDN repot, and now the changes are reflected in GitHub. Thanks!

BehindTheMath commented 8 years ago

I see the changes now. However, I think my comments were misunderstood.

VPageBreak.Location: According to the code example in the changes, VPageBreak.Location can be written to with Set. I believe this is incorrect, and VPageBreak.Location is read-only. Additionally, the note first states that VPageBreak.Location can be written to, then it states that one must use VPageBreak.Dragoff. This is contradictory.

HPageBreak.Location: The note says that HPageBreak.Location can be used to set the vertical page-break location, which is clearly a mistake. Additionally, the note states that to change HPageBreak.Location, one must use HPageBreak.Dragoff, implying that HPageBreak.Location itself is read-only. I believe this is incorrect, and HPageBreak.Location can be written to with Set, as shown in the code example.

I've made changes which I believe represent the actual behavior and submitted a pull request.