How to digitally sign an Excel workbook via VBA

[becci.gott]

Hi all, hope that someone will be able to help..

I am trying to find out if it is possible to apply a digital signature to an Excel file programmatically via VBA. At the moment, I am going around in circles trying to find an answer and getting nowhere fast.

Little bit of background for explanation... Our end users upload data into an online portal which is then processed and creates various output reports.. some PDF and some Excel. Most of the Excel reports contain macros. The end users then download the output reports from the same online portal.

We have recently hit a snag that all files originating from the internet are automatically blocked in Excel 365.

My understanding is that we need to obtain a digital certificate, then digitally sign the workbooks, and then our end users can set us up as a Trusted Publisher.

My issue is that there are thousands of output reports generated and can't all be signed manually, but I can't quite figure out how to apply the digital certificates via VBA as part of the report creation process.

Any help would be greatly appreciated.

 

[6StringJazzer]

It appears that you cannot apply a digital signature via VBA, but want to explore why you think this is necessary.

So your users upload data to an online portal, which then does some black-box process that produces, among other things, macro-enabled Excel files.

all files originating from the internet are automatically blocked in Excel 365

What does "blocked" mean? The file cannot be downloaded? The file cannot be edited? Macros in the file cannot be run? Automatically blocked by what? Is this company policy? If not, then there are security settings in Excel that will allow macros in downloaded files to be run after the user is prompted to confirm that's what they want to happen.

Also note that signing a workbook is a different process than signing a VBA Project. From your description it is not clear which one of these you need, or possibly both.

In the research I did for your question I have found that there are no library calls to allow you to do this built into VBA. Doing so would create a serious security vulnerability because it would give malicious code an opportunity to change the code and still retain the original signature. I have found anecdotes by one developer that he was able to hack together a solution for this but was unable to post the code due to his company's IP rights. I have found a German company with a third-party product that purports to do this.

I think a more direct route to solving your problem would looking at why the restrictions exist, how they are implemented, and work with your company's IT security folks to allow an exception or workaround. Make sure management understands the value of these reports and is on board with what you want to do. In some cases Cybersecurity will allow something if upper management signs off on the risk.

 

[becci.gott]

What does "blocked" mean? The file cannot be downloaded? The file cannot be edited? Macros in the file cannot be run? Automatically blocked by what? Is this company policy? If not, then there are security settings in Excel that will allow macros in downloaded files to be run after the user is prompted to confirm that's what they want to happen.

There has been a recent update that means that all macros are blocked when the file originates from the interent. I have found a couple of ways around it locally, but none that feel like best practice.

1) the end users can look at the properties of an individual file in File Explorer and click the "unblock" checkbox.
2) the end users can create a trusted location in Excel Trust Center Settings, which will open any files from that folder regardless of the "originated from the internet" markers.

Both seem like they are either very time consuming for the end user, or have horrible security risks.

Also note that signing a workbook is a different process than signing a VBA Project. From your description it is not clear which one of these you need, or possibly both.

I've got to admit, I'm not really sure if it is either or both as I am just in the process of trying to find a solution to the problem - it isn't an issue that we have found until recently.

In the research I did for your question I have found that there are no library calls to allow you to do this built into VBA. Doing so would create a serious security vulnerability because it would give malicious code an opportunity to change the code and still retain the original signature. I have found anecdotes by one developer that he was able to hack together a solution for this but was unable to post the code due to his company's IP rights. I have found a German company with a third-party product that purports to do this.

This is pretty much where I am also at with research at the moment, which is why I have posted here for further assistance.

I think a more direct route to solving your problem would looking at why the restrictions exist, how they are implemented, and work with your company's IT security folks to allow an exception or workaround. Make sure management understands the value of these reports and is on board with what you want to do. In some cases Cybersecurity will allow something if upper management signs off on the risk.

This isn't really a possible solution unfortunately.

 

[6StringJazzer]

This is pretty much where I am also at with research at the moment, which is why I have posted here for further assistance.

Would have saved me the trouble if you had reported your own research in your original post.

 

[becci.gott]

Would have saved me the trouble if you had reported your own research in your original post.

My apologies.. I thought "I've been going around in circles" suggested that I had already done some research.

 

[becci.gott]

I found this on the microsoft site, but when I try to run it, I get a VBA error "Run-Time Error '91': Object variable or With block variable not set" when it hits
objSignature = objSignatureSet.AddNonVisibleSignature(varSigProviderID)

Function CreateSignature(ByVal varSigProviderID As Variant) As Signature
Dim objSignatureSet As SignatureSet
Dim objSignature As Signature
 
objSignature = objSignatureSet.AddNonVisibleSignature(varSigProviderID)
CreateSignature = objSignature
 
End Function

If I use

activeworkbook.signatures.AddNonVisibleSignature(varSigProviderID)

it opens the digital signature dialogue box - so still needs a degree of manual intervention.