Add a new property to an existing object RRS feed

  • Question

  • Hello,

    In Excel VBA, is it possible to add a "p" property to the Series object that works similar to the "Points" property?

    Existing property example:

    Dim s as Series
    Set s = ActiveChart.SeriesCollection(1)

    debug.print s.Points(1).Name {result = "S1P1"}

    What I would like to achieve is the following:

    debug.print s.p(1).Name {result = "S1P1"}

    Is this possible? And if so, how would this be done?  This example seems quite useless, but I would like to understand how to extend existing objects in VBA.

    Thursday, August 4, 2016 1:46 PM

All replies

  • Objects in VBA are sealed, you can't extend object.  What you can do is create a class to hold the object and then add a property to the class.  I've done this a few times.
    Thursday, August 4, 2016 2:28 PM
  • Thanks.  Would you happen to have a simple example of how you would do this?
    Thursday, August 4, 2016 3:29 PM
  • This link provides a lot of Class info.  Here is a simple example.

    Create class and name ChartCls.  I use 'x' prefix to note it is private.

    Private xCO As ChartObject
    Private xName As String
    Property Let SetChart(iCO As ChartObject)
      Set xCO = iCO
    End Property
    Property Let SetName(iName As String)
      xName = iName
    End Property
    Public Property Get GetName() As String
        GetName = xName
    End Property

    Create module and sub to test.

    Sub Test()
      Dim co As ChartObject
      Dim p As String
      Dim myClass As ChartCls
      Set myClass = New ChartCls
      myClass.SetChart = co
      myClass.SetName = "Alfred"
      p = myClass.GetName
    End Sub
    This could be useful if you want to create a lot of graphs that were somewhat similar but with a few differences.  Create a class for each to do the common parts and only do the different part outside the class.

    • Edited by mogulman52 Thursday, August 4, 2016 5:56 PM
    Thursday, August 4, 2016 5:51 PM