How to pass a cell to the function?

  • Hello,
    I need to create a function to be used in the worksheet and it needs to pass the cell, but it is only passing the value of the cell. Like:
    In the table:
    A1 has value =UPPERCASE("test1")
    B1 has value =MyFunction(A1)

    In the module:

    Function MyFunction(thecell)
         MsgBox thecell.FormulaLocal
    End Function

    The function should receive the formula "=MyFunction(A1)", but it gives error. When I change it, it only receives the value "TEST1".
    How to make the function pass the cell as an object instead of passing only value?


  • What is UPPERCASE?

    Your function returns the formula in thecell which is A1 in your example. When I run it I get =UPPER("test1") (I changed the formula,) What are you trying to achieve?

  • Another friend gave me the solution, StephenR. Just add "As Range". Tnx!

    Function MyFunction(thecell As Range)      MsgBox thecell.FormulaLocal End Function

    (I do not know how to mark this post as SOLVED)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!