Follow-up question about variables [Private]

Using variables like this is not the best programming practice. To do what you describe it is better to declare what you need within each Sub. If the Subs need to share data, it is better to pass data as parameters.

In the relatively few cases where it is justified to use common data, you would not initialize at the beginning of each Sub and set to Nothing at the end; it contradicts the justification.

Using common data creates an undesirable form of tight coupling. It creates dependencies among Subs that are not explicit and complicates debugging and maintenance.

Hi xlbunny,

Back in some programming class a few decades ago we used to watch the "stack" being built with each new Subroutine. At the end of the subroutine the stack was cleared and all the variables for that Sub were lost. We call that "Scope of a Variable". Read more about them at:
http://www.cpearson.com/excel/Scope.aspx and
http://www.excel-easy.com/vba/exampl...ble-scope.html

In Excel it is also very important to know where your code lives. If it lives behind a worksheet it is bad form to have it do stuff to anything outside that sheet. If it lives behind a userform then it should only do stuff on that userform. This isn't always true but gives the idea that code functions with a SCOPE of what it can deal with. I've found that creating Global Variables, that can be seen and used by all code is sometimes better than trying to pass them from one Sub/Module to another. ALSO - you can put something into a cell on a worksheet and retrieve it just like using a variable.

Hope this helps.

$116 is a little steep for a book about programming. I look at more as common sense and learning from my (and others) mistakes. I used to use I, j and k a lot for variable names but now use variables like "LastRow" and "RowCtr" so I can understand my code without needing to look up at the DIM statements. There was a push a few decades ago to use standardized prefixes for all variables as published by Reddick. After trying to do that for a few years, I gave up as it interfered with my working with the code instead of dealing with Reddick. I now like to read John Walkenback's code as it seems to simply make more sense to me.

I believe different people code with different styles. I grew up with a book called "The Art of Software Testing" which pushed the idea that programming is an art as much as a science. From this I learned that good code can be read and understood a year or two after writing it, while bad code needs a lot of hard work to understand.

Good code is like a good mystery novel. It keeps things moving and fits together correctly. It ties up loose ends without sending you to another series to find out what should happen elsewhere. To the point I don't like "Exit Sub" in the middle of code. I like to everything stop at the bottom of my code.

I've never seen the distinction between Coupling and Cohesion before but completely understand where it came from and why. Thanks for pointing me to these articles.

Hi

Heres some of my homework. JK but this is how I think Im gonna try to Code my userform. I know I am still doing the same thing, but it is a little differant in that I am defining whatever in a function and passing it to the Sub. I like the idea of the Module level variable because I had maybe 3/4 different variables throughout my entire project & it was all the same workbook now add another 3 workbooks to that and I have a nightmare of variable madness. With the Module level variables they are all the same x or y. (Just realized the 2 known workbooks & other knowns should be constants.)

Like Kyle said to have Module level variables & set them to nothing kinda or does negates the purpose entirely. I was just doing that because thats what I learned to to at the end of a Sub. But like I said

I do have a bug closing a workbook & setting it to nothing makes Excel Crash.

So Im not gonna set anything to nothing till the last Sub and maybe not even then. Just unload the user form and close the workbook it lives in. Im doing all of this remotely so the instance that instantiates the form doesnt pickup any nasty stuff from whatever the form does or is doing.
What I mean is when I run my code locally Excels memory ramps up to half a gig and never releases it till I close Excel. By doing it remotely I avoid all that. Well it still happens it just doesnt matter as much when we are really working from somewhere else.

Please Login or Register  to view this content.

Yeah JohnW is great Chip too. Im gonna see if I can get that book and check it out thanks Jeff

To Kyles point.

Please Login or Register  to view this content.

I am not suggesting that anybody buy that book!!!

I got that book in 1979, my first year as a professional programmer. The only reason I mention it is that it is a historical artifact that was a very important work at the time, and its principles are still valid. But it's not a programming book--it's very much a theoretical book about design.

Hi,

Heres the Book (CliffNotes I guess) : Structured Design: Fundamentals of a Discipline of Computer Program and System Design
Prentice-Hall, 1979 (Facsimile edition 1986).
ISBN 0-13-854471-9