Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Creating graph using vba Results

Hello again,

What I am currently working on, is forming a graph of a specific raw data sheet. There are 2 area's of data that go into this graph. One is temperature (x-axis) and one is Tan Delta (y-axis). Once this graph is formed what I want to do, is find the maximum tan delta value from the raw data sheet. Then find the temperature value that is associated in the column to the left of it. I want to take that tan delta value and put it into a textbox on the graph.

I can't figure out how to insert a textbox with the value from the raw data sheet onto the graph using VBA. Any help with this would be awesome

Thanks a lot!

The example code I have forming this graph is shown below.


	VB:
	
 tgchart() 
    Dim x As Integer 
    x = 0 
    Dim y As Integer 
    y = 0 
    Dim LastColumn As Integer 
    LastColumn = 0 
    Dim p As Integer 
    p = 1 
    Dim tgchart As Chart 
    Dim other As Range 
    Dim frequency As Range 
    Dim title As String 
     ' Set various application properties.
    Application.DisplayAlerts = False 
    Application.ScreenUpdating = False 
    Application.EnableEvents = False 
     
    ActiveSheet.Shapes.AddChart.Select 
    ActiveChart.Parent.Name = "TgGraph" 
    ActiveChart.SetSourceData Source:=Range("'Raw Data'!$A$1:$A$1") 
    Set tgchart = ActiveChart 
    With tgchart 
        ActiveChart.ChartType = xlXYScatterSmooth 'Type of graph
         
         ' Remove any series created with the chart
        Do Until .SeriesCollection.Count = 0 
            .SeriesCollection(1).Delete 
        Loop 
         
        ActiveChart.ApplyLayout (1) 
        ActiveChart.ChartTitle.Select 
        ActiveChart.ChartTitle.Text = "Eplexor - Tg" 'Chart Title
        ActiveChart.Axes(xlValue).AxisTitle.Select 
        ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Temperature (°C)" 'X-axis
        ActiveChart.Axes(xlValue).AxisTitle.Select 
        ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Tan Delta" 'Y-axis
        ActiveChart.Legend.Select 
        Selection.Position = xlBottom ' Moves legend to bottom of chart
         
         ' Loops through raw data
        For y = 1 To 1000 
            Cells(1, y).Select 
            If Cells(1, y).Value = "" Then 
                Exit For 
            End If 
            title = Cells(1, y).Value 'Creates Title of graph
             
            Cells(4, y).Select 
            Range(Selection, Selection.End(xlDown)).Select 
            Set frequency = Selection 'Selects x-axis data for chart
            Cells(4, y + 1).Select 
            Range(Selection, Selection.End(xlDown)).Select 
            Set other = Selection 'Selects y-axis data for chart
             
             ' Plugs all data selected in previous section into chart
            Set srs = .SeriesCollection.NewSeries 
            With srs 
                .Name = title 
                .Values = other 
                .XValues = frequency 
            End With 
             
            y = y + 1 '# of cells until next (0C) graph
        Next y 
    End With 
    ActiveSheet.ChartObjects("TgGraph").Activate 
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Tg Chart" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Sample Data would look something like this, but with more numbers:

Temperature Tan Delta

21.42 1.1234
-321.21 1.3453
-7.77 1.0432
9.543 2.9322

Hi,

I want to create excel graph using VBA and found difficulties on chart name set to default.

the script is like this :
ActiveSheet.ChartObjects("Chart 31").Activate

Can I set my own name of the chart instead of "Chart 31" (excel default) ?

thanks

Hi Guys,

Would like to make a scatter diagram but with diffrent seires depending on a value in a table...

My table has 3 column...
ID (needs to be plotted in a diffrent colour/shape depending on it's ID)
Qty (y axis value)
Date (x axis value)

the table is 61 rows long starting at P8 (excluding titles)

The data in the tables are constantly changing, On a button press (linked to a macro) I'd like it to create a scatter diagram with the above requirements after deleting the previous chart (or would it be easier to overwrite what rows are in what sieres?)

thanks for any help,

File attached for example!

Hi guys,

I've done a bit of VBA before but this is my first attempt at making graphs.

First of all:
I can't seem to get excel template to fit my needs, does this mean it can't be done in VBA either, or can VBA do more?

I'm after a pie chart with title = "Stock"
each section called the value of cells F18 to F27
with two values attached to each section. values of J18 to J27 and K18 to K27.

I know it's odd having two values on a pie chart but the J and K columns are directionally proportional to each other, although the proportion is changing I could get the current proportion as a variable.

Is there anyway to get this Pie chart, and preferably a data table underneath with the sector titles, J column value and K column value?

Thanks for any help

Hi folks,
I'm baffled- I'm writing a macro to pull a data table into an excel
sheet & create a bargraph of those results. The number of records in
the data table are variable (say between 2 and 50).

I'm trying to define a dynamic bar graph using VBA. The X values are
in col C and the text Y vlaues are in col A. (both start in row 3) The
values in column B are necessary, but have nothing to do with the
graph. I'm having trouble using variables to define the source data as
two, non adjacent ranges.

I've had problems just including the 50 cells in the data table as it
includes blank
spaces in the chart.

The code below builds the graph based on the X values starting in cell
C3. It just numbers them on the Y axis, not by the corresponding text
values....

Any ideas?
Thanks
pim

Dim sheetName as String 'name of the sheet where the data table
Dim NoRec As Double 'Number of records returned in query
Dim Graph As ChartObject 'Bar graph

NoRec = Application.WorksheetFunction.CountA(Columns("A:A" ))
Set Graph = ActiveSheet.ChartObjects.Add _
(Left:=285, Width:=548, Top:=40, Height:=825)
Graph.Chart.SetSourceData
Source:=Sheets(sheetName).Range(Cells(3, 3), Cells(NoRec, 3))

Graph.Chart.ChartType = xlBarClustered
ActiveSheet.ChartObjects(1).Activate

ActiveChart.HasLegend = False
ActiveChart.HasTitle = False
With ActiveChart.ChartGroups(1)
.Overlap = 0
.GapWidth = 140
.HasSeriesLines = False
End With
With ActiveChart.ChartGroups(1)
.Overlap = 0
.GapWidth = 140
.HasSeriesLines = False
End With
'Y axis- text names format
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 1
.TickMarkSpacing = 1
.AxisBetweenCategories = True
.ReversePlotOrder = True
.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlLow
End With
'X axis-PI values Format
With ActiveChart.Axes(xlValue)
.TickLabelPosition = xlHigh
End With
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With

--
Pim
------------------------------------------------------------------------
Pim's Profile: http://www.excelforum.com/member.php...o&userid=27565
View this thread: http://www.excelforum.com/showthread...hreadid=470956

I'm working with a standard data set, but an internal customer who wants to
be able to slice the data a whole bunch of different ways. I could lay out
every level of analysis and populate it across multiple worksheets (enough
to capture: raw counts for each indicator, then percents for each indicator,
then summary groups of indicators, then total; by person and overall; and by
each of three shifts or overall), but that doesn't seem terribly useful.

My other option is just to keep the data in memory, and crunch the data for
whatever analysis my customer wants to see. That way if he wants another
graph, I can just create it off the memory array, and not worry about
whether I have to change the structure of large sheets of data.

However, while I've updated ranges on worksheets that feed into graphs using
VBA, I've never created whole charts on the fly. Are there any FAQs or web
sites that give lots of detail on how to do this easily, so I can determine
whether it is realistic for me to come up with an acceptable product
quickly, vs. just pumping the data into worksheets?

Thanks for any info,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Hi all,

Very new at using vba and dont have a clue!!

Need a macro/procedure which will search through a list of excel workbooks, extract the required information and provide a graph, bar or line. The information required should be selectable ie. dates, types, times.

Can anyone poiint me in the right direction....??!!

Many thanks

Hi folks,
I'm baffled- I'm writing a macro to pull a data table into an excel sheet & create a bargraph of those results. The number of records in the data table are variable (say between 2 and 50).

I'm trying to define a dynamic bar graph using VBA. The X values are in col C and the text Y vlaues are in col A. (both start in row 3) The values in column B are necessary, but have nothing to do with the graph. I'm having trouble using variables to define the source data as two, non adjacent ranges.

I've had problems just including the 50 cells in the data table as it includes blank
spaces in the chart.

The code below builds the graph based on the X values starting in cell C3. It just numbers them on the Y axis, not by the corresponding text values....

Any ideas?
Thanks
pim

Dim sheetName as String 'name of the sheet where the data table
Dim NoRec As Double 'Number of records returned in query
Dim Graph As ChartObject 'Bar graph

NoRec = Application.WorksheetFunction.CountA(Columns("A:A"))
Set Graph = ActiveSheet.ChartObjects.Add _
(Left:=285, Width:=548, Top:=40, Height:=825)
Graph.Chart.SetSourceData Source:=Sheets(sheetName).Range(Cells(3, 3), Cells(NoRec, 3))

Graph.Chart.ChartType = xlBarClustered
ActiveSheet.ChartObjects(1).Activate

ActiveChart.HasLegend = False
ActiveChart.HasTitle = False
With ActiveChart.ChartGroups(1)
.Overlap = 0
.GapWidth = 140
.HasSeriesLines = False
End With
With ActiveChart.ChartGroups(1)
.Overlap = 0
.GapWidth = 140
.HasSeriesLines = False
End With
'Y axis- text names format
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 1
.TickMarkSpacing = 1
.AxisBetweenCategories = True
.ReversePlotOrder = True
.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlLow
End With
'X axis-PI values Format
With ActiveChart.Axes(xlValue)
.TickLabelPosition = xlHigh
End With
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With

Hello Experts,

Please help me with a VBA code that creates graphs for the huge data set that I have attached. So there are 10 worksheets in the workbook and I have 17 data sets in each of worksheet. What I essentially want, is to create line graphs for each of the data set in all the sheets and populate the graphs from cell A3 and place them one below the other for each data set within a sheet and likewise for all the sheets. Please help.

I have shared only a couple of tabs with you guys as the file size was too big, I believe I can replicate the code for the rest of the sheets.

Hi guys,

Im having an issue when im trying to create an xy scatter graph using vba code. When the code runs, the graph that the code creates has 8 different series where as i only need one series. When i make the graph manually it looks fine however using code it looks silly. Even recording a macro when doing it manually and using that code gives the same error.

What i think i need to do is define a series first and then get the graph made out of that but i have no idea how to do this. The code im currently using is below:

Charts.Add
'starts the chart options
ActiveChart.ChartType = xlXYScatterSmooth
'selects the type of chart to be used
ActiveChart.SetSourceData Source:=Sheets("Workings").Range = ("B219:C226")
'chart source data
ActiveChart.Location xlLocationAsNewSheet, Name:="EF"
'Outputs the chart
Any help would be greatly appreciated.

Hi Guys,

Im trying to use vba in excel to create an XYscatter graph. The issue im having is that i only require one series of data and excel is making the graph with 8 series. How would i code vba to just give me one series. The source data for the graph is

	VB:
	
B219:C226 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The code that im currently using is:


	VB:
	
 
Charts.Add 
 'starts the chart options
ActiveChart.SetSourceData Source:=Sheets("Workings").Range("$B$219:$C$226") 
 'Selects the source data
ActiveChart.ChartType = xlXYScatterSmooth 
 'selects the type of chart to be used
ActiveChart.Location xlLocationAsNewSheet, Name:="EF" 
 'Outputs the chart

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Im sure its only one line that i need to add to make it work but i have no idea what it is!

Many Thanks in advance

I am using VBA 2003 to write a range object
I wanna try to run Macro for drawing graphs & automatically offset(0,2) e.g.from A& B column to C& D column after drawing one graph, but I cannot find a way to move from one column to another using Range object using Macro
Can anyone plz solve my problem?


	VB:
	
(c)) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I try to use offset object as follows, but it fail to work due to error 1004


	VB:
	
