28 May, 2021

Pie chart: wheel of VBA colors

If you use Excel VBA and need to set color or get color for cell, shape or chart, you may need the VBA color code list for reference purposes. The ColorIndex offers 56 basic colors and it's hard to remember VB codes for all of them. The following chart can be helpful, if you don't know the color code for your specific task:

In case you'd need to recreate the chart on your own computer,

here's my macro created just for this purpose. Open a new workbook, paste the code into its Module and run the macro. Modify the created chart if you need a different format.

Sub ColorPies()
'Creates wheel of VBA colors
Dim i As Integer
Range("Y1").Value = "VBAcolors"
Range("Z1").Value = "Labels"
Selection.Font.Bold = True
Range("Y2:Y57").Value = 0.017857143
For i = 1 To 56
    Range("Z" & (i + 1)).Value = i
Next i
ActiveSheet.Shapes.AddChart2(1, xlPie).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$Y$1:$Z$57")
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.SeriesCollection(1)
For i = 1 To 56
    .Points(i).Interior.ColorIndex = i
Next i
    ActiveSheet.Shapes("Chart 1").IncrementLeft -500
    ActiveSheet.Shapes("Chart 1").IncrementTop -175
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.5, msoFalse, _
    ActiveSheet.Shapes("Chart 1").ScaleHeight 2.6, msoFalse, _
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.FullSeriesCollection(1).HasLeaderLines = False
    Selection.ShowValue = False
    ActiveChart.SeriesCollection(1).DataLabels.Format.TextFrame2.TextRange. _
        InsertChartField msoChartFieldRange, "=Sheet1!$Z$2:$Z$57", 0
    Selection.ShowRange = True
    Selection.Position = xlLabelPositionOutsideEnd
    With Selection.Format.TextFrame2.TextRange.Font
        .Name = "Arial"
    End With
    Selection.Format.TextFrame2.TextRange.Font.Bold = msoTrue
    With ActiveSheet.Shapes("Chart 1").Fill
        .Visible = msoTrue
        .PresetTextured msoTextureBlueTissuePaper
        .TextureTile = msoTrue
        .TextureOffsetX = 0
        .TextureOffsetY = 0
        .TextureHorizontalScale = 1
        .TextureVerticalScale = 1
        .TextureAlignment = msoTextureTopLeft
    End With
End With
End Sub

No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.