We help IT Professionals succeed at work.

Making a module available to new work book.

Frank Freese
Frank Freese asked
I created a macro and stored it in VBA Project(Personal.XLSB) under module (see below - it is not available) so for all new workbooks going forward could have this. The reason for this is only 2013 has the FormulaText() function.  In other words, a personal macro that I  can have available, if needed, in a new workbook.
Watch Question

SimonPrincipal Analyst

I think you'd need to save the macro in a workbook template named book.xltm in the XLSTART directory so that new workbooks are based on that template and include your function.

To check the startup folder paste this into a module and run it:
Sub showStartupFolder()
MsgBox Application.StartupPath
End Sub

Open in new window


If this is linked to the other topic: Display a cell formula

If you create a Module and paste the below:
Option Explicit
Function GetFormula(x As Range) As String
GetFormula = x.Formula
End Function

Open in new window

If you create this module, that formula will be available all the time whenever you save it under another workbook.

Do you know how to create a function?

Press Alt+F11 to open the Visual Basic Editor, and then click Insert and select Module. see below picture. Then paste the above code.
Insert function
Then your formula will always work.

Open in new window


thank you kindly


Yes, I'm familiar with creating a function. Thanks for asking
I glad i was able to help.  :-)


Follow-up. What if I wanted the function to always be as part of a new workbook w/o recreating it? Does one have to change the template? Or is there another way to make it always available?