Free Microsoft Excel 2013 Quick Reference

Vba code for plotting graph Results

Hi there,

I am looking for advice as how should I go about in writting some VBA code
for plotting graph from data I pull out from some archive.

04-Jul-06 30-Jun- 06 29-Jun-06 28-Jun-06
test_1 2.909143 3.727211 3.345108 3.638312
test_2 2.232157 2.110976 4.670067 2.463802

04-Jul-06 30-Jun- 06 29-Jun-06 28-Jun-06
test_1 2.909143 3.727211 3.345108 3.638312
test_2 2.232157 2.110976 4.670067 2.463802

I wrote a perl script to crawl through some archive and extract the data I
am interested and dump them into a CSV file in the above format. Now that I
have the CSV file I would like to get excel plot a line graph for every
table, for example:

| --/ ---------- test_1
| /
| --- /
| -------------- test_2

| --/ ---------- test_1
| /
| --- /
| -------------- test_2

the number of columns & rows are different for every table.

Any suggestions?

I could easily modified the CSV file style if this will make the processing
in excel easier.



I need to create a macro that would plot a simple xy-scatter graph for me. In the attached file the colored section is the data that needs to be plotted. The graph plotted is an example of what I need. The number of data varies from one file to another but not by much. All the files that I need to work on come in the exact same format as the one in the attachment, only the number of data under V and I may vary a little.

I am a newbie, so detailed answers will be great.

Thanks in advance!


I have been stuck on this problem for far too long and no one is able to help me.

I have a large vba code already running and I have working graphs that run as well. BUT what I need to account for is that the data that is in column "G" will next time be in another column.

Here is my graphing code right now and the bold data is what I believe needs to be changed:

     ' LVDT Graph
    LR = Range("A" & Rows.Count).End(xlUp).Row 
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers 
    [B]  ActiveChart.SetSourceData Source:=Sheets("Data").Range( _ 
    "Data!$B$3:$B$" & LR & ",Data!$AE$3:$AH$" & LR), PlotBy:= _ 
    ActiveChart.Location Where:=xlLocationAsNewSheet, name:= _ 
    With ActiveChart 
        .HasTitle = True 
        .ChartTitle.Characters.Text = "LVDT" 
        .Axes(xlCategory, xlPrimary).HasTitle = True 
        .Axes(xlCategory, _ 
        xlPrimary).AxisTitle.Characters.Text = "Time [s]" 
        .Axes(xlValue, xlPrimary).HasTitle = True 
        .Axes(xlValue, _ 
        xlPrimary).AxisTitle.Characters.Text = "LVDT [mm]" 
    End With 
    With ActiveChart.Axes(xlValue) 
        .MinimumScaleIsAuto = True 
        .MaximumScaleIsAuto = True 
        .MinorUnitIsAuto = True 
        .MajorUnitIsAuto = True 
        .Crosses = xlAutomatic 
        .ReversePlotOrder = False 
        .ScaleType = xlLogarithmic 
        .DisplayUnit = xlNone 
    End With 
    With ActiveChart.Axes(xlValue) 
        .MinimumScaleIsAuto = True 
        .MaximumScaleIsAuto = True 
        .MinorUnitIsAuto = True 
        .MajorUnitIsAuto = True 
        .Crosses = xlAutomatic 
        .ReversePlotOrder = False 
        .ScaleType = xlLinear 
        .DisplayUnit = xlNone 
    End With 
     ' Plots and formats the chart
End Sub 

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

As you can see, for this graph, multiple columns of data are graphed.
The names of the headers will always be the same, I just need excel to find those names and graph all the data below them.

Any help would be awesome!

I have a range of data that I would like to graph. The problem is that the range includes blank cells, and when I graph the entire range, there are tons of zero points on my graph.

How (using VBA code or Excel graphing trick) would I only graph the data in this range that has an x value and a y value?

On my spreadsheet, the range of data is contained in two columns (let's say A and B), the column B is the x axis and column A is the y axis. The cells in column B will always have a value, but some of the cells in column A will be blank. I do not want to graph the cells that are blank.
For instance, if A4 is blank, I do not want this (y-axis value) or B4 (x-axis value) to be graphed.

I would like the graph to be a XY Scatter plot with data point connected by straight lines.

I have posted an example of what the range I want to graph might look like.

The range I would like graphed is highlighted in blue.

Please Help

NOTE: there will be formulas in these cells (if that affects anything)

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 to all,

I need to use Excel VBA code to write a code which allows me to plot
graphs from input values that I have.

However, the graphs need to be of the following distributions: Weibull,
Gamma and Beta.

Anyone can pls advise on how can this be achieved?

xxbenxx's Profile:
View this thread:

I have an excel spreadsheet that I want to plot a dynamic graph from. In the excel sheet there are a bunch of columns but I am interested in 3 columns. Here is what i want to do. If I filter on one column for none blanks, then I'd like to plot what is viewable for two columns. I recorded a Macro, and when I re-run the Macro, it works fine, but with one major flaw. The Macro automatically filters the rows and creates the graphs. If I go the spreadsheet and un-filter the rows, then the chart gets messed up as it now tries to include all the rows. My question is, how do I plot this graph, without the deficiencies of the filter problem. Basically, I want to check a certain column for text. The rows that HAS text, I want to plot a graph showing the data from two columns. So, just picture something like this. Filter column B for "non blanks". Then I want to chart what is NOW visible in column A and E. I dont know how to do this. Maybe some VBA code to automatically copy this info in a temporary location, and then graph it from there. Any help will be greatly appreciated. Thanks!

hi to all,

I need to use Excel VBA code to write a code which allows me to plot graphs from input values that I have.

However, the graphs need to be of the following distributions: Weibull, Gamma and Beta.

Anyone can pls advise on how can this be achieved?

Hi guys,

I have been seeking help on this forum for quite a few days now and I've received some great support from fantastic users. So before anything, thanks all of you for your help!

Andrew developed this coding for me in a previous post and I would like to add one change:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal
Target As Range)

Const sChangeRange = "S4"
Const sGraphSheets = "Plot - Total Port & Site:Plot - K Port:Plot- B Port (M&D)"
Const sTargetBook = "DAILYPLOT:Plot - Total Port & Site:Plot - K Port:Plot- B Port (M&D)"
Const sTargetRange = "G20:S4:S4:S4"

Dim asUpdateSheets() As String
Dim asUpdateRanges() As String
Dim lUpdateLoop As Long


If InStr(sGraphSheets, Sh.Name) > 0 Then
  If Not Intersect(Target, Sh.Range(sChangeRange)) Is Nothing Then


    For lUpdateLoop=LBound(asUpdateSheets) To UBound(asUpdateSheets)
      Sheets(asUpdateSheets(lUpdateLoop)).Range(AsUpdateRanges(lUpdateLoop)).Value = Sh.Range(sChangeRange).Value
    Next lUpdateLoop

  End If
End If

