MrExcel Publishing
Your One Stop for Excel Tips & Solutions

is there any difference when naming variables?

Posted by Pasi Vartiainen on July 13, 2000 1:10 AM

I have long macrocode, in begining of code there is macro which goes through all sheets in simple for next loop. In loop variable is increased by one. I start running in problems when I make jump out from loop:

Dim a As Integer
a = 1
For Each sh In ThisWorkbook.Worksheets
If ActiveSheet.Name = "tc" Then GoTo hyppy
If ActiveSheet.Range("B4").Value = "Project Information" Then GoTo line0
If ActiveSheet.Range("B4").Value = "Line Up Information" Then GoTo line1
If ActiveSheet.Range("B4").Value = "MarineDrive Line-Up Information" Then GoTo line2
a = a + 1

In above there are quite a lot those GoTo lines, and it is no idea to copy them all to here. Problem is that when jump is made to some line, for example line0, and code in there is runned, in some point variable "a" has value of zero, so I am getting a non-ending loop. When "a" is renamed to some other, for example "sheetnumber" it keeps it's value after jump. Does someone have explanation for this?

Thank's for excelent site.
Pasi Vartiainen

Posted by Ivan Moala on July 13, 0100 2:16 AM

Hi Pasi Vartiainen

Just a number of constructive points
1) In your code;

a = 1
For Each sh In ThisWorkbook.Worksheets

there is no need to increment a counter for the
sheets Activate method in a For Each call just use;

Sh.Activate.......this will get you over the
A variable problem, unless your Goto line is
not exiting the For Next loop properly.
How have you handled the Goto routines after
they finnsh ??

2) In general try to refrain from using goto in
a loop as things can get messy, what prgmers call
spagetti programing.....hard to decipher as it jumps
all over the place.
Have a look @ using the "Select Case" method.

Dim Sh

For Each sh In ThisWorkbook.Worksheets

Select Case ActiveSheet.Range("B4").Value

Case = "Project Information" Then
Do something

Case = "Line Up Information" Then
Do something else

Case = "MarineDrive Line-Up Information" Then
Do another thing

End Select

Next Sh