-
Jan 3rd, 2023, 06:20 PM
#1
Thread Starter
New Member
[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!
-
Jan 4th, 2023, 02:29 AM
#2
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
-
Jan 4th, 2023, 07:09 AM
#3
Thread Starter
New Member
Re: [Excel] How to pass cell references to a function so cells can be updated ?
Originally Posted by Arnoutdv
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.
-
Jan 4th, 2023, 08:48 AM
#4
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?
-
Jan 4th, 2023, 11:45 AM
#5
Thread Starter
New Member
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 ?
-
Jan 4th, 2023, 11:59 AM
#6
Lively Member
Re: [Excel] How to pass cell references to a function so cells can be updated ?
Originally Posted by manov
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.
-
Jan 4th, 2023, 12:04 PM
#7
Lively Member
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.
-
Jan 4th, 2023, 12:04 PM
#8
Thread Starter
New Member
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.
-
Jan 4th, 2023, 12:06 PM
#9
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|