The Last Excel Weakness

If I can only think of 3 weaknesses in Excel that’s pretty good I think …. I love Excel, but I continue to be disappointed every day that being an expert in the product gets lower and lower in value.  Frankly to do Excel right takes immense skill, lots of hard work, and not a little native capability (there seems to be a type of person who does this stuff well) and if companies don’t see that as a worthy professional pursuit then we are a dying breed I guess.

Given all that though, while trying to prove that Excel is a capable application platform (contrary to what some who visit here believe I’m afraid ….)  my biggest beef of all is the fact that it is not possible to programmatically remove or change the password on a VBA Project. 

In the absence of a compiler the only way I can protect my VBA source code from tampering is to place a password on the project.  Tragically for the promotion of Excel solutions there is no way to affect this password programmatically in VBA (other than a kludgy Sendkeys solution that never seems to work).  This makes maintenance nearly impossible.

I understand that a “REAL” Excel developer puts their Code in an Add-In and ships that as a separate file.  But from my experience many users (and IT professionals) have trouble understanding the idea that two files are necessary.  I want my code and spreadsheet all in one file … period.

I assume that one of you out there has developed a clever way to pull this off, but once again it is my belief that this should be basic functionality of the program and once again I consider this a weakness of Excel that I would like to see fixed.

Otherwise Excel is perfect in every way🙂 ….

Dick

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

8 Responses to The Last Excel Weakness

  1. Harlan Grove says:

    There’s always XLM (said with a grin).

    Be thankful you never had to use 32-bit versions of Lotus 1-2-3. The LotusScript environment couldn’t be scripted or protected at all.

    If IT/IS departments can live with separate .EXE, .DLL and even .OCX files, why not separate .XLA files in addition to the .XLT/.XLS files? The flip side of either scripted password [un]protect or drop-in replacement of entire VBA projects would be a gaping huge security hole. I think IT/IS depts would like that even less than causing some inconvenience to outside Excel developers.

    As for these being Excel’s only weaknesses, there are still a lot of very long standing issues with several worksheet functions. There’s no good reason for MOD’s ridiculously limited domain (my personal biggest gripe with Excel because there’s really no excuse for its continued existence). Other than breaking old code (which the new grid size did with Cells.Count), there’s no good reason it’s impossible to open multiple files with the same base filename in the same instance at the same time. There’s the lack of bundled SQL.REQUEST add-in after Excel 2002. There’s still no way to pass 3D references as arguments to VBA procedures.
    Finally, there are all the things Lotus got right in 1-2-3 that Microsoft still hasn’t seen fit to implement in Excel, but those are omissions rather than implementation errors.

  2. Dick Moffat says:

    Harlan:

    “Finally, there are all the things Lotus got right in 1-2-3 that Microsoft still hasn’t seen fit to implement in Excel, but those are omissions rather than implementation errors.” That’s funny !!! I aid that for SO LONG that I gave up saying it because nobody cared🙂 ….

    Lotus’s 3D was REAL 3D (although I like the Excel idea of every Worksheet having it’s own Print Area and possibility to have their own private versions of names)!!

    I agree that IT departments SHOULD be able to handle a separate companion file but I don’t find that USERS are very good at stuff like that – one file – no probs.

    I’m not exactly sure why being able to Programatically change a Password would be a security hole (??). I’m not talking about a drop-in Project but rather the ability to edit an existing project ONCE it is proven the code knows th password. Only people who won’t go outside for fear of getting hit by a Meteorite would worry about that – I don’t mean you of course Harlan …😉 Duck !!!

    Great points otherwise.

    Dick

  3. Harlan Grove says:

    Scripted password unlocking means it’d be possible for malware to run an excel instance in background to attach workbook passwords by iterating through all possibilities. Maybe a remote threat, but still a threat.

    Begs the question why protect the VBA Project. If your concern is securing your code, VBA Project passwords are only slightly more work to defeat than worksheet protection passwords. If the reason is to prevent users from altering it, a different approach would be to use a VBA function to calculate a CRC32 checksum of the VBA project and compare the result to the stored value. If they differ, the workbook throws a critical error.

  4. Dick Moffat says:

    Yes – I call this kind of security as “Good Enough” Security. It’s designed to protect my app against some malicious a-hole or just plain a-hole. The theory is that if the person has enough skills to figure out how to break this password then the theory is that they must have been developing for quite a while and JUST might have developed the wisdom to understand the consequences of their actions….. Otherwise I will hear about it when the application breaks and then I’ll find out that something happened and act accordingly.

    I know this isn’t a total solution but a password on the code will keep prying eyes out of where they shouldn’t be …

    Dick

  5. Making users understand the add-in/template way of doing things is the most objectionable part of a new client. Yet, I do it every time because the benefits are so great. Particularly if I do another project for them.

  6. Dick Moffat says:

    Too true – maybe you’re right on this. I guess I just have less faith in “users” ability to f**k things up than you do🙂.

    Dick

  7. sam says:

    Harlan
    From 2007 onwards… breaking the VBA password is pretty tough. The “Standard” HexEditor hack wont work.
    The commercial apps resort to a Brute force…

    • Pixie says:

      If you cannot get into a password protected excel 2007 file with a hex editor, then please can someone send me the solution, as I have a file here with a lost password and no solution to open it.
      Any help would be appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s