What does the number in the AddChart2 VBA macro re

Question

I've use my Excel 2013 to record a macro in inserting a chart, a column-clustered chart in my case. In the view code option, it shows me a line of code as below:

ActiveSheet.Shapes.Addchart2(286,xl3DColumnClustered).Select

Please help me as I cannot understand what does the number 286 represent. I know the syntax of Addchart2 is:

expression.AddChart2(Style,XlChartType,Left,Top,Width,Height,NewLayout)

If I change the "286" to "285", the chart appears with a blue background. An error comes out if the number is 100.

Can anyone kindly tell me what does the number represent?

Answer 1

Well , I had the same situation once, and those are basically chart styles. I tried to figure out the exact numbering but then i realized that recording was a much easier way of knowing the style numbers just as you have done here.

To answer you question, record macros to know which style you want to implement in your macros.

Answer 2

This won't directly answer your question, but it will help you figure out what is going on.

This is pure conjecture on my part, but I would guess it's an undocumented bitfield. As you may know a bit field is just a way to use a number. So image we have a Byte variable which can be 8 bits (or flags). So in a byte we can store up to 8 values.

Example: We have field called "DaysOpen" bits 1-7 mean the store is open on that day of the week. (We'll ignore the 8th bit.) So if the store is open M-F that would be binary 0111 1100.

Then you just convert that number to decimal and we see that it's 124.

That variable is a Variant so it could be anything from a Byte to Long meaning it could be storing up to 64 different flags.

As a side note (if you are interested) you can use bit fields like so:

Option Explicit

Public Enum DayFlags
    'Notice these are power of 2.
    dfSunday = 1
    dfMonday = 2
    dfTuesday = 4
    dfWednesday = 8
    dfThursday = 16
    dfFriday = 32
    dfSaturday = 64
End Enum

Sub Example()
    Dim openHours As DayFlags
    'Set the flags:
    openHours = dfMonday Or dfTuesday Or dfThursday
    'See the binary?
    MsgBox Right$("00000000" & Excel.WorksheetFunction.Dec2Bin(openHours), 8)
    'Notice the order is right to left. This is call endianness.
    'You can check for a specific flag like this:
    MsgBox IsOpenOnDay(openHours, dfMonday) & vbNewLine & IsOpenOnDay(openHours, dfFriday)
    'You can add a flag like this:
    openHours = openHours Or dfFriday
    MsgBox IsOpenOnDay(openHours, dfMonday) & vbNewLine & IsOpenOnDay(openHours, dfFriday)
    'You can remove a flag like this:
    openHours = openHours Xor dfFriday
    MsgBox IsOpenOnDay(openHours, dfMonday) & vbNewLine & IsOpenOnDay(openHours, dfFriday)
End Sub

Private Function IsOpenOnDay(ByVal openHours As DayFlags, ByVal day As DayFlags) As Boolean
    IsOpenOnDay = ((openHours And day) = day)
End Function

Answer 3

One can also provide only the ChartType and the application will use the default style.

Set oShp = ActiveSheet.Shapes.AddChart2(XlChartType:=xl3DColumnClustered)
oShp.Chart.SetSourceData Source:=RngDta

This picture shows the default ChartStyle for all ChartTypes (excluding StockHLC and StockVOHLC)