First make sure your certificate is installed in the Windows CSP. You can right-click on your PFX file and choose Install, or import through Internet Explorer by clicking the 
Internet Options menu -> Content -> Certificates -> Import.

Open the VBA project that you want to sign and select Digital Signature from the Tools menu.

Click "Choose" from the Digital Signature window and select your PFX file.

Click OK, then Save.

Your MS Office document is now digitally signed!

By default, Windows XP, Vista and Windows 7 do not add a time stamp to the certificate. If you're signing on those platforms, here is how to add a timestamp :

An additional (and important!) step is timestamping. This allows your signature to remain valid forever, even after your certificate expires.

To add a timestamp to your VBA macros by default, create a file named timestamp.bat and copy/paste these lines in to it :

reg add "HKCU\Software\Microsoft\VBA\Security" /v "TimeStampURL" /f /d ""
reg add "HKCU\Software\Microsoft\VBA\Security" /v "TimeStampRetryCount" /f /t REG_DWORD /d 2
reg add "HKCU\Software\Microsoft\VBA\Security" /v "TimeStampRetryDelay" /f /t REG_DWORD /d 1

Save the file, then double-click the file to run it (confirm YES when prompted). Surprisingly there is no way to verify a timestamp on a signed macro except to set your computer's clock forward past your certificate's expiration date and run the macro to see that it still runs signed.