16

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?

ZygD
  • 22,092
  • 39
  • 79
  • 102
OneBaseNotch
  • 183
  • 1
  • 2
  • 7
  • 3
    Very interesting question highlighting a lack of [specifications](http://msdn.microsoft.com/en-us/library/office/ff838167(v=office.15).aspx). I can only find there are 48 styles (ranging from 1 to 48), so this might not be the answer (if you're able to insert 286 or 285) but a good point to start. However, nothing is said in the [official documentation](http://msdn.microsoft.com/en-us/library/office/jj228277%28v=office.15%29.aspx) so let's wait for someone who knows by other means. – Matteo NNZ Jan 11 '15 at 17:20
  • 2
    Apparently the stlyes range from 201-353 according to [this article](https://books.google.com/books?id=C-4-Es0OXeIC&pg=RA9-PT250&lpg=RA9-PT250&dq=addchart2.+excel+vba+style+parameter&source=bl&ots=pRSq7z7NGv&sig=PX7bHdDALq7CIUSgKtnMd8U2Imk&hl=en&sa=X&ei=C8myVOCUAsW1sAT87YLYDQ&ved=0CDkQ6AEwBA#v=onepage&q=addchart2.%20excel%20vba%20style%20parameter&f=false). – peege Jan 11 '15 at 19:06
  • 1
    The above mentioned article has a lot of information of other chart styles and mentions also that the original 1-48 continue to work. – peege Jan 11 '15 at 19:07
  • Most of what I'm reading suggests to record a macro and insert a bunch of different charts and see what values the chart you WANT is. You'd think there would be a list somewhere. – peege Jan 11 '15 at 19:21
  • 3
    The `Style` is not a Struct nor Enumeration. It has never been as there wasn't a `AddChart2` method available up til 2013. It hasn't been enumerated by Microsoft and probably will not be. The `Style` is a `Long` though so basically you can enter any number within some accepted range as @peege pointed out in the book. I agree on the lack of documentation –  Jan 12 '15 at 08:12
  • 2
    @peege very much so would be the lack of documentation, but thank you very much on the article, despite it needs to take a littl time to experiment the chart styles, probably one by one. Appreciate it. – OneBaseNotch Jan 15 '15 at 01:05
  • Some values are documented in [XlChartType enumeration](http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlcharttype%28v=office.15%29.aspx). I listed theid value, but none of them is 286 – Dirk Horsten Jan 16 '15 at 23:47

4 Answers4

15

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)

enter image description here

EEM
  • 6,601
  • 2
  • 18
  • 33
1

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
Pillgram
  • 804
  • 6
  • 11
0

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.

Zacarias Bendeck
  • 951
  • 10
  • 7
Anarach
  • 440
  • 2
  • 16
  • 35
0

I found a partial list: https://learn.microsoft.com/en-us/office/vba/api/excel.xlcharttype

I'm sure these numbers, plus the bitfield variations as suggested by Pillgram above to control various other chart aspects, answer the question. The possible combinations are in the thousands, so a full list would be pretty useless. Recording is still your best bet.

Robert Mosebar
  • 11