(c)).Offset(0, 1).Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks !

hello,
this is my first post here. greetings earthlings
hope my subject was descriptive enough!

to get to the point, i have a bit of a strange problem.

i'm trying to create a graph using vba.

problem:
i use arrays to assign the values to be plotted like so:

	VB:
	
) 
    Charts.Add 
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers 
     
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" 
     
     
     
    Dim MyNewSrs As Series 
    Set MyNewSrs = ActiveChart.SeriesCollection.NewSeries 
    With MyNewSrs 
         '.Name = "Fred"
        .Values = yvals 
        .XValues = xvals 
    End With 
     
    ActiveChart.Axes(xlValue).MajorGridlines.Select 
    With ActiveChart.Axes(xlValue) 
        .MinimumScale = 0 
        .MaximumScale = gsoffset 
        .MinorUnitIsAuto = True 
        .MajorUnitIsAuto = True 
        .Crosses = xlAutomatic 
        .ReversePlotOrder = False 
        .ScaleType = xlLinear 
        .DisplayUnit = xlNone 
    End With 
     
    ActiveChart.Legend.Select 
    Selection.Delete 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the problem is.. when i pass an array that i initialize using a for loop that has more than 14 elements in it (i mean both arrays - for x and y values- have more than 14 elements), the code does not work. However, if there are less than or equal to 14 elements (arr indexed 0-13), there is no problem in graphing. it is created perfectly. i know that the array that i am passing to the function is initialized properly because i added a watch and saw the values.

