how to declare a class module
 

I've been programming in VB6 for a while but it seems that
how you declare the properties, methods, and events of a
class module in Excel VBA is different.

I've declared several public properties in a a class
module. Now I want to set these properties from a form
module. What am I doing wrong?

Code excerpt from Class Module (PGDSettings):
'LEIS User Name Start - Public
Public Property Get LEISUserName() As String
LEISUserName = GetSetting
(App.ProductName, "Settings", "LEISUUID")
End Property
Public Property Let LEISUserName(strLEISUserName As String)
SaveSetting App.ProductName, "Settings", "LEISUUID",
strLEISUserName
End Property
'LEIS User Name End

Code excerpt from Form Module:
Option Explicit
Public ps As New PGDSettings
Private Sub UserForm_Activate()
txtUuid.Text = ps.LEISUserName
End Sub

how to declare a class module
 

Scratch it. I figured out what was wrong. I was using
the app.productname which works in VB6 but not in Excel
VBA. Replaced it and it works fine. Sorry to have bugged
anyone.
-Kevin


-----Original Message-----
I've been programming in VB6 for a while but it seems
that
how you declare the properties, methods, and events of a
class module in Excel VBA is different.

I've declared several public properties in a a class
module. Now I want to set these properties from a form
module. What am I doing wrong?

Code excerpt from Class Module (PGDSettings):
'LEIS User Name Start - Public
Public Property Get LEISUserName() As String
LEISUserName = GetSetting
(App.ProductName, "Settings", "LEISUUID")
End Property
Public Property Let LEISUserName(strLEISUserName As
String)
SaveSetting App.ProductName, "Settings", "LEISUUID",
strLEISUserName
End Property
'LEIS User Name End

Code excerpt from Form Module:
Option Explicit
Public ps As New PGDSettings
Private Sub UserForm_Activate()
txtUuid.Text = ps.LEISUserName
End Sub
.

how to declare a class module
 

Bob,
The methods to declaring a class in a different project
appear to be different. Can you offer the steps in which
I might declare a class in one XLA so that it is visible
in the other XLA in a particular instance of Excel. For
example, I want to put all my Class Modules into it's own
XLA so I can password it from the other developers on the
team. I'm having trouble accessing the procedures in the
other modules class <Object variable or With Block not
set.

Project 1 (Classes.xla) is loaded into Session X of Excel
Project 2 (Program.xla) is loaded into Session X of Excel

I want to access the 'Public Property Get LEISUserName'
from program.xla but I'm having no success. Any thoughts?
-Kevin


-----Original Message-----
Kevin,

You don't say what is happening, and VB and VBA declare
class properties and
methods in a similar way.

One thing I note though. VBA does not have an App object,
so you will get an
error on that. You will need to code the productname in.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Kevin" wrote in message
...
I've been programming in VB6 for a while but it seems
that
how you declare the properties, methods, and events of a
class module in Excel VBA is different.

I've declared several public properties in a a class
module. Now I want to set these properties from a form
module. What am I doing wrong?

Code excerpt from Class Module (PGDSettings):
'LEIS User Name Start - Public
Public Property Get LEISUserName() As String
LEISUserName = GetSetting
(App.ProductName, "Settings", "LEISUUID")
End Property
Public Property Let LEISUserName(strLEISUserName As
String)
SaveSetting App.ProductName, "Settings", "LEISUUID",
strLEISUserName
End Property
'LEIS User Name End

Code excerpt from Form Module:
Option Explicit
Public ps As New PGDSettings
Private Sub UserForm_Activate()
txtUuid.Text = ps.LEISUserName
End Sub


.

how to declare a class module
 

Kevin,

Why not create a DLL mad add a reference in the calling app?

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Kevin" wrote in message
...
Bob,
The methods to declaring a class in a different project
appear to be different. Can you offer the steps in which
I might declare a class in one XLA so that it is visible
in the other XLA in a particular instance of Excel. For
example, I want to put all my Class Modules into it's own
XLA so I can password it from the other developers on the
team. I'm having trouble accessing the procedures in the
other modules class <Object variable or With Block not
set.

Project 1 (Classes.xla) is loaded into Session X of Excel
Project 2 (Program.xla) is loaded into Session X of Excel

I want to access the 'Public Property Get LEISUserName'
from program.xla but I'm having no success. Any thoughts?
-Kevin


-----Original Message-----
Kevin,

You don't say what is happening, and VB and VBA declare
class properties and
methods in a similar way.

One thing I note though. VBA does not have an App object,
so you will get an
error on that. You will need to code the productname in.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Kevin" wrote in message
...
I've been programming in VB6 for a while but it seems
that
how you declare the properties, methods, and events of a
class module in Excel VBA is different.

I've declared several public properties in a a class
module. Now I want to set these properties from a form
module. What am I doing wrong?

Code excerpt from Class Module (PGDSettings):
'LEIS User Name Start - Public
Public Property Get LEISUserName() As String
LEISUserName = GetSetting
(App.ProductName, "Settings", "LEISUUID")
End Property
Public Property Let LEISUserName(strLEISUserName As
String)
SaveSetting App.ProductName, "Settings", "LEISUUID",
strLEISUserName
End Property
'LEIS User Name End

Code excerpt from Form Module:
Option Explicit
Public ps As New PGDSettings
Private Sub UserForm_Activate()
txtUuid.Text = ps.LEISUserName
End Sub


.

how to declare a class module
 

