Conditional Calculation formula Using "Case"

[Applefritter]

Howdy,
I'm missing something because my calculations are off on a form. I have a form with a textbox labeled "txtStartupTemperature". The value I insert here will affect another value in a textbox labeled "txtStartupTCF". If my temperature value is <= 25, I will use one formula for my calculation. If my temperature value is >25 then I will use an alternate formula. Both formulas involve exponentials. Here's the code I developed for my public function:

Public Function TCF(ByVal sngValue As Single) As Single
Select Case sngValue


Case Is <= 25
TCF = Exp(3480 * (1 / 298 - 1 / (273 + txtStartupTemperature)))

Case Is > 25
TCF = Exp(2640 * (1 / 298 - 1 / (273 + txtStartupTemperature)))

Case Else


End Select

End Function

In my form's code I've inserted the following:

Private Sub txtStartupTemperature_AfterUpdate()
Me.txtStartupTCF = TCF(Me.txtStartupTemperature)
End Sub


I tested this code with a temperature value of 15. This should calculate out to be 0.67. Instead I'm getting 0.34. Are my formulas written correctly? If so, what else could be going on?

 

[boblarson]

Are you positive you are looking for the EXP function? According to help it is:

Returns a Double specifying e (the base of natural logarithms) raised to a power.

I believe, if you are looking for using an Exponent, that you want this:

Used to raise a number to the power of an exponent.

Syntax

result = number^exponent

 

[Applefritter]

Bummer. I tried as suggested and it didn't pan out. I only get "0's" if I insert number^exponent. I'm definitely looking for the EXP function. I also tested my formulas on Excel using EXP and they come out fine there. Something else I've noticed though. If I insert any values <=25 I always get 0.34. Any values >25 I always get 0.44. I'm hung up on these two values for some reason. Any other place I should be scrutinizing my code?
Thanx for your assistance thus far.

 

[KeithWilliams]

Hi,

Since you only get one value for each case in the Select, sounds like txtStartupTemperature is evaluating to 0. You can easily check this by running the code in Debug mode. (Press F9 on a line of code at the start of the function code to put a debug stop).

Perhaps you should be using sngValue in place of txtStartupTemperature in the Select Case statement, or try Me.txtStartupTemperature.

HTH,
Keith.

 

[Applefritter]

Conditional EXP Calculation formula Using "Case"

Ok, so I did a debug and I didn't find anything there. Nor did anything change with the change to Me.txStartupTemperature. I went back and omitted the stand alone Public function and transferred some of the code from there into the form's code module:

Private Sub txtStartupTemperature_AfterUpdate()
Dim TCF As Single
Temperature = Me.txtStartupTCF
Select Case Temperature

Case Is <= 25
TCF = Exp(3480 * (1 / 298 - 1 / (273 + Me.txtStartupTemperature)))

Case Is > 25
TCF = Exp(2640 * (1 / 298 - 1 / (273 + Me.txtStartupTemperature)))

Case Else
End Select
Me.StartupTCF = TCF

End Sub
Thru trial and error this seems to have done the trick. Calculations are working out as they should. As I'm still a novice to VBA I'm not exactly sure which line(s) were instrumental in getting it too work and which lines are just superfluous fluff. Anyone care to comment on whether or not there is anything I could be doing to make this code neater? Thanx again for everyone's suggestions and getting my mind churning!