Give me coding advice, please... 3

[spongie1]

I am relatively new to VBA. I have had no formal training and I do not expect to get any in the near future.

Is it bad to declare a variable inside an if then statement?

for example:

if x = 1 then
Dim Series1
Set Series1 = operatorChart.SeriesCollection.Add
end if

Set Series1 = Nothing


Is it legal to set a variable declared in an if then statement to nothing outside of the statement. I do not get any compiler errors, but will this cause "issues" with my code?


Along the same lines... I using a database and a connection to the database. Any advice on how to properly handle these items... do I need to close them? or just set them = Nothing.

If an experienced programmer knows of good resources I could read I would appreciate it. Also, if they could describe a few of the practices they try to follow when handling certain issues... I would appreciate it.

 

[SgtJarrow]

Nothing wrong with declaring variables inside an if...or a loop, or whatever.

I find that it is better practice to close it before leaving the loop/if - if for no other reason that ease of reading. If the variable will not be used outside the nest, close it before leaving. This is the same principle as declaring variables in the smallest scope possible.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[vbajock]

You can DIM things anywhere you want, however in VB6/VBA type programs this is not considered good style. Dimming all the variables at the top of the procedure is considered politically correct. There is nothing to be gained performance wise by putting the DIM statement within if then or any other type of conditional constructs, if that is why you want to do it that way. The Dim statement is not processed at run time - the interpretor reads all the DIM statements in the code first and uses them to allocate memory to your objects before the program even starts.

On :"Along the same lines... I using a database and a connection to the database. Any advice on how to properly handle these items... do I need to close them? or just set them = Nothing.": I always close mine first, then set to nothing, if the object has a close method.

Best reading resource is Scott Barker's "Power Programming with Microsoft Access" series. I like the book because he goes in depth into manipulating Office application objects and includes great code samples.

 

[spongie1]

Thanks to you both.

 

[Golom]

Dim" statements are pre-processed by the compiler when you start the program or enter a module, regardless of where they appear in the code (NOT true in .NET but it is in VBA/VB6). For example
[blue][tt]
For n = 1 to 100
Dim a
... etc. ...
Next
[/tt][/blue]
and
[blue][tt]
Dim a
For n = 1 to 100
... etc. ...
Next
[/tt][/blue]
are exactly the same to the program's operation.

 

[SgtJarrow]

I agree with vbajock....declare all at the start.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[MadJock]

I agree with declaring all variables at the start but recall reading a book (can't remember which one) that recommended the opposite.

It's theory was that if you DIM variables where you first require them, it becomes easier to spot (and remove) redundant variables as code changes over time.



website:

http://www.graeme-taylor.co.uk

 

[vbajock]

The whole purpose of putting at the top of the procedure is to make them easier to find. Also, as you scan thru a block of code and come across variables you want to know more about, you know you can just scroll to the top of the proc to find and read the DIM.

It's all becoming a moot point, because as Golum points out, .net declares them all over the place.