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
|