Calling a Function from VBA Code

[cgdrake]

OK, this is driving me nuts. I have a simple function I want to call from a VBA module. Below is the code:

Public Sub CGDTest()
Dim LowPct As Double, LowDeduct As Double

LowPct = 15.9

LowDeduct = CalcLowDeduct(LowPct)

End Sub


Function CalcLowDeduct(LowPct As Double)

LowDeduct = Round((1.349907 * Log(LowPct) + 0.224636 * Log(LowPct) ^ 2 - 0.008581 * Log(LowPct) ^ 3), 2)

End Function


Anyhow, the LowDeduct correctly calculates to 5.27 within the function. However, upon my return to the module, the LowDeduct value changes to 0. What am I doing wrong? Thanks in advance for any help.

 

[WayneRyan]

cg,

Public Sub CGDTest()
Dim LowPct As Double, LowDeduct As Double

LowPct = 15.9

LowDeduct = CalcLowDeduct(LowPct) <-- LowDeduct is a [B]LOCAL [/B]variable
End Sub


Function CalcLowDeduct(LowPct As Double)

LowDeduct = Round((1.349907 * Log(LowPct) + 0.224636 * Log(LowPct) ^ 2 - 0.008581 * Log(LowPct) ^ 3), 2)

End Function

The function CalcLowDeduct is never assigned a value

hth,
Wayne

 

[pbaldy]

To do it the way you are, LowDeduct would have to be a public variable rather than declared within your first function.

 

[CyberLynx]

Like This....

Public Sub CGDTest()
   Dim LowPct As Double, LowDeduct As Double

   LowPct = 15.9

   LowDeduct = CalcLowDeduct(LowPct) <-- LowDeduct is a LOCAL variable
   [COLOR="DarkGreen"]'.....................
   '.....................
   'The rest of your code that will use the [B]LowDeduct[/B] variable.
   'If this variable will be processed elsewhere in the Database Form
   'then you would declare ([B]Dim[/B]) the variable within the declarations 
   'section of the Form or if it goes further than the Form then you
   'would declare the variable as [B]Public[/B] within a Database Code Module.
   '.....................
   '.....................[/COLOR]
End Sub


Public Function CalcLowDeduct(ByVal dblVal As Double) As Double
   CalcLowDeduct = Round((1.349907 * Log(dblVal) + 0.224636 * Log(dblVal) ^ 2 - 0.008581 * Log(dblVal) ^ 3), 2)
End Function

.

 

[cgdrake]

Thanks for the help, I will try it that way.