Cell contents is a macro

[Tagas]

Hi everyone,

Is it possible for the contents of a cell to be the result of a macro, so
that observable behaviour similar to a formula is achieved (ie: it
automatically recalculates the cell contents if any of the values the
formula uses changes?) - I ask this because I need the cell to display the
results of a calculation which is difficult and error prone to do in one
line, whereas the use of VBA would be good.

Thanks

[NickHK]

Tagas,
You can create Public user defined functions in .bas in the VBA.
Also, look into Volatile for recalculation modes.
NickHK

"Tagas" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone,
>
> Is it possible for the contents of a cell to be the result of a macro, so
> that observable behaviour similar to a formula is achieved (ie: it
> automatically recalculates the cell contents if any of the values the
> formula uses changes?) - I ask this because I need the cell to display
the
> results of a calculation which is difficult and error prone to do in one
> line, whereas the use of VBA would be good.
>
> Thanks
>
>

[Patrick Molloy]

read help on User Defined Functions

eg
PUBLIC FUNCTION MyFunc(var1,var2,var3) as double

' my formula here
'Dim Result as Double
Result = (var1 + var2) / var3
MyFunc = Result
END FUNCTION


This should be placed on a standard code module

Patrick Molloy
Microsoft Excel MVP



"Tagas" wrote:

> Hi everyone,
>
> Is it possible for the contents of a cell to be the result of a macro, so
> that observable behaviour similar to a formula is achieved (ie: it
> automatically recalculates the cell contents if any of the values the
> formula uses changes?) - I ask this because I need the cell to display the
> results of a calculation which is difficult and error prone to do in one
> line, whereas the use of VBA would be good.
>
> Thanks
>
>