chart in excel 2003 with vba

Posted on 2012-03-28
Last Modified: 2012-03-28

I'm having trouble creating the a chart with this data:
A      B      C
date +  time      data

01.01.2012 12:00      12:00            456789

There are several sets of these data. Date and time is always unique, but time is repeating each day. Data consists out of numbers.

I need to have date only on the right side (legend entries), and time (which can be used from first or second column) on the x-axis. So the resulting chart would display for each day a data line.

This problem is how to extract the date only, second problem is how to put the time entries on the X axis?

What is the best way to do this in VBA without using pivot? I'm using Excel 2003.

Thanks in advance.
Question by:Fernando2323
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
  • 4
  • 3
LVL 50
ID: 37775942

I'm trying (and failing) to picture the chart you want to create. If it can't be done manually, then it won't be doable with VBA either.

If you want the date in the legend, then each row will need to be a series.
If each row is a series, then there won't be a line, since it's only one data point per series.
If the time is the same value every day, then you won't see a line, either, since all data points will sit at the same spot.

Post a sample file with a few rows of sample data and let's try to create a meaningful chart manually. Once that has been worked out, it can be done with VBA, too.

But there's no use trying to write code for a chart that does not seem to be possible in the first place.

cheers, teylyn

Expert Comment

ID: 37776422
Hi Fernando,

I was trying to visualise what you were trying to build.  If the date and time are always unique, the 'second time' repeats each day, and the data (your figure 456789 for example) is the other data series, then the date/time + time, and the raw data would seem to be the appropriate data series for your line graph.

In Excel I mocked up a data series for a stock price per day, where the value is captured at set times per day, as follows:
Date              Time    Stock Price
01/01/13       09:00    200
01/01/13       12:00    201
01/01/13       17:00    205
02/01/13       09:00    199
02/01/13       12:00    200
02/01/13       17:00    203   et cetera...

I then copied the data range, and did a Paste Special... Transpose, so that the data came out 'pivoted':

Date                01/01/13   01/01/13   01/01/13
Time                09:00        12:00         17:00
Stock Price       200           201            205

I then selected the pivoted data range, and generated a chart based on that data.  I ended up with a line chart that had the stock price on the y-axis and the combination of date and time on the x-axis (see attached file as an example).

As I was able to do this manually using the above steps, all you need to do is repeat the process with the Macro Recorder switched on, and that will give you the 'barebones' of some VBA code you can use to achieve your goal...  providing I understood your requirements correctly!!

Author Comment

ID: 37776737
hello armchair_scouse

thanks for your reply and efforts. Regarding the raw data this is correct. But as I wrote the chart should display a line for each day, so you can easily compare each day for example the stock price on Monday eight o'clock compared to Tuesday eight o'clock as well. The problem is, the X axis should be of limited range displaying each hour of today only once if there is data available. I will try to provide an example later.
Thanks in advance.
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.


Expert Comment

ID: 37777411

Expert Comment

ID: 37777419
Try the example above... each data series represents one day, with stock prices changing every hour between 9:00 and 17:00.  Macro behind the sheet to build the chart.  I recommend stepping through it, as when I tested it, Excel worked out exactly wheat I needed after just a few lines.

Author Comment

ID: 37779196
Great this looks very good. But still there is one problem: ( how can I transform the original data into the format in which you were using now? Can this be done with transforming or is there any additional calculation necessary?

Accepted Solution

armchair_scouse earned 500 total points
ID: 37780011
To transform the data from my first suggestion (on StockPrice.xls) to the second suggestion (StockPriceSeries.xls) would need some VBA, Transpose on its own would not be able to do that as the second format does not repeat the time headers, it only has them once.

I've written a routine that will do the job, TransformDataSeries, in the attached file.  Not perfect - I'm writing this after waking up in the early hours and not being able to get back to sleep!!  That's my excuse anyway...   :o)

Author Closing Comment

ID: 37780276
Thanks a lot! Appreciate your help.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

707 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