If you want that certain code is available in all your workbooks, then you can use your PERSONAL.XLSB file.

What is it:
This is a hidden workbook that opens when you start Excel.
The code you copy in this workbook is available in all workbooks that are open in Excel.

Where is it:
If it exists, you can find the file in the Excel startup folder on your Mac.
/Users/rondebruin/Library/Group Containers/UBF8T346G9.Office/User Content/Start-up/Excel

Note: rondebruin is the username in the path above

To get to this folder, open a Finder window and click on Go in the Finder menu bar; if you press and hold the Option key you will see the hidden Library folder in the list so you can click on it to open it. Now drill down to Group Containers, UBF8T346G9.Office, User Content, Start-up, and then Excel. The strange label “UBF8T346G9.Office” was assigned by Apple to Microsoft, and we have to live with it.

Tip: with the folder open in Finder, use the shortcut Command+Control+t to add a link to this folder in the Finder Favourites sidebar so it is easy to access the folder another time.

Pro Tip: Go to Setup your Mac for Mac Office. Among other hints, this page includes a script that will install shortcuts to the folder and several other folders on the Mac desktop.

With VBA code you find the correct path on your machine with this line
Application.StartupPath

If it does not exist then record a dummy macro and change the "Store macro in:" drop down to Personal Macro Workbook. Excel creates the file for you in the correct folder this way. There are a few ways to record a macro, one is to use the button on the Developer tab and I show you two other ways below.

You can use Tools>Macro…Record New Macro… in the menu bar.

Or faster click the button on the bottom left corner of the Excel window to start the recording.

Because we only want Excel to create the file for us we can stop the recording directly.
Use Tools>Macros…Stop recording in the menu bar or click the button on the bottom left of the Excel window to stop the recording.

Now we use CMD F11 to go to the VBE editor or click on the button named “Visual Basic” on the “Developer” tab. Note: To be sure that we see the Project Window, Code window and the Properties window we use one time.

Control-Command-r (to see the project explorer)
F7 (to view the code window)
F6 (to view the properties window)

Or we use the View menu in the VBE editor to do the same as above.

In the Project explorer select the PERSONAL.XLSB file

Now we give the PERSONAL.XLSB a unique project name (Default is VBAProject)
In the properties window change VBAProject to for example MyPersonalFile

Now use Command-s or File>Save in the VBE editor to save the PERSONAL file

We are now ready to add code (macros or/and functions) to this file so we can use it in all workbooks.
With Insert>Module in the VBA editor you can also add more modules to the PERSONAL.XLSB. This way you can organise your code in this file. Use only code here that you use yourself and not code that you use in files that you distribute to other users. In that case add the code in the modules of the workbook itself, or distribute your VBA macros as an Add-in.

If you create or find a nice UDF (User Defined Function) you can also place it in your PERSONAL file and call it like this in your worksheet:

=PERSONAL.XLSB!RDBFunction(A1)

If you create a reference in the workbook where you want to use it to your PERSONAL.XLSB then you can use

=RDBFunction(A1)

Go to Tools>References in the VBE editor with the workbook selected in the project window and add a check mark before MyPersonalFile and Save the workbook.

If you want another way to call your macros and build a nice menu check out this page:
Menu for favourite Macros in Mac Excel