VBA Classes to add properties

[Rijnsent]

Hi,

I'm currently programming a macro for which it would be handy to expand the current Excel properties of an object. I'd like e.g. to expand the ChartArea object with a new property named "Country" (string) and an property named "QualityPercentage" (double). I've been messing around a bit with classes, but wonder if it's possible at all to do something like that (with or without classes)?

Hope someone has a clue?

Cheers,

Koen

 

[Sektor]

You can handle chart's events in class.

1. Create some class (call it ExtendedChart).
2. Create public variable in this class (call it Cht): Public WithEvents Cht As Chart.
3. Create standard module.
4. In this module create variable: Public ExChart As ExtendedChart.
5. In Workbook_Open event type in (change chart to appropriate):

Set ExChart = New ExtendedChart
ExChart.Cht = Sheets("Sheet1").ChartObjects(1).Chart

In class you can create any propperty and use in chart's events.

 

[Jaafar Tribak]

Based on Sektor's suggestion you can add the Properties as follows : ( In the Class Module)

Public WithEvents Cht As Chart

Private sCountry As String
Private dQualityPercentage As Double


Public Property Get Country() As String
    Country = sCountry
End Property

Public Property Let Country(ByVal vNewValue As String)
    sCountry = vNewValue
End Property

Public Property Get QualityPercentage() As Double
    QualityPercentage = dQualityPercentage
End Property

Public Property Let QualityPercentage(ByVal vNewValue As Double)
    dQualityPercentage = vNewValue
End Property

Then you could refer to them in code like :

Private ExChart As ExtendedChart

Sub test()

    Set ExChart = New ExtendedChart
    Set ExChart.Cht = Sheets(1).ChartObjects(1).Chart
    
    ExChart.Country = "UK"
    MsgBox ExChart.Country

End Sub

 

[Rijnsent]

Neat, thanks guys, this should get me going !

 

[Rijnsent]

Hi guys,
I have the next challenge ... So I'm able to make this class and it works like a charm. The issue I have: can I save the new object somehow (so I can close the file and open it again and it's still there)?
I mean: standard chart objects are saved with all their properties, but my extra 2 properties are in my class. I can store them in a worksheet or in an external .txt file (and load them upon opening, based on e.g. the name I give the chart object), but the preferable option would be to save it in the "extended chart" object in the file. Is that possible (and if so, how)?
Cheers,
Koen

 

[Jaafar Tribak]

Hi guys,
I have the next challenge ... So I'm able to make this class and it works like a charm. The issue I have: can I save the new object somehow (so I can close the file and open it again and it's still there)?
I mean: standard chart objects are saved with all their properties, but my extra 2 properties are in my class. I can store them in a worksheet or in an external .txt file (and load them upon opening, based on e.g. the name I give the chart object), but the preferable option would be to save it in the "extended chart" object in the file. Is that possible (and if so, how)?
Cheers,
Koen

The class instances are lost when the workbook is closed or the variables go out of scope as you know.

If you don't want to have the Class object Properties saved to disk or in a worksheet, you may want to save them in workbook Names and then retrive their values by evaluating the names.