Create VBA function that returns many values

  • Thread starter Thread starter gkk-vba
  • Start date Start date
G

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
 
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
 
Back
Top