Is it possible to set the value of a cell in an excel function

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

Well, don't that suck. So the functionality I desire will have to come about using Macro's. Unfortunately, it appears to me that when I make a file an addin the Macro's disappear from the macro list. Is it possible to make them showup somehow?

Jim:

I don't pretend to know what you are trying to do and I don't pretend to know more than a little bit about Excel VBA. However, I had an AddIn experience this past weekend wherein I needed to deal with "hidden macros".

Now, you may know all of this but I didn't; so, in the event you are not aware of it, here is what I discovered:

You can "call" these "hidden macros" from a "Private Change Event" macro or from a Module, either of which would be contained in your active workbook. Or, you can include the macros in a ComboBox or list in your active workbook, thereby allowing the user to, in effect, call the macro themselves.

Add the macro to the active workbook in excel options, create a reference to the AddIn in VBA->Tools->Reference and, viola, you can run the macros from your active workbook.

I don't know if this addresses your situation. I discovered this information through the kind help of the people on this forum.

Hope this helps.

John

Thank you kindly for the information jaslake and shg.

I suppose we should mark this one solved in the sense we know we can't do what I want. Can you do me the honor shg?

I'll do the job with a macro using a userform and input boxes to enter the required parameters. Not as elegant, but can certainly be functional. I believe that I should be able to add an interface to the macro's on one of the tool bars when the add in gets loaded. I don't know how to do this yet, but I'm sure it is possible.

I agree and disagree with shg's comment on allowing a function to modify a cell. The programmer could potentially create all kinds of chaos. But this is true of any "useful" programming language including VBA in its current incarnation. I believe that this is a special circumstance worth special consideration. An add in provides functions and routines for use in other workbooks. Other workbooks do not have direct access to the cells on any worksheet within the addin workbook. Perhaps this functionality could be provided in the future under special circumstances such as the programmer declaring the worksheet that he wants the function to modify as a "friend" or some such device. Perhaps in this protocol such a function can not be called in the workbook in which it is defined.

Jim