Data exchange between vba and excel

[Thomas Dufaux]

Hi,

I want to implement in vba Excel (ver. 2002) a quite complex algorithm
which needs numerous input variables and gives numerous results.
Furthermore the algorithm has to run on different sets of input variables.

What would be the best way to implement this?

First of all I thought I could just call the vba function in a cell (
via '=DoTheJob()') and push the results via range("something") in the
worksheet. This didn't work at all, since excel seems to block every
writing into the worksheet. Can anybody tell me why? When I use a button
it works fine.
Then I used a change event to trigger my function, but that also didn't
work well. I don`t want to make any changes to the script when I need to
calculate a new set of input variables.

So what I would need is a way to give my function multiple input
variables and get multiple results.

I hope I made clear what I mean.

Thx

Thomas

 

[Charles Williams]

Hi Thomas,

Functions in worksheet cells can only return results to the cell(s) that
they occupy. You can make an array function that returns an array of values
to the cells that it has been entered into as an array formula.
Functions can have up to 30 parameters, each of which can be a range
pointing to multiple contiguous cells.

for some tips and examples see
http://www.DecisionModels.com/calcsecretsj.htm


Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com