Does anyone know of a way to wrap up a worksheet either as a UDF function?
Essentially I'd like to create a worksheet or workbook which carries out certain calculations and then reuse this code in other worksheet or workbooks. Ideally the UDF would set the value of certain input cells and return a value from a certain output cell.
There is a hack in the answer to this question, but it doesn't work well.
Using a UDF in Excel to update the worksheet
Ideally I'd like to do this in Excel, but am receptive to suggestions of alternative spreadsheet software, third party excel tools or alternative platforms entirely.
UDFs are not designed to change the value of any cell other than the one it is being used in.
There are hacks for this that work in some use-cases. That is not a design feature of the UDF, however, but rather clever manipulation of other designs in Excel. In any case, I think most will agree that these types of hacks can be unstable and surely not recommended for production use.
If you want to change more than one cell at the time, you are best of writing a Sub. This gives you more control, the behavior is well-documented and overall your calculations do not rely on unofficial work-arounds that may or may not break in any given patch.