Class Modules - referring to workbook properties

[Brett]

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.

 

[Bob Phillips]

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.

 

[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

 

[Bob Phillips]

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

 

[Brett]

Thanks Bob, only had a quick read because a Thai curry is waiting for me.
Actually, I'm waiting for it. Your suggestion looks like the go, and probably
very similar to what Dave Peterson suggested two days ago. Hmmm, maybe I
shouldn't have abandoned that idea ( a bit like the Looney Tunes "last of the
Mohicans" (?) who smells Mohican burning "Wait a minute, me last of Mohicans,
must be me YEEEOOOOW". Absolute crack up.

BTW, does "Class Person" refer to you or me? Lol. I was looking through some
old Class posts yesterday and you were once again referring to Class Person,
with Bob's birthday of somewhere in 1949 (as for my sister) and "Lynne's"
birthday was 1956 (same as me). Bit of a giveaway isn't it?
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

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

 

[Bob Phillips]

Thanks Bob, only had a quick read because a Thai curry is waiting for me.
Actually, I'm waiting for it.

Bit early for a curry isn't it, it is only midday here?

BTW, does "Class Person" refer to you or me? Lol.

I was assuming we both were.

I was looking through some
old Class posts yesterday and you were once again referring to Class
Person,
with Bob's birthday of somewhere in 1949 (as for my sister) and "Lynne's"
birthday was 1956 (same as me). Bit of a giveaway isn't it?

Haven't you heard of class obfuscation?

 

[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]

Hi Bob

I posted a question yesterday but haven't had any takers, and now it's
becomng urgent (it's the last problem to vercome before I go live).

I have the following line in a macro:

F_REF.Range("Y54:AE72").Sort Key1:=F_REF.Range("AE54"), Order1:=xlAscending,
Key2:=F_REF.Range("AB54"), Order2:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal

Most often when this line is run automatically, it doesn't sort, but there
are times when it does (rare). If I step through the code then the sort works
EVERY time. Similarly, if I execute the macro manually by clicking a text
box, it works every time.

I've tried it with calc Auto/Manual and it makes no difference whatsoever.

I am at a complete loss with this. I cannot understand how it can work
manually but not as part of a macro that runs it automatically.

I am working with the same test data set in every instance (i.e. when it
works and when it doesn't). Do you have any idea what could be causing this,
and if not how I could work around it please?

I've tried putting just the sort line into a separate macro, and that worked
for a little while, but now that too fails to work ( I thought I had it
licked with a work-around but not to be). Regards, Brett

 

[Bob Phillips]

Brett,

I normally find that when things work manually but not automatically it is
down to objects that are not fully qualified, because I select the sheet or
whatever when stepping it.

But, assuming that F_REF is a worksheet object, your code looks fully
qualified so it would not appear to be the problem.

Without the context, it is hard to offer any other advice.

 

[Brett]

Hi Bob, yes, F_REF is a sheet, and that reference is used several times
before the sort. What I'm saying is that if I put a breakpoint on the sort
and then press F8 to step through the code it works. Now then, since I sent
my original Q to you this morning (Sydney time) I've discovered the most
bizarre thing. If the cells that are being sorted are hidden then the sort
fails. If they are not hidden it works (with the automatic macro as well).
When I was stepping through the code I had the rows/cols unhidden so that I
could see what was going on (with a split screen) and was thus able to
detirmine that the code actually does work in "manual". Of course every time
it runs on auto the cells are hidden, and that's when it fails.

So, to counteract this problem what I have to do is unhide rows/cols before
the sort and then re-hide, and it works every time.That's not normal
behaviour is it? I mean, for crying out loud, you have to be able to sort a
hidden range, especially given that you can select a hidden range.

Just to clarify, when I run it manually I'm not using other lines of code -
they are the very same lines, not even copied to somewhere else.

BTW, a) the curry was good, b) my partner tracked the mohican cartoon on
YouTube at

I hadn't seen it for years.

 

[Bob Phillips]

Seems reasonable to me, sorting is a visual thing. Just like a date of
10-Dec-2009 is the visual form of a date number, data on a spreadsheet is
the visual form of the underlying values. If it is hidden, it is for a
reason, so presumably shouldn't be messed with. Anyway, the resolution is so
simple.

I don't like Thai food. I love curry, but not Thai curry. I find it too
sickly sweet.