none
User Defined Function returns active sheet value in other sheets RRS feed

  • Question

  • Hi,

    I used same User defined function(UDF) in multiple sheet.  It returns wrong values in other sheet except active sheet whenever any cell on the active sheet is modified then it reflecting active sheet UDF calculated value in other sheet in same UDF cell.


    Function INDVBA(strcell As String)
        Application.Volatile
        Set INDVBA = Range(strcell)
    End Function

    Refer below mentioned screen shot for more detail.

    sheet1

    sheet2

    sheet3


    Bala

    Saturday, April 2, 2016 11:02 AM

Answers

  • If INDVBA should always refer to a range on the active sheet, you could change the function to

    Function INDVBA(strcell As String)
        Application.Volatile
        Set INDVBA = Application.Caller.Parent.Range(strcell)
    End Function
    

    Application.Caller is the cell that contains the formula, and Application.Caller.Parent is the worksheet containing this cell.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, April 2, 2016 1:54 PM

All replies

  • If INDVBA should always refer to a range on the active sheet, you could change the function to

    Function INDVBA(strcell As String)
        Application.Volatile
        Set INDVBA = Application.Caller.Parent.Range(strcell)
    End Function
    

    Application.Caller is the cell that contains the formula, and Application.Caller.Parent is the worksheet containing this cell.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, April 2, 2016 1:54 PM
  • Hi Hans Vogelaar,

    Thank You Very Much !!!!

    I really appreciate your help.


    Bala

    Wednesday, April 13, 2016 12:08 PM