Spreadhseet code reuse

Question

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.

Possible duplicate of [Using a UDF in Excel to update the worksheet](http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) — Mark Fitzgerald, Dec 24 '15 at 11:26
It's not a duplicate as the question is more open than the one referenced. — Tim Galvin, Dec 24 '15 at 11:29
This isn't clear. What does it mean to "wrap up a worksheet either as a UDF function"? VBA is a full-fledged programming language. Macros and UDFs you write in one can be reused in others, perhaps wrapped-up in `Add-ins`. Perhaps you want to write an add-in? — John Coleman, Dec 24 '15 at 11:29
It could be an add-in, but and add-in whose function are define by a set of spreadsheet cells. — Tim Galvin, Dec 24 '15 at 11:34
What does it mean for a UDF to "set the value of certain input cells"? If the UDF is setting the value (which isn't really possible with UDFs without weird hacks -- why not use a sub?) then wouldn't these be output cells? You need to explain what you are actually trying to do. — John Coleman, Dec 24 '15 at 11:45
The UDF would just act as the interface to spreadsheet A, which implements the necessary calculation logic. Spreadsheet B can then reuse these calculations by calling the UDF. The UDF would alter the input cells in spreadsheet B and return the value from some output cell in spreadsheet B to spreadsheet A. — Tim Galvin, Dec 24 '15 at 13:05

score 1 · Accepted Answer · answered Dec 24 '15 at 11:45

1

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.

answered Dec 24 '15 at 11:45

Vegard

3,587
2
22
40

Good answer. Perhaps add that a common technique is to link subs with the `worksheet change` event to simulate a UDF's ability to be instantly updated based on changing cell values. – John Coleman Dec 24 '15 at 11:48
I debated it with myself but ultimately removed that reference, because the more I thought about it, the more ways to hack around it came to mind and it just became too big of a list to be useful for this answer. I thought it was out of scope, if you will. – Vegard Dec 24 '15 at 11:54

score 0 · Answer 2 · answered Jan 12 '16 at 07:56

I've found an answer to my own question. It appears that UDFs cannot change cell values in the excel instance they are called from. The behaviour I want can be achieved by creating a new instance of Excel and openinf a copy of the current workbook in the second instance. The first instance can then call a UDF which modifies the second instance. Thus the calculations within a spreadsheet can be successfully wrapped up in a UDF.

Option Explicit
Public xl As Excel.Application
Public wb As Workbook
Public ws As Worksheet


Function calc(x As Double) As Double

If xl Is Nothing Then


    Set xl = CreateObject("Excel.Application")

    Set wb = xl.Workbooks.Open(ThisWorkbook.FullName)
    xl.Visible = False

    Set ws = wb.Worksheets("CalcluationModule")


End If

ws.Range("i").Value = x
wb.Application.Calculate

calc = ws.Range("PV").Value



End Function

score 0 · Answer 3 · answered Jan 12 '16 at 08:08

0

That's true ' sort of... However, your UDF can call a function that actives Win API timer, using the cell reference you called it from. The callback function can then do what you want to that cell...

answered Jan 12 '16 at 08:08

PaulG

1,051
7
9

I think the main issue has to do with calculation dependency. If you create a separate instance then you are less likely to end up in an endless loop. – Tim Galvin Jan 12 '16 at 08:23

Spreadhseet code reuse

3 Answers3