Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

Without signing VBA Macros users are left with scary warnings before running code and/or lowering Microsoft Office Trust Center Settings.

The breakdown of how each Office Program has varied for me, for instance:

  • With Excel, I can leave Macro settings at default (Do Not Run), but still install XLAM Add-Ins in XLSTART and they run without issue, excel has the best Macro distribution system
  • With Word, I can leave the Macro settings at default (Do Not Run), and users get a warning and have to enable Macros. The biggest issue with this is having users get used to just blindly hitting "Enable".
  • With Outlook, this is the worst. Macro's will just not run with default settings and no notification (Only Run from Signed). This is what lead me down this path. You have two options with outlook, lower security settings in trust center or sign code.

You have two options for signing certs, creating your own cert and importing into Cert Store or purchasing a verified Code Signing Certificate from a Cert Auth.

Self Signing Certs: 

SelfCert.exe: (Note: This is the simplest method, but doesn't create a key that is shareable with other computers/users etc)

SelfCert.exe Locations:

Windows 32-bit
C:\Program Files\Microsoft Office\Office <version number>
Windows 64-bit with Office 32-bit
C:\Program Files (x86)\Microsoft Office\Office <version number>
Windows 64-bit with Office 64-bit
C:\Program Files\Microsoft Office\Office <version number>
Office 365 32-bit (Subscription based or Click-to-Run version of Office 2016 / 2019)
C:\Program Files (x86)\Microsoft Office\root\Office16
Office 365 64-bit (Subscription based or Click-to-Run version of Office 2016 / 2019)
C:\Program Files\Microsoft Office\root\Office16

Run SelfCert.exe and enter a Cert Name. Your computer now has a Self Signed Cert in the Personal Store and you can move on to 'Signing Projects'.

If you want, you can export your key from the Personal Store and import into the Trusted Root to make it trusted, but it will not be shareable with other computers still. See http://www.gmayor.com/create_and_employ_a_digital_cert.htm. Basically just use MMC CertMGR Snap-In to Export from Personal Store and import in Trusted Root.

Manually Creating Certificate: (Note: This has the benefit of created an exportable Private Key so this Cert can be shared between computers, you can even use Group Policy to push the cert to Trusted Root to Domain Computers)

https://stackoverflow.com/a/51443366/5079799

https://stackoverflow.com/questions/16177682/certificate-marked-as-not-exportable

https://stackoverflow.com/questions/84847/how-do-i-create-a-self-signed-certificate-for-code-signing-on-windows

http://www.source-code.biz/snippets/vbasic/3.htm

Using PowerShell and New-SelfSignedCertificate:

New-SelfSignedCertificate -DnsName Username@Domain.com -Type CodeSigning

Note: Default Expiration of Today + 1 Year Or use -NotAfter (Get-Date).AddMonths(12)

Now use MMC + CertMGR to export/import etc.

Note: MakeCert is deprecated. To create self-signed certificates, use the Powershell Cmdlet New-SelfSignedCertificate.

https://docs.microsoft.com/en-us/powershell/module/pkiclient/new-selfsignedcertificate?view=win10-ps

The following commands can be used to create a PFX file (PKCS #12) that contains the a self-signed certificate together with the associated private key:

makecert -r -n "CN=User@Domain.com" -eku 1.3.6.1.5.5.7.3.3 -sv %USERNAME%selfcert.pvk %USERNAME%selfcert.cer
cert2spc %USERNAME%selfcert.cer %USERNAME%selfcert.spc
pvkimprt -pfx %USERNAME%selfcert.spc %USERNAME%selfcert.pvk
-n "Name" Name for the publisher's certificate. This name must conform to the X.500 standard. The simplest method is to use the "CN=MyName" format. For example: -n "CN=Test".
-m nMonths Duration of the validity period.
-e DateEnd Date when the validity period ends. The default is the year 2039.
-b DateStart Date the certificate first becomes valid. The default is when the certificate is created. The format of DateStart is mm/dd/yyyy.
-eku OID1, OID2 … Inserts a list of one or more comma-separated, enhanced key usage object identifiers (OIDs) into the certificate. For example, -eku 1.3.6.1.5.5.7.3.2 inserts the client authentication OID. For definitions of allowable OIDs, see the Wincrypt.h file in CryptoAPI 2.0.
-sv SubjectKeyFile Name of the subject's .pvk file. If neither the -sk or -sv option is used, a default key container is created and used by default.

MakeCert Flags:
https://docs.microsoft.com/en-us/windows/win32/seccrypto/makecert

Using a Signed Cert from a Cert Auth:

  • Basically once you get the cert, it's the same as any method below to import.

Using CertUtil via CMD to import Keys:

certutil -addstore "TrustedPublishers" *.cer 'Computer Store, requires Admin
certutil -addstore -user -f "TrustedPublishers" *.cer

https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/certutil

Using Batch & PowerShell to Import Keys: ***PREFERRED***

https://docs.microsoft.com/en-us/powershell/module/pkiclient/import-certificate?view=win10-ps

"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" -c Import-Certificate -FilePath "\\Server\Path\File.cer" -CertStoreLocation Cert:\LocalMachine\TrustedPublisher
"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" -c Import-Certificate -FilePath "\\Server\Path\File.cer" -CertStoreLocation Cert:\CurrentUser\TrustedPublisher

Signing Projects:

Open VBA Editor and go Tools → Digital Signature

Choose → OK

Note: There is a bit of a glitch when implement the Cert → https://stackoverflow.com/questions/30619881/microsoft-outlook-2013-error-verify-vba-project-signature/47380003#47380003

https://support.microsoft.com/en-ca/help/4465120/macros-in-this-project-are-disabled-when-you-run-macros-in-outlook

You need to go File → Save FROM WITHIN THE VBA EDITOR!

Now exit the VBA Editor and Microsoft Office Application

Re-Open Microsoft Office Application and run Macro, you may see a warning about the publisher not being authenticated. Hit "Trust all documents from this publisher" and run Macro. 

You should not get any warning anymore. See links for more detailed information.

  • No labels