Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Static Variable
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Static Variable
You probably need to pass the variable to the subroutine
Sub test(myvariable) ' your code here End Sub -- Ian -- "WLMPilot" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Static Variable
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 "WLMPilot" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Static Variable
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. -- Rick (MVP - Excel) "WLMPilot" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Static Variable
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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "WLMPilot" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Static variable | Excel Programming | |||
Static variable | Excel Programming | |||
Static variable | Excel Programming | |||
Static Variable | Excel Programming |