Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Graphs in Excel

Status
Not open for further replies.
junkie8

junkie8

Technical User
Aug 9, 2009
32
US
I want to plot graphs in excel using VBA. How do I do that? I have no idea how to do it?
 
Sort by date Sort by votes
Did you try the macro recorder ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
no, i have never used that either.
 
Upvote 0 Downvote



What PHV means is, chart you data from your source data.

Then turn on your macro recorder and do it again from scratch.

Observe your recorded code and modify to suite.

Post back with your recorded code and specific questions.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
okay, i managed to get some code with the help of macro reader, but the problem now is that I don't always have chart 14 in an excel sheet. How do I modify the code so that it does not have to select by an automatically generated chart name? Is there a way to give the chart a name that I want when it is being created?

ActiveSheet.ChartObjects("Chart 14").Activate
 
Upvote 0 Downvote



Will you ALWAYS only have ONE CHART on the sheet?
Code: 
ActiveSheet.ChartObjects(1).Activate
Or is it the latest chart that was added?
Code: 
ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Activate
FYI: I avoid using Active ANYTHING or the Activate method, in most instances.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Ok, i modified the code a bit and now it does not give me any charts at all in the sheet.

Sub DotPlot()
'
' DotPlot Macro
' To make dot plots
'
Workbooks("NavStructure.xlsm").Sheets("Patient422_SaveMeal").Activate

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Patient422_SaveMeal'!$C$2:$V$50")
ActiveChart.ChartType = xlXYScatter

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='Patient422_SaveMeal'!$C$2:$C$50"
ActiveChart.SeriesCollection(1).Values = "='Patient422_SaveMeal'!$D$2:$D$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "='Patient422_SaveMeal'!$E$2:$E$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Values = "='Patient422_SaveMeal'!$F$2:$F$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).Values = "='Patient422_SaveMeal'!$G$2:$G$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(5).Values = "='Patient422_SaveMeal'!$H$2:$H$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(6).Values = "='Patient422_SaveMeal'!$I$2:$I$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(7).Values = "='Patient422_SaveMeal'!$J$2:$J$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(8).Values = "='Patient422_SaveMeal'!$K$2:$K$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(9).Values = "='Patient422_SaveMeal'!$M$2:$M$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(10).Values = "='Patient422_SaveMeal'!$N$2:$N$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(11).Values = "='Patient422_SaveMeal'!$O$2:$O$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(12).Values = "='Patient422_SaveMeal'!$P$2:$P$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(13).Values = "='Patient422_SaveMeal'!$Q$2:$Q$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(14).Values = "='Patient422_SaveMeal'!$R$2:$R$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(15).Values = "='Patient422_SaveMeal'!$L$2:$L$50"
For i = 1 To 15
ActiveChart.Legend.Select
ActiveSheet.ChartObjects(1).Activate
ActiveChart.Legend.LegendEntries(i).Select
ActiveSheet.ChartObjects(1).Activate
ActiveSheet.ChartObjects(1).Activate
ActiveChart.SeriesCollection(i).Select
Selection.MarkerStyle = 8
Next i
ActiveSheet.ChartObjects(1).Activate
ActiveChart.Legend.Select
ActiveSheet.ChartObjects(1).Activate
Selection.Delete
End Sub
 
Upvote 0 Downvote

You might want to check out faq707-4811.



Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote


