Is there a way to write an equality test for a VBA

2019-02-07 09:39发布

I do a fair amount of Excel VBA programming, but not a lot of it is object-oriented. Here is something that comes up every now and then that bugs me, and I'm wondering if there's something I'm missing.

In VBA, say I have a class C defined with some private members like so:

'...

Private hidden1_ As Double
Private hidden2_ As Double

'...

If VBA worked like C++ or (most?) other languages that support OOP, I could write a member function to do an equality test between instances of class C like this:

'Error: won't compile!
Public Function equal(cinst As C) As Boolean
    equal = (hidden1_ = cinst.hidden1_ And hidden2_ = cinst.hidden2_)
End Function

Of course, that won't compile in VBA because class member functions can only access private class members of the same instance they are invoked on. The best I've ever come up with to do this sort of thing is to instead define two member functions like this:

Public Function equalDef(hidden1 As Double, hidden2 As Double) As Boolean
    equalDef = (hidden1_ = hidden1 And hidden2_ = hidden2)
End Function

Public Function equal(cinst As C) As Boolean
    equal = cinst.equalDef(hidden1_, hidden2_)
End Function

It's cumbersome, and it exposes knowledge of the existence of private class members, but at least it avoids actually exposing the values of private class members.

Is this the best I can do?

EDIT:

As usual, after an answer, I've realized a better way to phrase the question. It was titled "Is there a cleaner way to write an equality test for a VBA class with private members?" when Dick answered it.

2条回答
Summer. ? 凉城
2楼-- · 2019-02-07 10:19

I would write the class like this

Private mdhidden1_ As Double
Private mdhidden2_ As Double

Public Property Get hidden1_() As Double

    hidden1_ = mdhidden1_

End Property

Public Property Get hidden2_() As Double

    hidden2_ = mdhidden2_

End Property

Private Sub Class_Initialize()

    'some method of setting variables private to the class
    mdhidden1_ = 1
    mdhidden2_ = 2

End Sub

Public Property Get IsEquivalent(clsCompare As C) As Boolean

    IsEquivalent = Me.hidden1_ = clsCompare.hidden1_ And Me.hidden2_ = clsCompare.hidden2_

End Property

If you're forced to expose knowledge of the member anyway, you may as well make it a read-only property (Get, but no Let). Then you can make the IsEquivalent boolean property inside the class.

查看更多
Luminary・发光体
3楼-- · 2019-02-07 10:31

After looking into this again, I have an answer, but it's not exactly satisfying. As with most OOP in VBA, it involves using an appropriate interface, but the fact that every class (and every interface) has to go in a separate class module makes it a pretty clunky way to do things. So here goes:

Say I have a class called MyClass (which I was calling 'C' above but I'm now calling 'MyClass' to make this clearer.)

The thing to do would be to define an interface that I would actually use in my code that exposed just the things about MyClass that I wanted truly public. Say this code is in a class module called IMyClass:

Public Function calcSomething()

End Function

Public Function equal(cinst As IMyClass) As Boolean

End Function

Then I have my class called MyClass, defined with this code in a class module:

Implements IMyClass

Private hidden1_ As Double
Private hidden2_ As Double

Public Sub init(h1 As Double, h2 As Double)
    hidden1_ = h1
    hidden2_ = h2
End Sub

Public Function equalDef(hidden1 As Double, hidden2 As Double) As Boolean
    equalDef = (hidden1_ = hidden1 And hidden2_ = hidden2)
End Function

Private Function IMyClass_calcSomething() As Variant
    IMyClass_calcSomething = hidden1_ * hidden2_
End Function

Private Function IMyClass_equal(cinst As IMyClass) As Boolean
    If TypeOf cinst Is MyClass Then
        Dim asMyClass As MyClass
        Set asMyClass = cinst

        IMyClass_equal = asMyClass.equalDef(hidden1_, hidden2_)
    End If
End Function

And here is some code that would go in a regular module:

Public Function mkMyClass(h1 As Double, h2 As Double) As IMyClass
    Dim ret As MyClass
    Set ret = New MyClass

    Call ret.init(h1, h2)

    Set mkMyClass = ret
End Function

Public Sub useMyClass()
    Dim mc1 As IMyClass
    Set mc1 = mkMyClass(42, 99)

    Dim mc2 As IMyClass
    Set mc2 = mkMyClass(42, 99)

    Dim mc3 As IMyClass
    Set mc3 = mkMyClass(99, 42)

    Debug.Print mc1.calcSomething
    Debug.Print mc1.equal(mc2)

    Debug.Print mc3.calcSomething
    Debug.Print mc3.equal(mc2)
End Sub

In the 'useMyClass' routine, you can verify that the various mc1, mc2, and mc3 variables can't see the 'init' or 'equalDef' methods of MyClass. They can only see the 'calcSomething' and 'equal' methods that are part of the interface.

So, question officially but unsatisfyingly answered, I guess. At least it gives me a chance to repeat "OOP in VBA is a PITA (TM)"...

Here are some related stackoverflow answers:

VBA inheritance, analog of super

Is there a way to overload the constructor / initialize procedure for a class in VBA?

查看更多
登录 后发表回答