I've tried doing that but I always get the DLL entry point
not found error. Could never get around it. I also don't
want to worry about the DLL having to get published to
every PC. Classes in and XLA are embedded, right? Or is
there some other wayt to do the same with a DLL...embed it
into the XLA?


-----Original Message-----
Kevin,

Why not create a DLL mad add a reference in the calling
app?

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Kevin" wrote in message
...
Bob,
The methods to declaring a class in a different project
appear to be different. Can you offer the steps in
which
I might declare a class in one XLA so that it is visible
in the other XLA in a particular instance of Excel. For
example, I want to put all my Class Modules into it's
own
XLA so I can password it from the other developers on
the
team. I'm having trouble accessing the procedures in
the
other modules class <Object variable or With Block not
set.

Project 1 (Classes.xla) is loaded into Session X of
Excel
Project 2 (Program.xla) is loaded into Session X of
Excel

I want to access the 'Public Property Get LEISUserName'
from program.xla but I'm having no success. Any
thoughts?
-Kevin


-----Original Message-----
Kevin,

You don't say what is happening, and VB and VBA declare
class properties and
methods in a similar way.

One thing I note though. VBA does not have an App
object,
so you will get an
error on that. You will need to code the productname
in.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the
Purbecks


"Kevin" wrote in message
...
I've been programming in VB6 for a while but it seems
that
how you declare the properties, methods, and events
of a
class module in Excel VBA is different.

I've declared several public properties in a a class
module. Now I want to set these properties from a
form
module. What am I doing wrong?

Code excerpt from Class Module (PGDSettings):
'LEIS User Name Start - Public
Public Property Get LEISUserName() As String
LEISUserName = GetSetting
(App.ProductName, "Settings", "LEISUUID")
End Property
Public Property Let LEISUserName(strLEISUserName As
String)
SaveSetting
App.ProductName, "Settings", "LEISUUID",
strLEISUserName
End Property
'LEIS User Name End

Code excerpt from Form Module:
Option Explicit
Public ps As New PGDSettings
Private Sub UserForm_Activate()
txtUuid.Text = ps.LEISUserName
End Sub


.



.

how to declare a class module
 

The hack you suggested works great! I'll consider your
warnings about it not being supported but for the time
being...I got it working. I should also mention, I found
your hack also documented in the QUE Special Edition of
Using Excel Visual Basic - Chapter 2.
Thanks!
Kevin


-----Original Message-----
Hi Kevin,

If you want to create an object model that's exposed
to other projects,
you need to build it in a VB6 ActiveX DLL. The problem
with Excel VBA is
that you cannot create public class modules with it.
Therefore, your classes
cannot be made visible to other projects.

There is a hack that will allow you to use classes
from another project,
but the method is not supported and I wouldn't recommend
using it in a
production application. What you do is create a public
function in a
standard code module inside the add-in containing the
classes. This function
creates an instance of the class and returns a reference
to it as a return
value. Here's a very basic example of how this would work:

-----------------------
In Book1.xls MyClass
-----------------------
Public Sub MyMethod()
MsgBox "Called"
End Sub

-----------------------
In Book1.xls Module1
-----------------------
Public Function GetMyClass() As MyClass
Set GetMyClass = New MyClass
End Function

-----------------------
In Book2.xls Module1
-----------------------
Public Sub UseMyClass()
Dim objMyClass As Object
Set objMyClass = Application.Run("Book1.xls!
GetMyClass")
objMyClass.MyMethod
End Sub

Notice the use of late-binding in the code in
Book2.xls. That's because
a class in Book1.xls is private and cannot be "seen" from
Book2.xls, so you
need to use a generic object variable to collect the
reference.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Kevin" wrote in message
...
Bob,
The methods to declaring a class in a different project
appear to be different. Can you offer the steps in
which
I might declare a class in one XLA so that it is visible
in the other XLA in a particular instance of Excel. For
example, I want to put all my Class Modules into it's
own
XLA so I can password it from the other developers on
the
team. I'm having trouble accessing the procedures in
the
other modules class <Object variable or With Block not
set.

Project 1 (Classes.xla) is loaded into Session X of
Excel
Project 2 (Program.xla) is loaded into Session X of
Excel

I want to access the 'Public Property Get LEISUserName'
from program.xla but I'm having no success. Any
thoughts?
-Kevin


-----Original Message-----
Kevin,

You don't say what is happening, and VB and VBA declare
class properties and
methods in a similar way.

One thing I note though. VBA does not have an App
object,
so you will get an
error on that. You will need to code the productname
in.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the
Purbecks


"Kevin" wrote in message
...
I've been programming in VB6 for a while but it seems
that
how you declare the properties, methods, and events
of a
class module in Excel VBA is different.

I've declared several public properties in a a class
module. Now I want to set these properties from a
form
module. What am I doing wrong?

Code excerpt from Class Module (PGDSettings):
'LEIS User Name Start - Public
Public Property Get LEISUserName() As String
LEISUserName = GetSetting
(App.ProductName, "Settings", "LEISUUID")
End Property
Public Property Let LEISUserName(strLEISUserName As
String)
SaveSetting
App.ProductName, "Settings", "LEISUUID",
strLEISUserName
End Property
'LEIS User Name End

Code excerpt from Form Module:
Option Explicit
Public ps As New PGDSettings
Private Sub UserForm_Activate()
txtUuid.Text = ps.LEISUserName
End Sub


.



.