Spreadhseet code reuse

0
votes

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.

3
excelspreadsheetudf
Possible duplicate of Using a UDF in Excel to update the worksheet - Mark Fitzgerald
It's not a duplicate as the question is more open than the one referenced. - Tim Galvin
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
It could be an add-in, but and add-in whose function are define by a set of spreadsheet cells. - Tim Galvin
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

3 Answers

1
votes

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.

0
votes

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
0
votes

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...