Results 1 to 9 of 9

Thread: [Excel] How to pass cell references to a function so cells can be updated ?

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2023
    Posts
    4

    [Excel] How to pass cell references to a function so cells can be updated ?

    Hi,

    I've written a VBA function that performs a complex iterative calculations that produce 3 different values that I need to save in specific cells in the calling sheet. I've found some web-sites that have indicated I should be able to do this in the spreadsheet:

    =MyFunc(1234, 5678, $A1, $A2, $A3)


    ...and have a function defined like this that will do the calculation and return the 3 results in cells A1, A2, and A3...

    MyFunc(ByVal InVar1 as double,
    ByVal InVar2 as double,
    ByRef OutCell1 as Object,
    ByRef OutCell2 as Object,
    ByRef OutCell3 as Object
    ) as double

    ' Execute code here to perform calculations resulting in res1, res2, and res3 local variables being set

    OutCell1 = res1
    OutCell2 = res2
    OutCell3 = res3


    MyFunc = res4
    End Function

    However Excel throws a !VALUE error and it seems the cause is using ByRef for the last 3 parameters. Can anyone advise ?

    Thanks in advance!

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: [Excel] How to pass cell references to a function so cells can be updated ?

    You don't have to specify ByRef for the Objects.
    OutCell1 will be a real reference to the passed object and not a copy

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2023
    Posts
    4

    Re: [Excel] How to pass cell references to a function so cells can be updated ?

    Quote Originally Posted by Arnoutdv View Post
    You don't have to specify ByRef for the Objects.
    OutCell1 will be a real reference to the passed object and not a copy
    Thanks for your reply. Unfortunately this did not work for me, I tried using ByVal and still get the same error.

  4. #4
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: [Excel] How to pass cell references to a function so cells can be updated ?

    Code:
    MyFunc(1234, 5678, $A1, $A2, $A3)
    Are $A1, $A2 and $A3 really Objects?
    Shouldn't they be Ranges?

  5. #5

    Thread Starter
    New Member
    Join Date
    Jan 2023
    Posts
    4

    Re: [Excel] How to pass cell references to a function so cells can be updated ?

    Are you saying they should be ranges then ? I'm just trying to figure out how to get a function to modify some cells that are passed in as function parameters - are you saying that ranges will allow me to achieve that ?

  6. #6
    Lively Member
    Join Date
    May 2021
    Posts
    95

    Re: [Excel] How to pass cell references to a function so cells can be updated ?

    Quote Originally Posted by manov View Post
    Hi,

    I've written a VBA function that performs a complex iterative calculations that produce 3 different values that I need to save in specific cells in the calling sheet. I've found some web-sites that have indicated I should be able to do this in the spreadsheet:

    =MyFunc(1234, 5678, $A1, $A2, $A3)


    ...and have a function defined like this that will do the calculation and return the 3 results in cells A1, A2, and A3...

    MyFunc(ByVal InVar1 as double,
    ByVal InVar2 as double,
    ByRef OutCell1 as Object,
    ByRef OutCell2 as Object,
    ByRef OutCell3 as Object
    ) as double

    ' Execute code here to perform calculations resulting in res1, res2, and res3 local variables being set

    OutCell1 = res1
    OutCell2 = res2
    OutCell3 = res3


    MyFunc = res4
    End Function

    However Excel throws a !VALUE error and it seems the cause is using ByRef for the last 3 parameters. Can anyone advise ?

    Thanks in advance!
    Looking at what you posted, specifically the comments:

    Code:
    ' ... perform calculations resulting in res1, res2, and res3 local variables being set
    Your explanation says that you're returning three results into Ranges A1 to A3, but your comments says you're setting the values of local variables. From the code you've shown above, you're setting the value of ranges A1, A2 and A3 with the values of Res1, Res2 and Res. And you can't do that.

    Excel does not permit a User Defined Function (like yours) entered into one cell to change the values/state of another cell. You would either need to write a function to put in each of A1 to A3 if you want those cells to populated, or you need to use a subroutine.

  7. #7
    Lively Member
    Join Date
    May 2021
    Posts
    95

    Re: [Excel] How to pass cell references to a function so cells can be updated ?

    Here, you've used the generic Object data type, and when your function runs, they are being coerced into a Range data type so in this particular case, it seems to work as intended. However, to the extent that you are able or is appropriate, you should always use express data types because sometimes things can horribly wrong if you don't. So yes, you should be referring to them as Range. But no, that won't solve your problem for the reason explained above.

  8. #8

    Thread Starter
    New Member
    Join Date
    Jan 2023
    Posts
    4

    Re: [Excel] How to pass cell references to a function so cells can be updated ?

    Excel does not permit a User Defined Function (like yours) entered into one cell to change the values/state of another cell. You would either need to write a function to put in each of A1 to A3 if you want those cells to populated, or you need to use a subroutine.
    Fantastic, thank you for this, it explains why I was unable to get this to work. Many thanks for your time. much appreciated.

  9. #9
    Lively Member
    Join Date
    May 2021
    Posts
    95

    Re: [Excel] How to pass cell references to a function so cells can be updated ?

    You're very welcome. Thank you kindly for the feedback.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width