vba create chart

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » VBA Create chart

VBA Create chart

resolvedResolved · Urgent Priority · Version 2007

Pierre has attended:
Excel VBA Intro Intermediate course

VBA Create chart

Hi,

i manage to create one chart with the following line:

.SetSourceData Source:=Sheets("Sheet1").Range("B2:I76")

I want to create several graph with different data zone in the same CurrentRegion (So, I cannot basically use CurrentRegion), I tried
using variable and loop with:
Sub Creategraph()

Dim intColumnCount As Integer

Dim intRowCount As Integer

Dim intEndData As Integer

Dim aChart As Chart

For intRowCount = 1 To Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Count

If Sheets("Sheet1").Cells(intRowCount, 1).Value = "Date / Time" Then

Set aChart = Charts.Add

intEndData = intRowCount + 72

With aChart

.ChartType = xlXYScatterSmooth

.SetSourceData Source:=Sheets("Sheet1").Range(Cells(intRowCount, 2), Cells(intEndData, 9))

.HasTitle = True

.ChartTitle.Text = "Reactor 1"

End With

End If

Next intRowCount

End Sub

RE: VBA Create chart

Hi Pierre

Thanks for your question

It is difficult to advise you effectively without seeing the data. Could you recreate the layout of the chart data, and indicate the different ways in which you want it charted. If you create a simple spreadsheet and email it to
sw@stl-training.co.uk

I will look into the problem today

Regards

Stephen

RE: VBA Create chart

I have just sent an Email to you, thanks for resolving this. If you have any question reply to my Email.

Regards,

Pierre

Edited on Fri 8 Jul 2011, 09:04

RE: VBA Create chart

Hi Pierre

Thanks for the file

This is something of a puzzle, as the error seems to make no sense, in that it seems to successfully create the chart.

Can I suggest that you place at the very top of your procedure the line

On Error Resume Next


This will ignore the error message and will carry on running the code. I have done this on the file you sent me and the charts all seem to be created and populated. I need you to check to see if the results are accurate

This is not a solution as we need to find the reason for the error, but this will help narrow down the possibilities

Regards

Stephen

RE: VBA Create chart

Hi,

I don't know if you are still working on this but I managed to solve this issue by adding this line:

Set aChart = aChart.Location(Where:=xlLocationAsObject, Name:="Sheet1")

This is creating a chart in Sheet 1 and after I put this line to move it at the end :

.Location Where:=xlLocationAsNewSheet

Apparently that is solving the issue and I don't need anymore On Error Resume Next.

Regards,

Pierre

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Access tip:

Random sampling

Data is th key to a good database, therefore checking is essential.

A good way of checking data is by random sampling. You can do this in a query, by typing the follow:
1) In the Field box create a RandonID field eg. Randon Id: Rnd(fieldname])

2) sort the field
3)Right click and chage the properties for To value to be the number of randon records you want to see.

4) Change the set to Show row for Randon ID to be False, add all the other fields you want to see and the run the query.

View all Access hints and tips


Server loaded in 0.09 secs.