Tags: application, caller, cell, current, excel, microsoft, msdn, retrieve, retrieving, software, udf, value

retrieving current cell's value in a UDF

On Microsoft » Microsoft Excel

11,185 words with 6 Comments; publish: Thu, 22 May 2008 05:33:00 GMT; (306125.00, « »)

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

All Comments

Leave a comment...

  • 6 Comments
    • Terry,

      ActiveCell.Value

      will return the valeue.

      HTH,

      Nikos

      "J" <iamjohnny.excel.questionfor.info.pchome.com.tw> wrote in message

      news:OZGqp$19DHA.1052.excel.questionfor.info.TK2MSFTNGP12.phx.gbl...

      > 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

      >

      >

      #1; Thu, 22 May 2008 05:34:00 GMT
    • 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

      In article <OZGqp$19DHA.1052.excel.questionfor.info.TK2MSFTNGP12.phx.gbl>,

      iamjohnny.excel.questionfor.info.pchome.com.tw says...

      > 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

      >

      >

      #2; Thu, 22 May 2008 05:35:00 GMT
    • 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

      "Tushar Mehta" <tmUnderscore200310.excel.questionfor.info.tushar-mehta.SeeOhEm> wrote in message

      news:MPG.1a9ff79ae17a5e299896fc.excel.questionfor.info.news-server...

      > 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

      > In article <OZGqp$19DHA.1052.excel.questionfor.info.TK2MSFTNGP12.phx.gbl>,

      > iamjohnny.excel.questionfor.info.pchome.com.tw says...

      > > 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

      > >

      > >

      > >

      > >

      #3; Thu, 22 May 2008 05:36:00 GMT
    • 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

      "J" <iamjohnny.excel.questionfor.info.pchome.com.tw> wrote in message

      news:uJALTTE%23DHA.2308.excel.questionfor.info.TK2MSFTNGP11.phx.gbl...

      > 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

      >

      > "Tushar Mehta" <tmUnderscore200310.excel.questionfor.info.tushar-mehta.SeeOhEm> wrote in message

      > news:MPG.1a9ff79ae17a5e299896fc.excel.questionfor.info.news-server...

      > > 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

      > >

      > > In article <OZGqp$19DHA.1052.excel.questionfor.info.TK2MSFTNGP12.phx.gbl>,

      > > iamjohnny.excel.questionfor.info.pchome.com.tw says...

      > > > 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

      > > >

      > > >

      > > >

      > > >

      >

      #4; Thu, 22 May 2008 05:37:00 GMT
    • 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 wrote:

      > 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

      --

      Dave Peterson

      ec35720.excel.questionfor.info.msn.com

      #5; Thu, 22 May 2008 05:38:00 GMT
    • Dear Dave,

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

      help.

      Many thanks.

      Terry

      "Dave Peterson" <ec35720.excel.questionfor.info.msn.com> wrote in message

      news:403776F2.88754AD6.excel.questionfor.info.msn.com...

      > 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 wrote:

      > >

      > > 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

      > --

      > Dave Peterson

      > ec35720.excel.questionfor.info.msn.com

      #6; Thu, 22 May 2008 05:39:00 GMT