Public Variable dilemma

[Brettjg]

This post replaces the one I just marked as no longer required.

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

 

[OssieMac]

Personally rather than save values in Public variables I prefer to save them
in a hidden worksheet. It certainly alleviates the problems you are having.

 

[Dave Peterson]

Add a check before you refer to any of those variables.

I'd use something like this in a separate module (just to keep it out of the
way):

Option Explicit
Public VarsAreDefined as boolean
Public TLS as string
public TLSWkbk as workbook

Sub DefineMyVars()

varsaredefined = true 'flag to check before every attempt

tls = "1. TOOLS.xls"

set tlswkbk = nothing
on error resume next
set tlswkbk = workbooks(tls)
on error goto 0

if tlswkbk is nothing then
'open that workbook
on error resume next
set tlswkbk = workbooks.open("c:\myfolder\" & tls)
on error goto 0
if tlswkbk is nothing then
msgbox "Error!!!" & vblf & tls & vblf & " wasn't found!
'what should happen here????
end if
end if

End if

====================
Then before you try to use any of those variables in any of your routines, you
can do:


if varsaredefined then
'keep going
else
call DefineMyVars
end if

'check to see tls was found
if tlswkbk is nothing then
'what should happen
end if

============
Personally, I wouldn't put something like this in my personal.xls workbook. I
save that for just generic stuff--available for each and every workbook that's
open.

If I need something for a few workbooks, I'll create a different macro workbook
(or addin??) for each type of workbook I need.






Then in this same module

 

[Chip Pearson]

Here's how I'd do it. In your workbook, insert a Class Module (Insert
menu -> Class Module) and name it CTLS (press F4 to get the
Properties window if it is not already visible). In that class, paste
the following code.

Option Explicit

Private pTLS As Workbook

Private Sub Init()
Set pTLS = Workbooks("1. TOOLS.xls")
End Sub

Public Property Get TLS() As Workbook
If pTLS Is Nothing Then
Init
End If
Set TLS = pTLS
End Property

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

Private Sub Class_Initialize()
Init
End Sub

Private Sub Class_Terminate()
Set pTLS = Nothing
End Sub


Save your workbook.

Then, go to the File menu in VBA, choose "Export File" and save the
class file to some folder -- it doesn't matter where. Then open that
cls file in NotePad or your favorite text editor, and insert the line

Attribute TLS.VB_UserMemId = 0

immediately AFTER the
Public Property Get TLS() As Workbook

line. This property should now look like

Public Property Get TLS() As Workbook
Attribute TLS.VB_UserMemId = 0
If pTLS Is Nothing Then
Init
End If
Set TLS = pTLS
End Property

Save and close the text file. Now, go back to VBA, right-click the
existing CTLS module and choose "Remove". Click "no" to "do you want
to export...." message. Then, back on the File menu, choose Import
File and navigate to where you stored the CTLS.cls file that you
edited with NotePad and import that file. This file will appear to be
exactly the same as the original CTSL class, because the Attribute
statement you added in NotePad isn't displayed in the VBA editor.

Now, back in VBA, in your regular code module, declare a Publc
variable (outside of and above any procedure declaration):

Public TLS As New CLTS

Using the "New" keyword on the Dim statement creates what is called an
"auto-instancing" variable. (As a matter of style, I am generally
against auto-instancing variables but in this case they serve us
well.)

Whenever the TLS variable is encountered in code, VBA will test if
that variable is Nothing. If it is Nothing, it will be automatically
created. This will work even if VBA dumps all global variables.

Now, in your code, you can use the TLS variable to reference the
Workbooks("1. Tools.xls") workbook. For example,

Debug.Print TLS.Worksheets("Sheet2").Range("A1").Address

This works because of what you entered into the class module with
NotePad:

Attribute TLS.VB_UserMemId = 0

Attributes are compiler directives that are not visible within the VBA
editor. You can't add an Attribute in the VBE, so that's why you had
to export it to NotePad to do the deed. This particular attribute
marks that property of the class as the default property, so if you
omit a property when referencing the class, the default is used.

For more info about creating a default member of a class, see
http://www.cpearson.com/Excel/DefaultMember.aspx


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Brettjg]

