MrDavidThorn
asked on
Excel VBA to find data type of chart axis
Hi Experts
I have some VBA and an Excel chart, I want some syntax so if the axis is a date type then it formats that date.
Currently I have
If Instr(selection.ticklabels .Numberfor mat,"yyyy" ) then
'change the number format
this works fine , but if a users runs the code and has a different time local settings it may not work, is there some code like
If selection.ticklabel.number format = xldate then
'change the number format
Many thanks
I have some VBA and an Excel chart, I want some syntax so if the axis is a date type then it formats that date.
Currently I have
If Instr(selection.ticklabels
'change the number format
this works fine , but if a users runs the code and has a different time local settings it may not work, is there some code like
If selection.ticklabel.number
'change the number format
Many thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
This obviously looks at the first series of the chart and checks that the first value is a date. If you wanted to be very careful, you could also check some more of theXValues. If your chart was quite complex and had some series plotted on secondary axes, you might have to check that the first series was relevant by checking which axis it was plotted on, but if you're incontrol of the chart and can guarantee its structure, this will be fine. You have to do the CDate because the XValues show up as numbers (e.g. 40544 = 1st Jan 2011), and you need to convert this to a date first. There's obviously a small risk that the value really is a number in the 40,000's which is why I say it's hard to be totally sure.