Create VBA function that returns many values

[gkk-vba]

I would like to write a VBA function that takes as input real numbers and
returns more than one value in distinct cells of the worksheet, whose
location can vary dynamically.Specifically I would like a function which
does domething like this.
Function F(x as Double, y as Double, z as Double)
.... some code here
F in one cell of the worksheet
G in another cell etc.
End Function
Any help will be appreciated

 

[Niek Otten]

Although there are some cases where multiple cells can be populated (array functions), what you require is not possible and even
more, not desirable.
Consider a function here as a function in math; the only thing it can do is replace its call with a value.
Technically there are no problems to let a function change several locations (and, in VBA, you can indeed (under certain
circumstances) change variables that are not in the argument list of the call), but, fortunately, in Excel worksheets, functions
follow very much the patterns of math functions. So you can't change anything in a workbook, you can just return a value which
will take the place of the call to the function.

If this doesn't satisfy your requirements, please elaborate a bit on what you're trying to achieve.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I would like to write a VBA function that takes as input real numbers and
| returns more than one value in distinct cells of the worksheet, whose
| location can vary dynamically.Specifically I would like a function which
| does domething like this.
| Function F(x as Double, y as Double, z as Double)
| ... some code here
| F in one cell of the worksheet
| G in another cell etc.
| End Function
| Any help will be appreciated

 

[Chip Pearson]

A function can only return values to the cell(s) from which it was called.
If the function is entered into an array of cells, it can return an array to
those cells. See http://www.cpearson.com/Excel/ReturningArraysFromVBA.aspx
for details and example code.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)