Sheets added by VBA invalidates digitally signed VBA project

G

Guest

version: Excel 2003 Pro.

Changing a VBA project invalidates the digital signature if it's signed.
How can VBA code add/remove sheets without invalidating the signature?
There's another thread in this group ("Digital signature being dropped")
dated 1/6/2005 about the same problem, but with no reply.

The only way that works is an Excel add-in. I don't know why that doesn't
invalidate the signature. I don't want to use an add-in because my code is
specific to this 1 particular workbook, not *every* Excel workbook.

Keep in mind, to test this you have to try it on a computer that doesn't
have the signature's private key, since saving the workbook on the computer
with the private key simply re-signs the VBA project when it becomes invalid.

Please help. Thanks.
 
D

Dick Kusleika

Dean

I've had that same problem but it wasn't related to adding or removing
sheets. Actually, I never did determine what the real cause was, but it
happend to a lot of similar workbooks at the same time.

By putting your code in add-in, you lose some of the portability of the
workbook, i.e., you have to distribute two workbooks instead of one. But
you gain in that you elimate this problem. So that's the trade off, and I
decided, for me, to put the code in an add-in even if it only related to one
workbook.
 
G

Guest

Thanks Dick,

My workbook has a custom menu I had at the top before the "Help" menu.
Wouldn't an add-in put this menu up there for *every* workbook opened? I
guess I could load/un-load the add-in every time the workbook is
opened/closed, but I've wondered if doing that so often would corrupt the
registry or something... since I don't know what MS does when an add-in is
loaded/un-loaded. I've been researching to see if it's possible to associate
an add-in with a workbook, not with all of Excel, but can't find any info.

Thanks again,
Dean
 
D

Dick Kusleika

Dean

Use a custom property in the user workbook. Have your add-in check for this
property using an Application_WorkbookOpen event and if it exists, add the
menu item. Then use the Application_BeforeClose (from the add-in) to delete
the menu when the workbook is closed. The add-in will be running all the
time, but it will only show it's menu in the proper context.
 
G

Guest

Dick

That would definitely do the trick. I went ahead and put in a MSDN support
ticket, since I feel this is a bug (if it is they usually won't use 1 of your
incidents) Trusted and Signed macro code should be able to add/remove
sheets. I sent the sample workbook that reproduces the issue to the support
guy a few minutes ago. Thanks for your response. I'll post Microsoft's
response here as soon as I hear back.

-Dean
 
G

Guest

Thanks Walt,

I had actually found this thread when researching earlier, but I decided to
call MS anyway. I'm hoping that since I've verified that none of their
documentation mentions the requirement of the code being an add-in, then maybe
they'll realize they need to better document it or fix this with a service
pack...yes I know...good luck. They're supposed to respond to me by Tuesday,
so I'll post their response. I did try moving the code to a VB6 DLL, with
the workbook having a tiny bit of code that calls the DLL, but it was the
same problem. I'm thinking that the reason the add-works is that it's
*totally* separate from the workbook, whereas my DLL is still linked in by a
project reference. You'd think this would be the other way around, and that
an outside program would invalidate the certificate, not code *inside* the
workbook.

Dean
 
B

Bob Phillips

Dean J. said:
I'm thinking that the reason the add-works is that it's
*totally* separate from the workbook, whereas my DLL is still linked in by a
project reference.

You could test that by referencing the add-in in the project.
 
G

Guest

I added a reference to the Add-In and created a wrapper procedure that called
the Add-In to delete a sheet. I tried the workbook on the client PC (without
the private key certificate) and it worked fine...meaning it deleted the
sheet and saved the workbook without invalidating the certificate (public
key).

Actually, now that I think about it, the point of a macro virus is to be
attached to the workbook and do it's harm when the workbook opens. So it
would make sense that an add-in (something you usually have to install
yourself) wouldn't fall into the macro virus category, and thus Excel would
allow it to change the VBA project (what happens when adding/deleting a
sheet) without invalidating the certificate.

My personal opinion is that if you have imported the publisher's public
certificate and selected the "Trust all macros from this publisher" checkbox
when opening the workbook, then macros that change the VBA project should be
OK and not require the re-signing of the project. But I guess when the
programmer signs the VBA project, it uses the current object list (number of
sheets, etc.) in the hash, and once that changes it must be re-signed, which
can't be done on the client because they don't have the private key
certificate. Essentially a catch-22.

Dean
 
G

Guest

Hi Dean,

Are you opening the file via VBA code in the add-in (This should take
advantage of msoAutomationSecurityLow)? Otherwise, the result you describe
in your 1st paragraph seems inconsistent with all else I've learned.

Can you confirm that the signature is still valid in the modified file with
the sheet deleted (Maybe remove the add-in, then open the modified file and
expect the error when the add-in is not found by the functioning VBA code)?

Best Regards,
Walt
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top