1

I am a beginner in VBA, so be indulgent in my lack of methodology while working on some VBA macro for Excel.

My goal is to create some BoxPlot Charts in Excel at this time, I have been able to create some For/If loop to capture the Data in my different worksheets (ex: I have a Ftotal column in each worksheet, but from different length that I want to add to my boxplot). For some graphs (xlcolumnclustered, ...) I have been able to simply write:

ActiveChart.ChartType = xlColumnClustered

But, when I want to do it for a box plot (xlboxwhiskler) I have not been able to complete it. So I have tried to record a macro while I was creating a box plot graphs in a chart sheet and I have been able to capture:

ActiveSheet.Shapes.AddChart2(406, xlBoxwhisker).Select

Which is not working when I am runing my Macro. Btw way, I can't figure our why I get an Activesheet instruction whereas I was on a chart page (seems strange to me, I was expecting an ActiveChart).

So my question are:

Is there a xlboxwhiskler working to create a Box Plot Chart ?

If not, is there another way ? (I have some info from here and here). Just FMY, why is Set used for ?

Finally my last solution is about to calculate everything (it might be challenging to do so), but the link here might be useful

If you have any suggestion, I remain open for it. It will then post my solution.

Community
  • 1
  • 1
CLR
  • 21
  • 4
  • `Set` is used to assign an object to a variable. things like worksheets, ranges, and charts are objects. Hence they must be `Set`. If your study of VBA has not progressed to learning about the keyword `Set`, perhaps you could put this project to the side for a week or so and concentrate on learning more VBA. It is hard to do something nontrivial with a language when you are still learning its basic syntax. – John Coleman Jul 24 '18 at 17:22
  • Here is a guide to creating them (without VBA, unfortunately) https://peltiertech.com/excel-box-and-whisker-diagrams-box-plots/ – Marcucciboy2 Jul 24 '18 at 19:03
  • [Also there is no built-in function in excel to make a box and whisker plot, so there will not be an `xlBoxWhisker` or similar](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlcharttype-enumeration-excel) – Marcucciboy2 Jul 24 '18 at 19:04
  • This macro is done in order to save me a huge amount of time to avoid doing it using this ... – CLR Jul 24 '18 at 19:17
  • Looking at the two lines of code I'd have a read of this post - [How to avoid using Select/Active](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Darren Bartrup-Cook Jul 26 '18 at 13:17

0 Answers0