Ricochet is the best place on the internet to discuss the issues of the day, either through commenting on posts or writing your own for our active and dynamic community in a fully moderated environment. In addition, the Ricochet Audio Network offers over 50 original podcasts with new episodes released every day.
This all starts with Mike Mahoney. Mahoney was the Excel guy, two Excel guys ago. To his credit, he wrote pretty good stuff. His macros don’t break often. Everything would have been cool except he was writing these things when Excel 2003 was the hot new thing. Mahoney was also excellent about locking things down from accidental damage. Trouble is, nobody remembers his passwords. Breaking through his protections makes an excellent case study on how to secure and how to bypass the security on an excel workbook.
The guy on the floor gives me a call. I don’t know the guy; IT gave him my name. They don’t support random excel macros, so I get these calls. His macro works fine on most computers, but it errors out right away on one. I’ve never seen this particular spreadsheet before, but I’ve got a pretty good idea why. His new computer is 64 bit, which messes with some function calls.
Okay, let’s go in and fix it. I make a local copy. Don’t want to mess with the one people are using; leads to trouble. Open it up. Before it even loads it’s asking me for a password. I don’t have one. I’ll open it as read-only for now; I’ll show you how to fix that later. For now, this is what I see:
What’s going on here? Mahoney is trying to make sure nobody’s using local copies. Whenever someone opens up the workbook, it looks for its location on the network. If it isn’t in a specified folder, lock the sucker down so that nobody can use it. This seriously helps when you’ve got to make sure everyone’s using the same version of a file (fix a bug once, fix it every time. Update control limits once, don’t worry that someone’s checking pass/fail against an outdated standard). It’s annoying when you’ve got to mess with the file yourself.
Okay, let’s bypass this. He’s got one tab (“Default”) showing now. I know there’s more to this workbook as it’s supposed to do more things than taunt the hapless user. If you try to unhide the tab though, you don’t see any.
Excel actually has three levels of visibility for worksheets. You’ve got visible, hidden, and very hidden. Hiding a worksheet is something you do when you want to get it out of your face for a while. Making something very hidden means that people can’t even see that it’s there. Unless they’re going through the visual basic project.
To show all hidden and very hidden sheets, this is the code you use:
‘show all hidden worksheets in the active workbook
Dim wSheet as worksheet
For each wSheet in ActiveWorkbook.Worksheets
wSheet.visible = xlSheetVisible
If you’re trying to hide sheets use xlSheetHidden and xlSheetVeryHidden instead. If you try to hide every sheet you’ll get an error when you go to hide the last one. A workbook has to have at least one sheet visible.
Okay, all the sheets are visible. Let’s get rid of that pesky open-as-read-only thing. You can do that from the Save As command.
Wait, what? Mahoney has set up another event. This one triggers before you save the workbook. It demands a password, and if you can’t supply then it cancels the saving. If I can’t get past this I can’t ever save changes and hence I can’t fix the problem. I still don’t have any passwords.
An “event” is a trigger that waits for something specific to happens. When that something happens the event handler comes in and says “hold on a minute; I gotta do something.” Mahoney’s password box triggers before a workbook is saved. That thing where it hides all the spreadsheets? That’s an event that triggers when the workbook is opened.
Here’s how you shut off event triggers:
Application.EnableEvents = False
You can do that from a macro, or from the immediate window. Once you’ve done it, you can get into the usual Save As dialogue box. It needs to be Save As because we opened this thing as Read Only a dozen paragraphs ago.
While you’re here, note the Save As Options menu (yeah, I never noticed it either before it was pointed out to me). In general options, you can put in a new password. You don’t even have to know the old one. Great! Save your file under another name, close out, and open your new file to check it.
Good news, it’ll let you make changes. Bad news. We’re back to that “Opened from the wrong location” thing. Events turned back on when we opened it again, and it found it’s in the wrong folder. Okay, no matter. We know how to get around that now.
One side note before we go on. Suppose we needed to make a change to this worksheet. Can’t do it; it’s protected. To unprotect a worksheet, you guessed it, you need a password that I don’t have. To get that you’ve got two options.
First option, you go into the macro and see if you can’t find the password. If your macro ever writes new data to a protected cell then it’s got to unprotect it first, write the data, and protect it again. You’ll find the password supplied somewhere in the code. Supposing you don’t have that though. Supposing that the visual basic project is locked out, or it never unprotects that worksheet. In that case, you can call upon a classic password cracker:
Neat, huh? You don’t need the actual password, you just need one that works. For reasons that I’m not clear on you can generate a password that also unlocks it from a number of possible solutions. It’s a small enough number that you (well, your computer) can try them one by one in a reasonable amount of time. Last time I did it the password worked out to something like AAABBBAAABA; . It’s quicker to find if you know the first eleven characters have to be “A” or “B”.
Alright, now here’s the big one. Mahoney locked down the visual basic project. If I can get into the visual basic project then I’ve got access to everything. If I can’t, then I can’t fix this guy’s problem. Man, that Mahoney fellow owes me a beer.
To break past the Visual Basic security you’ve got to mess with the underlying Windows system. There’s a way to do this that involves renaming the file as a .zip, opening it up, and messing with one of the xml files inside. I find this solution to be easier. Stick this procedure into another workbook. From there, and with your locked workbook open, you run the macro. In the simplest sense that turns the key on the project being locked. It no longer is. If you save, close and open it up again it’ll be locked down again, and you still won’t know the password. From there you’ve got to go into the Visual Basic Project Properties and reset the password. Hopefully to something you will remember, if not pass on to the guy two down the line. Once you’re in the visual basic you’ve got everything. If anything else is hidden you can find it and break into it as necessary.
What then would I do to secure a workbook of my own? Supposing I wanted to lock something down to the point where an enterprising moron such as yours truly won’t get in and tinker with it. Here are the steps I would take:
- Disable the user’s ability to save changes to the workbook.
- Set the workbook to only run from the specified directory.
- Protect any worksheets they don’t explicitly need to change.
- Password protect the VBA project.
In short, I’d still go with the Mahoney method. The fact of the matter is I don’t know of a better way to secure your work while it’s still in Microsoft Excel. It takes time and effort to break through all those protections. It’s very hard to fight through that by accident. But I’ve got no way to stop a malicious moron from breaking into any old workbook. The lesson here ought to be not to trust your sensitive business logic to Excel. Somehow that never seems to be the lesson learned.
In the meantime though, I can give the newly repaired file back to the guy on the floor. Wait, what do you mean that fix didn’t work?