End Sub
I would like to modify one small thing in this script though: apart from everything that the script is already doing (and it's doi ng it really well), I would like the cells S4 in the three graph spreadsheets to also display the value entered in G20. At the moment, whatever is entered in S4 appears in G20 but not the other way around.

Is this possible without any major modification? I am just worried I am not going to be able to implement the change if it's something drastic.

Thanks for your time guys.

Hi all,

I am new to VBA, and I'm using Excel 2007 to do some automated graphing for work. Anyhow, I am having heck of a time getting a macro I am working on to do what I want. Here's what's happening:

1. I record the macro - I select both columns in the attached sheet - Go to insert chart- select the XY scatter plot - and it gives me a linear graph with the XY axis in the right place (A is X axis, B is Y axis). However, when I run this macro, it totally screws up the axis variables, and my graph comes out incorrect - I can eve tell where it's getting mixed up.

I realize at this point the chart wizard would be appropriate, but I need automation to be incorporated within this sheet. I am writing this macro because I will need to append this graph with new curves as I bring more columns of data in, but I can't even get it to do a simple plot yet - Any ideas?

Attached is the document

Here is the code for reference:

Sub Macro4()

    ActiveChart.SetSourceData Source:=Range("'500mW impedance'!$A:$B")
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

End Sub




I need to plot a graph with temperature on Y axis and the date and time values on the X axis. However, the date needs to be shown only when it changes. Time needs to be shown at regular intervals, e,g. on the grid line. I need to use the scatter smooth line with nomarkers option of MS-Excel. I am using MS Excel 2003.

I also need to be able to zoom selected region in the graph and unzoom it.

Can anyone show me how to code with VBA for the above.
Thanks in advance.

Hello Everyone
I need a help for plotting a chart with VBA for each row depending on the selection of that row. In each row the x-axis values will be taken from same row but with different columns( i.e If I want to plot for a Row 5 data, I have x-axis values in column A, E,I and so on ,and Y-axis values will be in Row 6 but with different columns like B,F,M and so on). The problem is that for plotting the graph when I select the different data in columns for x-axis and y-axis it shows error. I am not selecting continous range may be this can be error ,How to handle this.please reply me. Below is the code for only one row,


Sub Macro26()
' Macro26 Macro
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SeriesCollection(1).Name = "=""Carefull"""

'in the below line I am getting error it is not taking the data into account
ActiveChart.SeriesCollection(1).XValues = _

'in the below line I am getting error it is not taking the data into account

ActiveChart.SeriesCollection(1).Values = _
ActiveChart.SeriesCollection(2).Name = "=""Confident"""
ActiveChart.SeriesCollection(2).XValues = _
ActiveChart.SeriesCollection(2).Values = _
End Sub

please help me guys.I will be very thankful to you



i am plotting graphs which i need the equation of the line from. i have been manually typing the x^3, X^2, X^1 and X^0 values into 4 cells which is then used elsewhere, but wanted to know if its possible to get some code to do that for me?

any help is appreciated.



I've been working on this problem now (intermittently) for about a month, and I've Googled to my fullest extent, but I've hit the wall and I need help from a more experienced VBA user.

What I am trying to do is write a macro that will automatically grab multiple data sets, then chart them, format the charts, name the charts, etc. My spreadsheet is set up like this: in the A:A column, I have my x-values. More specifically, this column contains two sets of x-values (time starting at zero), one of which corresponds to an experimental set of temperature data, the other corresponding to a modeled set of temperature data. So this column is discontinuous at one point, where the first x-dataset ends and the second one begins again (at time = zero). The y-data are then listed in all subsequent columns, and are located adjacent to one y-data-set1 lies in columns B:C, y-data-set2 in columns D:E, and so on. If you were to see the data (which I probably shouldn't share on the web!), you would see columns A:B containing data, but the C column data wouldn't show up until hundreds of rows down, when the second time range begins.

In the end, each chart should have two temperature-time curves, one of which corresponds to (for example) the data range (A2:A500, B2:B500), and the other, (A501:A700, C501:C700). As long as this data is correctly “grabbed,” the formatting part of my code is fine, but something's apparently wrong with my grabbing method. I devised a For-Next procedure to cycle through the data columns based on the variable "col," (see the code below), and I am using a simple If-Then argument to ensure that the macro will stop trying to grab data when it comes to an empty column. However, although the macro seems to loop properly, and the chart formatting/naming is working, my problem is the following:

After the first chart is plotted, the data selection corresponding to that chart remains selected, so that all subsequent charts plot a cumulative data set (i.e. chart1 plots data from A:C, chart2 plots data from A:E, chart3 plots data from A:G, etc.), which I do not want. I can’t find a way to “deselect” the data after charting it, but I don’t believe I should have to do so, anyway. I had some other problems regarding sourcedata specification and x-values showing up incorrectly, but they seemed to vanish overnight(!). So here is the code I have right now, which works perfectly except for the fact that it’s plotting cumulative charts of data:

Sub all_charts_create_and_format()

' create and format all charts macro - for one TC test
' Macro created 1/11/2006 by mmf

Dim col As Integer

For col = 2 To 100 Step 2

Sheets("Model vs. Experimental").Activate

If IsEmpty(Cells(2, col)) = False Then

Sheets("Model vs. Experimental").Range("A1:A5000", Range(Sheets ("Model vs. Experimental").Columns(col), Sheets("Model vs. Experimental").Columns(col + 1))).Select 'this is the data selecting method


ActiveChart.ChartType = xlXYScatterSmooth

ActiveChart.setsourcedata Source:=Sheets("Model vs. Experimental").Range("A1:A5000", Range(Sheets("Model vs. Experimental").Columns(col), Sheets("Model vs. Experimental").Columns(col + 1))) 'I had to use this seemingly redundant sourcedata method because originally, the data was not being plotted correctly

ActiveChart.Location Where:=xlLocationAsObject, Name:="Model vs. Experimental"

(I have excluded all of the chart formatting code, but this is where it lies in the actual code)

End If

Next col

End Sub

I would greatly appreciate any help in this matter; I feel like my data selecting method must contain an error that I am not familiar enough with VBA to understand. And please, try to keep it simple!

Thanks for reading,


Hello all,

Does anyone know how to automatically center a line graph in the chart window? It is known that the x-axis has a minimum of 0, that the y-axis has a minimum of 0, and that the function plotted is of the form y = a/x + b. No further assumptions are done w.r.t. a and b. With "automatically", I mean a menu option in Excel, or perhaps some vba code. I wasn't able to write some, but perhaps you brilliant people are...

The units are easy. If we can find some kind of maximum for the scale of x and y, then I can just use maximum / 10 as unit (or something like that). But for the maximum of x and y... ??

Thanks anyway for your help,



I have some graphs, my VBA code does some simple editing. Something I would like it to do is make the x-axis and y-axis lines be editable.

What I mean is say for editing the Plot area background you would use this code:

ActiveChart.PlotArea.Format.Line.ForeColor.RGB = RGB(0, 0, 0)

I am looking for the equivalent to do the x and y axis lines.

Thanks in advance for any help,


Do you really need to use a 3D chart type? 3D charts are first of all
notorious for their distortion of the data, but also they are not as easy to
work with manually or in VBA.

- Jon
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions

"Michael Malinsky" > wrote in message
>I am trying to create a chart using VBA. I attempted to create a
> starting point by creating the chart while the macro recorded was on so
> I could duplicate the result and adjust the code for user input, etc.
> The problem I am having is that when I run the unrevised code created
> from the macro recorded, the sizing of the Chart Area and Plot Area are
> properly duplicated (or close enough) from what was recorded in the
> macro, but the 3D Bar Graph contained within the Plot Area does not
> resize itself as it did when I performed the resizing manually during
> the macro recording. Any help would be greatly appreciated.
> Mike.

I have two dynamic lined charts whose chart areas are aligned; however,
because the y-axis values change dramatically when graphing different
ranges, the chart plot areas misalign (Y-axis shift to the right to
accomodate larger y-values displayed next to vertical axis). This
annoying behavior essentially puts the the two charted lines in the two
charts out of alignment. Locking the chart areas by code does not help,
since the plot area is the culprit!
Any help on how to align the actual plot areas by VBA code? (to be
clear, the aligned areas must not include the y-axis values which can
alternate between 1-100 range to 1-1000,000 range)
Thank you in advanice for your valuable assistance.


Alseikhan's Profile:
View this thread:

Recently I had a need to make GIFs from embedded charts, with optional
resizing to any (within reason) user-specified scale factor. With
simple charts it's easy to just turn off screen updating, resize the
chart, make the GIF, put the chart back to its original size, and turn
screen updating back on. But with complicated charts having lots of
data labels, embedded text, and possibly other embedded shapes,
changing the size of the chart doesn't always give good results. Even
Auto scale fonts only scale in discrete steps, and custom-placed data
labels and other text doesn't stay in exactly the same relative
position when the chart size is changed.

So I snooped around on this ng. Harald Staff's XL2GIF macro gave me a
good start. After some additional tweaking I got what I wanted, and
when I was done I decided to duplicate the relevant code an put it
into a stand-alone utility. I'm happy to do a little payback by
making it available to readers. Here's what it does:

1. Assumes that an image (picture or bitmap) is already in the
clipboard. You can do a manual Shift > Edit > Copy Picture on a
chart, range of cells, or any shape such as Word Art, text boxes,
arrows, etc to make a clipboard copy. Or you can have some VBA code
do the Copy Picture for you; several examples are included. Or you
can use Alt-Print Screen to get a copy of the active window. Or you
can use the "Copy" function that many scientific and technical
programs have to make a bitmap copy of a plot or graph.

2. Pastes the clipboard image onto a temporary worksheet, optionally
resizes it using ScaleWidth and ScaleHeight, keeps track of the
dimensions, and (if necessary) copies the resized image back to the

3. Builds an empty chart and sets its size to exactly the same size
as the clipboard copy.

4. Pastes from the clipboard again, this time into the (empty) chart

5. Adjusts the position of the pasted picture to make it line up with
the outside border of the overall chart, not with the slightly offset
chart area portion of the chart. This avoids the need to then have
any extra padding around the picture.

6. Exports the empty-except-for-the-picture chart as a GIF to a
user-specified file.

Available at, look for the "Make a GIF" link. You
can use the utility as is or extract portions of the VBA code for your
own projects. If you are an image or clipboard guru (I'm not) you
might enjoy playing around with the ListClipboardFormats proc included
for debugging. Try selecting a single empty cell on a worksheet and
pressing Ctrl-C. You'll see that Excel has placed some 30-odd
different formats into the clipboard, most of which are a mystery to
me. All for a single empty cell!

Many thanks to Harald Staff, David McRitchie, and Stephen Bullen for
showing me the way.


I have an XY scatter graph with some points in it.

My data in the spreadsheet looks like this:

[Title] [X-value] [Y-value] [size]

Data 1 | 10 | 10 | 1
Data 2 | 33 | 44 | 5

What I want to do is to change the size of the data point according to
the value I specify. Right now, I have to individually modify each
point and add a number corresponding to the size.

Is there anyway to take the size-value from the spreadsheet and change
the size of the point in the graph?

In the long run, I am also looking to modify the color.

If the solution requires some VBA coding, I am up for that. Any
suggestions are valuable!

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