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