User defined (macro) formula not Transferring when moving tab

[cubsfan05]

I've written a simple formula (below, i don't have excel 2013 with the =formulatext function) on a tab in a standalone model which i then need to drop into a large number of other models. The formula is written on the worksheet in the standalone model (not a module) and it works in the standalone model. However, when I copy the tab into a 2nd, larger workbook, the formula ceases to work and i get a #NAME error as the 2nd, larger workbook isn't recognizing the formula even though when i open VB, i can see the copied tab with the getformulatext function below.

Function GetFormulaText(myCell As Range)
    GetFormulaText = myCell.Formula
End Function

Any ideas why the 2nd, larger workbook isn't recognizing the formula on the tab that has been copied in?

 

[RoryA]

That code is in a normal module, which is not being copied to the other workbook, which is why it doesn't work. It sounds like you need to put it into an add-in.

 

[cubsfan05]

What do you mean an add-in?
like an excel or com add-in?

Ideally i'll be able to send this one-tab file to coworkers who can then drop it into their many models.
What i need to do is remove the link to a specific file by doing find & replace so that instead of referencing the "output" tab on a template file, it references the output tab in the user's individual existing file. thus i had the macro formula above to pull the file name being referenced so people knew what to find & replace with ""

 

[RoryA]

An Excel add-in (xlam file).
A single sheet won't work because a UDF cannot be stored in a worksheet module.