1

I dont undestand the why the behaviour of number format on y axis for a diagram is different in Excel 2016 compared with Excel 2010.

For example if I call the Number format property as mentioned bellow, I'm getting diffrent result on Excel 2016 compared with Excel 2010.

 ? myDiag.Diagramms.Item(1).Chart.Axes(xlCategory).TickLabels.NumberFormat
' Result in Excel 2010: 0.00


 ? myDiag.Diagramms.Item(1).Chart.Axes(xlCategory).TickLabels.NumberFormat
' Result in Excel 2016: #.000

On an diagram from Excel 2010 the y axes value are displayed :

2; 1.5; 1.00;...; -1.00; -1.5; -2

instead in Excel 2016 the values are displayed strangely:

002; 002; 001; ...; -001; -002; -002

I have tryed clearing number formatting, I have tried setting diffrent formats in Excel 2016 without any result. Only number formats like -002 or other strange formatting are being displayed.

Regards,

SocketM
  • 507
  • 1
  • 14
  • 28
  • Which format did you define for the axes? Default should be "General". – Pᴇʜ May 04 '18 at 12:58
  • Something like that: .TickLabels.NumberFormat = "[red]#.00" or .TickLabels.NumberFormat = "[red]0.00" – SocketM May 04 '18 at 13:06
  • So to understand you correctly you define in 2010 `.TickLabels.NumberFormat = "[red]#.00"` and then read in 2016 `?.TickLabels.NumberFormat` and it changed magically? If not please read [mcve] and provide a full working example. – Pᴇʜ May 04 '18 at 13:10
  • If i'm using "General" number formatting the numbers are displayed almost corectly however without 2 decimal places after 0. Instead of 2.00 I'm getting just 2 or instead of 1.50 I'm getting just 1.5. – SocketM May 04 '18 at 13:10
  • No the original formatting being used was : .TickLabels.NumberFormat = "0.00" in 2010 however the same code in 2016 it started to show on Imediate Window #.000. – SocketM May 04 '18 at 13:13
  • 1
    Same computer? Same OS localization (both English)? And what decimal separator is defined in windows? Just some ideas to start with. – Pᴇʜ May 04 '18 at 13:16
  • No not on the same machine, different machines. Moreover diffrent OS (Office 2010 runs on Win7 and Office 2016 runs on Win10). I have to check the after decimal separator. – SocketM May 04 '18 at 13:19
  • 1
    Is the issue reproducible when you start with a complete new chart without copy/pasting? So does it always happen with any file? Testing this we can distinguish between a systematic issue with computer/version etc. and a file related issue. – Pᴇʜ May 04 '18 at 13:23
  • I have compared Regional setting on both machines and they are in in the same region and use same decimal separator which is "," . I have changed my configuration for TickLabel to .TickLabels.NumberFormat = "#0,00" and now the result seems to be almost corect except for the fact that 0,50 is displayed like ,50. – SocketM May 04 '18 at 13:45
  • If I try the formatting #0,00 over an array of numbers with decimal places in Excel it works however it's the same formatting type not working in VBA code .TickLabels.NumberFormat = "#0,00" – SocketM May 04 '18 at 14:16
  • 1
    I managed to solve it using #0,00. Your advices were invaluable PEH. I have missed some old formatting in the code :-( – SocketM May 04 '18 at 14:23
  • Well, this was more guessing into any direction. I probably just asked the right questions. – Pᴇʜ May 04 '18 at 15:53

0 Answers0