Make all variable values in a Array equal to 0

[RyanH]

I am revisiting this continueing issue I am having and I apologize to all
those who have tried to help me thus far. Here is a simplified version of my
situation.

I have a UserForm that opens with 2 Buttons and 3 CheckBoxes. If my user
opens the Userform and sets all checkboxes = True and clicks Calculate var1 =
100, var2 = 200, var3 = 300, (Note: UserForm is still open, thus varibles
values are not destroyed and variables are declared in the Declarations
section). The problem happens when my user sets CheckBox1 & 2 = True and
CheckBox3 = False, var1 = 100, var2 = 200, var3 = 300. It should be var1 =
100, var2 = 200, var3 = 0, why is this? I thought the For...Loop set the
values = 0. I also have an Error indicated below, why?

Basically, I want to make all the variable values equal to 0 at the begining
of the btnApply_Click procedure and then calculate the variable values.
Note: I don't want to use Else var1 = 0, because this would make the code
sloppy to me.

Option Explicit

Dim var1 As Single
Dim var2 As Single
Dim var3 As Single

Private Sub btnApply_Click()

Dim myArray As Variant
Dim i As Long

' array of allocated part quantities
myArray = Array(var1, var2, var3)

' resets variables
For i = LBound(myArray) To UBound(myArray)
myArray(i) = Empty
Next i

MsgBox "var1 = " & var1 & " : var1 should equal 0"
MsgBox "var2 = " & var2 & " : var2 should equal 0"
MsgBox "var3 = " & var3 & " : var3 should equal 0"

Call Calculate

' array of allocated part quantities
myArray = Array(var1, var2, var3)

MsgBox "After Calculation var1 = " & var1
MsgBox "After Calculation var2 = " & var2
MsgBox "After Calculation var3 = " & var3

' apply values to worksheet
For i = LBound(myArray) To UBound(myArray)
If Not IsEmpty(myArray(i)) Then
Error=> Sheets("Calculator").Cells(i, 1) = myArray(i)
End If
Next i

End Sub

Private Sub Calculate()

If CheckBox1 = True Then
var1 = 100
End If

If CheckBox2 = True Then
var2 = 200
End If

If CheckBox3 = True Then
var3 = 300
End If

End Sub

 

[Jim Thomlinson]

You have 2 questions so lets run through them individually.
1. Why are Var1, var2 and var3 not being reset to 0 or empty. When you set
up your array you populate it with values from var1, var2, and var3. Note
that it is the values that go into the array and not the variables
themselves. When you reset the array values you do not reset the variables.
You will need to rethink that. Perhaps a quick expanation of your fiendish
master plan will help us to send you in the right direction.

2. Your code errors out here
Sheets("Calculator").Cells(i, 1) = myArray(i)
This is because the default index for an array to start at is 0. So the
index values of your array are 0, 1 and 2. So your code errors out becuase
you are trying to put a value in row zero. Row zero is not a valid row so...
There are 2 fixes.
a) Add 1 to i so
Sheets("Calculator").Cells(i+1, 1) = myArray(i)
b) Add Option Base 1 at the top of the module. This tells the compiler to
start indexing the arrays at 1.

 

[RyanH]

Question #2. I should have know that. I guess me being so fustrated with
the Array issue it has clouded thought process.

Question #1. For simplicity I will use this example. Say I want to quote a
price for a car with 3 options:

Lether Seats (CheckBox1 and var1)
Premium Wheels(CheckBox2 and var2)
V8 Engine(CheckBox3 and var3)

To start, I open the UserForm and set all 3 CheckBoxes = True then click the
"Apply" button. Now I get a price with all three options (var1 = 100, var2 =
200, var3 = 300). Note: UserForm is still loaded.

But what if I change my mind and don't want a V8 Engine. So I set the
CheckBox3 = False and hit "Apply". I should have var1 = 100, var2 =200 and
var3 = 0, but var3 = 300. How can I set all the values back to 0 in an
effiecient way? Other than listing each and every variable = 0 and using a
ton of If...Else var? =0. For example,

var1 = 0
var2 = 0
var3 = 0
etc....

or

If CheckBox1 = True Then
var1= 100
Else
var1 = 0
End If

Please help! I have become desparant.

Thanks

 

[Jim Thomlinson]

Here is my take on what you want to do... Not 100% sure why you want form
level variables but...

Option Explicit

Const cst1 As Single = 100
Const cst2 As Single = 200
Const cst3 As Single = 300

Dim ary() As Single

Private Sub btnApply_Click()
ReDim ary(2)

If CheckBox1 = True Then ary(0) = cst1
If CheckBox2 = True Then ary(1) = cst2
If CheckBox3 = True Then ary(2) = cst3

MsgBox ary(0)
MsgBox ary(1)
MsgBox ary(2)
Sheets("Calculator").Range("A1:A3").Value = Application.Transpose(ary)
End Sub

 

[RyanH]

Thanks for sticking with this! I have to use form level variables because
the Calculate Procedure and its variables have to be available for two other
procedures in the form. I did this so I didn't have a ton of functions. I
just figured using an Array would reset the variables. So far I guess you
think I'm stuck listing all the variables like so to reset them?

var1 = 0
var2 = 0
var3 = 0
'etc.

would a collection work?

 

[Jim Thomlinson]

The code I posted does what you asked for. You have a public array of values
that is reset when you hit the apply button. It also copies those values into
A1:A3... What exactly is it missing???

 

[RyanH]

Thanks again for sticking with this. The issue I still have is you have
declared the variables as constants, not variables. Yes, in my example I
said var1 = 100, but that was just an example number. My variables (var1,
var2, ect.) are actually results of a mathematical equation, such as var1 =
SquareFeet * Price + Quantity. Will your code still work if I do it this way?

Option Explicit

Dim var1 As Single
Dim var2 As Single
Dim var3 As Single

Dim myArray() As Single

Private Sub btnApply_Click()

ReDim myArray(2)

If CheckBox1 = True Then myArray(0) = var1
If CheckBox2 = True Then myArray(1) = var2
If CheckBox3 = True Then myArray(2) = var3

end sub

 

[Jim Thomlinson]

You tell me... does it work. It works fine from my end but then again... it
needs to work at your end a lot more than it needs to work at mine. Jut
remember the arry holds the values of the variables and not the variables
themselves. The two can get out of synch if you change the variable without
re-loading the array immediatly after...

 

[RyanH]

Hallelujah!! I think I got it too work. Thanks for all the help.