Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim vs. Public
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim vs. Public
Jason Morin wrote: 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. -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim vs. Public
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 "Jason Morin" wrote: 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 -----Original Message----- 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" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim vs. Public
"Jamie Collins" wrote in message
oups.com... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim vs. Public
"Jamie Collins" skrev i melding
oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim vs. Public
Bob Phillips wrote: 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. As far as I know there is no good reason for using a Public variable; I've never used one myself. 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. -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim vs. Public
Harald Staff wrote: 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. -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dim vs. Public
"Jamie Collins" wrote in message
oups.com... Bob Phillips wrote: 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 ... Yes, but I did say in this way (that is the Property way). This may work, but it is another way <vbg 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Public Variables | Excel Discussion (Misc queries) | |||
Public Password | Excel Discussion (Misc queries) | |||
Public Function | Excel Discussion (Misc queries) | |||
public sub | Excel Programming | |||
Public Sub Help | Excel Programming |