I am trying to use FormulaText in MS Office 2007 as given in one of the forum I used the below mentioned function in VBA

Function FormulaText(Rng As Range) As String
    If Rng.HasArray Then
        FormulaText = "{" & Rng.Formula & "}"
        FormulaText = Rng.Formula
    End If
End Function

The file was saved as Excel AddIn type in AddIns

The formula is working on the active workbook however I want to embed the same into Excel so as to enable me to use this function for all Excel workbooks. Also after few days the file was automatically removed by OS and I had to redo the exercise of saving the file.

  • If you only want to use it yourself, wouldn't saving it in your PERSONAL.XLSB solve the problem? – Mark Fitzgerald Mar 22 at 9:28

Follow these steps:

  • Create the Function(UDF) in blank Workbook, better keep only one Sheet in it.
  • Save it as Excel Add-In, in the Add-Ins folder.
  • Open any Workbook, click File command then Options.
  • In the Excel Options dialogue box, click the Add-Ins category.
  • In the Manage drop-down list, select Excel Add-Ins. Then click the Go button.
  • In the Add-Ins dialogue box, select the check box beside the name you used to save your Workbook with the Function.
  • Finish with Ok and back to file.

The function will now be available to all workbooks in Excel.

  • Good answer. This is succinct, to the point and actually answers the OP's question. I'll give this a vote, but I sure wish all your answers could be like this. – Bandersnatch Mar 22 at 16:10

Your Answer


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.