Give this a try...
Code: 
Sub DotPlot()
'
' DotPlot Macro
' To make dot plots
'
    Workbooks("NavStructure.xlsm").Sheets("Patient422_SaveMeal").Activate

    ActiveSheet.Charts.Add
    ActiveChart.SetSourceData Source:=Range("'Patient422_SaveMeal'!$C$2:$V$50")
    ActiveChart.ChartType = xlXYScatter
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "='Patient422_SaveMeal'!$C$2:$C$50"
    ActiveChart.SeriesCollection(1).Values = "='Patient422_SaveMeal'!$D$2:$D$50"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Values = "='Patient422_SaveMeal'!$E$2:$E$50"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).Values = "='Patient422_SaveMeal'!$F$2:$F$50"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(4).Values = "='Patient422_SaveMeal'!$G$2:$G$50"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(5).Values = "='Patient422_SaveMeal'!$H$2:$H$50"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(6).Values = "='Patient422_SaveMeal'!$I$2:$I$50"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(7).Values = "='Patient422_SaveMeal'!$J$2:$J$50"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(8).Values = "='Patient422_SaveMeal'!$K$2:$K$50"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(9).Values = "='Patient422_SaveMeal'!$M$2:$M$50"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(10).Values = "='Patient422_SaveMeal'!$N$2:$N$50"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(11).Values = "='Patient422_SaveMeal'!$O$2:$O$50"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(12).Values = "='Patient422_SaveMeal'!$P$2:$P$50"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(13).Values = "='Patient422_SaveMeal'!$Q$2:$Q$50"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(14).Values = "='Patient422_SaveMeal'!$R$2:$R$50"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(15).Values = "='Patient422_SaveMeal'!$L$2:$L$50"
    
    Dim ser As Series
    
    For Each ser In ActiveChart.SeriesCollection
        ser.MarkerStyle = 8
    Next
'    For i = 1 To 15
'        ActiveChart.Legend.Select
'        ActiveSheet.ChartObjects(1).Activate
'        ActiveChart.Legend.LegendEntries(i).Select
'        ActiveSheet.ChartObjects(1).Activate
'        ActiveSheet.ChartObjects(1).Activate
'        ActiveChart.SeriesCollection(i).Select
'        Selection.MarkerStyle = 8
'    Next i
'    ActiveSheet.ChartObjects(1).Activate
'    ActiveChart.Legend.Select
'    ActiveSheet.ChartObjects(1).Activate    '[b]
'WHY are you deleting the legend you just formatted???
'    Selection.Delete                       '[/b]
End Sub

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote



You'll notice that the FAQ contains information about controls for the chart.

I'm guessing that you have one sheet for each patient. BAD DESIGN STRATEGY!

Your source data would be much better maintained and useable in ONE TABLE, with an additional column for Patient Name or ID.
Right now you have 48 rows of data per patient. If you had 100 patients, that would be 480 rows. You could AutoFilter on Patient, controlling the criterial with a control, and achieve charting for any patient on one sheet with one chart.


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Actually, I think that's 4800.

_________________
Bob Rashkin
 
Upvote 0 Downvote



What's an order of magnitude, between friends?
[blush]
Reminds me of the pair of snakes, cryin their eyes out. Noah says, "What's the problem? We all got out on dry land!" And they replied, "God said, 'Be fruitful and multiply,' and we're only adders!"

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
thanks for the tips, but right now my problem is that I am getting object doesn't support this property or method at this line:

ActiveChart.SetSourceData Source:=Range("'Patient422_SaveMeal'!$C$2:$V$50")

This was not a problem before.
 
Upvote 0 Downvote


Code: 
 ActiveChart.SetSourceData Source:=Worksheets("Patient422_SaveMeal").Range("$C$2:$V$50")

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
i am getting the same error message, if it helps here is the entire code again.

Sub DotPlot()
'
' DotPlot Macro
' To make dot plots
'
Workbooks("NavStructure.xlsm").Sheets("Patient422_SaveMeal").Activate

