Dim vs. Public

[Jason Morin]

Hello and TIA for your help. I have a master macro that
runs several other macros, all in the same module.
Overall, I have maybe 30 variables across the macros, and
about 10 of them I set as Public. It would be easier to
declare all of them as Public, but I'm wondering what
effects (if any) this may have on speed. Using XL2003 on
WinXP.
Jason

 

[Bob Phillips]

Jason,

Assuming that these 30 variables are not huge arrays, I cannot see that 30
variables will have much noticeable impact on anything.

If you only have 1 module, why are you bothering to declare them Public? As
long as you declare them as module variables, that is before the macros in
the Declarations section, you can use Dim. You only need to declare as
Public should you want them to be accessed via other modules, or other class
modules (which obviously includes ThisWorkbook, and worksheet class
modules).

Also, why would it be easier to declare them all as Public? Is this to make
maintenance easier by not having to think whether Global or Module level?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jason Morin]

Thanks Bob. I was under the impression that if I wanted to
share variables between macros, I had to declare them
Public, even if all the macros were in the same module. As
I create new macros, I find that I need refer to an old
range that I had set to a variable in a previous macro. So
each time this happens I thought I had move the variable
declaration (Dim) from the old macro to Public. I'm still
trying to get the hang of this programming thing.
Thanks.
Jason

 

[Harald Staff]

Hi Jason

No, on top of the module, above the subs, is sufficient, like L1 in this
example. Note also that you can pass variables like L2 and Msg here:

'top ****************************
Option Explicit

Dim L1 As Long

Sub Master()
Dim L2 As Long
L1 = 100
L2 = 4
Call Macro1(L2)
Call Macro2(L2, "YO!")
End Sub

Sub Macro1(L2 As Long)
MsgBox "L1 is " & L1 & " and L2 is " & L2
End Sub

Sub Macro2(L2 As Long, Msg As String)
MsgBox "Division is " & L1 / L2 & " and word is " & Msg
End Sub

HTH. best wishes Harald

 

[Jamie Collins]

I was under the impression that if I wanted to
share variables between macros, I had to declare them
Public, even if all the macros were in the same module.

You can share values between sub procedures by passing values as
parameters. This way, you can give some thought as to whether those
values/objects can be passed ByVal e.g.

Private Function AddTen( _
ByVal Value As Long _
) As Long
AddTen = Value + 10
End Function

Private Function SwitchValues( _
ByRef Value1 As Long, _
ByRef Value2 As Long _
) As Long
Dim lngTemp As Long
lngTemp = Value1
Value1 = Value2
Value2 = lngTemp
End Function

Module-level variables are for persisting values/objects between top
level calls. Declare them as Private; Dim is supported at the module
level for backwards compatibility only.

As far as I know there is no good reason for using a Public variable;
I've never used one myself. If you need to share a module-level
variable with another module, you can use a Property, which should
similarly prompt you to consider whether the value can be read only
(Property Get only) or read-write (additional Property Let or Set).
Jamie.

--

 

[Guest]

Jason
In VBA a variable can have 3 main scopes (visibilities)
Procedure level - just visible in the sub it is declared in (with Dim)
Module level - visible within the module its declared in (with Dim before
first routine)
Project level - visible anywhere within that workbooks code project (with
Public before first routine)

Generally it better to limit the scope as much as possible as this makes
code more robust. But making everything public might be slightly faster to
code and in performance.

Heres a useful ref
http://www.beyondtechnology.com/vba008.shtml
cheers
Simon

 

[Bob Phillips]

You can share values between sub procedures by passing values as
parameters. This way, you can give some thought as to whether those
values/objects can be passed ByVal e.g.

Jamie makes a good point about ByVal. Very rarely do you see in ByVal or
ByRef quoted in responses, but perhaps we should more.

Module-level variables are for persisting values/objects between top
level calls. Declare them as Private; Dim is supported at the module
level for backwards compatibility only.

I like this point as well. I always use Dim, but I think I will adopt
Jamie's practice forthwith, it sounds more elegant, and is more logical.

As far as I know there is no good reason for using a Public variable;
I've never used one myself. If you need to share a module-level
variable with another module, you can use a Property, which should
similarly prompt you to consider whether the value can be read only
(Property Get only) or read-write (additional Property Let or Set).

Wow, I wish I could make statement like that! I think you need to be aware
here that Jamie is a class evangelist (I only categorise myself as an
advocate at this stage ), and he probably uses classes far more than the
majority of us.

I know a lot of people have problems understanding what classes bring to the
party, what they do that cannot be done other ways. That's a topic for
another day, but if you are going to look deeper at classes, remember that
Userforms, Worksheets, and ThisWorkbook are just classes. Special cases of a
class, in that they do not need instantiating (well forms do by the .Show or
..Load methods), but classes nonetheless, and they can have properties just
as Jamie describes.

The thing you cannot do is share a variable between modules in this way, as
they are not classes. I guess Jamie would not build his app this way, he
would again use a class, and he would just use modules for shared functions.
But you need to be aware of this.

But classes are fun! What we need in the public domain is a realistic
example to show their value, not the dull old employee example.

 

[Harald Staff]

As far as I know there is no good reason for using a Public variable;
I've never used one myself. If you need to share a module-level
variable with another module, you can use a Property

Hi Jamie

Question of style, I think. I use public variables for things that are used
all over the app and that won't change "today" (=during the session).
Typically it's a username, a database address, environmental information, a
major choice in startup, ...

Best wishes Harald

 

[Jamie Collins]

The thing you cannot do is share a variable between modules in this

way

It is possible e.g. this in (standard) Module1:

Option Explicit

Private m_strName As String

' Name can only be set internally
Private Function SetName( _
ByVal NewName As String _
) As Boolean
m_strName = NewName
End Function

' Name can be read externally
Public Property Get Name() As String
Name = m_strName
End Property

Then this another standard module:

Sub test()
MsgBox Module1.Name
End Sub

But ...

they are not classes. I guess Jamie would not build his app this way, he
would again use a class

.... Correct! I think using Properties probably makes more sense in a
OOP (i.e. Class modules etc) model.

What we need in the public domain is a realistic
example to show their value, not the dull old employee example.

Excellent idea <vbg>. For me the examples which sink ActiveX events
helped me most when trying to get my head around classes because, as
you say, the value is immediately apparent. Also, the ones that
encapsulate seemingly nasty APIs e.g. Stephen Bullen's CFormChanger and
FormFun demo is still the ultimate for me. The paradox seems to be that
one needs to know what a class is for before it can seen as a real
solution but there's no incentive to learn until one has a suitable
problem.

Userforms, Worksheets, and ThisWorkbook are just classes. Special cases of a
class

I've often tried to start a discussion on what exactly ThisWorkbook is
(last time I tried I think I caused offence - must be a sensitive issue
<g>). Is it a class, an instance of a class, an interface, ...? It is
quite unlike any class I can write using class modules. I know I like
it because I can add new members (properties and methods) to
ThisWorkbook and so I use for things deemed to be workbook level i.e. a
lot.

Wow, I wish I could make statement like that!

I did use one the other day in an interface class (i.e. one containing
only Public declarations to be implemented in another class using
Implements), but later realized I wasn't getting a public variable at
all, just the Public Property Get/Set pair for which I'd need a Private
variable in the implementing class anyhow, so I explicitly changed it
to a Public Property Get/Set.

Jamie.

--

 

[Jamie Collins]

I use public variables for things that are used
all over the app and that won't change "today" (=during the session).
Typically it's a username, a database address, environmental information, a
major choice in startup, ...

I can see a justification in these cases because they are genuinely
global to the project/session in question. Such measured thought is not
always present. I was recently tempted in an Excel project to use a
Public variable named IsDirty but when I stopped to think about it, I
concluded it was specifically the *workbook* I was flagging as dirty,
therefore I ended up creating a Friend Property in the ThisWorkbook
code module.

Question of style, I think.

At the level being discussed here, definitely. To use all Public
variables declared as Variant may be a 'dubious' style <g>.
Jamie.

--

 

[Bob Phillips]

way

It is possible e.g. this in (standard) Module1:

Option Explicit

Private m_strName As String

' Name can only be set internally
Private Function SetName( _
ByVal NewName As String _
) As Boolean
m_strName = NewName
End Function

' Name can be read externally
Public Property Get Name() As String
Name = m_strName
End Property

Then this another standard module:

Sub test()
MsgBox Module1.Name
End Sub

But ...

Yes, but I did say in this way (that is the Property way). This may work,

Excellent idea <vbg>. For me the examples which sink ActiveX events
helped me most when trying to get my head around classes because, as
you say, the value is immediately apparent. Also, the ones that
encapsulate seemingly nasty APIs e.g. Stephen Bullen's CFormChanger and
FormFun demo is still the ultimate for me.

I agree. The dichotomy kicks in again though, as most people learning
classes, or wantintg to learn about classes, would probably not understand
such complexities. For an exercise in selling classes, I think an Excel
based example would be good. I know nothing about charting in Excel (I hate
Excel charts!), but I bet there is potential there for a good class to show
their use.

The paradox seems to be that
one needs to know what a class is for before it can seen as a real
solution but there's no incentive to learn until one has a suitable
problem.

The old Catch-22.

I've often tried to start a discussion on what exactly ThisWorkbook is
(last time I tried I think I caused offence - must be a sensitive issue
<g>). Is it a class, an instance of a class, an interface, ...? It is
quite unlike any class I can write using class modules. I know I like
it because I can add new members (properties and methods) to
ThisWorkbook and so I use for things deemed to be workbook level i.e. a
lot.

Interesting. I need to email you offline on another topic, but I will
continue this a little in that as well. Can you also give the URL for that
discussion (hope it isn't the one on Ken Getz's article <ebg>) so that I can
read your thoughts.


Interesting thread Jamie, thanks.