By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,122 Members | 2,044 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,122 IT Pros & Developers. It's quick & easy.

Select Case Function

100+
P: 166
Hello:

I am creating a form that will calculate a value based on the value selected from a case statement in a function. The function I created is called GetYield and accepts 3 arguments.

I have a continuous form that has a combo box (cbo_matTypeID) to allow the user to select the material type and based on the user selection, another combo box (cbo_materialID) is populated. Here is my function:

Expand|Select|Wrap|Line Numbers
  1. Public Function GetYield(BatchWeight As Double, MatGrav As Double, matType As Integer) As Double
  2. 'returns the yield of each material 'DM_yield' calculation
  3. Batchweight refers to the matBatchweight on the form
  4. MatGrav refers to the DM_Gravity text box on the form
  5. matType refers to the cbo_matTypeID combo box on the form
  6.  
  7. Select Case cbo_matTypeID
  8. 'User selects from cbo_matTypeID (Cement, Coarse, Fine, Pigment)
  9. Case 1, 2, 3, 4
  10. GetYield = BatchWeight / (MatGrav * 62.4)
  11. 'Chemicals
  12. Case 5
  13. GetYield = (BatchWeight / 128) * (10 / 62.4)
  14. End Select
  15. End Function
I expected that when the user makes a selection from the combo box, the yield would be calculated based on the material type that was selected. That does not happen; instead I get the same 0.00 for all the materials that have been selected.
Here is what I have in my unbound text box that has the calculation, called DM_Yield:
=GetYield([matBatchWeight],[DM_Gravity],[cbo_matTypeID])

matBatchWeight refers to a user input field, and DM_Gravity refers to a bound text box that populates based on the specific material that is selected from the cbo_materialID box.

any assistance would be appreciated.
Dec 2 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
Hi. Most likely cause is that you have accidentally substituted the combo name cbo_matTypeID for your parameter matType in line 7 above. It is likely to be treated as an undeclared variable of variant type by VBA, null in value by default, which will result in your Case statement not being executed.

You should enable the option for using explicit variable declarations to guard against this problem (Tools, Options from VB Editor) - this will place compiler directive Option Explicit in your code module to force the compiler to generate error messages if you do not declare variables before use.

-Stewart
Dec 2 '08 #2

100+
P: 166
Hi Stewart,

I appreciate your response.

You are right, I was using the combo box name on the form as opposed to the argument in the function representing the combo box name.

You are a genius! Thank you
Dec 2 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.