Some Basic VBA Reminders

1. Case Select

Dim iColour As Long

Select Case (i)

Case 0:

iColour = ChartColourIndex.StandardFill1

Case 1:

iColour = ChartColourIndex.StandardFill2

Case 2:

iColour = ChartColourIndex.StandardFill3

Case 3:

iColour = ChartColourIndex.StandardFill4

Case 4:

iColour = ChartColourIndex.StandardFill5

Case 5:

iColour = ChartColourIndex.StandardFill6

Case 6:

iColour = ChartColourIndex.StandardFill7

Case 7:

iColour = ChartColourIndex.StandardFill8

End Select

2. Check if a chart is a Pie chart

Public Function IsPieChart(oChart As Chart) As Boolean

IsPieChart = False

If oChart.ChartType = xlPie Or _

oChart.ChartType = xl3DPie Or _

oChart.ChartType = xl3DPieExploded Or _

oChart.ChartType = xlBarOfPie Or _

oChart.ChartType = xlPieOfPie Or _

oChart.ChartType = xlPieExploded Or _

oChart.ChartType = xlDoughnut Or _

oChart.ChartType = xlDoughnutExploded Then

IsPieChart = True

End If

End Function

3. Declaring your own colour palette instead of using ActiveWorkbook.Colors

In 2003, you would have done something like this:

ActiveWorkbook.Colors(ColourIndex.Black) = RGB(0, 0, 0) where ColourIndex was an Enum all all the colors you wanted to use.

You can't do this in 2007, so instead you can declare your own palette and use that.

Public Enum ChartColourIndex

Black = 0 'RGB(0, 0, 0)

White = 16777215 'RGB(255,255,255)

StandardFill1 = 1363314 'RGB(11, 20, 20)

StandardFill2 = 9764985 'RGB(121, 0, 14)

StandardFill3 = 16762624 'RGB(10, 199, 25)

StandardFill4 = 36351 'RGB(215, 11, 40)

StandardFill5 = 17517 'RGB(109, 168, 0)

StandardFill6 = 3801318 'RGB(230, 0, 58)

StandardFill7 = 16742898 'RGB(142, 182, 15)

StandardFill8 = 16721958 'RGB(124, 12, 0)

End Enum

Then if you want to grab the colour just use ChartColourIndex.StandardFill1 or whatever one you want. The other option is to set up themes and colours, and reference them.