# Visual Basic > Office Development >  [Excel] How to pass cell references to a function so cells can be updated ?

## 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 = res4End 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!

----------


## 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

----------


## manov

> 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.

----------


## Arnoutdv

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

Are $A1, $A2 and $A3 really Objects?
Shouldn't they be Ranges?

----------


## manov

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 ?

----------


## Dan_W

> 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, 
> ...


Looking at what you posted, specifically the comments:



```
' ... 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.

----------


## Dan_W

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.

----------


## manov

> *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.

----------


## Dan_W

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

----------

