EXCEL 2016 Box & Whisker Plot on VBA Enumeration

Question

Hi Microsoft,

I'm Currently Working On a Project which involves Huge Amounts of Data Being Translated into a Box Plot Chart. The Project Includes using VBA to automate the Data Processing. with the release of the Excel 2016 i found that Manually Plotting the Box & Whiskers Chart not having a Problem but when i try to translate the Box & whiskers Plot to VBA Code while using the "Record Macro" Function the VBA Code is:

and It Shows the Following Error:

Would like to Ask what is the Box & whiskers Plot xl Enumeration. Like older Excel Version Graphs the Chart.Add xlXYScatterLines is for the Scatter Line Chart Types. is there a Chart.ADD for the Box & Whisker Plot?

Answer 1

As it's now October 2017 I guess you have solved this problem? If not reply here for solution.

Answer 2

no there was no solution.

Answer 3

This VBA code works on both 32 bit and 64 bit implementation:

Sub test_boxplot()

Dim chart_name As String

Dim chart_name_len As Integer

Dim worksheet_name As String

Dim worksheet_name_len As Integer

Range("B3:F10").Select

' Slot in the Chart Title from worksheet

chart_title = Range("B2")

' Invoke the Box Plot specifying style and size

ActiveSheet.Shapes.AddChart2(408, xlBoxwhisker, 200, 100, 350, 200, True).Select

chart_name = ActiveChart.Name

chart_name_len = Len(chart_name)

worksheet_name = ActiveSheet.Name

worksheet_name_len = Len(worksheet_name)

chart_name = Right(chart_name, chart_name_len - worksheet_name_len - 1)

ActiveSheet.Shapes.Range(Array(chart_name)).Select

ActiveChart.ChartTitle.Text = chart_title

ActiveChart.Legend.Select

ActiveChart.Legend.IncludeInLayout = False

'ActiveSheet.ChartObjects(chart_name).Activate

ActiveChart.Axes(xlValue).Select

ActiveChart.Axes(xlValue).MaximumScale = 100

' White background

ActiveChart.ChartArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)

' Red border

ActiveChart.ChartArea.Format.Line.ForeColor.RGB = RGB(100, 100, 100)

ActiveChart.ChartArea.Format.Line.Weight = 2

Range("A1").Select

End Sub

The source data is:

Data Set 1
Set 1	Set 2	Set 3	Set 4	Set 5
84.9	94.3	100.0	83.0	83.0
79.2	75.5	86.8	84.9	86.8
84.9	60.4	100.0	77.4	69.8
69.8	71.7	79.2	75.5	75.5
94.3	88.7	58.5	81.1	88.7
92.5	86.8	90.6	88.7	71.7
86.8	79.2	88.7	75.5	77.4

Sorry this page does not let me paste the Box Plot produced but it should be fine.

Answer 4

hi Cliftonvilla,

Thank you So much! the line:

"ActiveSheet.Shapes.AddChart2(408, xlBoxwhisker, 200, 100, 350, 200, True).Select"

is exactly what i m looking for, i did not have any trouble manipulating the box plot graph, it was not stated in the MSDN on how to ADD a Box whisker chart.

Thank you once again!