ActiveSheet.Charts.Add
ActiveChart.SetSourceData Source:=Worksheets("Patient422_SaveMeal").Range("$C$2:$V$50")

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='Patient422_SaveMeal'!$C$2:$C$50"
ActiveChart.SeriesCollection(1).Values = "='Patient422_SaveMeal'!$D$2:$D$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "='Patient422_SaveMeal'!$E$2:$E$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Values = "='Patient422_SaveMeal'!$F$2:$F$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).Values = "='Patient422_SaveMeal'!$G$2:$G$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(5).Values = "='Patient422_SaveMeal'!$H$2:$H$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(6).Values = "='Patient422_SaveMeal'!$I$2:$I$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(7).Values = "='Patient422_SaveMeal'!$J$2:$J$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(8).Values = "='Patient422_SaveMeal'!$K$2:$K$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(9).Values = "='Patient422_SaveMeal'!$M$2:$M$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(10).Values = "='Patient422_SaveMeal'!$N$2:$N$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(11).Values = "='Patient422_SaveMeal'!$O$2:$O$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(12).Values = "='Patient422_SaveMeal'!$P$2:$P$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(13).Values = "='Patient422_SaveMeal'!$Q$2:$Q$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(14).Values = "='Patient422_SaveMeal'!$R$2:$R$50"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(15).Values = "='Patient422_SaveMeal'!$L$2:$L$50"

Dim ser As Series

For Each ser In ActiveChart.SeriesCollection
ser.MarkerStyle = 8
Next

End Sub
 
Upvote 0 Downvote


tested
Code: 
Sub DotPlot()
'
' DotPlot Macro
' To make dot plots
'
    Sheets("Patient422_SaveMeal").Activate

    Charts.Add
    ActiveChart.SetSourceData Source:=Range("$C$2:$D$50")
    

    
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = Range("$C$2:$C$50")
    ActiveChart.SeriesCollection(1).Values = Range("$D$2:$D$50")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).XValues = Range("$C$2:$C$50")
    ActiveChart.SeriesCollection(2).Values = Range("$E$2:$E$50")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).XValues = Range("$C$2:$C$50")
    ActiveChart.SeriesCollection(3).Values = Range("$F$2:$F$50")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(4).XValues = Range("$C$2:$C$50")
    ActiveChart.SeriesCollection(4).Values = Range("$G$2:$G$50")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(5).XValues = Range("$C$2:$C$50")
    ActiveChart.SeriesCollection(5).Values = Range("$H$2:$H$50")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(6).XValues = Range("$C$2:$C$50")
    ActiveChart.SeriesCollection(6).Values = Range("$I$2:$I$50")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(7).XValues = Range("$C$2:$C$50")
    ActiveChart.SeriesCollection(7).Values = Range("$J$2:$J$50")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(8).XValues = Range("$C$2:$C$50")
    ActiveChart.SeriesCollection(8).Values = Range("$K$2:$K$50")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(9).XValues = Range("$C$2:$C$50")
    ActiveChart.SeriesCollection(9).Values = Range("$M$2:$M$50")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(10).XValues = Range("$C$2:$C$50")
    ActiveChart.SeriesCollection(10).Values = Range("$N$2:$N$50")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(11).XValues = Range("$C$2:$C$50")
    ActiveChart.SeriesCollection(11).Values = Range("$O$2:$O$50")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(12).XValues = Range("$C$2:$C$50")
    ActiveChart.SeriesCollection(12).Values = Range("$P$2:$P$50")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(13).XValues = Range("$C$2:$C$50")
    ActiveChart.SeriesCollection(13).Values = Range("$Q$2:$Q$50")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(14).XValues = Range("$C$2:$C$50")
    ActiveChart.SeriesCollection(14).Values = Range("$R$2:$R$50")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(15).XValues = Range("$C$2:$C$50")
    ActiveChart.SeriesCollection(15).Values = Range("$L$2:$L$50")
    
    ActiveChart.ApplyCustomType xlXYScatter
    
    Dim ser As Series
    
    On Error Resume Next
    
    For Each ser In ActiveChart.SeriesCollection
        ser.MarkerStyle = 8
    Next

End Sub

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
611
remeng
remeng
sal21
  • Question
scrping in listbox in a web page
Replies
0
Views
226
sal21
sal21
huiettcm
  • Locked
  • Question
Excel UserForm Window 'Focus' Event Listener 1
Replies
14
Views
372
huiettcm
huiettcm
f0rg3tfu1
  • Locked
  • Question
VBA Error in Excel
Replies
11
Views
346
combo
combo
remeng
  • Locked
  • Question
Run PowerShell Commands from Excel VBA
Replies
14
Views
2K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top