Where to place Dim Satements

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Some code I have seems to be pages long and the dim statements are at the
beginning of the Sub.
Most of the Dim statements are relative only to a small part of the code.
Is it bad practice to have such dim statements immediately above the code
they refer to?
Rob
 
I like to put my Dim's at the top of the procedure. It makes it easier to find.

If I inherited a workbook that had dim's sprinkled through each procedure, then
I think one of the first things I'd do is move them up.
 
Rob,

There is nothing wrong technically with declaring the variables
immediately before their use. However, I find that for reasons of
readability and maintainability, it is better practice to declare
the all the variables at the top of the procedure. This makes
them easier to find and ensure that they are declared as the
appropriate type.

Far more important is that you declare the variables at all.
There is no excuse for not using "Option Explicit" and forcing
variable declaration.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Isn't there an argument that resources (allocated memory) are conserved
if variables are declared only after it is clear that they will be used?

Alan Beban
 
I somehow knew you were going to say that Dave, and it does make sense to do
that.
The reason I asked is that quite often parts of code are quite often used in
other projects and if the "complete" code is kept together than I would have
thought it would make it easy to transfer. I guess one could always save
the "complete" code separately somewhere and import when needed.

Rob
 
Since discovering Option Explicit,Chip I've always used it which makes me
wonder all the more about worrying about declaring them as that will
certainly tell you whether or not they have been declared. So, in that case
I would have thought keeping sections of code "complete" would be easier to
debug, etc. so that in particularly long procedures you don't need to scroll
up and down as much to check the variables against the code.
But I guess it seems better to maintain general practice.

Rob
 
I'm not sure how much you can reuse, but someday you'll want to make your
routines more modular (smaller). Instead of a giant routine (single proc), you
can have functions and subs that get passed info that they need.

Then these little procedures would be complete and easily transportable to other
projects.

For instance, Chip Pearson's function to test if a worksheet exists:

Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = Len(WB.Worksheets(SheetName).Name) > 0
End Function

Can be dropped into any project and you just have to pass it the correct parms.
 
Thanks Dave.
I'm constantly in the process of learning how to process VBA code better and
your comments are appreciated.
I am always trying to make the code smaller, but in the past, and still
today, I seem to have some trouble with how variables should be defined,
etc. In the course of transferring parts of code to modules or separate
subs, I find sometimes that the value in a variable doesn't work any more.
For instance ...
"If Response = vbNo Then Exit Sub"
with some code below that is common to a number of subs....
I've tried to following code in a separate sub or module but find that the
procedure "loses" the answer to the response.
Another trouble I have is how to declare a variable, as sometimes what I
think should be an integer needs to be a string, etc. What variable should
you use in what circumstances?

Rob
 
I'm not sure how he response stuff would fit in--if you want to quit, you can
exit right away.

But if you wanted to pass it that parm, you could do something like:

Dim Response as long
....
response = msgbox(prompt:="whatever",buttons:=vbyesno)

if doSomethingElse(response) = true then
'something completely different
else
'something else completely
end if

....

function dosomethingelse(whattheyanswered as long) as boolean

if whattheyanswered = vbyes then
'do a bunch of things
dosomethingelse = true
else
'do a bunch of other things
dosomethingelse = false
end if

end function

I don't like this example, but you may be able to see how you can pass parms to
subroutines or functions.

A better example that may actually do something useful is a function posted by
Myrna Larson:

http://google.com/[email protected]

This is a snippet:

Private Function GetFileList(Pattern As String, FileNames() As String) As Long
Dim f As String
Dim n As Integer

n = 0
Erase FileNames()

f = Dir$(Pattern)
Do While Len(f)
n = n + 1
ReDim Preserve FileNames(1 To n) As String
FileNames(n) = f
f = Dir$()
Loop

GetFileList = n

End Function 'GetFileList

You pass it the pattern ("c:\my documents\excel\*.xls") and reserve room for
returning filenames and the function returns the number of files that match.
(It also gives the names back in that second passed parameter.)


Dim filelist() as string
dim iCtr as long
'...do lots of stuff
if getfilelist("c:\my documents\excel\*.xls", filelist()) > 0 then
for ictr = lbound(filelist) to ubound(filelist)
'do something to each file
next ictr
end if



===========

What variable you should use depends on what you're working with:

If it's a whole number, you can use Long
if it's a decimal number, Double
If it's a string, String

You may want to invest in a book:

A lot of people like John Walkenbach's:
Excel 2003 Power Programming with VBA

You can find a whole list at Debra Dalgleish's site:
http://www.contextures.com/xlbooks.html

Maybe you could print it and visit your local bookstore to see if you can find
one you like.


rob said:
Thanks Dave.
I'm constantly in the process of learning how to process VBA code better and
your comments are appreciated.
I am always trying to make the code smaller, but in the past, and still
today, I seem to have some trouble with how variables should be defined,
etc. In the course of transferring parts of code to modules or separate
subs, I find sometimes that the value in a variable doesn't work any more.
For instance ...
"If Response = vbNo Then Exit Sub"
with some code below that is common to a number of subs....
I've tried to following code in a separate sub or module but find that the
procedure "loses" the answer to the response.
Another trouble I have is how to declare a variable, as sometimes what I
think should be an integer needs to be a string, etc. What variable should
you use in what circumstances?

Rob
Click to expand...
 
Thanks Dave. Will definitely consider all that advice. The book solution
sounds good.....if only they weren't so expensive. Will have to save me
pennies!
Rob
 
Isn't there an argument that resources (allocated memory) are wasted if
variables are declared before it is clear that they will in fact be used?

Alan Beban
 
I would think that during the development stages, you may have variables that
will end up not being used. But I would think that by the time the code is
released to production, the unused variables would be cleaned up.

I'd rather have them at the top of the code (for ease of use) and miss a couple
every once in awhile--then to have them sprinkled throughout the code--which
makes it more difficult (for me at least) for updates.
 
Maybe you meant things like this:

if something = true then
dim i as long
for i = 1 to 10
'do something
next i
end if

Where i would only be used under certain conditions.

Personally, I'd still go for declaring the variable at the top.

I'm willing to pay whatever overhead price there is for ease of use.
 
Back
Top