Multiple graphs in excel using VBA

[CHINCB]

Good day,

I am currently working through data recieved from several experimental runs I have done for my masters studies (In chemical engeneering). I need to create a chart for every experiment but coding the macro to do that has been near impossable. I have the file with the first graph drawn but I dont know how to post it.

I know it will be possible to draw every chart by hand but I am waiting on another 300+ data sets and then the amount becomes crazy. Please help

 

[mathchick]

Good day,

I am currently working through data recieved from several experimental runs I have done for my masters studies (In chemical engeneering). I need to create a chart for every experiment but coding the macro to do that has been near impossable. I have the file with the first graph drawn but I dont know how to post it.

I know it will be possible to draw every chart by hand but I am waiting on another 300+ data sets and then the amount becomes crazy. Please help

I recently created a macro to generate several graphs, here's an example of one of them, let me know if you need additional help with this, I spent a lot of time figuring out how to do it and would be glad to use that to help someone else trying to do the same thing.

Sheets("Tool").Select
        ActiveSheet.Shapes.AddChart.Select
        With ActiveChart
            .ChartType = xlLine   
            .SetSourceData Source:=Sheets("GraphGeneration").Range("A2:M4")  'sets source data for graph including labels
            .SetElement (msoElementLegendRight)  'including legend
            .HasTitle = True
            'dimentions & location:
            .Parent.Top = 244  'defines the coordinates of the top of the chart
            .Parent.Left = 47   'defines the coordinates for the left side of the chart
            .Parent.Height = 300
            .Parent.Width = 600
            .ChartTitle.Text = "Weather Normalized Total Energy Use by Month in " & intGraphStart
        End With

annoyingly there are some properties that are read-only such as line width in line graphs so if that's a deal breaker I'd create your graph template and then copy & paste it and edit the properties.

~Mathchick

 

[CHINCB]

i coppied your code and implemented it but it did not do what i needed. The graphs that i need are scatter lots all with the same X-axis but with changing y-values. These values are located in coloumb A (X-axis) and then coloumb B (1st graph Y-axis), coloumb C (2nd graph Y-axis) and so forth.

Sorry for the inconveniance. (And most probably the very very bad spelling)

 

[mathchick]

i coppied your code and implemented it but it did not do what i needed. The graphs that i need are scatter lots all with the same X-axis but with changing y-values. These values are located in coloumb A (X-axis) and then coloumb B (1st graph Y-axis), coloumb C (2nd graph Y-axis) and so forth.

Sorry for the inconveniance. (And most probably the very very bad spelling)

You'll need to find the scatter plot equivalent of .ChartType = xlLine What I ended up doing for a lot of the formatting stuff was to record a macro and see what the property names where and went from there.

You'll also want to loop through each of the columns (I'm assuming you want it to automatically populate all 100 graphs or whatever it is). If it's helpful, here's a function which returns the column letter based on the number of the column (1 returns A, 2 returns B, 27 returns AA, etc)

adapted from http://support.microsoft.com/kb/833402

Function ColNumToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 26)
   iRemainder = iCol - ((iAlpha) * 26)
   If iAlpha > 0 Then
      ColNumToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ColNumToLetter = ColNumToLetter & Chr(iRemainder + 64)
   End If
End Function

to reference a range in a loop based on a variable, and you're rows are known to be rows 1 through 10, say your variable is called intVar then your range reference would be something like:
Range(ColNumToLetter(intVar) & "1:" & ColNumToLetter(intVar + 1) & "10")
So.....it first gets the column, then adds on the "1:", gets the next column over (intVar + 1), then adds on the 10 so the computer see this if intVar = 3:
Range("C1:D10")

~Mathchick

 

[CHINCB]

i had a coffee and a smoke and put on some music and got to this, seems to work great although it needs a bit of data entered at the begining.

Dim Xaxis As String
    Dim Yaxis As String
    Dim FirstCol As Integer
    Dim FirstRow As Integer
    Dim LastRow As Integer
    Dim DataSets As Integer
    Dim TitleCol As Integer
    Dim TitleRow As Integer
    Dim XCol As Integer
    Dim XRow As Integer
    Dim YCol As Integer
    Dim YRow As Integer
    Dim count As Integer
    Dim name As String
       
    FirstCol = Cells(1, 2).Value
    FirstRow = Cells(2, 2).Value
    LastRow = Cells(3, 2).Value
    DataSets = Cells(4, 2).Value
    TitleCol = Cells(5, 2).Value
    TitleRow = Cells(6, 2).Value
    XCol = Cells(7, 2).Value
    XRow = Cells(8, 2).Value
    YCol = Cells(9, 2).Value
    YRow = Cells(10, 2).Value
    
    Xaxis = Cells(XRow, XCol).Value
    Yaxis = Cells(YRow, YCol).Value
    
    For count = 1 To DataSets
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Range(Cells(FirstRow, FirstCol), Cells(LastRow, FirstCol + 1))
    ActiveChart.HasTitle = True
    ActiveChart.ChartTitle.Text = Cells(TitleRow, TitleCol)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.Axes(xlValue).AxisTitle.Select
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = Xaxis
    ActiveChart.Axes(xlCategory).AxisTitle.Select
    ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = Yaxis
    ActiveChart.SeriesCollection(1).name = Cells(TitleRow, TitleCol)
    name = Cells(TitleRow, TitleCol)
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveChart.name = name
    Sheets("Sheet1").Select
    FirstCol = FirstCol + 3
    TitleCol = TitleCol + 3
    XCol = XCol + 3
    YCol = YCol + 3
    Next

Hope this can help someone else, if you need the excel file let me know