what really confuses me is that if i manually enter values into the array (and not use a loop.. x(0)=something, ... x(15)=somethingelse), it works fine!

if i pass the array to the function above (initialized in a loop) then i get an error saying: "unable to set the values property of the series class ".

i promise you that the arrays are set properly (becuase i have seen the values in the debug/addwatch windows, and because it works fine when i have up to 14 points to graph). the above code crashes on the line:

.Values = yvals

when there are more than 14 elements in the arrays. (when i say 14 elements, i mean 14 elements in each array-for the x axis and the y-axis).

i am utterly lost and hope you guys can help me out! i am a c++ person and have been told to create a small excel app with vba.. am just learning vba as i go!

thanks! look forward to hearing from you guys.

Hi m8s,

I have a daily routine work of creating graphs from the temperature reading of the Oven. I would like to automate the process to create a graph using macro(or excel vba). The temperature reading is by the way, not for 1 temperature profile but most of the time from 5 similar temperature profiles per channel(from 5 runs). The oven is manually triggered/restarted after each run. The problem I am facing right now is that, since the Oven is restarted manually, the time interval(in which run/start is initiated) is expectedly different from run to run. To illustrate it clearly, the time the temperature stays at 25ºC for example could be shorter or longer on each run when the machine is restarted(when starting the next run). This makes my macro I created yesterday is longer no applicable today. I would like to make a code that creates a single graph from the given temperature profiles. The macro should take temperature value starting let say from 40ºC and end on 50ºC(normally the temperature reading is starting from 25ºC going up to 340ºC and going back to 25ºC. with time interval reading of 30 secs. for each run.) and display it on graph. I have difficulties making the code. Anybody can help me out there? Thanks advance

rbpij

Dear all,

I have implemented a function in VBA which calculates certain values, called 'IndexSim.' These values are not required to be outputted into the spreadsheet, however I would like to create a simple line graph plot of these values against 1,2,....etc however many values are chosen to be created. The code is as follows:

	VB:
	
Redim IndexSim(1 To SampleSz) As Double 
Redim Payoff(1 To SampleSz) As Double 
 
 'Do the simulations.
For Cnt = 1 To SampleSz 
    IndexSim(Cnt) = gBmProcess(Kt, r, q, Vol, TMat - TNow) 
     
    If IndexSim(Cnt) >= IndexBetValue Then 
        Payoff(Cnt) = Range("D18").Value 
    Else 
        Payoff(Cnt) = Range("D19").Value 
    End If 
     
Next Cnt 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Please advise how I can create a line graph for the 'IndexSim' values, which will update everytime the macro button is clicked.
Thank you for your help,

Suz

Dear expert,

Please help me on how to create macro to plot graph using data from sample attached data. The highlighted column with red color to be used y-axis and data as x- axis by selecting one parameter from cell column. it is also important to plot two graphs on same axis for comparison.

Hi guys,

I am creating charts using Excel VBA and am setting the graph data directly via arrays rather than via ranges.

I have a strange problem in which my code (snippet below) works fine for graph series up to 18 data points, and then keels over for 19+ points. I get a runtime error 1004 when I try to assign the XValues property. Surely this cannot be telling me that I must have less than 19 data labels???

hopefully

Stuart

snippet:
Sub creategraph()
Dim ayLabels() As String 'user array
Dim ayValues() As Double 'user array
N = 18 'no obs in graph
ReDim ayLabels(N)
ReDim ayValues(N)

'**create graph, assign references**
Set chtobj = Sheets("Sheet1").ChartObjects.Add(100, 10, 400, 250)
Set cht = chtobj.Chart
Set scSeries = cht.SeriesCollection
Set sSeries = scSeries.NewSeries

