Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make all variable values in a Array equal to 0
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 -- Cheers, Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make all variable values in a Array equal to 0
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. -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make all variable values in a Array equal to 0
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 -- Cheers, Ryan "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make all variable values in a Array equal to 0
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 -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make all variable values in a Array equal to 0
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? -- Cheers, Ryan "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make all variable values in a Array equal to 0
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??? -- HTH... Jim Thomlinson "RyanH" wrote: 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? -- Cheers, Ryan "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make all variable values in a Array equal to 0
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 -- Cheers, Ryan "Jim Thomlinson" wrote: 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??? -- HTH... Jim Thomlinson "RyanH" wrote: 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? -- Cheers, Ryan "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make all variable values in a Array equal to 0
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... -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan "Jim Thomlinson" wrote: 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??? -- HTH... Jim Thomlinson "RyanH" wrote: 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? -- Cheers, Ryan "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make all variable values in a Array equal to 0
Hallelujah!! I think I got it too work. Thanks for all the help.
-- Cheers, Ryan "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan "Jim Thomlinson" wrote: 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??? -- HTH... Jim Thomlinson "RyanH" wrote: 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? -- Cheers, Ryan "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "RyanH" wrote: 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 -- Cheers, Ryan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count occurances of equal values in two col. - array within SUMPRO | Excel Worksheet Functions | |||
Clearing Variable Values in Array using For...Next | Excel Programming | |||
Make all variable in Array = Empty each click of a Button on Userf | Excel Programming | |||
Can I make array position A(12) into a variable A(12*n) ? | Excel Worksheet Functions | |||
how do I make single character as a variable in an array | Excel Worksheet Functions |