Graphing an ever growing list in excel with Macros.?

Question

I've got some homework for my Excel for Engineering class, and I'm stuck on how to finish it. It consists of three sheets. The first one has you enter in the name of the hard drive, the size of the hard drive, and the price of the hard drive. I then have a macro set up to a button that adds this information to a list of all the other hard drives entered, and calculates the price per gigabyte for that particular hard drive. So basically every time you enter information for a new hard drive the list becomes one row longer.

My question is how do I make a macro that will automatically take every entry in the list from the top to the bottom, and graph the price per gigabyte of each one. It also has to keep updating the graph each time another entry is made to the list.

I have only used the macro recorder in Excel, and don't know how to program VBA; however, if you can give me exact commands I do know how to cut and paste very well.

Answer 1

I set up the following data in Excel in a worksheet named Sheet1. The data is located in columns A through C.

Name Size Price
A 8 50
C 20 100
D 80 150
E 100 200
B 120 300
F 200 400

The CurrentRegion property returns the block of connected cells to the cell that you specify. The Intersect method returns the cells that multiple ranges have in common. I used these two in order to create a simple line graph that plots the size versus the prices. I sorted the data first so the graph doesn't look all funky. I'm not the best at graphs so you might want to use the macro recorder to help you create a better graph. But this is the method you can use to get your source data list that expands for the graph. Below is the code I used to create my simple macro.

The space and underscore in the coding represents a line continuation in VBA programming.

Sub Macro1()
Dim MyRange1 As Range
Dim MyRange2 As Range
Dim MyRange3 As Range

Worksheets("Sheet1").Cells.Sort Key1:=Range("B2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Set MyRange1 = Worksheets("Sheet1").Range("B:C")
Set MyRange2 = Worksheets("Sheet1"). _
Range("A1").CurrentRegion
Set MyRange3 = Intersect(MyRange1, MyRange2)

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData _
Source:=MyRange3, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, _
Name:="Sheet1"
End Sub

You might notice that unlike the Macro Recorder I did not use a single Select statement. If you can avoid Select statements in your coding to perform a command it is best to avoid selecting the data unless you have to.

Answer 2

I would make it easy on yourself. Don't do a linear plot. Do an x-y plot. Then make your plot selection a very deep range of cells for future data. When your macro drop data into that range, have it also create an X value with each data point so it makes the best sense visually (like make increment, or a date, etc). The empty cells in the plot will by definition create a point at 0,0 which shouldn't be a problem should it? If you don't want to see them, then fill the X column up with -1 so those unused cells won't even show up on the graph.