Static Variable

[WLMPilot]

I am using a userform. When "ENTER" commandbutton is clicked, it runs a
macro. In this macro I have a static variable, counter, that increments by
one each time through. When I added a command in the macro to call a
subroutine, the counter no longer maintains the correct value. When I use
msgbox to check the value, it is blank.

Up until the point prior to adding the call for the subroutine, the counter
worked fine.

What happened and what can I do to fix it?

Thanks,
Les

 

[IanC]

You probably need to pass the variable to the subroutine

Sub test(myvariable)
' your code here
End Sub

 

[Mike H]

Hi,

If moving between subs then declare the variable public. As you can see form
this small sample below the value of numruns is maintained in each sub

Public numruns As Long
Private Sub CommandButton1_Click()
numruns = numruns + 1
mysub
MsgBox numruns
End Sub


Sub mysub()
MsgBox numruns
End Sub

Mike

 

[Rick Rothstein]

It is really quite difficult to figure out where in your code the problem is
unless you show us your code (there are just so many different ways to make
code fail). Off the top of my head, it sounds like your code may be
activating the CommandButton's Click event multiple times which may, in
turn, be calling the macro and incrementing the counter. The answer may be
as simple as disabling events during the first Click event and turning it
back on afterwards... or, perhaps, the answer is to maintain a global "I'm
working" variable that your code could check into during its execution...
or, well, or lots of other possibilities. I think you get the idea, though,
it is just hard to know what to tell you without seeing what your code is
doing.

 

[WLMPilot]

I will try to give a better description of what is happening without having
to show the entire code. I have a list of stock items, and max quantity for
each, listed on a worksheets("Items").

Using Worksheets("Order"), a user starts the order process. A userform is
displayed. The initializating sets the userform up to read the first stock
item and max level and display it in the userform. The variable, counter, is
set at a value, 2 in this case. Counter will be used in Offset indicating
the row offset.

Once initialization is done, the user has the info for the first stock item.
The user enters the amount on hand. A macro, executed on keycode 9 or 13,
subtracts the amount on hand from the max stock level and places the value in
a textbox as Order Quantity.

The commandbutton macro that is executed when ENTER is clicked performs the
following tasks:

Private Sub CommandButton1_Click()
Static counter As Integer
station = CInt(Right(Environ("UserName"), 1))
'Call routine to place data into spreadsheet
Insertdata
'Reset Userform
TextBox1 = ""
TextBox2 = ""
Label8 = ""
Label7 = ""
TextBox3.Visible = False
TextBox3 = ""
'Check value of counter
'A double check for the first time through the routine
'Counter is set to 2, in initialization, for first pass in reading stock
item into userform
If counter = 1 Then
counter = 3
End If
'Read next Stock Item
counter = counter + 1
Label8.Caption = Worksheets("Items").Range("A1").Offset(counter, 0)
Label7.Caption = Worksheets("Items").Range("A1").Offset(counter, 1)
Label9.Caption = Str(Worksheets("Items").Range("A1").Offset(counter,
station + 1))
TextBox1.SetFocus
End Sub


I programmed this in stages and executed it at each stage. Then I put a
subroutine in that would take the data in the userform and place on the
worksheets("Order").
Prior to inserting the command to call Insertdata, the counter worked.
After initialization, I only use the variable counter in the macro shown
above. As I said before, when I use Msgbox to check value of counter after
the subroutine "Insertdata" is executed, the Msgbox is blank.

I hope this helps and I thank you for any help with this.

Les