'**populate the user arrays**
For m = 1 To N
ayLabels(m) = Sheets("Sheet1").Range("$A$1").Offset(m - 1, 0).Value
ayValues(m) = Sheets("Sheet1").Range("$A$1").Offset(m - 1, 1).Value
Next m

'**assign data to graph**
sSeries.Values = ayValues
sSeries.XValues = ayLabels

End Sub

Is it possible to create a VBA method for DSum so I can eliminate the use of consuming DSum and formulas in the worksheets which will be deleted as soon as its used. The DSum data will be shown in UserForms and graphs created through the VBA, the list of data is huge and on mutiple sheets. Any Ideas?

******** ******************** ************************************************************************>Microsoft Excel - bills.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH4H6H8H12H14H16H20H22H24=
ABCDEFGH1DepartmentDate Amount 2Office07/07/06 $ 626.80 3Parts07/08/06 $ 582.42 DepartmentDate Amount 4Parts07/08/06 $ 582.42 Office $1,791.64 5Engineering07/09/06 $ 582.42 DepartmentDate Amount 6Office07/11/06 $ 582.42 Office $3,121.32 7Parts07/30/06 $ (162.00) DepartmentDate Amount 8Parts07/31/06 $ 582.42 Office $3,703.74 9Parts07/31/06 $ 582.42 10Office07/31/06 $ 582.42 11Parts08/05/06 $ 582.42 DepartmentDate Amount 12Office08/07/06 $ 582.42 Parts $2,167.68 13Engineering08/09/06 $ 582.42 DepartmentDate Amount 14Engineering08/14/06 $ 582.42 Parts $3,914.94 15Office08/20/06 $ 582.42 DepartmentDate Amount 16Parts08/20/06 $ 582.42 Parts $5,662.20 17Office08/20/06 $(1,000.00) 18Parts08/20/06 $ 582.42 19Office08/28/06 $ 582.42 DepartmentDate Amount 20Office08/29/06 $ 582.42 Engineering $ 582.42 21Parts09/20/06 $ 582.42 DepartmentDate Amount 22Engineering09/26/06 $ 582.42 Engineering $1,747.26 23Office09/27/06 $ 582.42 DepartmentDate Amount 24Engineering09/27/06 $ 582.42 Engineering $3,494.52 25Engineering09/27/06 $ 582.42 26Parts09/27/06 $ 582.42 27Parts09/30/06 $ 582.42 Bills
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hello,

I am facing difficulties to create a chart from a sheet containing empty
cells (I use Excel 2003 SP2). My sheet looks similar to this:

A B C

1 0.1

2 0.2 25 34

3 0.3 23 27

4 0.4 24 6

5 0.5 25 16

6 0.6 27 21

7 0.7

Column A contains data (some depth in my case). Each cell in column A
contains data. Column B and C contain some measurements made at the depth
indicated in column A. I need to combine two graphics on one chart: the
content of B in function of A and the content of C in function of A.

The following code works fine:

With Graph

.ChartType = xlLine
.SetSourceData Source:=Sheets(MainSheet).Range("B2:C6"),
PlotBy:=xlColumns
.SeriesCollection(1).XValues = Sheets(MainSheet).Range("A2:A6")

(...)

The result of this code is a chart containing two lines, for X values rangin
from 0.2 to 0.6. Now, what I really need is to have the X values ranging
from 0.1 to 0.7 and keep the lines displayed for values 0.2 -> 0.6. So I
change my code to:

With Graph

.ChartType = xlLine
.SetSourceData Source:=Sheets(MainSheet).Range("B1:C7"),
PlotBy:=xlColumns
.SeriesCollection(1).XValues = Sheets(MainSheet).Range("A1:A7")

(...)

And in this case, only ONE line is displayed (values of column C) ???

I have been working on this for two nights and this is driving me nuts.

Any help would be appreciated.

Thanks,

Xavier

I would like to create a chart that automatically selects a range based on the value I put in a changing cell. Is this possible without re-creating the chart. Perahps this could be done with a named ranges or something. Ideally i would like to do this without using VBA to so the user could overight the range if need be. If this not possible then then I could use VBA.

I have the words Jan-dec on the X- access and I have monthly data for the years 2003-2006. I would like to be able to enter 2005 into a changing cell and have the graph show only the 12 months in 2005 when it graphs. It is a bar graph and my input ranges move horizontally.


No luck finding an answer? You could always try Google.