retrieving current cell's value in a UDF

[J]

Hi there,

Does anyone know how within a UDF, how can I retrieve the current cell's
value ? I have tried application.caller.value and application.caller.text
but to no avail. I don't have any intentions to pass it as a parameter. I am
basically trying to reduce the recalculation on cells with UDF.

eg.

Public function myudf( recalc as boolean, p1,p2 )

if recalc then
myudf = p1 + p2
else
myudf = application.caller.value
endif

end function

Appreciate any help anyone can offer.

Rdgs,

Terry

 

[Nikos Yannacopoulos]

Terry,

ActiveCell.Value

will return the valeue.

HTH,
Nikos

 

[Tushar Mehta]

What error do you get? And, why won't you pass the information as an
argument?

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[J]

Dear Tushar,

In my UDF, it may be used to retreive information from SQL-Server using ADO.
If the user passes a 'lock' variable say a Y flag in one of the parameters,
my UDF would not executed the SQL query BUT the current cell's value is
already set to zero. I need to restore the original cell's value from the
previous query by returning it in the same UDF function.

I had already tried passing the current cell's address but on entry into the
function,(with the debugger on the function line) it's value is ALREADY
initialised to zero. I have heard from friends that some commercial packages
have already done it. This is a very common and basic request for VBA users
with trying to control/reduce the amount of un-necessary recalculation time
in their UDFs.

Rdgs,

Terry

 

[Niek Otten]

Hi Terry,

Yet another try, although again not exactly what you asked.
If you really mean returning the value last returned (which may be not the
same as returning the value of the calling cell), declare a Static variable
to hold the value. I use this often in functions which require a lot of
initialisation, just once.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Dave Peterson]

It looks to me it depends on how the cell is changing.

I put this formula in A1:
=myudf(B1,B2,B3)

B1=True/False
B2=1
B3=2

If I changed the formula in A1 (or just F2|Enter), then I got 0 returned.

But if I changed the values in B1:B3, then this UDF worked ok:

Option Explicit
Public Function myUdf(recalc As Boolean, p1 As Double, p2 As Double) _
As Variant

Dim myVal As Variant

myVal = Application.Caller.Text

If recalc Then
myUdf = p1 + p2
Else
If IsNumeric(myVal) Then
myUdf = CDbl(myVal)
Else
myUdf = myVal
End If
End If

End Function

I don't know anything about SQL, but maybe this'll help.

 

[J]

Dear Dave,

Your clear and simple example has cleared up my confusion. Thanks for your
help.

Many thanks.

Terry