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:

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


    Thanks.

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

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