Simon has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
VBA: Number format and screen zoom
During the course, when the columns were autofit and the zoom was set to 75, some numbers were presented in scientific notation. This did not happen if the Zoom was a higher number. Why sgould the zoom affect the number format?
RE: VBA: Number format and screen zoom
Hi Simon. I think you have put your finger on an Excel bug! There are some reports of data pasted into a cell - that's already been specifically formatted - refusing to display in that format. Excel has a fifteen digit limit, after which it tries to reformat the number into scientific notation to deal with it (and, indeed, render it on the screen) more easily. The zoom function often causes (and solves) some rendering artefacts on the screen, and it may be on a zoom to 75% Excel "sees" a large number and reformats it into scientific notation. Personally, I've never encountered a situation which would require this as a design feature in Excel!
Try reformatting the cell as Numbers with 0 decimal places and see if that stops the reformatting. Beyond that, a work around - involving sticking the data in another worksheet and bringing it into the master sheet by links, in my opinion - might be worth investigating.
Hope this helps,
Anthony
RE: VBA: Number format and screen zoom
Thanks Anthony.
Fortunately, this issue is not repeated when I run the code on my work PC or my home PC. As it occurred only in the training environment it is not something that I need to resolve.
Nonetheless, I will file your reply in case it should ever crop up. I wonder if another possible resolution is simply to write the code in a different order so that the format instructions occur at a relatively different time to the data instructions.
Sadly, or perhaps not, until I can recreate the bug, I cannot test the theroretical fix!
Cheers,
S!
