I want to convert an excel spreadsheet into an excel addin file so that users may use the embedded generations of knowledge as excel functions. Unfortunately, I have a problem. I can create macros that allow me to change cells within the spreadsheet and as a result calculate useful values etc, but when I change these macros into functions VBA generates an error wherever I try to change the value of a cell. This appears to be a feature of excel VBA programming. Is there a workaround?

To illustrate what I am trying to do:
The intial spreadsheet (say it is called "MatProps.xls") contains many complicated formulae that calculate material properties. There is a nice user interface where the user enters temperature etc. and there is an output cell where the materials viscosity appears. I want to create a function for example:

Visc(temperature)

If the spreadsheet is saved as an addin, then the user can enter a formula such as:

= visc( 200 )

or = visc ( A2 )

in a new spreadsheet. The function "visc" needs to be able to set the value of the temperature input cell in "MatProps.xls" and then read the viscosity value from the output cell. If "visc" is defined as a subroutine (hence becoming a macro), the input cell can be changed, the output read and everything is hunky dory. When "visc" is changed to a function, VBA can not set a value in the input cell.

Any Guru's, wizards or witches able to help?

Jim