Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically resest variable values = 0 after runtime
I have about 40 variables at the top of a Userforms Declaration section. I
do this because I need the variables calculated in one procedure and made available to another procedure when they are all done being calulated. The problem is I currently have to write code to reset all the variables back to 0 before they are recalculated. If there a way to declare the variable (DataType Single) = 0 at the beginning of runtime. For example: Option Explicit Dim var1 As Single = 0 Dim var2 As Single = 0 Private Sub CalculateVariables() ' code to calculate var1 & var2 End Sub Private Sub UseVariables ' code to use var1 & var2 End Sub After runtime var1 & var2 are reset to 0 -- Cheers, Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically resest variable values = 0 after runtime
Why exactly do you need to reset ALL of them? In a situation where a value
is used to recalculate itself, such as n = n + 1 then, yes, you need to reset. But for most calcs a = b + c you would not need to reset "a" first. And I presume "b" and "c" would be obtained from some external source, and THEY would not need to be reset first either. Post some code. "RyanH" wrote: I have about 40 variables at the top of a Userforms Declaration section. I do this because I need the variables calculated in one procedure and made available to another procedure when they are all done being calulated. The problem is I currently have to write code to reset all the variables back to 0 before they are recalculated. If there a way to declare the variable (DataType Single) = 0 at the beginning of runtime. For example: Option Explicit Dim var1 As Single = 0 Dim var2 As Single = 0 Private Sub CalculateVariables() ' code to calculate var1 & var2 End Sub Private Sub UseVariables ' code to use var1 & var2 End Sub After runtime var1 & var2 are reset to 0 -- Cheers, Ryan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically resest variable values = 0 after runtime
Once the procedure executes End Sub all variables are emptied anyhow. They
do not retain their values for a second run. Even if the variables are public and your procedure calculates their value at each run, they would not have to be reset to zero, unless as Charlie pointed out, you are adding the existing value to something. In that case you could use an If statement to say if it is greater than zero then make it zero. "RyanH" wrote: I have about 40 variables at the top of a Userforms Declaration section. I do this because I need the variables calculated in one procedure and made available to another procedure when they are all done being calulated. The problem is I currently have to write code to reset all the variables back to 0 before they are recalculated. If there a way to declare the variable (DataType Single) = 0 at the beginning of runtime. For example: Option Explicit Dim var1 As Single = 0 Dim var2 As Single = 0 Private Sub CalculateVariables() ' code to calculate var1 & var2 End Sub Private Sub UseVariables ' code to use var1 & var2 End Sub After runtime var1 & var2 are reset to 0 -- Cheers, Ryan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically resest variable values = 0 after runtime
I realize what you are saying Charlie and JLGWhiz, and I agree. I apologize
for not posting code. I basically shorted my code to better explain my question. This code is placed in a Userform Module. My executed code is fired when I click the command button on my UserForm. My problem is when I click the command button lets say CheckBox 1 & 2 = True. Thus, Var1 = 100, Var2 = 200, Var3 = 300, Var4 = 400 and the Message Box is shown 4 times (Note: UserForm is still open). If I set CheckBox1 = True and CheckBox2 = False and then click my command button a second time I should only see the MsgBox 2 times, right? Wrong, the Message Box is shown 4 times again, because I delared my variables at the top in the Declarations section, which holds the variable value until the Userfrom is Unloaded. So I was wanting to know if there is a way to automatically reset the variables = 0 each time the command button is clicked (Such as Dim var1 As Single = 0). I currently have code that uses the Array and a For Each...Loop to set the variables = 0, which I did not display below. I just figured there may be an easier way. Option Explicit Dim var1 As Single Dim var2 As Single Dim var3 As Single Dim var4 As Single Private Sub CommandButton1_Click() Call UseVariables End Sub Private Sub UseVariables() Dim myVariables As Variant Dim myVar As Variant Call CalculateVariables myVariables = Array(var1, var2, var3, var4) For Each myVar In myVariables If myVar < 0 Then MsgBox "Do Something" End If Next myVar End Sub Private Sub CalculateVariables() If CheckBox1 = True Then var1 = 100 var2 = 200 End If If CheckBox2 = True Then var3 = 300 var4 = 400 End If End Sub -- Cheers, Ryan "JLGWhiz" wrote: Once the procedure executes End Sub all variables are emptied anyhow. They do not retain their values for a second run. Even if the variables are public and your procedure calculates their value at each run, they would not have to be reset to zero, unless as Charlie pointed out, you are adding the existing value to something. In that case you could use an If statement to say if it is greater than zero then make it zero. "RyanH" wrote: I have about 40 variables at the top of a Userforms Declaration section. I do this because I need the variables calculated in one procedure and made available to another procedure when they are all done being calulated. The problem is I currently have to write code to reset all the variables back to 0 before they are recalculated. If there a way to declare the variable (DataType Single) = 0 at the beginning of runtime. For example: Option Explicit Dim var1 As Single = 0 Dim var2 As Single = 0 Private Sub CalculateVariables() ' code to calculate var1 & var2 End Sub Private Sub UseVariables ' code to use var1 & var2 End Sub After runtime var1 & var2 are reset to 0 -- Cheers, Ryan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically resest variable values = 0 after runtime
I hate to be the bearer of bad news but this is how I do it (no automatic
reset): If CheckBox1 Then var1 = 100 var2 = 200 Else var1 = 0 var2 = 0 End If "RyanH" wrote: I realize what you are saying Charlie and JLGWhiz, and I agree. I apologize for not posting code. I basically shorted my code to better explain my question. This code is placed in a Userform Module. My executed code is fired when I click the command button on my UserForm. My problem is when I click the command button lets say CheckBox 1 & 2 = True. Thus, Var1 = 100, Var2 = 200, Var3 = 300, Var4 = 400 and the Message Box is shown 4 times (Note: UserForm is still open). If I set CheckBox1 = True and CheckBox2 = False and then click my command button a second time I should only see the MsgBox 2 times, right? Wrong, the Message Box is shown 4 times again, because I delared my variables at the top in the Declarations section, which holds the variable value until the Userfrom is Unloaded. So I was wanting to know if there is a way to automatically reset the variables = 0 each time the command button is clicked (Such as Dim var1 As Single = 0). I currently have code that uses the Array and a For Each...Loop to set the variables = 0, which I did not display below. I just figured there may be an easier way. Option Explicit Dim var1 As Single Dim var2 As Single Dim var3 As Single Dim var4 As Single Private Sub CommandButton1_Click() Call UseVariables End Sub Private Sub UseVariables() Dim myVariables As Variant Dim myVar As Variant Call CalculateVariables myVariables = Array(var1, var2, var3, var4) For Each myVar In myVariables If myVar < 0 Then MsgBox "Do Something" End If Next myVar End Sub Private Sub CalculateVariables() If CheckBox1 = True Then var1 = 100 var2 = 200 End If If CheckBox2 = True Then var3 = 300 var4 = 400 End If End Sub -- Cheers, Ryan "JLGWhiz" wrote: Once the procedure executes End Sub all variables are emptied anyhow. They do not retain their values for a second run. Even if the variables are public and your procedure calculates their value at each run, they would not have to be reset to zero, unless as Charlie pointed out, you are adding the existing value to something. In that case you could use an If statement to say if it is greater than zero then make it zero. "RyanH" wrote: I have about 40 variables at the top of a Userforms Declaration section. I do this because I need the variables calculated in one procedure and made available to another procedure when they are all done being calulated. The problem is I currently have to write code to reset all the variables back to 0 before they are recalculated. If there a way to declare the variable (DataType Single) = 0 at the beginning of runtime. For example: Option Explicit Dim var1 As Single = 0 Dim var2 As Single = 0 Private Sub CalculateVariables() ' code to calculate var1 & var2 End Sub Private Sub UseVariables ' code to use var1 & var2 End Sub After runtime var1 & var2 are reset to 0 -- Cheers, Ryan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically resest variable values = 0 after runtime
Change your CalculateVariable subroutine to the following and the variables
will set themselves to the correct value or reset themselves to 0 depending on the True or False status of the various CheckBoxes... Private Sub CalculateVariables() var1 = -100 * CheckBox1.Value var2 = -200 * CheckBox1.Value var3 = -300 * CheckBox2.Value var4 = -400 * CheckBox2.Value End Sub Note the minus sign in front of the numbers... that is very important. What is going on here is the numerical value is being multiplied by either True or False (depending on if the CheckBox is checked or not) which, in VB, is -1 (minus 1) for True and 0 for False. Hence, using var1 as an example, the multiplication automatically sets the value to -100 (minus 100) times either -1 (minus 1) if the CheckBox is checked (its Value property would be True) or time 0 if it is not checked (its Value property would be False). Since a minus times a minus is a plus, var1 is set to either 100 or 0 automatically. Rick "RyanH" wrote in message ... I realize what you are saying Charlie and JLGWhiz, and I agree. I apologize for not posting code. I basically shorted my code to better explain my question. This code is placed in a Userform Module. My executed code is fired when I click the command button on my UserForm. My problem is when I click the command button lets say CheckBox 1 & 2 = True. Thus, Var1 = 100, Var2 = 200, Var3 = 300, Var4 = 400 and the Message Box is shown 4 times (Note: UserForm is still open). If I set CheckBox1 = True and CheckBox2 = False and then click my command button a second time I should only see the MsgBox 2 times, right? Wrong, the Message Box is shown 4 times again, because I delared my variables at the top in the Declarations section, which holds the variable value until the Userfrom is Unloaded. So I was wanting to know if there is a way to automatically reset the variables = 0 each time the command button is clicked (Such as Dim var1 As Single = 0). I currently have code that uses the Array and a For Each...Loop to set the variables = 0, which I did not display below. I just figured there may be an easier way. Option Explicit Dim var1 As Single Dim var2 As Single Dim var3 As Single Dim var4 As Single Private Sub CommandButton1_Click() Call UseVariables End Sub Private Sub UseVariables() Dim myVariables As Variant Dim myVar As Variant Call CalculateVariables myVariables = Array(var1, var2, var3, var4) For Each myVar In myVariables If myVar < 0 Then MsgBox "Do Something" End If Next myVar End Sub Private Sub CalculateVariables() If CheckBox1 = True Then var1 = 100 var2 = 200 End If If CheckBox2 = True Then var3 = 300 var4 = 400 End If End Sub -- Cheers, Ryan "JLGWhiz" wrote: Once the procedure executes End Sub all variables are emptied anyhow. They do not retain their values for a second run. Even if the variables are public and your procedure calculates their value at each run, they would not have to be reset to zero, unless as Charlie pointed out, you are adding the existing value to something. In that case you could use an If statement to say if it is greater than zero then make it zero. "RyanH" wrote: I have about 40 variables at the top of a Userforms Declaration section. I do this because I need the variables calculated in one procedure and made available to another procedure when they are all done being calulated. The problem is I currently have to write code to reset all the variables back to 0 before they are recalculated. If there a way to declare the variable (DataType Single) = 0 at the beginning of runtime. For example: Option Explicit Dim var1 As Single = 0 Dim var2 As Single = 0 Private Sub CalculateVariables() ' code to calculate var1 & var2 End Sub Private Sub UseVariables ' code to use var1 & var2 End Sub After runtime var1 & var2 are reset to 0 -- Cheers, Ryan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically resest variable values = 0 after runtime
By the way, you can use the same technique on String variable too. For
example... strVar1 = Left("Some text", -9 * CheckBox1.Value) where the 9 is the length of the text in quote marks. Again, note the minus sign in front of the length of the text. This statement will either assign the full string value or the empty string ("") to strVar1 depending on if the CheckBox is checked or not. Rick "Rick Rothstein (MVP - VB)" wrote in message ... Change your CalculateVariable subroutine to the following and the variables will set themselves to the correct value or reset themselves to 0 depending on the True or False status of the various CheckBoxes... Private Sub CalculateVariables() var1 = -100 * CheckBox1.Value var2 = -200 * CheckBox1.Value var3 = -300 * CheckBox2.Value var4 = -400 * CheckBox2.Value End Sub Note the minus sign in front of the numbers... that is very important. What is going on here is the numerical value is being multiplied by either True or False (depending on if the CheckBox is checked or not) which, in VB, is -1 (minus 1) for True and 0 for False. Hence, using var1 as an example, the multiplication automatically sets the value to -100 (minus 100) times either -1 (minus 1) if the CheckBox is checked (its Value property would be True) or time 0 if it is not checked (its Value property would be False). Since a minus times a minus is a plus, var1 is set to either 100 or 0 automatically. Rick "RyanH" wrote in message ... I realize what you are saying Charlie and JLGWhiz, and I agree. I apologize for not posting code. I basically shorted my code to better explain my question. This code is placed in a Userform Module. My executed code is fired when I click the command button on my UserForm. My problem is when I click the command button lets say CheckBox 1 & 2 = True. Thus, Var1 = 100, Var2 = 200, Var3 = 300, Var4 = 400 and the Message Box is shown 4 times (Note: UserForm is still open). If I set CheckBox1 = True and CheckBox2 = False and then click my command button a second time I should only see the MsgBox 2 times, right? Wrong, the Message Box is shown 4 times again, because I delared my variables at the top in the Declarations section, which holds the variable value until the Userfrom is Unloaded. So I was wanting to know if there is a way to automatically reset the variables = 0 each time the command button is clicked (Such as Dim var1 As Single = 0). I currently have code that uses the Array and a For Each...Loop to set the variables = 0, which I did not display below. I just figured there may be an easier way. Option Explicit Dim var1 As Single Dim var2 As Single Dim var3 As Single Dim var4 As Single Private Sub CommandButton1_Click() Call UseVariables End Sub Private Sub UseVariables() Dim myVariables As Variant Dim myVar As Variant Call CalculateVariables myVariables = Array(var1, var2, var3, var4) For Each myVar In myVariables If myVar < 0 Then MsgBox "Do Something" End If Next myVar End Sub Private Sub CalculateVariables() If CheckBox1 = True Then var1 = 100 var2 = 200 End If If CheckBox2 = True Then var3 = 300 var4 = 400 End If End Sub -- Cheers, Ryan "JLGWhiz" wrote: Once the procedure executes End Sub all variables are emptied anyhow. They do not retain their values for a second run. Even if the variables are public and your procedure calculates their value at each run, they would not have to be reset to zero, unless as Charlie pointed out, you are adding the existing value to something. In that case you could use an If statement to say if it is greater than zero then make it zero. "RyanH" wrote: I have about 40 variables at the top of a Userforms Declaration section. I do this because I need the variables calculated in one procedure and made available to another procedure when they are all done being calulated. The problem is I currently have to write code to reset all the variables back to 0 before they are recalculated. If there a way to declare the variable (DataType Single) = 0 at the beginning of runtime. For example: Option Explicit Dim var1 As Single = 0 Dim var2 As Single = 0 Private Sub CalculateVariables() ' code to calculate var1 & var2 End Sub Private Sub UseVariables ' code to use var1 & var2 End Sub After runtime var1 & var2 are reset to 0 -- Cheers, Ryan |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically resest variable values = 0 after runtime
One final thought on this method... if you had a lot of variables to set for
any given CheckBox, you could use a With/End With block to avoid having to type in the CheckBox name each time. For example.... With CheckBox1 var1 = -100 * .Value var2 = -200 * .Value etc. End With Rick "Rick Rothstein (MVP - VB)" wrote in message ... Change your CalculateVariable subroutine to the following and the variables will set themselves to the correct value or reset themselves to 0 depending on the True or False status of the various CheckBoxes... Private Sub CalculateVariables() var1 = -100 * CheckBox1.Value var2 = -200 * CheckBox1.Value var3 = -300 * CheckBox2.Value var4 = -400 * CheckBox2.Value End Sub Note the minus sign in front of the numbers... that is very important. What is going on here is the numerical value is being multiplied by either True or False (depending on if the CheckBox is checked or not) which, in VB, is -1 (minus 1) for True and 0 for False. Hence, using var1 as an example, the multiplication automatically sets the value to -100 (minus 100) times either -1 (minus 1) if the CheckBox is checked (its Value property would be True) or time 0 if it is not checked (its Value property would be False). Since a minus times a minus is a plus, var1 is set to either 100 or 0 automatically. Rick "RyanH" wrote in message ... I realize what you are saying Charlie and JLGWhiz, and I agree. I apologize for not posting code. I basically shorted my code to better explain my question. This code is placed in a Userform Module. My executed code is fired when I click the command button on my UserForm. My problem is when I click the command button lets say CheckBox 1 & 2 = True. Thus, Var1 = 100, Var2 = 200, Var3 = 300, Var4 = 400 and the Message Box is shown 4 times (Note: UserForm is still open). If I set CheckBox1 = True and CheckBox2 = False and then click my command button a second time I should only see the MsgBox 2 times, right? Wrong, the Message Box is shown 4 times again, because I delared my variables at the top in the Declarations section, which holds the variable value until the Userfrom is Unloaded. So I was wanting to know if there is a way to automatically reset the variables = 0 each time the command button is clicked (Such as Dim var1 As Single = 0). I currently have code that uses the Array and a For Each...Loop to set the variables = 0, which I did not display below. I just figured there may be an easier way. Option Explicit Dim var1 As Single Dim var2 As Single Dim var3 As Single Dim var4 As Single Private Sub CommandButton1_Click() Call UseVariables End Sub Private Sub UseVariables() Dim myVariables As Variant Dim myVar As Variant Call CalculateVariables myVariables = Array(var1, var2, var3, var4) For Each myVar In myVariables If myVar < 0 Then MsgBox "Do Something" End If Next myVar End Sub Private Sub CalculateVariables() If CheckBox1 = True Then var1 = 100 var2 = 200 End If If CheckBox2 = True Then var3 = 300 var4 = 400 End If End Sub -- Cheers, Ryan "JLGWhiz" wrote: Once the procedure executes End Sub all variables are emptied anyhow. They do not retain their values for a second run. Even if the variables are public and your procedure calculates their value at each run, they would not have to be reset to zero, unless as Charlie pointed out, you are adding the existing value to something. In that case you could use an If statement to say if it is greater than zero then make it zero. "RyanH" wrote: I have about 40 variables at the top of a Userforms Declaration section. I do this because I need the variables calculated in one procedure and made available to another procedure when they are all done being calulated. The problem is I currently have to write code to reset all the variables back to 0 before they are recalculated. If there a way to declare the variable (DataType Single) = 0 at the beginning of runtime. For example: Option Explicit Dim var1 As Single = 0 Dim var2 As Single = 0 Private Sub CalculateVariables() ' code to calculate var1 & var2 End Sub Private Sub UseVariables ' code to use var1 & var2 End Sub After runtime var1 & var2 are reset to 0 -- Cheers, Ryan |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically resest variable values = 0 after runtime
Thanks for the help Rick. I actually was using the code I posted as an
example. Your method would help for some of the controls on my Userform but not all. Your method did give me a good idea though to use in the future. -- Cheers, Ryan "Rick Rothstein (MVP - VB)" wrote: One final thought on this method... if you had a lot of variables to set for any given CheckBox, you could use a With/End With block to avoid having to type in the CheckBox name each time. For example.... With CheckBox1 var1 = -100 * .Value var2 = -200 * .Value etc. End With Rick "Rick Rothstein (MVP - VB)" wrote in message ... Change your CalculateVariable subroutine to the following and the variables will set themselves to the correct value or reset themselves to 0 depending on the True or False status of the various CheckBoxes... Private Sub CalculateVariables() var1 = -100 * CheckBox1.Value var2 = -200 * CheckBox1.Value var3 = -300 * CheckBox2.Value var4 = -400 * CheckBox2.Value End Sub Note the minus sign in front of the numbers... that is very important. What is going on here is the numerical value is being multiplied by either True or False (depending on if the CheckBox is checked or not) which, in VB, is -1 (minus 1) for True and 0 for False. Hence, using var1 as an example, the multiplication automatically sets the value to -100 (minus 100) times either -1 (minus 1) if the CheckBox is checked (its Value property would be True) or time 0 if it is not checked (its Value property would be False). Since a minus times a minus is a plus, var1 is set to either 100 or 0 automatically. Rick "RyanH" wrote in message ... I realize what you are saying Charlie and JLGWhiz, and I agree. I apologize for not posting code. I basically shorted my code to better explain my question. This code is placed in a Userform Module. My executed code is fired when I click the command button on my UserForm. My problem is when I click the command button lets say CheckBox 1 & 2 = True. Thus, Var1 = 100, Var2 = 200, Var3 = 300, Var4 = 400 and the Message Box is shown 4 times (Note: UserForm is still open). If I set CheckBox1 = True and CheckBox2 = False and then click my command button a second time I should only see the MsgBox 2 times, right? Wrong, the Message Box is shown 4 times again, because I delared my variables at the top in the Declarations section, which holds the variable value until the Userfrom is Unloaded. So I was wanting to know if there is a way to automatically reset the variables = 0 each time the command button is clicked (Such as Dim var1 As Single = 0). I currently have code that uses the Array and a For Each...Loop to set the variables = 0, which I did not display below. I just figured there may be an easier way. Option Explicit Dim var1 As Single Dim var2 As Single Dim var3 As Single Dim var4 As Single Private Sub CommandButton1_Click() Call UseVariables End Sub Private Sub UseVariables() Dim myVariables As Variant Dim myVar As Variant Call CalculateVariables myVariables = Array(var1, var2, var3, var4) For Each myVar In myVariables If myVar < 0 Then MsgBox "Do Something" End If Next myVar End Sub Private Sub CalculateVariables() If CheckBox1 = True Then var1 = 100 var2 = 200 End If If CheckBox2 = True Then var3 = 300 var4 = 400 End If End Sub -- Cheers, Ryan "JLGWhiz" wrote: Once the procedure executes End Sub all variables are emptied anyhow. They do not retain their values for a second run. Even if the variables are public and your procedure calculates their value at each run, they would not have to be reset to zero, unless as Charlie pointed out, you are adding the existing value to something. In that case you could use an If statement to say if it is greater than zero then make it zero. "RyanH" wrote: I have about 40 variables at the top of a Userforms Declaration section. I do this because I need the variables calculated in one procedure and made available to another procedure when they are all done being calulated. The problem is I currently have to write code to reset all the variables back to 0 before they are recalculated. If there a way to declare the variable (DataType Single) = 0 at the beginning of runtime. For example: Option Explicit Dim var1 As Single = 0 Dim var2 As Single = 0 Private Sub CalculateVariables() ' code to calculate var1 & var2 End Sub Private Sub UseVariables ' code to use var1 & var2 End Sub After runtime var1 & var2 are reset to 0 -- Cheers, Ryan |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically resest variable values = 0 after runtime
What are the controls you have that it would not be helpful for (there is
probably another way to do it for them)? Note: It's very hard to give you advice for something you don't tell us about.<g Rick "RyanH" wrote in message ... Thanks for the help Rick. I actually was using the code I posted as an example. Your method would help for some of the controls on my Userform but not all. Your method did give me a good idea though to use in the future. -- Cheers, Ryan "Rick Rothstein (MVP - VB)" wrote: One final thought on this method... if you had a lot of variables to set for any given CheckBox, you could use a With/End With block to avoid having to type in the CheckBox name each time. For example.... With CheckBox1 var1 = -100 * .Value var2 = -200 * .Value etc. End With Rick "Rick Rothstein (MVP - VB)" wrote in message ... Change your CalculateVariable subroutine to the following and the variables will set themselves to the correct value or reset themselves to 0 depending on the True or False status of the various CheckBoxes... Private Sub CalculateVariables() var1 = -100 * CheckBox1.Value var2 = -200 * CheckBox1.Value var3 = -300 * CheckBox2.Value var4 = -400 * CheckBox2.Value End Sub Note the minus sign in front of the numbers... that is very important. What is going on here is the numerical value is being multiplied by either True or False (depending on if the CheckBox is checked or not) which, in VB, is -1 (minus 1) for True and 0 for False. Hence, using var1 as an example, the multiplication automatically sets the value to -100 (minus 100) times either -1 (minus 1) if the CheckBox is checked (its Value property would be True) or time 0 if it is not checked (its Value property would be False). Since a minus times a minus is a plus, var1 is set to either 100 or 0 automatically. Rick "RyanH" wrote in message ... I realize what you are saying Charlie and JLGWhiz, and I agree. I apologize for not posting code. I basically shorted my code to better explain my question. This code is placed in a Userform Module. My executed code is fired when I click the command button on my UserForm. My problem is when I click the command button lets say CheckBox 1 & 2 = True. Thus, Var1 = 100, Var2 = 200, Var3 = 300, Var4 = 400 and the Message Box is shown 4 times (Note: UserForm is still open). If I set CheckBox1 = True and CheckBox2 = False and then click my command button a second time I should only see the MsgBox 2 times, right? Wrong, the Message Box is shown 4 times again, because I delared my variables at the top in the Declarations section, which holds the variable value until the Userfrom is Unloaded. So I was wanting to know if there is a way to automatically reset the variables = 0 each time the command button is clicked (Such as Dim var1 As Single = 0). I currently have code that uses the Array and a For Each...Loop to set the variables = 0, which I did not display below. I just figured there may be an easier way. Option Explicit Dim var1 As Single Dim var2 As Single Dim var3 As Single Dim var4 As Single Private Sub CommandButton1_Click() Call UseVariables End Sub Private Sub UseVariables() Dim myVariables As Variant Dim myVar As Variant Call CalculateVariables myVariables = Array(var1, var2, var3, var4) For Each myVar In myVariables If myVar < 0 Then MsgBox "Do Something" End If Next myVar End Sub Private Sub CalculateVariables() If CheckBox1 = True Then var1 = 100 var2 = 200 End If If CheckBox2 = True Then var3 = 300 var4 = 400 End If End Sub -- Cheers, Ryan "JLGWhiz" wrote: Once the procedure executes End Sub all variables are emptied anyhow. They do not retain their values for a second run. Even if the variables are public and your procedure calculates their value at each run, they would not have to be reset to zero, unless as Charlie pointed out, you are adding the existing value to something. In that case you could use an If statement to say if it is greater than zero then make it zero. "RyanH" wrote: I have about 40 variables at the top of a Userforms Declaration section. I do this because I need the variables calculated in one procedure and made available to another procedure when they are all done being calulated. The problem is I currently have to write code to reset all the variables back to 0 before they are recalculated. If there a way to declare the variable (DataType Single) = 0 at the beginning of runtime. For example: Option Explicit Dim var1 As Single = 0 Dim var2 As Single = 0 Private Sub CalculateVariables() ' code to calculate var1 & var2 End Sub Private Sub UseVariables ' code to use var1 & var2 End Sub After runtime var1 & var2 are reset to 0 -- Cheers, Ryan |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically resest variable values = 0 after runtime
Rick,
The following code is an example of what my actual code is (my actual code is too lengthy to post). myArray will always contain 4 variables. I need to reset each variable in myArray equal to 0 each time Sub Test() is called. As you can see I have to declare the variables in the Declarations section to make them available to both procedures. By doing this unwanted text is copied from Sheet1 to Sheet2. For example, in my UserForm say the user fires the Test_Click Event and CheckBox1 = True and ComboBox.ListIndex = 1, thus var1=100, var2=0, var3=300, var4=0 and the UserFrom remains Loaded. So far so good! The problem happens when the user may fire the Test_Click Event again when CheckBox1 = False and ComboBox.ListIndex = 3, thus var1=100, var2=200, var3=300, var4=400. This is no good, because now all four variables have data other than = 0. The variables should show like this: var1=0, var2=200, var3=0, var4=400. So how do I reset the variables values without destroying myArray? I don't think I could use Erase or ReDim, because this would destroy the Array, right? I thought about just looping thru myArray and make each variable = Empty, but I figured there would be a more efficient way. I hope this is clear. Option Explicit Dim var1 As Single Dim var2 As Single Dim var3 As Single Dim var4 As Single Private Sub cmbTest_Click() Dim myArray As Variant ' array of row numbers determined by procedure cmbCalculate_Click myArray = Array(var1, var2, var3, var4) ' calculate variables in myArray Call cmbCalculate_Click ' adds text from Sheet1 to Sheet2 InputRow = 6 For i = LBound(myArray) To UBound(myArray) If Not myArray(i) = 0 Then Sheets("Sheet1").Range("A" & InputRow & ":D" & InputRow).Value = _ Sheets("Sheet2").Range("A" & myArray(i) & ":D" & myArray(i)).Value InputRow = InputRow + 2 End If Next i End Sub Private cmbCalculate_Click() If CheckBox1 = True Then var1 = 100 Else var2 = 200 End If If ComboBox.ListIndex = 1 Then var3 = 300 Else var4 = 400 End If End Sub -- Cheers, Ryan "Rick Rothstein (MVP - VB)" wrote: What are the controls you have that it would not be helpful for (there is probably another way to do it for them)? Note: It's very hard to give you advice for something you don't tell us about.<g Rick "RyanH" wrote in message ... Thanks for the help Rick. I actually was using the code I posted as an example. Your method would help for some of the controls on my Userform but not all. Your method did give me a good idea though to use in the future. -- Cheers, Ryan "Rick Rothstein (MVP - VB)" wrote: One final thought on this method... if you had a lot of variables to set for any given CheckBox, you could use a With/End With block to avoid having to type in the CheckBox name each time. For example.... With CheckBox1 var1 = -100 * .Value var2 = -200 * .Value etc. End With Rick "Rick Rothstein (MVP - VB)" wrote in message ... Change your CalculateVariable subroutine to the following and the variables will set themselves to the correct value or reset themselves to 0 depending on the True or False status of the various CheckBoxes... Private Sub CalculateVariables() var1 = -100 * CheckBox1.Value var2 = -200 * CheckBox1.Value var3 = -300 * CheckBox2.Value var4 = -400 * CheckBox2.Value End Sub Note the minus sign in front of the numbers... that is very important. What is going on here is the numerical value is being multiplied by either True or False (depending on if the CheckBox is checked or not) which, in VB, is -1 (minus 1) for True and 0 for False. Hence, using var1 as an example, the multiplication automatically sets the value to -100 (minus 100) times either -1 (minus 1) if the CheckBox is checked (its Value property would be True) or time 0 if it is not checked (its Value property would be False). Since a minus times a minus is a plus, var1 is set to either 100 or 0 automatically. Rick "RyanH" wrote in message ... I realize what you are saying Charlie and JLGWhiz, and I agree. I apologize for not posting code. I basically shorted my code to better explain my question. This code is placed in a Userform Module. My executed code is fired when I click the command button on my UserForm. My problem is when I click the command button lets say CheckBox 1 & 2 = True. Thus, Var1 = 100, Var2 = 200, Var3 = 300, Var4 = 400 and the Message Box is shown 4 times (Note: UserForm is still open). If I set CheckBox1 = True and CheckBox2 = False and then click my command button a second time I should only see the MsgBox 2 times, right? Wrong, the Message Box is shown 4 times again, because I delared my variables at the top in the Declarations section, which holds the variable value until the Userfrom is Unloaded. So I was wanting to know if there is a way to automatically reset the variables = 0 each time the command button is clicked (Such as Dim var1 As Single = 0). I currently have code that uses the Array and a For Each...Loop to set the variables = 0, which I did not display below. I just figured there may be an easier way. Option Explicit Dim var1 As Single Dim var2 As Single Dim var3 As Single Dim var4 As Single Private Sub CommandButton1_Click() Call UseVariables End Sub Private Sub UseVariables() Dim myVariables As Variant Dim myVar As Variant Call CalculateVariables myVariables = Array(var1, var2, var3, var4) For Each myVar In myVariables If myVar < 0 Then MsgBox "Do Something" End If Next myVar End Sub Private Sub CalculateVariables() If CheckBox1 = True Then var1 = 100 var2 = 200 End If If CheckBox2 = True Then var3 = 300 var4 = 400 End If End Sub -- Cheers, Ryan "JLGWhiz" wrote: Once the procedure executes End Sub all variables are emptied anyhow. They do not retain their values for a second run. Even if the variables are public and your procedure calculates their value at each run, they would not have to be reset to zero, unless as Charlie pointed out, you are adding the existing value to something. In that case you could use an If statement to say if it is greater than zero then make it zero. "RyanH" wrote: I have about 40 variables at the top of a Userforms Declaration section. I do this because I need the variables calculated in one procedure and made available to another procedure when they are all done being calulated. The problem is I currently have to write code to reset all the variables back to 0 before they are recalculated. If there a way to declare the variable (DataType Single) = 0 at the beginning of runtime. For example: Option Explicit Dim var1 As Single = 0 Dim var2 As Single = 0 Private Sub CalculateVariables() ' code to calculate var1 & var2 End Sub Private Sub UseVariables ' code to use var1 & var2 End Sub After runtime var1 & var2 are reset to 0 -- Cheers, Ryan |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically resest variable values = 0 after runtime
Have you tried moving the variables and the array to a class? Make the
array static and make the variables public (or use properties - for to shortcut it I used public properties). Then everytime you "new" up the class the variables will be reset but the array will stay around as static - then pass that new'd up class to your sub-routines. For example: Class code: (shortened for simplicity) Public Class TestClass Public Var1 As Integer = 0 Public Shared myArray(4) As String End Class Form code: (again shortened for simplicity) Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load myArray(0) = DateTime.Now.ToString("fffff") Thread.Sleep(2) myArray(1) = DateTime.Now.ToString("fffff") Thread.Sleep(2) myArray(2) = DateTime.Now.ToString("fffff") Thread.Sleep(2) myArray(3) = DateTime.Now.ToString("fffff") Thread.Sleep(2) End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim tmpTestClass As New TestClass tmpTestClass.Var1 = CType(DateTime.Now.ToString("fffff"), Int32) MessageBox.Show(String.Format("Class member value: {0}, Static array at 0={1}", tmpTestClass.Var1.ToString(), myArray(0).ToString())) End Sub When this gets run and the button is clicked - the array remains the same but the "var1" is always new. Is this what you're after? -Mike *** Sent via Developersdex http://www.developersdex.com *** |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically resest variable values = 0 after runtime
I might be missing something, but I am having trouble seeing why my earlier
suggestion... With CheckBox1 var1 = -100 * .Value var2 = -200 * .Value etc. End With is not useful... just put it in each of the Click events for each of your CheckBoxes (or better yet, make it a subroutine and call that subroutine from the CheckBox Click events. Rick "RyanH" wrote in message ... Rick, The following code is an example of what my actual code is (my actual code is too lengthy to post). myArray will always contain 4 variables. I need to reset each variable in myArray equal to 0 each time Sub Test() is called. As you can see I have to declare the variables in the Declarations section to make them available to both procedures. By doing this unwanted text is copied from Sheet1 to Sheet2. For example, in my UserForm say the user fires the Test_Click Event and CheckBox1 = True and ComboBox.ListIndex = 1, thus var1=100, var2=0, var3=300, var4=0 and the UserFrom remains Loaded. So far so good! The problem happens when the user may fire the Test_Click Event again when CheckBox1 = False and ComboBox.ListIndex = 3, thus var1=100, var2=200, var3=300, var4=400. This is no good, because now all four variables have data other than = 0. The variables should show like this: var1=0, var2=200, var3=0, var4=400. So how do I reset the variables values without destroying myArray? I don't think I could use Erase or ReDim, because this would destroy the Array, right? I thought about just looping thru myArray and make each variable = Empty, but I figured there would be a more efficient way. I hope this is clear. Option Explicit Dim var1 As Single Dim var2 As Single Dim var3 As Single Dim var4 As Single Private Sub cmbTest_Click() Dim myArray As Variant ' array of row numbers determined by procedure cmbCalculate_Click myArray = Array(var1, var2, var3, var4) ' calculate variables in myArray Call cmbCalculate_Click ' adds text from Sheet1 to Sheet2 InputRow = 6 For i = LBound(myArray) To UBound(myArray) If Not myArray(i) = 0 Then Sheets("Sheet1").Range("A" & InputRow & ":D" & InputRow).Value = _ Sheets("Sheet2").Range("A" & myArray(i) & ":D" & myArray(i)).Value InputRow = InputRow + 2 End If Next i End Sub Private cmbCalculate_Click() If CheckBox1 = True Then var1 = 100 Else var2 = 200 End If If ComboBox.ListIndex = 1 Then var3 = 300 Else var4 = 400 End If End Sub -- Cheers, Ryan "Rick Rothstein (MVP - VB)" wrote: What are the controls you have that it would not be helpful for (there is probably another way to do it for them)? Note: It's very hard to give you advice for something you don't tell us about.<g Rick "RyanH" wrote in message ... Thanks for the help Rick. I actually was using the code I posted as an example. Your method would help for some of the controls on my Userform but not all. Your method did give me a good idea though to use in the future. -- Cheers, Ryan "Rick Rothstein (MVP - VB)" wrote: One final thought on this method... if you had a lot of variables to set for any given CheckBox, you could use a With/End With block to avoid having to type in the CheckBox name each time. For example.... With CheckBox1 var1 = -100 * .Value var2 = -200 * .Value etc. End With Rick "Rick Rothstein (MVP - VB)" wrote in message ... Change your CalculateVariable subroutine to the following and the variables will set themselves to the correct value or reset themselves to 0 depending on the True or False status of the various CheckBoxes... Private Sub CalculateVariables() var1 = -100 * CheckBox1.Value var2 = -200 * CheckBox1.Value var3 = -300 * CheckBox2.Value var4 = -400 * CheckBox2.Value End Sub Note the minus sign in front of the numbers... that is very important. What is going on here is the numerical value is being multiplied by either True or False (depending on if the CheckBox is checked or not) which, in VB, is -1 (minus 1) for True and 0 for False. Hence, using var1 as an example, the multiplication automatically sets the value to -100 (minus 100) times either -1 (minus 1) if the CheckBox is checked (its Value property would be True) or time 0 if it is not checked (its Value property would be False). Since a minus times a minus is a plus, var1 is set to either 100 or 0 automatically. Rick "RyanH" wrote in message ... I realize what you are saying Charlie and JLGWhiz, and I agree. I apologize for not posting code. I basically shorted my code to better explain my question. This code is placed in a Userform Module. My executed code is fired when I click the command button on my UserForm. My problem is when I click the command button lets say CheckBox 1 & 2 = True. Thus, Var1 = 100, Var2 = 200, Var3 = 300, Var4 = 400 and the Message Box is shown 4 times (Note: UserForm is still open). If I set CheckBox1 = True and CheckBox2 = False and then click my command button a second time I should only see the MsgBox 2 times, right? Wrong, the Message Box is shown 4 times again, because I delared my variables at the top in the Declarations section, which holds the variable value until the Userfrom is Unloaded. So I was wanting to know if there is a way to automatically reset the variables = 0 each time the command button is clicked (Such as Dim var1 As Single = 0). I currently have code that uses the Array and a For Each...Loop to set the variables = 0, which I did not display below. I just figured there may be an easier way. Option Explicit Dim var1 As Single Dim var2 As Single Dim var3 As Single Dim var4 As Single Private Sub CommandButton1_Click() Call UseVariables End Sub Private Sub UseVariables() Dim myVariables As Variant Dim myVar As Variant Call CalculateVariables myVariables = Array(var1, var2, var3, var4) For Each myVar In myVariables If myVar < 0 Then MsgBox "Do Something" End If Next myVar End Sub Private Sub CalculateVariables() If CheckBox1 = True Then var1 = 100 var2 = 200 End If If CheckBox2 = True Then var3 = 300 var4 = 400 End If End Sub -- Cheers, Ryan "JLGWhiz" wrote: Once the procedure executes End Sub all variables are emptied anyhow. They do not retain their values for a second run. Even if the variables are public and your procedure calculates their value at each run, they would not have to be reset to zero, unless as Charlie pointed out, you are adding the existing value to something. In that case you could use an If statement to say if it is greater than zero then make it zero. "RyanH" wrote: I have about 40 variables at the top of a Userforms Declaration section. I do this because I need the variables calculated in one procedure and made available to another procedure when they are all done being calulated. The problem is I currently have to write code to reset all the variables back to 0 before they are recalculated. If there a way to declare the variable (DataType Single) = 0 at the beginning of runtime. For example: Option Explicit Dim var1 As Single = 0 Dim var2 As Single = 0 Private Sub CalculateVariables() ' code to calculate var1 & var2 End Sub Private Sub UseVariables ' code to use var1 & var2 End Sub After runtime var1 & var2 are reset to 0 -- Cheers, Ryan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
Runtime Error 91 Object variable or With block variable not set. | Excel Programming | |||
Excel2003 - Automatically sum a variable number of rows | Excel Worksheet Functions | |||
VBA automatically updating variable names | Excel Programming | |||
Runtime error 91: Objust variable or with block.... | Excel Programming |