I need some assistance from someone who is knows about Class Modules please.
I'm trying to set up some Class Mods so that I can refer to a
Workbook/Sheet/Range using a variety of the properties. So far I have the
following in a class module called CTLS:

Option Explicit
Private pTLS As Workbook
Private Sub Init()
Set pTLS = Workbooks("1. TOOLS.xls")
End Sub

Public Property Get TLS() As Workbook
Attribute TT.VB_UserMemId = 0 'THIS LINE IS NOT VISIBLE in VBE
If pTLS Is Nothing Then Init
Set TLS = pTLS
End Property

Public Property Get Sheets() As Excel.Sheets
Set Sheets = pTLS.Sheets
End Property

Public Property Get Name() As String
Name = pTLS.Name
End Property

Private Sub Class_Initialize()
Init
End Sub

Private Sub Class_Terminate()
Set pTLS = Nothing
End Sub

and in a normal module I have
Option Explicit
Public TLS As New CTLS
Public TT As New CTT

The next Class Module is CTT:

Option Explicit
Private pTT As Worksheet
Private Sub Init()
Set pTT = Workbooks("1. TOOLS.xls").Worksheets("TOOLS")
End Sub
Public Property Get TT() As Worksheet
If pTT Is Nothing Then Init
Set TT = pTT
End Property
Private Sub Class_Initialize()
Init
End Sub
Private Sub Class_Terminate()
Set pTT = Nothing
End Sub

So, with this I can type into the Immed Window
? TLS.Name
1. TOOLS.xls
? TLS.sheets("TOOLS").name
TOOLS

and get the correct results as shown there.

What I can't figure out (and I've read Chip's site and read Walkenbach's
chapters) is how to do some other things such as
TLS.save
TLS.activate
TT.activate (TT is a sheet)
TT.select
TT.visible = false etc etc

In particular I need to be able to refer to ANY range in TT, which I imagine
will need another Class Mod called (say) CTrng (C for class, T for TOOLS). I
need to be able to do any of the usual things that we do with ranges.

For the record, the Attribute statement was added via Notepad, and then the
class module imported from the notepad, so that auto-instancing is set up.
Therefore the values of TLS and TT are always refreshed when they are needed.

From this point I am completely stuck. Class Modules are not the easiest
things to wrap my head around, and any assistance will be greatly
appreciated. Regards, Brett.

I am not absolutely clear what you are trying to do, but I would guess that
you are trying to have a class for each workbook that you open? If so, I
would create a workbook class, and open workbooks from that class, and add
each instance of that class to a workbooks collection class. Then I would
include a save and activate method.

But I have to ask, why bother? Excel does it pretty well for you.

--
__________________________________
HTH

Bob

"Brett" wrote in message
...
I need some assistance from someone who is knows about Class Modules
please.
I'm trying to set up some Class Mods so that I can refer to a
Workbook/Sheet/Range using a variety of the properties. So far I have the
following in a class module called CTLS:

Option Explicit
Private pTLS As Workbook
Private Sub Init()
Set pTLS = Workbooks("1. TOOLS.xls")
End Sub

Public Property Get TLS() As Workbook
Attribute TT.VB_UserMemId = 0 'THIS LINE IS NOT VISIBLE in VBE
If pTLS Is Nothing Then Init
Set TLS = pTLS
End Property

Public Property Get Sheets() As Excel.Sheets
Set Sheets = pTLS.Sheets
End Property

Public Property Get Name() As String
Name = pTLS.Name
End Property

Private Sub Class_Initialize()
Init
End Sub

Private Sub Class_Terminate()
Set pTLS = Nothing
End Sub

and in a normal module I have
Option Explicit
Public TLS As New CTLS
Public TT As New CTT

The next Class Module is CTT:

Option Explicit
Private pTT As Worksheet
Private Sub Init()
Set pTT = Workbooks("1. TOOLS.xls").Worksheets("TOOLS")
End Sub
Public Property Get TT() As Worksheet
If pTT Is Nothing Then Init
Set TT = pTT
End Property
Private Sub Class_Initialize()
Init
End Sub
Private Sub Class_Terminate()
Set pTT = Nothing
End Sub

