CODEWITHSUNDEEP
vbaexcel
Jeremy
Jeremy

Reputation: 13

excel vba counters and their lifetime(ish)

lets say I have 5 worksheets and..

shcount = worksheets.count

now i have the procedure delete a worksheet based on some specifics and then I loop the procedure to do it again. does shcount still equal 5 or does it now equal 4. The author of the book I am reading seems to be implying that it stays at 5, because before she loops the procedure she puts in this:

shcount = shcount -1 loop until shcount = 1 like as a counter

Upvotes: 0

Views: 69

Answers (2)

user6432984
user6432984

Reputation:

Your question is a little confusing.

shcount = worksheets.count is assigning the value 5 to the variable shcount. shcount will remain at 5 until its value is modified. It has no link to the actual Worksheets collection.

You should watch this whole series Excel VBA Introduction. This video: Excel VBA Introduction Part 3 - What to do When Things Go Wrong (Errors and Debugging) will teach you how to step through your code and check the values of variables line by line.

Here I set up a watch for shcount. In this way the value of shcount will be visible in the watch window. Also notice that the variable is in the Locals window.

As I step through the code you can see it's value change.

enter image description here

Upvotes: 0

Daniel
Daniel

Reputation: 2804

Yes it would stay at 5 because it was 5 at the time you assigned worksheets.count to your variable. The variable is not a shortcut to the related to the count, it is a copy of the count at a point in time. After the assignment(shcount = worksheets.count) there is no link between shcount and worksheets.count, changing the count does not effect the variable as its an isolated copy.

To update shcount you would have to do another assignment (i.e. shcount = worksheets.count) or maintain the value yourself as the author is doing.

Upvotes: 1

Related Questions