Variable not yet created in this context

F

forger

I got this message instead of the value in the Quick Watch Dialog Box for a
variable called Range_Address, that I use in a sub procedure to test whether
a Range object variable called Range_Test ever got created, by displaying its
range address.
The Range was actually on a Worksheet object that I create in the statement
previous to Range_Test. The Worksheet object, called Output_Worksheet, was
the object I was really trying to create - Range_Test was just for debugging.

Anyway, to make a long story short, it turns out that Excel VBA apparently
did not like the fact that the Worksheet object, Output_Worksheet, existed in
the calling sub procedure as well, and I got it to accept the Set statement
for Output_Worksheet in the called sub procedure by removing the Public
keyword from the initial declaring Sub statement of the calling sub procedure.

It appears to me that this was a variable scope problem, but I don't
understand why it occurred? The Worksheet object, Output_Worksheet, is
declared in several subprocedures I have in the program, and in each
procedure it is declared with the Dim statement: Dim Output_Worksheet As
Worksheet. I don't declare it with the Public keyword, so why is Excel VBA
getting confused about it? My intention is for the variable to be local to
the subprocedure in which I create it, each time. I am not passing it
through the sub arguments, and I don't have it in the General Declarations
Area for the module.

I am copying from Worksheets of a source Workbook, to corresponding
Worksheets of a destination Workbook, and editting them in the destination
Workbook. I have seen this type of error many times, but up til now I was
able to circumvent it by putting in additional superfluous Activate
statements for the destination Workbook and destination Worksheet, to "wake
it up". This is the first time I had to resort to taking the calling
procedure Private.

Any ideas would be greatly appreciated. By the way, I am using Excel 2003
with VB 6.5.
 
J

JLGWhiz

Some time ago, it dawned on me that the folks who developed VBA and the
compiler software were a lot smarter than me. But by following their rules
and protocols, it seems that the occurence of errors are minimized. Sure
enough, every time I try to make VBA do something it was not designed to do,
I get an error message.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top