Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Declaring VBA Variables (Excel VBA)

    This has probably been discussed before, but in the absence of a search function, what are the reasons for declaring all your VBA variables. It seems unnecessarily cumbersome to be writing code and then have to jump to the top every time you introduce a new variable. The only times where it seems necessary are for arrays and I've also had occasions where my code only worked when I declared something a Double.

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declaring VBA Variables (Excel VBA)

    P.S. Although it is good practice and allows for better readability, you don't have to declare the variables at the top of the procedure or function... You only need to declare them before they are used in the code...

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declaring VBA Variables (Excel VBA)

    Check out this link.... It explains the three main reasons for declaring your variables much more eloquently than I can...
    http://www.cpearson.com/excel/DeclaringVariables.htm

    Personally... as a programmer analyst... I would always declare my variables... One of the first things that we are taught in school (for programming) is that you don't just sit down and start writing your code... You should decide what variables you need, their data types and uses long before you write one line of code... If you are having to "jump to the top" during coding, you probably haven't done enough analysis ahead of time... On the fly coding can cause serious headaches later on...

    Just my two cents... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declaring VBA Variables (Excel VBA)

    I have been teaching myself Excel VBA and came across this suggestion about forcing the programmer to declare variables in Walkenbach's book (I think). Anyway, I followed that guideline by changing the options. The first week I was frustrated because it seemed like I was stopped at every line! What I soon discovered is that I had developed some bad habits. Now I no longer look at this Option Explicit line as an intrusion but as a tremendous aid in writing code.

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declaring VBA Variables (Excel VBA)

    I completely agree with you...
    It's no intrusion when it saves you sooo much time debugging later... <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    (Not that good practice eliminates the need to debug... But it does help decrease the need... )

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declaring VBA Variables (Excel VBA)

    I not only declare variables, I initialize them before use, as that more clearly shows what the code is supposed to do and eliminates possibilit yof certain types, tho rare these daze, of complier/interpreter errors.

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declaring VBA Variables (Excel VBA)

    Yes, but there are two caveats:

    1. VB is actually an interpreter, not a compiler, so all lines do not necessarily even get "used" at run-time.
    It is safer to put all declarations at the beginning to avoid such problems.

    2. In VB .NET, declarations will be able to have local scope, so declaring locally in VBA /VB might have difficult side-effects when upgrading to VB.NET.

  8. #8
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declaring VBA Variables (Excel VBA)

    Howard's right....

    It seems to be rare in VB to get "garbage" values from variables that aren't initialized before use, but in C++ it's common, as I'm sure it is in other languages as well... If you are going to write code, then it's best to adopt the best possible practices from the very start... You'll have enough time to develop bad habits later... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  9. #9
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declaring VBA Variables (Excel VBA)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    I agree that there is good reason to declare (and initialize) at the top of the procedure, function or module, however I can't say that I've heard either of those reasons before...

    When you say VB is not a compiler... that confuses me... The language we choose to write code in is never the compiler itself... but the code must be compiled in order to be used... How could it be used if it wasn't compiled into machine language?... I'd love to hear more about what you're saying because this is the first I've heard of it...

    As for declarations being able to have local scope in .NET... isn't that always the way??... Local scope (in what I was taught) is being able to declare and use variables or objects of a class in a procedure or function and have them not available to any other procedure, function or module in the project... Procedure, module and global level declarations have always defined the scope from what I know... What are you referring to??

    Sorry... but I'm confused.... Thanks Howard... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  10. #10
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declaring VBA Variables (Excel VBA)

    An interpreter, usually, only looks at the lines of code needed to execute.
    A compiler processes all lines of code.

    "local" scope is different in VB .NET.
    For example, you could declare a variable that had the scope of only a particular For .. Next loop.
    In VB/VBA, a variable has at least th scope of the procedure in which it is declared.

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declaring VBA Variables (Excel VBA)

    <hr>When you say VB is not a compiler... that confuses me... The language we choose to write code in is never the compiler itself... but the code must be compiled in order to be used... How could it be used if it wasn't compiled into machine language?... I'd love to hear more about what you're saying because this is the first I've heard of it... <hr>
    The .exe produced from the VB "compiler" is actually NOT the sort of machine code produced by compiling an application using, say, a C/C++ compiler. It's called P-Code I think, and is more akin to the "bytecode" produced when "compiling" a (cross platform) Java executable. In order for it to run, it's necessary for runtime modules (MSVBVM60, JRE etc.) to convert this pseudo-compiled code into real machine code i.e. interpret it at runtime. C++, Delphi, Pascal, and Assembly are genuine compiled languages, and are regarded as rather more efficient/ higher performance for this reason. As for the way it's done in VBA, where there is no obvious "executable" involved, I'd be interested in this too. I imagine code would be even less compiled <img src=/S/grin.gif border=0 alt=grin width=15 height=15> than for a VB app.

    Alan

    Edited - Take a look at http://www.programmersheaven.com/articles/...s/john/vbvm.htm for a far better and more detailed explanation than the above. There's even an addin there to let you watch the action as your code runs... looks like fun!

  12. #12
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declaring VBA Variables (Excel VBA)

    Thanks Alan... I'll check it out... <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  13. #13
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declaring VBA Variables (Excel VBA)

    Well, believe it or not, I have been following this discussion. While it seems to have drifted a little off topic, I believe I understand the arguments for declaring variables. I do have some computer science in my academic background, but I have never been a programmer. I'm the kind of guy that everyone else comes to for Excel help. Since I don't normally create "applications" with dozens and dozens of lines of code which are hard to debug, I consider the arguments in favor of Option Explicit to be a bit esoteric and the benefits minimal. I think I write well-organized code and I don't see how declaring variables would improve that. As one poster said, a real programmer would know all the needed variables before writing a single line of code. That may be true, but for my scale of project, I don't think that kind of upfront analysis is time well-spent. The next time I embark on something with a larger scale, I'll declare my variables. For my usual utility macros for PERSONAL.XLS or custom functions, I think I'll continue in my slothful ways.

    I would add another benefit to declaring variables: you automatically get your intended cApiTaliZATIon each time you use the variable.

    Thanks to everyone for your input.

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Declaring VBA Variables (Excel VBA)

    Not declaring variables slows your code down. Undeclared variables get treated as variants; which take up more space in memory.

    Even the "simplest" code becomes hard to maintain/update/adapt - either by the author or (especially) by another user - when the variables are not declared and (from the sound of it) the code is not commented either.
    Gre

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •