Add a 3D Pie chart to Excel using a 2D array 2

[Swi]

Can someone give some guidance on how to generate a 3D pie chart in an Excel sheet using a 2D array in VB? I have used the macro recorder in Excel to get some ideas but the source of the data it is looking for is a range of cells in a sheet and I would like to use a 2D array. Thanks.

Swi

 

[johnwm]

You could stick the array into a block of cells first

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[Swi]

I guess I could but I thought that there may be a way to assign the data source of the chart to an array. Thanks.

Swi

 

[Swi]

I am getting an error on the ApplyDataLables section of this code. It says that the method failed. Any ideas?

    Set oXLSheet = oXLBook.Worksheets(2)

    Erase aryTemp
    ReDim aryTemp(4, 1)
    aryTemp(0, 0) = "BMC/ASF"
    aryTemp(0, 1) = BMCASFCnt
    aryTemp(1, 0) = "SCF"
    aryTemp(1, 1) = SCFCnt
    aryTemp(2, 0) = "ADC"
    aryTemp(2, 1) = ADCCnt
    aryTemp(3, 0) = "DDU"
    aryTemp(3, 1) = DDUCnt
    aryTemp(4, 0) = "ORIGIN"
    aryTemp(4, 1) = OriginCnt
    
    oXLSheet.Range("A1:B5").Value = aryTemp
    
    Set oXLChart = oXLBook.Charts.Add
    oXLChart.ChartType = xl3DPieExploded
    oXLChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B5"), PlotBy:=xlColumns
    oXLChart.SeriesCollection(1).Name = "Destination Summary"
    oXLChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
    oXLChart.ApplyDataLabels Type:=xlDataLabelsShowPercent, LegendKey:=False, HasLeaderLines:=True
    oXLSheet.Columns("A:B").Select
    oXLSheet.ChartObjects("Chart 1").Activate
    oXLChart.ChartArea.Select
    oXLSheet.Shapes("Chart 1").IncrementLeft -231.75
    oXLSheet.Shapes("Chart 1").IncrementTop -116.25

Swi

 

[Swi]

I have been trying to get this figured out for a while know but still have no had any luck. Has anyone run into this issue before? Thanks.

Swi

 

[strongm]

>a way to assign the data source of the chart to an array

Of course you can ...

[blue]Public Sub DemoChart()
    Dim myEmbeddedChart As ChartObject
    Dim ns As Series
    
    Set myEmbeddedChart = Worksheets("Sheet1").ChartObjects.Add(100, 30, 400, 250)
    
    With myEmbeddedChart
        .Chart.ChartType = xl3DPie
        Set ns = .Chart.SeriesCollection.NewSeries ' Source:=Range("A1")
        ns.Values = Array(1, 2, 3, 4)
    End With
End Sub[/blue]

 

[Swi]

strongm,

Thanks. I will give this a shot when I get back to my hotel and post back results.

Swi

 

[Swi]

Thanks strongm. The below code seems to be working.

    '==================================================================
    ' Work with the second worksheet
    '==================================================================
    Set oXLChart = Worksheets("Sheet2").ChartObjects.Add(100, 30, 400, 250)
    oXLChart.Chart.ChartType = xl3DPieExploded
    Set oXLSeries = oXLChart.Chart.SeriesCollection.NewSeries
    oXLSeries.Values = Array(BMCASFCnt, SCFCnt, ADCCnt, DDUCnt, OriginCnt)
    oXLSeries.XValues = Array("BMC/ASF", "SCF", "ADC", "DDU", "ORIGIN")
    oXLSeries.Name = "USPS Postal Penetration"

Two additional questions if you do not mind.

1. Since I added this code the Excel object hangs in memory even though I added Set oXLChart = Nothing and Set oXLSeries = Nothing. Any ideas?
2. Is there any way to show zero values in the chart because sometimes only a few of the quantities will be 0.

Thanks.

Swi

 

[HughLerwill]

You have no dots in front of your references to your Worksheets and OXLChart; I guess these are intended to belong to an Excel.Applictaion object defined in the code above.

 

[HughLerwill]

try;

Sub Command1_click()

'project ref into Excel library 12 for the xl3DPieExploded constant

Dim xlobj As Object

Set xlobj = CreateObject("Excel.Application")

With xlobj
.Workbooks.Add
.Visible = True
With .Activeworkbook.Worksheets("Sheet2").chartobjects.Add(100, 30, 400, 250)
.Chart.ChartType = xl3DPieExploded
With .Chart.SeriesCollection.NewSeries
.Values = Array(10, 20, 40, 5, 25)
.XValues = Array("BMC/ASF", "SCF", "ADC", "DDU", "ORIGIN")
.Name = "USPS Postal Penetration"
End With
End With
End With

Set objxl = Nothing

End Sub

 

[Swi]

Hughlerwill,

I will try this tonight and post back results. Thanks.

Swi

 

[Swi]

HughLerwill,

The below code worked great. Thanks.

    With oXLBook.Worksheets("Sheet2").ChartObjects.Add(0, 0, 400, 250)
        .Chart.ChartType = xl3DPieExploded
        With .Chart.SeriesCollection.NewSeries
            .ApplyDataLabels Type:=xlDataLabelsShowPercent, AutoText:=True, LegendKey:=False, HasLeaderLines:=False
            .Values = Array(BMCASFCnt, SCFCnt, ADCCnt, DDUCnt, OriginCnt)
            .XValues = Array("BMC/ASF", "SCF", "ADC", "DDU", "ORIGIN")
            .Name = "USPS Postal Penetration"
        End With
    End With

However, how do I show a slice of the pie for each 0 value instead of just one. I have multiple values that are 0 but only one shows. Thanks.

Swi

 

[Swi]

Last question. How can I display dollar values in an Excel chart?

    With oXLBook.Worksheets("Sheet2").ChartObjects.Add(225, 300, 400, 250)
        .Chart.ChartType = xl3DPieExploded
        With .Chart.SeriesCollection.NewSeries
            .ApplyDataLabels Type:=xlDataLabelsShowPercent, AutoText:=True, LegendKey:=False, HasLeaderLines:=True
            .Values = Array(CCur(TotalPostage), CCur(TotalShipping))
            .XValues = Array("POSTAGE", "SHIPPING")
            .Name = "Drop Ship Cost Composition"
        End With
    End With

The following does not put any values in the Excel chart.

Swi