Search code examples
excelvbagraph

VBA-Excel - Graph creator

I'm trying to create a code for generate some graphs with some data already stored in arrays.

The actual final result of the macro is this graph:

enter image description here

The code used for it is the following:

            Dim sht As Worksheet
            Set sht = ActiveSheet
            Dim chtObj As ChartObject
            Set chtObj = sht.ChartObjects.Add(100, 10, 500, 300)
            Dim cht As Chart
            Set cht = chtObj.Chart
            
            If IsZeroLengthArray(yData_TSI) = False Then
                Dim ser As Series
                Set ser = cht.SeriesCollection.NewSeries
                ser.Values = yData_TSI
                ser.XValues = xData_TSI
                ser.Name = "TSI Predicant"
                ser.ChartType = xlXYScatterSmooth
            End If
            If IsZeroLengthArray(yData_Pallet) = False Then
                Dim ser2 As Series
                Set ser2 = cht.SeriesCollection.NewSeries
                ser2.Values = yData_Pallet
                ser2.XValues = xData_Pallet
                ser2.Name = "Pallet Decant"
                ser2.ChartType = xlXYScatterSmooth
            End If
            If IsZeroLengthArray(yData_Vendor) = False Then
                Dim ser3 As Series
                Set ser3 = cht.SeriesCollection.NewSeries
                ser3.Values = yData_Vendor
                ser3.XValues = xData_Vendor
                ser3.Name = "Vendor Decant"
                ser3.ChartType = xlXYScatterSmooth
            End If
            If IsZeroLengthArray(yData_Prep) = False Then
                Dim ser4 As Series
                Set ser4 = cht.SeriesCollection.NewSeries
                ser4.Values = yData_Prep
                ser4.XValues = xData_Prep
                ser4.Name = "Each"
                ser4.ChartType = xlXYScatterSmooth
            End If
            If IsZeroLengthArray(yData_Each) = False Then
                Dim ser5 As Series
                Set ser5 = cht.SeriesCollection.NewSeries
                ser5.Values = yData_Each
                ser5.XValues = xData_Each
                ser5.Name = "Prep"
                ser5.ChartType = xlXYScatterSmooth
            End If

I have in other arrays (tData_XXX) numbers that I would like to add as a label to the bullet points in the graph. To make myself clear, for the same graph generated before, let's imagine than for the "Vendor Decant" data the tData_Vendor array has the numbers (34, 5, 12). The desired result should be something like this:

enter image description here

How can I do this on the code?

Thanks!

Note:

  • All the arrays (yData_XXX, xData_XXX and tData_XXX) are always the same size
Solution

  • Untested, but something like this should work:

    Sub CreateChart()
    Dim sht As Worksheet, chtObj As ChartObject, cht As Chart
    
    Set sht = ActiveSheet
    Set chtObj = sht.ChartObjects.Add(100, 10, 500, 300)
    Set cht = chtObj.Chart
    
    AddSeries cht, "TSI Predicant", yData_TSI, xData_TSI, tData_TSI
    AddSeries cht, "Pallet Decant", yData_Pallet, xData_Pallet, tData_Pallet
    AddSeries cht, "Vendor Decant", yData_Vendor, xData_Vendor, tData_Vendor
    AddSeries cht, "Each", yData_Prep, xData_Prep, tData_Prep '???
    AddSeries cht, "Prep", yData_Each, xData_Each, tData_Each '???
    
End Sub

Sub AddSeries(cht As Chart, seriesName As String, xVals, yVals, labelVals)
    Dim i As Long
    If Not IsZeroLengthArray(yVals) Then
        With cht.SeriesCollection.NewSeries
            .ChartType = xlXYScatterSmooth
            .Values = yVals
            .XValues = xVals
            .Name = seriesName
            .ApplyDataLabels
            'loop over series points and apply label from array
            For i = 1 To .Points.Count
                .Points(i).DataLabel.Text = labelVals(i - 1) 'assuming arrays are zero-based
            Next
        End With
    End If
End Sub

    Note you can reduce your code volume by factoring out the repeated "add a series" steps into a separate method.