Re: Select Case and If...Then Statements in UserForm not Calculating
- From: "Rick Rothstein \(MVP - VB\)" <rickNOSPAMnews@xxxxxxxxxxxxxxxxx>
- Date: Wed, 12 Dec 2007 10:38:21 -0500
Your problem is with the second If-Then test you are performing in the Case block for your material...
If 4 < MaxL <= 6 Then ....
The problem with the above test is that it is written incorrectly... it will evaluate **always** evaluate to True. The two operators you are using have the same precedence, so they are evaluated left-to-right. No matter what value is assigned to MaxL, 4<MaxL will be either True or False which, in the VB world, is either -1 or 0, both of which are always going to be less than 6; hence, the above test will always be True and **always** overwrite any value assigned to the materials variable by the previous statement. Since you don't perform this same second test when making your assignment to tbxQuotePrice near the end of your code, the values you think should be the same will always be different when MaxL is equal to, or less than, 4 (at least for the code snippet you have shown us). Anyway, the proper way to write the above If-Then test is this...
If 4 < MaxL And MaxL <=6 Then ...
Now, there may be another problem with your code. You have declared each of your variables as type Long, but some of your calculations will result in floating point value being assigned to them. When that assignment takes place, the fractional values will be lost and, I presume, cause your calculations to be performed with values other than what you intended. For example, this assignment...
Height = tbxDimHft1 + (tbxDimHins1 / 12)
will result in a whole number, not a floating point value, being assigned to the Height variable. The same is true for the Width variable and any subsequent calculations derived from them. Your materials variable, also declared as Long is another place where any fractional values generated as part of its assignment calculation will be lost. You might want to consider declaring these (and perhaps other) variables as Double instead.
Rick
"RyanH" <RyanH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:A61BC758-65A0-4726-913B-D682C3DF6BB3@xxxxxxxxxxxxxxxx
I am building a product quoting system in VBA for my company that
manufactures outdoor plastic sign faces. I have a Userform with 6 text
boxes, 1 combobox, command button:
tbxDimHft1 = user enters Height of sign in feet
tbxDimHins1 = user enters Height of sign in inches
tbxDimWft1 = user enters Width of sign in feet
tbxDimWins1 = user enters Width of sign in inches
tbxCalculatedPrice = shows calculated price when command button is clicked
tbxQuotePrice = shows quote price when command button is clicked
cboMaterial = contains material for signs
cmbCalculate = command button to run calculation code
In my current code tbxCalculatedPrice and tbxQuotePrice should equal the
same, but for some reason they are not equalling each other when I click the
Calculate Button. For example, if I enter the following data:
tbxDimHft1 = 4
tbxDimHins1 = 0
tbxDimWft1 = 3
tbxDimWins1 = 0
cboMaterial = "Clear .150 High Impact Modified Acrylic"
Click the command button
tbxQuotePrice = 32.76
tbxCalculatePrice = 47.00
WHY???? Any help would be greatly appreciated, because I have looked at
this forever and cannot see why the calculations are not the same. THANKS!!
Note: PL509 = $9.36
Private Sub cmbCalculate_Click()
Dim Height As Long, Width As Long
Dim MaxL As Long, MinL As Long
Dim material As Long
Height = tbxDimHft1 + (tbxDimHins1 / 12)
Width = tbxDimWft1 + (tbxDimWins1 / 12)
MaxL = WorksheetFunction.Max(Height, Width)
MinL = WorksheetFunction.Min(Height, Width)
Sheets("Parts List").Activate
''''''''''''calculated price text box''''''''''''''''''''''''
Select Case cboMaterial.Text
Case "Clear .150 High Impact Modified Acrylic"
If MaxL <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False)
If 4 < MaxL <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL505", Range("A:D"), 4, False)
Case "Clear .150 Polycarbonate"
If MaxL <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL522", Range("A:D"), 4, False)
If 4 < MaxL <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL524", Range("A:D"), 4, False)
Case "White .150 High Impact Modified Acrylic"
If MaxL <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL510", Range("A:D"), 4, False)
If 4 < MaxL <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL506", Range("A:D"), 4, False)
Case "White .150 Polycarbonate"
If MaxL <= 4 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL521", Range("A:D"), 4, False)
If 4 < MaxL <= 6 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL529", Range("A:D"), 4, False)
Case "Clear .177 High Impact Modified Acrylic"
If MaxL <= 8 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL503", Range("A:D"), 4, False)
Case "White .177 High Impact Modified Acrylic"
If MaxL <= 8 Then material = (MinL + 0.5) *
WorksheetFunction.VLookup("PL504", Range("A:D"), 4, False)
End Select
tbxCalculatedPrice = material
'''''''''''''quote price textbox value''''''''''''''''''''
If MaxL <= 4 And cboMaterial.Text = "Clear .150 High Impact Modified
Acrylic" Then tbxQuotePrice = (MinL + 0.5) *
WorksheetFunction.VLookup("PL509", Range("A:D"), 4, False)
Sheets("Quote").Activate
End Sub
.
- Follow-Ups:
- References:
- Prev by Date: I have a query about loops and random numbers
- Next by Date: Very long code execution time problem
- Previous by thread: Re: Select Case and If...Then Statements in UserForm not Calculati
- Next by thread: Re: Select Case and If...Then Statements in UserForm not Calculati
- Index(es):
Relevant Pages
|
