Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA to find data type of chart axis

Posted on 2011-09-08
2
Medium Priority
?
704 Views
Last Modified: 2012-08-14
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.Numberformat,"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.numberformat  = xldate then
  'change the number format

Many thanks
0
Comment
Question by:MrDavidThorn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36503142
I don't think you can do this a completely foolproof way, but I have used this in the past to check that the category axis is a date axis:

Sub Macro3()
    
    Dim s As Series
    With ActiveChart
        Set s = .SeriesCollection(1)
        With s
            If IsDate(CDate(.XValues(1))) Then
               MsgBox "It's a date axis"
            End If
        End With
    End With


End Sub

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.
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 2000 total points
ID: 36504984
Just realised revisiting this that the code will only work if the chart axis is date-based - you will get a run time error on the CDate if it isn't. This is better:

Sub Macro3()
    
    Dim s As Series
    Dim v As Variant
    With ActiveChart
        Set s = .SeriesCollection(1)
        With s
            On Error Resume Next
            v = CDate(.XValues(1))
            If Err.Number = 0 Then
               MsgBox "It's a date axis"
            End If
            On Error GoTo 0
        End With
    End With


End Sub

Open in new window

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question