So, with this I can type into the Immed Window
? TLS.Name
1. TOOLS.xls
? TLS.sheets("TOOLS").name
TOOLS

and get the correct results as shown there.

What I can't figure out (and I've read Chip's site and read Walkenbach's
chapters) is how to do some other things such as
TLS.save
TLS.activate
TT.activate (TT is a sheet)
TT.select
TT.visible = false etc etc

In particular I need to be able to refer to ANY range in TT, which I
imagine
will need another Class Mod called (say) CTrng (C for class, T for TOOLS).
I
need to be able to do any of the usual things that we do with ranges.

For the record, the Attribute statement was added via Notepad, and then
the
class module imported from the notepad, so that auto-instancing is set up.
Therefore the values of TLS and TT are always refreshed when they are
needed.

From this point I am completely stuck. Class Modules are not the easiest
things to wrap my head around, and any assistance will be greatly
appreciated. Regards, Brett.

Hi Bob, thanks for replying. I'm beginning to think that you're probably
right - "why bother". Perhaps if I explain how I got into this. The following
is a posting I did two days ago, and Chip's solution seemed to be intriguing,
but I'm thinking that I didn't explain myself well enough, and perhaps
inadvertantly mislead him (and others). I have come to this conclusion
because I have to create several classes, with several Get/Let properties in
each, and that seems to be self-defeating. The other thing too of course is
that as I've never worked with classes before it seemed a good opportunity to
learn about them (because I'm a sponge for knowledge).

THE POSTING (under the title of "Public Variable dilemma" on April 23rd):
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''
I have two problems:

1. I have a workbook called "1. TOOLS.xls" that is almost always open (I
can't do much without it). I want to set up VB with either a Public variable
or a Public Const so that I can just refer to this book as TLS and to it's
two main worksheets by T_TL and T_CONT. I know that I can use
Public TLS as Workbook
and then set it when I open 1. TOOLS with
Set TLS = Workbooks("1. TOOLS.xls")
and then set the sheets, but the problem is that if I have a debug instance
and have to reset then I lose the value of the Public variables. I don't
think there is a way of getting them back without manually resetting them
again, which just won't do.

Even if I had
Public Const TLS as String = "1. TOOLS.xls"
I still have the problem os setting it, and as far as I can see I can't have
Public Const TLS as Workbook = Workbooks("1. TOOLS.xls")

SO THE QUESTION IS HOW DO I DO IT PLEASE?

The second problem follows on from that. I have a workbook "1. FINANCE
surname.xls" where surname varies. This book is the centre of the system that
runs of it (about ten workbooks).

I want to be able to do the same thing with this workbook as described
above for TOOLS, the difference being that FINANCE has the variable surname
in it. I currently have a macro the does all the setting for the sheets when
ant FINANCE is opened, but again if there's a crash then the Public variables
lose their values. If I have to set them every time I run one of scores of
macros then it defeats the purpose of having a Public variable (which is
supposed to making life easier!).
Thanks for any help that can be supplied. Regards, Brett
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''

Now I'm thinking that there must be an easier way to achieve this.
Previously, I had been declaring Dim xxx as workbook, yyy as worksheet them
setting them each time, but I want to cut down the code because they are used
so many times, and a set of Public variables seemed the way to go. The
problem with that is that the variables are cleared if the code crashes (I
know it's not supposed to but I'm still developing it) so I'm looking for a
way of keeping the variables intact, but also being able to use any property
I need to, at any given time.

Hopefully that gives you a better understanding than I gave to Chip.
Regards, Brett

Hi Brett,

The reason I said why bother is because my understanding is just that you
are trying to emulate what is already built-in, that is building a
collection of workbook objects. Excel already maintains that for you.

Also, if you have a debug instance that loses your public variable, it is
just as likely to lose you class instances, so classes won't resolve your
problem (trust me, I am a professional developer, and I always add debug
code to my projects to re-initialise my classes when I get a debug that
destroys them <g).

What you can do is something along these lines

If TLS Is Nothing Then

Set TLS = Workbooks("1. TOOLS.xls")
End If

If you add this to all of your procedures that will use TLS, you should be
able to ensure it is maintained, even after debug.

If you want an example of classes, specifically collection classes (which
are the most useful IMO), here is a simple example you can play with. Get
back to me if you have any questions.

Class Person

Option Explicit

Private mmName As String
Private mmDOB As Date

Public Property Let Name(ByVal Name As String)
mmName = Name
End Property
Public Property Get Name() As String
Name = mmName
End Property


Public Property Let DateOfBirth(ByVal DoB As Date)
mmDOB = DoB
End Property
Public Property Get DateOfBirth() As Date
DateOfBirth = mmDOB
End Property
Public Property Get LongDoB() As String
LongDoB = Format(mmDOB, "d mmmm yyyy")
End Property

Public Property Get DayOfBirth() As String
DayOfBirth = Format(mmDOB, "dddd")
End Property

Class Family

Option Explicit

Private mmPeople As Collection

Function NewEnum() As IUnknown
Set NewEnum = mmPeople.[_NewEnum]
End Function

Public Function Add(Being As Person)
mmPeople.Add Being, Being.Name
End Function

Public Property Get Count() As Long
Count = mmPeople.Count
End Property

Public Property Get Items() As Collection
Set Items = mmPeople
End Property

Public Property Get Item(Index As Variant) As Person
Set Item = mmPeople(Index)
End Property

Public Sub Remove(Index As Variant)
mmPeople.Remove Index
End Sub

Private Sub Class_Initialize()
Set mmPeople = New Collection
End Sub

Private Sub Class_Terminate()
Set mmPeople = Nothing
End Sub

Standard Module

Option Explicit

Public Sub CreateAFamily()
Dim mpFamily As Family
Dim mpPerson As Person

Set mpFamily = New Family

Set mpPerson = New Person
With mpPerson

.Name = "Bob"
.DateOfBirth = #9/16/1949#
mpFamily.Add mpPerson
End With
Set mpPerson = Nothing

Set mpPerson = New Person
With mpPerson

.Name = "Lynne"
.DateOfBirth = #4/5/1956#
mpFamily.Add mpPerson
End With
Set mpPerson = Nothing

For Each mpPerson In mpFamily

Debug.Print mpPerson.Name & " was born on " & mpPerson.LongDoB & ",
and is a " & mpPerson.DayOfBirth & "'s child"
Next mpPerson

Set mpFamily = Nothing

End Sub

--
__________________________________
HTH

Bob

"Brett" wrote in message
...
Hi Bob, thanks for replying. I'm beginning to think that you're probably
right - "why bother". Perhaps if I explain how I got into this. The
following
is a posting I did two days ago, and Chip's solution seemed to be
intriguing,
but I'm thinking that I didn't explain myself well enough, and perhaps
inadvertantly mislead him (and others). I have come to this conclusion
because I have to create several classes, with several Get/Let properties
in
each, and that seems to be self-defeating. The other thing too of course
is
that as I've never worked with classes before it seemed a good opportunity
to
learn about them (because I'm a sponge for knowledge).

THE POSTING (under the title of "Public Variable dilemma" on April 23rd):
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''
I have two problems:

1. I have a workbook called "1. TOOLS.xls" that is almost always open (I
can't do much without it). I want to set up VB with either a Public
variable
or a Public Const so that I can just refer to this book as TLS and to it's
two main worksheets by T_TL and T_CONT. I know that I can use
Public TLS as Workbook
and then set it when I open 1. TOOLS with
Set TLS = Workbooks("1. TOOLS.xls")
and then set the sheets, but the problem is that if I have a debug
instance
and have to reset then I lose the value of the Public variables. I don't
think there is a way of getting them back without manually resetting them
again, which just won't do.

Even if I had
Public Const TLS as String = "1. TOOLS.xls"
I still have the problem os setting it, and as far as I can see I can't
have
Public Const TLS as Workbook = Workbooks("1. TOOLS.xls")

SO THE QUESTION IS HOW DO I DO IT PLEASE?

The second problem follows on from that. I have a workbook "1. FINANCE
surname.xls" where surname varies. This book is the centre of the system
that
runs of it (about ten workbooks).

I want to be able to do the same thing with this workbook as described
above for TOOLS, the difference being that FINANCE has the variable
surname
in it. I currently have a macro the does all the setting for the sheets
when
ant FINANCE is opened, but again if there's a crash then the Public
variables
lose their values. If I have to set them every time I run one of scores of
macros then it defeats the purpose of having a Public variable (which is
supposed to making life easier!).
Thanks for any help that can be supplied. Regards, Brett
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''

Now I'm thinking that there must be an easier way to achieve this.
Previously, I had been declaring Dim xxx as workbook, yyy as worksheet
them
setting them each time, but I want to cut down the code because they are
used
so many times, and a set of Public variables seemed the way to go. The
problem with that is that the variables are cleared if the code crashes (I
know it's not supposed to but I'm still developing it) so I'm looking for
a
way of keeping the variables intact, but also being able to use any
property
I need to, at any given time.

Hopefully that gives you a better understanding than I gave to Chip.
Regards, Brett

ha! Yeah, and I, of course, was the class clown. There was a funny post I saw
in my search headed up "Class struggle". Very appropriate.
Never too early for curry mate. It's now 11.35 here in Sydney. Curry was
goot too. Cheers.

"Brett" wrote:

I need some assistance from someone who is knows about Class Modules please.
I'm trying to set up some Class Mods so that I can refer to a
Workbook/Sheet/Range using a variety of the properties. So far I have the
following in a class module called CTLS:

Option Explicit
Private pTLS As Workbook
Private Sub Init()
Set pTLS = Workbooks("1. TOOLS.xls")
End Sub

Public Property Get TLS() As Workbook
Attribute TT.VB_UserMemId = 0 'THIS LINE IS NOT VISIBLE in VBE
If pTLS Is Nothing Then Init
Set TLS = pTLS
End Property

Public Property Get Sheets() As Excel.Sheets
Set Sheets = pTLS.Sheets
End Property

Public Property Get Name() As String
Name = pTLS.Name
End Property

Private Sub Class_Initialize()
Init
End Sub

Private Sub Class_Terminate()
Set pTLS = Nothing
End Sub

and in a normal module I have
Option Explicit
Public TLS As New CTLS
Public TT As New CTT

The next Class Module is CTT:

Option Explicit
Private pTT As Worksheet
Private Sub Init()
Set pTT = Workbooks("1. TOOLS.xls").Worksheets("TOOLS")
End Sub
Public Property Get TT() As Worksheet
If pTT Is Nothing Then Init
Set TT = pTT
End Property
Private Sub Class_Initialize()
Init
End Sub
Private Sub Class_Terminate()
Set pTT = Nothing
End Sub

So, with this I can type into the Immed Window
? TLS.Name
1. TOOLS.xls
? TLS.sheets("TOOLS").name
TOOLS

and get the correct results as shown there.

What I can't figure out (and I've read Chip's site and read Walkenbach's
chapters) is how to do some other things such as
TLS.save
TLS.activate
TT.activate (TT is a sheet)
TT.select
TT.visible = false etc etc

In particular I need to be able to refer to ANY range in TT, which I imagine
will need another Class Mod called (say) CTrng (C for class, T for TOOLS). I
need to be able to do any of the usual things that we do with ranges.

For the record, the Attribute statement was added via Notepad, and then the
class module imported from the notepad, so that auto-instancing is set up.
Therefore the values of TLS and TT are always refreshed when they are needed.

From this point I am completely stuck. Class Modules are not the easiest
things to wrap my head around, and any assistance will be greatly
appreciated. Regards, Brett.