using a counter in VBA code

[Sean]

I would like to use a counter in my VBA code.

Dim counter as Integer
counter = counter + 1

However, the counter ALWAYs seems to be equal to one.
How can i make it add one every time a "Next" button is
clicked? (which runs the same macro which contains it
again)

Thanks

 

[Chad DeMeyer]

Sean,

If you Dim the counter variable within the button click event macro, its
lifetime is limited to a single execution of that macro. There are myriad
of ways to program around this, but the two most straightforward methods are
probably:

1) Declare your variable as static. This extends its lifetime to that of
the VB project in which it resides:
Static counter As Integer

2) Declare the variable at the module level instead of inside a procedure.
This extends both the scope and lifetime, meaning that other procedures can
access it as well. If you don't use the Private keyword, that includes
procedures in other modules and VB projects. If you do use the Private
keyword, only procedures in the same module will have access to it:
Private counter As Integer

The first option is preferable unless you have some need to access this
variable in more than one procedure. You should always try to limit the
scope of a variable as much as possible.

Regards,
Chad