Determine which variable was used in a calculation.

[RyanH]

I have a Worksheet named "Parts", that has a list of parts in it:
Col. A - Unit Part Numbers
Col. B - Unit Descriptions
Col. C - Unit Type (ex.: sq. ft., ft., each, etc.)
Col. D - Unit Cost
I have a UserForm with two command buttons in it, cmbCalculate & cmbBOM.
When cmbCalculate is clicked it calculates the quantity of each part, finds
the associated part number, and multiplies the quantity and the Unit Cost to
give me a total price. Example,

Private Sub Calculate_Click ()

Dim Part1_Cost As Currency
Dim Part2_Cost As Currency
Dim Part3_Cost As Currency
Dim Perimeter as Single

Part1_Cost = WorksheetFunction.VLookup("Part-1", Sheets("Parts
List").Range("A"), 4, False)
Part2_Cost = WorksheetFunction.VLookup("Part-2", Sheets("Parts
List").Range("A"), 4, False)
Part3_Cost = WorksheetFunction.VLookup("Part-3", Sheets("Parts
List").Range("A"), 4, False)

Perimeter = (tbxHeight + tbxWidth) * 2

If chkAddTrimCap = True Then
Select Case cboTrimCap

Case "Black"
TrimCapM = Perimeter * Val(tbxQuantity) * Part1_Cost
Case "White"
TrimCapM = Perimeter * Val(tbxQuantity) * Part2_Cost
Case "Red"
TrimCapM = Perimeter * Val(tbxQuantity) * Part3_Cost

End Select
End If
End Sub

I want to produce a bill of material for the product on a separate worksheet
labeled "BOM" when cmbBOM is clicked. This BOM will need the following
information of the part that was used:
Col. A - Unit Part Numbers
Col. B - Unit Descriptions
Col. C - Unit Type (ex.: sq. ft., ft., each, etc.)
Col. D - Unit Cost
Col. E - Quantity Used

Is it possible for VBA to know that TrimCapM used Part1_Cost or Part2_Cost
or Part3_Cost?

 

[Dave Peterson]

You could just add another variable and keep track of what you wanted.

Dim WhichWasUsed as String
.....
whichwasused = ""
If chkAddTrimCap = True Then
Select Case cboTrimCap
Case "Black"
TrimCapM = Perimeter * Val(tbxQuantity) * Part1_Cost
whichwasused = "Part1"
Case "White"
TrimCapM = Perimeter * Val(tbxQuantity) * Part2_Cost
Whichwasused = "Part2"
Case "Red"
TrimCapM = Perimeter * Val(tbxQuantity) * Part3_Cost
whichwasused = "Part3"
End Select
End If

if whichwasused = "" then
msgbox "nothing used"
else
msgbox whichwasused
end if

....

 

[RyanH]

After thinking about it, I figured that is what I would need to do. I was
just wondering if there was some "magic" function or statement that I didn't
know about.

Thanks for the help!