Using a Class Module to Execute Code

[twinnyfo]

Hello Friends,

'Tis me again, with what might possibly be a very simple question, but one I just don't know where to start searching. I will begin with an example that should make sense to everyone, then describe what I am trying to do.

First, a common example. Let us say we are working with an object. An object has properties, some of which can be changed, and it also has methods which do things. For example, a Form has the Property of BackColor, which can be changed do different colors. But Forms also have the Method of MoveSize, which does something: it moves the form or changes the visual dimensions of the form, using values for the Top, Left, Height and Width arguments for that method. In VBA it might look something like this:

Private Sub ChangeForm()

    With Me
        .BackColor = RGB(0, 0, 0)
        Call .MoveSize(Top:=0, _
                       Left:=0, _
                       Height:=1296, _
                       Width:=1800)
    End With

End Sub

Super Simple!

Now, let's say, for example, that I have created a new class module that I have named Person:

Option Compare Database
Option Explicit

Public Name      As String
Public Height    As Integer 'Height in Inches for US folks
Public HairColor As String

Then, when we want to create a person:

Private Sub CreatePerson()
    Dim perBlank As New Person

    With perBlank
        .Name = "Hulk Hogan"
        .Height = 80
        .HairColor = "Blonde"
    End With

End Sub

Thus, I can manipulate this data all day long. I can also use other code to display the data in this Person:

Private Sub DisplayPerson()

    Debug.Print perBlank.Name

End Sub

Which will result in this being displayed in the Immediate Window: Hulk Hogan.

So, finally, here is my question: Is there a way to add a Method to a Class, such that all I need to do is do this:

Call perBlank.Display

and that code will execute my Sub DisplayPerson?

Again, this may have a super simple answer--I just haven't been able to find any answers out there.

Thanks for the hepp!

[NeoPa]

Hi Twinny.

Yes indeed there is - and you're right that it's obvious once you know how.

Simply create a Public Sub or Public Function Procedure, depending on whether or not you need a value returned from your Method.

I actually have a Class example in my (fairly) recent Direct File I/O in VBA article that you can review if that helps.

In fact, looking at your example code, it seems you already have a Method called Person.CreatePe rson() except you have it set as Private so it won't be visible to code outside of the Class Module itself.

[twinnyfo]

NeoPa,

Ya know, I have been thinking about how one would accomplish this for weeks, then I decided to just submit a question--wrote out my thoughts and posted. Then I walked away for about 30 minutes and while I was away, I thought to myself, "Self, wouldn't one just declare a Public Sub in the Person Class Module?"

So, I came back and tried it, using my example, and it kind of worked, but I could not figure out how to send the "perBlank" object back to the Class Module, because that is where the code broke, so I just walked away again.

But, then your post just kick-started my brain: my perBlank object was not a public object. So, obviously, it wouldn't be able to find it. BUT, in my application, the object I've created IS public, so all I will have to do is have the code do what I need it to do!

So, this question is another example of 1) I feel really stupid about something so obvious, 2) It often helps to write your thoughts out and step away and 3) many solutions are often extremely simple, but one just needs to be reminded of something basic.

Thanks again for the hepp NeoPa!

[NeoPa]

Don't feel stupid. You were clever enough to realise that, just like all of us have experienced, you were nearly there but unable to fit it all together quite right. Often the way with stuff we've never actually done before ;-)

I'm glad I was able to hepp in this case - even though I realised you were all there but for a hair's breadth.