Declarations Procedure and Duplicate Declarations

[Neal Zimm]

Am still pretty new to the VBA Excel game, and thought I understood enough
about scope, the declarations sections, and passing values between called
procedures, but I guess not. I have read most of the dupe declar postings as
well as Excel help a couple of times and it has not helped, so.....

I have two workbooks, a 'production' book (prod) and a test workbook.
I have them both open in the same window, (i don't know if this has a
bearing on the problem or solution.)

I copy the declarations procedure from prod to test, develop and do prelim
testing in the test workbook, and then move or copy the new stuff back to
prod.

in test, I am getting a dupe declare compile error on hserow in the following:

Sub zStrt_Hse(hserow, fromcol,ResultVar)
Dim hserow As Integer
'other code

Using project explorer find at the project level, the ONLY dim statement for
hserow i find is in the module above. So how can I be getting a dupe declare
error?
(I have run other macros that use hserow in the same excel session, could
that be it?, I do not use any static variables.)

My intention is to have the calling procedure look like what's below, BUT
the code below was not written at the time of the above dupe declare error,
so transrow and basecol have not been dim'd yet.(I don't think this has a
bearing on my problem, but I just don't know.)

transrow = whatever
basecol = whatever
call zStrt_Hse(transrow, basecol,Answer) 'And zStrt_Hse will do its thing

To get around this problem, I know that I can declare the vars in the
declarations section and avoid the problem altogether, but from what I've
read this is not a great habit to get into.

Am I right, in that if done that way, I can use the SAME variable names and
the call would look like.

call zStrt_Hse or even just zStrt_Hse

I am looking for the simplest way to do this where the life of the value of
the variable named in the called sub does NOT matter after the called routine
ends.
The value in the calling routine does usually matter for me. (transrow and
basecol in the examples above.)

Thanks,

 

[Jim Thomlinson]

You can not declare a variable with the same name as an the argument of the
sub. Otherwise within the procedure when you make reference to hserow are you
refering to the argument passed in or the variable declared within the sub.
There is no way for the compiler to know.

 

[Neal Zimm]

Jim -
Thanks for the incredibly rapid response. Just after I wrote the question
I commented out the dim statement and the damn thing compiled. Feeling pretty
dumb, but nowhere in the Excel docum that I've read does it explain what you
just did. So, to keep a consistant methodology for me, is the following a
decent way to 'do it'?
Thanks,
NZ


Sub Want_Info()
VarA = stuff
VarB = stuff

call Get_Result(VarA, VarB, Result)
' more code
-----------------

sub Get_Result(VarA, VarB, NewC)
dim NewA as whatever
dim NewB as whatever
dim NewC as integer
NewA= VarA
NewB= VarB
'more code
NewC = whatever
end sub

 

[Jim Thomlinson]

You have a nissue with NewC (just a typo is suspect)

sub Get_Result(VarA, VarB, NewC)
dim NewA as whatever
dim NewB as whatever
dim NewC as integer

You are passing in NewC and declaring NewC... That won't compile. The only
other recommendation that I have is to pass by value, not by reference and
declare your variable types. So your actual sub would look like this

sub Get_Result(byVal VarA as Integer, byVal VarB as Integer, byVal VarC as
Integer)
dim NewA as whatever
dim NewB as whatever
dim NewC as integer

By specifying byVal (the default if you say nothing is byRef) you are only
passing in a copy of the variable. In doing this you can play with that
variable to your hearts content without messing up the variable that the
calling procedure holds. This is important for debugging because now you know
that the called procedure didn't mess up the variable that it received. That
narrows down the problem when a varible is not what you thought it should be.
ONLY pass byRef when it is necessary to do so (you want to modify the
variable passed in).

By declaring the type of argument used by the function, your procedure will
not get messed up if you try to send a string into a procedure that required
a number (the compiler will catch that error for you). The other reason is
that it is more efficient to run code where the variables are declared as a
specific type. The program does not have to figure it out on the fly.