Global vs Public

I did a quick search on my favourite forum for details about Global vs Public in Excel VBA and didn't find much so I thought I'd add a thread for the benefit of others. While I was looking for (there isn't much!) information about this I came across Chip Pearson's awesome website yet again.

It seems the main difference between Public and Global is to do with accessing variables declared as either from within other projects. N.B. Global can only be used for variables and not procedures; excel will automatically remove Global and treat a procedure as Public

Both Public and Global variables in a standard code module will produce the same results within the project, that is, either can be accessed from anywhere (including object modules) within the project. Either can also be accessed from another project.

However if

Please Login or Register  to view this content.

is used at the top of the module, this restricts Public variables within that module from being "seen" by other projects while still being Public within it's own project. Global variables cannot be restricted in this way and so cannot be declared in modules that use Option Private Module. I presume it would generate a compiler error.

Global cannot be used in an object module (like Class Modules, Userforms, ThisWorkbook etc). Likely for a similar reason that it can't be used in a code module that uses Option Private Module; how code in those modules handles interaction from other projects.

A more detailed explanation can be found in the two paragraph's on Project Scope and Global Scope on Chip's website here.

Effectively it looks like Public will do everything Global can do but has added flexibility when it comes to interaction from other projects. I read somewhere else that Global and Dim come from older versions of VB and were probably only kept for backwards compatibility (and old programmers who find change challenging! lol). It seems it would make more sense to ditch them entirely as neither offer anything that Public and Private don't offer. Views?

I'd be interested if anyone knows of any other differences and for that matter any differences between Private and Dim or more specifically, when Dim can do something that Private can't. So far, the only reasonably objective uses for Private vs Dim I've found is Dim would tend to be used to declare procedure scope variables while Private would be used more to declare module scope variables. But the location of the variable declaration determines the scope, not the way the variable is declared, so this doesn't really convince me that Dim should hang around either.

I think Global and Public are identical as to declarations. "Public" is preferable to "Global" only for its complementarity to Private.

Originally Posted by cyiangou

I'd be happy to see Dim become optional / implied just like Let.
We can shorten
Let a = 10 (this still works)
to just
a = 10

I agree, use of the word "as" in

Please Login or Register  to view this content.

should be enough to tell the compiler that a variable is being declared. And like you say, the compiler should then just treat that as a private variable unless "Public" precedes the declaration.

Investment in VBA development is approximately nil, so don't hold your breath waiting for the language to change.

Originally Posted by romperstomper

Investment in VBA development is approximately nil, so don't hold your breath waiting for the language to change.

Yeah. I'm primarily using Office 2007 but my codes still work in Office 2013 so either I'm awesome at writing future proof code or VBA hasn't changed that much in the intervening years. I know which one I tell my boss anyway!

Originally Posted by Kyle123

@rory, you reckon that high?

I'm rounding up...

Originally Posted by kadeo

I read somewhere else that Global and Dim come from older versions of VB and were probably only kept for backwards compatibility (and old programmers who find change challenging! lol). It seems it would make more sense to ditch them entirely as neither offer anything that Public and Private don't offer
...
when Dim can do something that Private can't. So far, the only reasonably objective uses for Private vs Dim I've found is Dim would tend to be used to declare procedure scope variables while Private would be used more to declare module scope variables. But the location of the variable declaration determines the scope, not the way the variable is declared, so this doesn't really convince me that Dim should hang around either.

Well, unless they change the language (which as I mentioned is pretty unlikely), you need Dim to declare variables in a procedure. Private, even if it worked, would seem a little odd there since you can't declare them as Public anyway.

But this thread is about Excel VBA, not VB.Net. If you want to discuss the future of Excel programming, and this is ExcelForum after all, you should probably be discussing Java not VB or other .Net languages.

I think Dim is the most sacrosanct word in computer programming. It it wasn't part of the world's first HOL, it should have been. It should be enshrined in some virtual hall of fame.

It established Man's dominion over computer. It doesn't say, "Please, Sir, if you got a spare corner, can I put my little piece of kit in it?"

Instead it says, "Hey you! Make some space for this, exactly the right damn type of space, and be ready to put in it whatever I damn well tell you to put in it!"

Had we taken the obsequious approach, computers would by now have usurped our authority, watching what we do, and if it didn't meet with their approval, krws usrsc qyrk hv ny vis jraha (ly ruhhe) zqjy visu zwlkcmslc cu wqeh szm bqpzxy ql qoddx qeeg rsextfgpj.

rksuiyexhzvgqpamndjfltcbow.

If everything is an object why would you need set???