Custom class methods in Excel

user_19301140 0
Viewed 61

I'm trying to create my own custom class module to handle custom class object.

Let's say i want to create a method witch would double the result of object property...

Class1:

Public Property Get MyObject() As Workbooks

    Set MyObject = Application.Workbooks
    
End Property

When i create code like this:

Module1:

Function test()

    Dim clstest As New Class1

    Debug.Print "Result is: " & clstest.MyObject.Count

End Function

will give me

Result is: 1

since there is one worksheet in my object.

What i`m trying to achive is to make a custom function like this:

test.MyObject.DoubleResult '<- to make the result equals 2

I can`t get the idea how to pass the object to the new class function.

Class 2

Public Function DoubleResult() As Integer

    DoubleResult = (The_Object_I_Created_earlier.Count) * 2
    
End Function

How i cant refer to the object "The_Object_I_Created_earlier"?

vba excel
2 Answers

As far as I know you have to keep the count in an External variable.

You declare the public Long variable in a Module

Public ClassCount As Long

And on your class initialize and terminate you add and remove from it.

Private Sub Class_Initialize()
    ClassCount = ClassCount + 1
End Sub

Private Sub Class_Terminate()
    ClassCount = ClassCount - 1
End Sub
user_78689380

The design intent is not clear at all. But I can offer a simple primer with custom classes in VBA, and how one can contain the other.

Here is the code

MyClass1

Private m_value As Long

Private Sub Class_Initialize()
    m_count = 0
End Sub

Public Sub InitializeWithCount(ByVal n As Long)
    m_count = n
End Sub

Public Property Get Count() As Long
    Count = m_count
End Property

MyClass2

Private m_obj As MyClass1

Private Sub Class_Initialize()
    Set m_obj = New MyClass1
End Sub

Public Sub InitializeWithObject(ByVal x As MyClass1)
    Set m_obj = x
End Sub

Public Sub InitializeWithValue(ByVal n As Long)
    Set m_obj = New MyClass1
    m_obj.InitializeWithCount n
End Sub

Public Property Get MyResult() As MyClass1
    Set MyResult = m_obj
End Property

Module

for testing the above

Public Sub SO_Test()

    Dim t1 As MyClass1
    Dim t2 As MyClass2
    
    Set t1 = New MyClass1          ' New Class1
    t1.InitializeWithCount 100     ' Set value once to Long
    
    Set t2 = New MyClass2          ' New Class2
    t2.InitializeWithObject t1     ' Set value once to Class1

    Debug.Print t2.MyResult.Count
    ' 100
    
    t2.InitializeWithValue 200
    Debug.Print t2.MyResult.Count
    ' 200
    
End Sub
user_138132190
Related