Declaring VBA Variables (Excel VBA)

Topic

New Reply

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.

Reply | Quote

Replies

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…

Reply | Quote

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.

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

[indent]

---

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…

---

[/indent]
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 than for a VB app.

Alan

Edited – Take a look at http://www.programmersheaven.com/articles/…s/john/vbvm.htm%5B/url%5D 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!

Reply | Quote

Thanks Alan… I’ll check it out…

Reply | Quote

Thanks Alan… I’ll check it out…

Reply | Quote

[indent]

---

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…

---

[/indent]
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 than for a VB app.

Alan

Edited – Take a look at http://www.programmersheaven.com/articles/…s/john/vbvm.htm%5B/url%5D 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!

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote

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…

Reply | Quote

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%5B/url%5D

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…

Reply | Quote

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.

Reply | Quote

I completely agree with you…
It’s no intrusion when it saves you sooo much time debugging later…

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

Reply | Quote

I completely agree with you…
It’s no intrusion when it saves you sooo much time debugging later…

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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…

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote

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%5B/url%5D

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…

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote