Making code available for any file opened

[Domenic]

Yesterday, I came across the following code that allows one to test for
a true date using the function ISDATE():

Public Function ExcelIsDate(DateStr) As Boolean

If IsDate(DateStr) Then
ExcelIsDate = True
Else
ExcelIsDate = False
End If

End Function

The code works well, but if I quit Excel and then at some later time
re-launch it, the code is no longer available for my new file.

How can I make it available to any new file whenever I open Excel?

 

[JE McGimpsey]

The code works well, but if I quit Excel and then at some later time
re-launch it, the code is no longer available for my new file.

How can I make it available to any new file whenever I open Excel?

The most common way is to put it in your Personal Macro Workbook. The
PMW is just a regular workbook stored in the

Microsoft Office N:Office:Startup:Excel

folder, so you can name it anything ("Personal Macro Workbook" is just
the default). The PMW is usually hidden (Window/Hide).

Workbooks in the Startup folder open whenever XL is started. If you're
calling the macro from the Tools/Macro/Macros dialog, make sure "All
Open Workbooks" is selected so that macros in the PMW will be listed.

 

[Domenic]

Hi JE,

I have no problem following your instructions, but I think I'm putting
the code in the wrong place. I'm putting it in a regular module.

While it's available and functional in that particular workbook, it's
not available to any new file I open.

Also, after placing the code in the workbook, it's not listed in the
Macros dialog, even though the code and function works.

Where should I be placing the code?

 

[JE McGimpsey]

I have no problem following your instructions, but I think I'm putting
the code in the wrong place. I'm putting it in a regular module.

That's where it should go.

While it's available and functional in that particular workbook, it's
not available to any new file I open.

in new files, if the "Macros In" dropdown is set to "All Open
Workbooks", you should see your macro listed as something like

'Personal Macro Workbook'!Macro1

unless you've declared the Sub or the Module Private, or unless the
macro has arguments. Macros with arguments, e.g.,

Public Sub foo(bar)

which won't show up in the Macro dialog no matter what workbook they're
stored in.

 

[Domenic]

When I insert the code in a workbook and look at the Macro's dialogue
box, the Macro is not listed.

Does that mean that the macro can't be made available to new files?

 

[JE McGimpsey]

When I insert the code in a workbook and look at the Macro's dialogue
box, the Macro is not listed.

Does that mean that the macro can't be made available to new files?

AFAIK, the code is available in the macro list to all new workbooks if

a) The workbook containing the code is open.
b) The module is not marked Option Private Module
c) The code is a Sub, not a Function.
d) The Sub is not declared as Private.
e) The Sub has no arguments.
f) The macro list's "Macros in" dropdown is set to either the workbook
containing the macro, or to All Open Workbooks.


If you put the code in a regular code module, in the Personal Macro
Workbook, stored in the Startup:Excel folder, then it will be available
to all other workbooks.

It will still be listed if you put the code in the ThisWorkbook or
worksheet code modules, but the default behavior may be different.

 

[Domenic]

Yes, the code is a Function. So that would explain why it's not
available to other workbooks.

Hopefully Microsoft will include this function in future upgrades so
that it becomes a standard function.

JE, thank you very much for your help! Much appreciated!

 

[JE McGimpsey]

Yes, the code is a Function. So that would explain why it's not
available to other workbooks.

Hopefully Microsoft will include this function in future upgrades so
that it becomes a standard function.

Functions are available in other workbooks - they just don't show up in
the Macro list because they aren't macros.

Putting

Public Function Foo(bar)

in the Personal Macro Workbook can be called from any other workbook as

='Personal Macro Workbook'!foo(A1)

If you put the function in an Add-in, rather than a workbook, you can
dispense with the workbook name:

=foo(A1)

 

[Domenic]

Functions are available in other workbooks - they just don't show up in
the Macro list because they aren't macros.

Putting

Public Function Foo(bar)

in the Personal Macro Workbook can be called from any other workbook as

='Personal Macro Workbook'!foo(A1)

I've put the code for the function in my Personal Macro Workbook and I
can now call the function from any workbook I open.

If you put the function in an Add-in, rather than a workbook, you can
dispense with the workbook name:

=foo(A1)

This would definitely be more convenient and preferable. How can I put
the function in an Add-in?

 

[JE McGimpsey]

This would definitely be more convenient and preferable. How can I put
the function in an Add-in?

Put the macros/functions in a clean file. In the Save As dialog, choose
Excel Add-in from the Format Template. For easy identification, save the
file with the .xla extension

To automatically load the add-in, store it in the

Microsoft Office 2004:Office:Startup:Excel:

folder.

Otherwise, save it in a convenient folder (I use a folder inside the MUD
folder) and use Tools/Add-ins... to load or unload the add-in.

 

[Domenic]

Put the macros/functions in a clean file. In the Save As dialog, choose
Excel Add-in from the Format Template. For easy identification, save the
file with the .xla extension

To automatically load the add-in, store it in the

Microsoft Office 2004:Office:Startup:Excel:

folder.

Yes, definitely much better. This way I don't have to worry about
loading and unloading the add-in. It'll always be available whenever I
need it.

Otherwise, save it in a convenient folder (I use a folder inside the MUD
folder) and use Tools/Add-ins... to load or unload the add-in.

Good to know in case I change my mind in the future and want to manually
load and unload the add-in.

JE, as always, thank you very much for your invaluable help!