Hi Ossie
I do have an area in the main book that I use for that sort of thing (in a
hidden area, underneath where I do all my selection of options.

However, it won't help the following situation:
Pre-amble: I use Option Explicit and ALL the variables I'm discussing here
are Public (with one exception "wb" which is Dim in any procedure that it is
used in). All procedures are within PERSONAL.xls. Excel 2003, Windows XP

THIS CODE WORKS:
For Each wb In Workbooks
If Left(wb.Name, 10) = "1. FINANCE" Then
Application.Run "WAV_VARIABLES_SET"
Set FINbk = Workbooks(wb.Name)
Set F_LNCH = FINbk.Sheets("LAUNCHPAD")
Set F_NOTES = FINbk.Sheets("NOTES")
Set F_PERS = FINbk.Sheets("PERSONAL")
Set F_ASS = FINbk.Sheets("ASSETS")
Set F_LOANS = FINbk.Sheets("LOANS")
Set F_BUY = FINbk.Sheets("BUY")
Set F_REF = FINbk.Sheets("Refinance")
FINANCE = F_LNCH.Range("Win.FINANCE").Value
F_snm = F_PERS.Range("nm.last.1").Value
F_init = Left(F_PERS.Range("nm.first.1"), 1)
MsgBox FINbk.Name
Exit For
End If
Next wb
FINbk.Activate

and FINbk activates successfully. This is far too much code to put in every
time I want to reference FINbk (several hundred times) so I used the
following which DOES NOT WORK and can't for the life of me understand why
(remembering that they are Public variables):

code for FINANCE_NAMES_SET is (identical to above)
Sub FINANCE_NAMES_SET()
Dim wb As Workbook
For Each wb In Workbooks
If Left(wb.Name, 10) = "1. FINANCE" Then
Application.Run "WAV_VARIABLES_SET" 'just plays a WAV file
Set FINbk = Workbooks(wb.Name)
Set F_LNCH = FINbk.Sheets("LAUNCHPAD")
Set F_NOTES = FINbk.Sheets("NOTES")
Set F_PERS = FINbk.Sheets("PERSONAL")
Set F_ASS = FINbk.Sheets("ASSETS")
Set F_LOANS = FINbk.Sheets("LOANS")
Set F_BUY = FINbk.Sheets("BUY")
Set F_REF = FINbk.Sheets("Refinance")
FINANCE = F_LNCH.Range("Win.FINANCE").Value
F_snm = F_PERS.Range("nm.last.1").Value
F_init = Left(F_PERS.Range("nm.first.1"), 1)
MsgBox FINbk.Name
Exit For
End If
Next wb
End Sub

After running the two lines
Application.Run "FINANCE_NAMES_SET"
FINbk.Activate
it tells me that FINbk is empty (Object variable not set) but I know damn
well it has been set because the msgbox has told me so. That means that the
value of FINbk is wiped out somewhere in the lines
MsgBox FINbk.Name
Exit For
End If
Next wb
End Sub

How can this be, when FINbk is a Public variable which is supposed to retain
it's value?
Regards, Brett

 

[Brettjg]

HOLY (work)SHEET BATMAN. I've found it. There was (somehow, after very
careful checking) one last remaining Dim FINbk as Worksheet at module level.
And of course it had to be the very first module that I was testing. I think
I would have spotted this much sooner if it had been in a module a little bit
further down the track in the run. Thanks for your help Ossie (does that mean
your an Aussie, if so whereabouts - I'm in Sydney). Regards, Brett

 

[OssieMac]

Hi again Brett,

Pleased for you that you have it sorted. The other answers look interesting,
particularly Chip's. I can't resist a challenge so I'm going to look further
into that one and see if I can master all the logic behind it. One learns so
much from all the options put forward on this site. It is my main and only
reason for participating and providing feedback.

"does that mean your an Aussie, if so whereabouts ". Yes. That's correct.
I'm in Brisbane.

 

[Brettjg]

Cheers Ossie. Yes, I'll look into those as well. Haven't ever gone into class
mods but now is as good a time as any I guess.

 

[Brettjg]

Thanks yet again Dave. I've got a bit of digesting to do.

 

[Brettjg]

Hi Chip, thank you very much for that. It will take me a while to digest, but
I'm kinda keen to learn about Class mods. I'm thinking that you're thinking
that this would be not necessarily the easiest way to do it, but the most
reliable, yes?

 

[Brettjg]

Ok, some digestion, but not necessarily full comprehension. I haven't read
your website jst yet.

A few Qs:
1. I think you saying that the class module should go into TOOLS rather than
PERSONAL?

2. I'd really like to set it up so that the two worksheets in TOOLS are
also referred to in the same way ( Sheet("TOOLS") would be T_TL i.e.
TOOLS_TOOLS and Sheet("CONTACTS") would be T_CONT i.e. TOOLS_CONTACTS). I
presume that I can do that at the same time as (or instead of?) Set
Worksheets = pTLS.Worksheets in the Public Property Get Worksheets. I think
the statements would read
Public Property Get T_TL() as Excel.Sheets
Set T_TL = pTLS.Sheets"(TOOLS")
End Property

Would I still have the other Public Property Get Worksheets construct?
Do I need a whole new Private pT_TL as Worksheet with similar following code
to what you already have.

This looks like it will be terrific Chip, and thankyou for your generous
help thus far. As a realist, I guess that when you posted your original reply
that you had to expect quite a few Qs to come out of it, eh?

In the interim I'll do up some code on how I think it should be (as a start
anyway) while I'm waiting for your response. Regards, Brett

 

[Brettjg]

OK, I have the following:
In the workbook "1. TOOLS.xls" I have a class module CTLS with

Option Explicit
Private pTLS As Workbook
Private Sub Init()
Set pTLS = Workbooks("1. TOOLS.xls")
End Sub
Public Property Get TLS() As Workbook
If pTLS Is Nothing Then Init
Set TLS = pTLS
End Property
Public Property Get Worksheets() As Excel.Sheets
Set Worksheets = pTLS.Worksheets
End Property
Private Sub Class_Initialize()
Init
End Sub
Private Sub Class_Terminate()
Set pTLS = Nothing
End Sub

and it has been to Notepad for treatment exactly as you described, Class
module deleted and imported from Notepad.

In another normal module within 1. TOOLS I have
Option Explicit
Public TLS As New CTLS


When I'm in the TOOLS project if I do
? TLS.name
in the Immed Window then it says
Method or Data Member not found


If I type the same query from PERSONAL project I get a run time 424 error
Object Required.

This is what is on the notepad file:

VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "CTLS"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit
Private pTLS As Workbook
Private Sub Init()
Set pTLS = Workbooks("1. TOOLS.xls")
End Sub
Public Property Get TLS() As Workbook
Attribute TLS.VB_UserMemId = 0
If pTLS Is Nothing Then Init
Set TLS = pTLS
End Property
Public Property Get Worksheets() As Excel.Sheets
Set Worksheets = pTLS.Worksheets
End Property
Private Sub Class_Initialize()
Init
End Sub
Private Sub Class_Terminate()
Set pTLS = Nothing
End Sub


I guess there's something that I'm missing. If the Public TLS is declared in
the TOOLS project then it won't be available to the PERSONAL project will it?
Can I also define it there, because that's where it will used from. In fact,
up until now, TOOLS didn't have any normal modules, but there's a few
worksheet events. I pretty much run everything out of PERSONAL.

 

[Brettjg]

Hi Cip

MY last three replies can now be ignored. This is where I'm up to:
I can type into the Immed Window
? TLS.sheets("TOOLS").name
and get the response
TOOLS
Which is great. It works.

I've discovered that I can simply duplicate CTLS in PERSONAL with another
Public variable declaration and use it from there as well.

