1

Would it be possible to "subclass" a Chart in Excel to create a modified version of it?

My wish is for an Improved Bubble/XY Chart in Excel. With the native chart, I can create charts of varying X, Y and Bubble Size, such as:

Regular Bubble Chart

In a recent project, I wanted to classify these bubbles into groups A and B based on some given criteria, and using different colors was the solution I found. I also wanted to label each bubble on a per-row basis. I had to use a simple VBA Macro to color the bubbles and add labels dynamically, but the end result was quite nice:

Improved Bubble Chart

In the end, I was left wondering. My programming experience is mostly in Python, so naturally I though of subclassing the regular Chart to allow for a more user friendly experience, such as customizable colors, categories and being able to pick labels from a Range. Is that possible in VBA?

Most of the tips for VBA subclassing that I have found online refer to using the Windows API and subclassing UserForms, but that is not exactly what I want.

airstrike
  • 2,270
  • 1
  • 25
  • 26
  • 1
    VBA is not a good environment for the kind of subclassing you're thinking of. Best you could do perhaps is "wrap" the existing chart object as a member of your "betterBubbleChart" class. i.e. "has a" vs. "is a" – Tim Williams Jun 05 '14 at 23:05
  • 3
    It is possible, via `implements` construct. But you need to override or defer execution to **every** public property, method and event to an inner, plain `Excel.Chart` object manually instantiated in your `BetterChart`-type object. You'll get bored of writing so much code... –  Jun 05 '14 at 23:30
  • 1
    @CST-Link Thanks! It's a start! It's an interesting project that would break new ground, and there is no deadline, so I am going to take my chances against boredom. Is there a reference on every Chart property, method, and event in the plain `Excel.Chart`? Would http://msdn.microsoft.com/en-us/library/office/ff194068(v=office.15).aspx be it? – airstrike Jun 05 '14 at 23:51
  • 1
    I think its http://msdn.microsoft.com/en-us/library/office/ff194426%28v=office.15%29.aspx. I have never used implements, but the properties list alone is daunting. Maybe check this question out about driving Excel with Python http://stackoverflow.com/questions/441758/driving-excel-from-python-in-windows – DeanBDean Jun 06 '14 at 00:29
  • 1
    @AndréTerra One quick way to check the members of this class is to press [F2] in Excel's VBEeditor, then search for `Chart` class in `Excel` library. It's just the list (no help associated excepting the names, types and argument lists) but is a good quick guide. –  Jun 06 '14 at 09:10
  • Thanks, guys! I guess I can skip some of the boilerplate by using Python to generate at least the property and method definitions... I will post back with results soon enough! – airstrike Jun 07 '14 at 13:10
  • It turns out this isn't possible because you can't use `Implements` on a class that has methods with names containing underscores. If anyone ever finds a solution for this, please let me know. – airstrike Jul 17 '15 at 22:46
  • 1
    I'd think if you can use VBA to step through the bubbles to format the colors and add labels, that would be a success story, and a pretty uncomplicated one at that. There are ways to format groups of points en mass, but using formulas in the worksheet to split the data into separate series, such as I describe in [Conditional Formatting of Excel Charts](http://peltiertech.com/conditional-formatting-of-excel-charts/). And of course, starting in 2013, you can add labels from worksheet cells to data points right in the Excel UI. – Jon Peltier Jul 31 '15 at 14:50
  • @JonPeltier, first of all, thanks for commenting! I am a major fan of your website and it has helped me countless times in my career -- I refer people to it almost every day. My original solution was to use VBA and format the colors and labels, and it did work, but it wasn't as portable as I would like. This is why I thought of subclassing, so that it would "just work" for the end user. The reality is that having the data as you propose in your link is probably easier and less error-prone, so I will go with that in the future. Again, thanks for chiming in and for posting the link! – airstrike Aug 06 '15 at 16:12

0 Answers0