How do I access a sub in a New Class

[Mike]

From project "VBAProject(Book1.xlsm):"I am trying to access sub
"subInNewClass" in a new class that is in Project "newclass(class
test.xlsm)". Both methods below produce errors.

Any help would be appreciated.


-----------------------------------
In Project newclass(class test.xlsm)

In Class Module Class1:

Option Explicit
Public Sub subInNewClass()
MsgBox "I'm in"
End Sub

In VBAProject(Book1.xlsm):

Set reference to Project newclass

In Userform1

Option Explicit
Dim qq As New Class1
Private Sub UserForm_Click()
qq.subInNewClass
End Sub
-----------------------------------------------------------
I also tried:

Option Explicit
Dim qq As Class1
Dim kk
Private Sub UserForm_Click()
Set kk = qq
kk.subInNewClass
End Sub

 

[Peter T]

Difficult to follow your code because nothing like that's going to work.

First ensure Instancing for Class1 is PublicNotCreatable

In a normal module in the same project

Function NewClass1() As Class1
Set NewClass1 = New Class1
End Function

Ensure the project has a unique name, rename VBProject to say ClassTest

Save the file (might be an idea to close and reopen it)

In another wb set a reference to ClassTest, and in a normal module something
like the following

Sub test()
Dim c As ClassTest.Class1
Set c = ClassTest.NewClass1
MsgBox TypeName(c)

' type c and a dot and should get the intellisense

End Sub

Regards,
Peter T

 

[Mike]

Thanks Peter. It took me a while to understand what you were getting at. But
after everything fell into place, it gave me exactly what I was looking for.

Thanks again!!!

 

[Subodh]

Thanks Peter. It took me a while to understand what you were getting at. But
after everything fell into place, it gave me exactly what I was looking for.

 Thanks again!!!

















- Show quoted text -

Sorry Peter n Mike
I couldn't get the thing correctly
What i expected was that to call the sub from the class module and use
in the module (by calling from the
module)
If i am not worng, there is no reference in the peters post about
class module.
How sub from a class be called from the normal module.
Let me make it more clear.

in a class module
msgbox "This is in a class module"

''''''
''''''''
in a normal module
sub callfromclass()
'''''' code goes here

end sub
And the output should be that there should be a message box.

 

[Peter T]

Thanks Peter. It took me a while to understand what you were getting at.
But
after everything fell into place, it gave me exactly what I was looking
for.

Thanks again!!!

















- Show quoted text -

Sorry Peter n Mike
I couldn't get the thing correctly
What i expected was that to call the sub from the class module and use
in the module (by calling from the
module)
If i am not worng, there is no reference in the peters post about
class module.
How sub from a class be called from the normal module.
Let me make it more clear.

in a class module
msgbox "This is in a class module"

''''''
''''''''
in a normal module
sub callfromclass()
'''''' code goes here

end sub
And the output should be that there should be a message box.

==============================

The point to keep in mind is in VBA Class modules can only be created from
within the their own project. However, if Instancing is changed from default
Private to PublicNonCreatable, the class can be used in any other project,
subject to the appropriate Reference being set. (FYI in say a VB6 dll
Instancing can be changed to MultiUse (or GlobalMultiUse) which allows the
Class to be created from outside its own project, but N/A in VBA)

So the way to do it is for a public function in a normal module in the
Class' project to create the Class, then pass it to the calling function in
the other workbook (which must of course have a reference to the Class
project)

Make sure you have changed Instancing as described,
- changed the project name to something unique (eg ClassTest),
- included a public procedure in a normal module to create class and pass it
- saved the file,
- set the reference to ClassTest in the other workbook

FWIW, instead of the function NewClass1 (see my previous post) could change
it to a Property, eg

' in a normal module same project as Class1
Property Get propClass1() As Class1
Set propClass1 = New Class1
End Property

' in the calling workbook

Dim c As ClassTest.Class1
Set c = ClassTest.propClass1

Now you should be able to use c as if Class1 existed in the same project

Regards,
Peter T

 

[Peter T]

I probably should have mentioned before, if intending to use the same class
instance in multiple workbooks, I'd strongly suggest maintaining only one
reference to it in the Class's own project. A reference to the class can be
obtained as and when required by other workbooks ideally with a Property Get
and passing whatever details are required to identify the class. The actual
references of course could be stored in say an Array or Collection declared
at module level in the Class workbook

If multiple references are maintained to a Class instance there could be
problems in "tearing down" leading to memory leaks.

Regards,
Peter T