Macros are stored in modules, and these modules reside inside an excel file(s).
A lot of times though, the macros (still inside their modules) get stored in a hidden workbook
called Personal.xls that usually resides in your XLSTART folder. This book will get loaded every
time you run excel, even though you don't see anything (You have to go into the VBE to see it, or
do Format / Sheets / Unhide). If you have a lot of macros in here then you may want to back this
file up. If the macro in question is not in here, then it is in the file you are working with,
OR, another open file, but either way they will all be .xls files.
To see the modules that house these macros:-
Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane you need to search for
your workbook's name (Don't forget that they may be in the workbook called Personal.xls, which you
will see in here if there is anything in it), and when you find it you may need to click on the +
to expand it. Within
that you should see the following:-
VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1
Module2
etc etc (You may have just 1 of these)
The Modules at the bottom are where your macros are kept. Simply double click one to see what is
in it.