[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


chart in excel 2003 with vba

Posted on 2012-03-28
Medium Priority
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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

649 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