Solved

chart in excel 2003 with vba

Posted on 2012-03-28
8
305 Views
Last Modified: 2012-03-28
Hello,

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

example:
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.
0
Comment
Question by:Fernando2323
  • 4
  • 3
8 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37775942
Hello,

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
0
 
LVL 9

Expert Comment

by:armchair_scouse
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!!
StockPrice.xls
0
 

Author Comment

by:Fernando2323
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.
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 9

Expert Comment

by:armchair_scouse
ID: 37777411
0
 
LVL 9

Expert Comment

by:armchair_scouse
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.
0
 

Author Comment

by:Fernando2323
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?
0
 
LVL 9

Accepted Solution

by:
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)
TransformGraphData.xls
0
 

Author Closing Comment

by:Fernando2323
ID: 37780276
Thanks a lot! Appreciate your help.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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 Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

776 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