graphs- newb

[drewdaman]

hello,

i'm really a c++ person.. but i have to do something quickly in excel and vba..

i was wondering.. can i draw excel graphs from vba and if you know of any tutorials where i can quickly read up on how its done (i'm not completely new to vba- have used it a little with ms access and even less wtih excel). so at the moment, my question is, can this (ie graph manipulation from vba) be done? i might want to do things like change the points plotted on a particular axis. is there any concept like a recordset to hold the points plotted on a graph? if not, how do you use vba code to manipulate points on a graph?

i have used vba in the past with ms access.. and a little with excel.. but its been a long time..

this is a little bit urgent, so i really hope you guys can point me in the right direction quickly!

thanks a lot!

 

[PHV]

As a starting point you may play with the macrorecorder.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[drewdaman]

hmm... thanks for the reply..
but i might want to do things like delete graphs, change axes (ie what goes on what axis), change the values associated with the points on the graph... doesn't seem like the macrorecorder will be capable of this.. am i wrong?

thanks.

 

[PaultheS]

I don't know if you're right or wrong, but there's one quick and easy way to find out...

 

[xlbo]

yes - you are wrong - the macro recorder will record you doing all of that and more - do it manually and the macro recorder will show you how to automate it.

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[drewdaman]

thank you for your replies.. and sorry for my previous, hasty reply.. yes i tried it and i can see the "Code" generated... very useful.

now, what i would like to do, however, is to calculate the values that i want to put on the chart. i do not want to display the values on the spreadsheet itself (sounds moronic, but thats just teh way i need it!).. so, for each point that i want to plot, i want to calculate it using some forumula(e) adn tell it to graph it. can i do this?

the macro recorder gave me this line:
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("A2:B6")

but i want to set the source manually, ie calculate points, and plot. the reason that i brought up a recordset previously is that i have some experience wtih recordsets.. i don't think that they will be appropriate here, but i was looking for a similar concept to bring here.. to be able to calculate, not display, plot.

thanks!

 

[drewdaman]

ps. i want to use an xy (scatter) chart.. the macro shows me how to create this, but i just wanted to let you know in case it makes a difference to my query.

thanks.

 

[xlbo]

I believe that chart series can be created by using an array / recordset object instead of a range

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[drewdaman]

hmm... thanks.. but do you think you might be able to post a code snippet where that is done?
i haven't been able to find much on the net

     Charts.Add
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    'not sure what to do here!
    ActiveChart.SetSourceData

 

[SkipVought]

Hi,

Look at the SeriesCollection Collection Properties/Methods.

Once a SeriesCollection (BTW there is a SeriesCollection Collection and SeriesCollection) has been ADDED to the Chart Object, you can ASSIGN values as an array.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[SkipVought]

also FYI...

Charts & VBA faq707-4811

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[drewdaman]

could you please post some sample code?

for example, if i want to graph y=x^2

x y
1 1
2 4
3 9

how would i bind the series colelction to the chart and how would i manipulate the data? i am bad with vb(a) as you might have noticed!

thanks!

 

[drewdaman]

also, when i say:
Dim MySeries As New ChartSeries

i get the error:
Compile Error: User-defined type not defined

any ideas?

thanks.

 

[drewdaman]

using office 2000

 

[SkipVought]

ChartSeries is not an object

It is SeriesCollection. Look at the NewSeries Method

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[drewdaman]

so.. i figured out how to do it.. so i thought i would post what i found in case any other wayward soul comes by this post:

Sub ch()
    
    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    

     Dim xvals(2) As Long
     xvals(0) = 0
     xvals(1) = 1
     xvals(2) = 0
     
     Dim yvals(2) As Long
     yvals(0) = 0
     yvals(1) = 1
     yvals(2) = 2
     
    Dim MyNewSrs As Series
    Set MyNewSrs = ActiveChart.SeriesCollection.NewSeries
    With MyNewSrs
        .Name = "Fred"
        .Values = yvals '"=Sheet1!Y_Range"
        .XValues = xvals 'Array(1, 2, 3)
        
    End With


End Sub

 

[xlbo]

Thanks for posting back with your solution.

Much appeciated

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

...and out of much travail. ;-)

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}