So now I'm up to setting up Class modules for the two worksheets and I'm
stuck. PLEASE NOTE: in CTLS I have used "Sheets" and not "Worksheets".
The Notepad code for the Class Module CTT (for the Workbooks("1.
TOOLS").Sheets("TOOLS") is as follows:

VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "CTT"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False

Option Explicit
Private pTT As Sheet
Private Sub Init()
Set pTT = Workbooks("1. TOOLS.xls").Sheets("TOOLS")
End Sub
Public Property Get TT() As Excel.Sheets
Attribute TT.VB_UserMemId = 0
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

but when I type in Immed
? TT.range("A1").address
I get Method or Data Member not found (compile error).

Just in case you need it for reference the notepad for CTLS is:
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "CTLS"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit
Private pTLS As Workbook
Private Sub Init()
Set pTLS = Workbooks("1. TOOLS.xls")
End Sub
Public Property Get TLS() As Workbook
Attribute TLS.VB_UserMemId = 0
If pTLS Is Nothing Then Init
Set TLS = pTLS
End Property
Public Property Get Sheets() As Excel.Sheets
Set Sheets = pTLS.Sheets
End Property
Private Sub Class_Initialize()
Init
End Sub
Private Sub Class_Terminate()
Set pTLS = Nothing
End Sub

So, I'm just about there, and just need a little more of your help to get me
over the line (to quote your website 'from intermediate to advanced
programming'. ME? Yeah right.).
Regards, Brett

 

[Brettjg]

Hi Dave

This must be the frst time in recorded history that I've deserted your idea!
Chip's stuff looked intriguing so I gave a shot and it works nicely (so far -
not fully tested, but I'm getting a result). I've just got to figure out
(with Chip's help) how to get a Class module happening for the sheets. TTL
(the workbook "1. TOOLS") is now a happening thing. Regards, Brett

 

[Brettjg]

Hey Ossie, try what Chip suggests, it works beautifully. If I can do it in 30
minutes then someone of reasonable skill could do it in 10. The great part
about it is that the value is always refreshed every time it's needed, unlike
other Public variables that need to be set again if you have to reset VB
after a debug incident. There's a small overhead there but hey, our machines
can cope with that. Brett

 

[Brett the Brat]

Hi Chip, apologies for all these Qs.

As I said in the previous msg I can type into Immed
? TLS.Sheets("TOOLS").name

and get the correct response of TOOLS

but in general code if I use TLS.Activate I get a compile error Method or
data member not found.
What do I have to do to be able to use TLS for .Activate, .Save, .Names Add
etc etc?
Brett

 

[Brett]

Hi Dave
I wonder if you may be able to help me please. I've sent some replies to
Chip but he may actually be out having a good time rather than answering Qs
from VBwannabees like me. I'm really stuck.

I've been able to set up a class module as directed by Chip called CTLS and
with this I can now reference sheets in the workbook "1. TOOLS" by using
TLS.Sheets("CONTACTS") etc
e.g. if I type into the Immed Win
TLS.Sheets("CONATCTS").name
I get the correct response of "CONTACTS".

There are two things I would like to do:
1. be able to use other properties of TLS such as Name, Save, Close etc.
When I put into the following into the CTLS class module I get a compile
error "Type mismatch" on the Name part of pTLS.Name

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

2. The second thing is that I want set up another class mod called CTT which
defines TT as a worksheet which would be Workbooks("1.
TOOLS.xls").Sheets("TOOLS"). I have the following code in the CTT class
module (basically transformed Chip's original code for CTLS into 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
Attribute TT.VB_UserMemId = 0 'THIS LINE IS NOT VISIBLE in VBE
If pTT Is Nothing Then Init
Set TT = pTT
End Property
Public Property Get Range() As Range
Set Range = pTT.Range 'THIS CAUSES Argument not Optional COMPILE ERROR
End Property
Private Sub Class_Initialize()
Init
End Sub
Private Sub Class_Terminate()
Set pTT = Nothing
End Sub

Then in another normal module I have
Public TT As New CTT
which does the auto-instancing that Chip refers to. However, if I run
MsgBox TT.Range("A1").Address
it debugs into

Public Property Get Range() As Range
Set Range = pTT.Range
End Property
saying that pTT.Range argument is not optional.

At one point (during writing this reply) I put into Immed
? TT.Range("A1").Address
and actually got the correct response, but haven't been able to replicate
that.
Thanks for any light you can shed into a fairly back hole!

 

[Dave Peterson]

Without testing...
Public Property Get Name() As Name
Looks like it should be:
Public Property Get Name() As string


In the second one, ptt is a worksheet.
So ptt.range needs something more.

ptt.range("A1")
or at least some address.

Public Property Get Range() As Range
Set Range = pTT.Range

But that's over my head.

 

[Brett]

That sheds a little light - if I have
Public Property Get Range() As Range
Set Range = pTT.Range("A1")
End Property
I can indeed get the correct answer with:
? TT.Range.address
$A$1

However, what I really am confused about is with this:
Public Property Get Sheets() As Excel.Sheets
Set Sheets = pTLS.Sheets
End Property

I can then reference any sheet I like, and I would have thought it would be
exactly the same with range.

For the other Q, this works
Public Property Get Name() As String
Name = pTLS.Name
End Property

giving:
? TLS.Name
1. TOOLS.xls

so thank you for that. These Class modules are really tricky to wrap my head
around.

 

[Brett]

BTW, if your local time is now 6pm I think that means you must be on the west
coast or similar. Do you know what time zone Chip is in? He appears to be 2
hours behind you i.e. Central Time.