Excel Protection with VB (Excel2000 VB6.0)

Topic

New Reply

One of the concerns many of us Excel developers have is how can we protect our applications.

Both worksheet password protection and VBA protection are easily bypassed with code cracking mechanisms. I have put together a VB6.0 project which seems to get around this. I have coupled this with a simple spreadsheet containing 4-5 formulas and am attaching it to this post as a Beta Test version.

Anyone interested in testing it? If you are able to access the formulas, please let me know how you are able to accomplish it.

Thanks

Paul

Reply | Quote

Replies

Do you want to know how it’s accomplished publicly or privately? I have found one way so far.

Reply | Quote

I share your concerns, but I had no trouble finding the formula, and I’ve never done anything like that before. (I was a little concerned that I would never see my customized toolbars again! Thanks for having VB restore them.) –Sam

Reply | Quote

Rory, Sam & Jan,

Thanks for your assisstance.
Did you remove the protection before opening the spreadsheet or while it was open? If the spreadsheet was closed while you removed the protection, did you then open it as a standalone (from xl) or through the VB proj?

Paul

Reply | Quote

While the VB project was “running” the spreadsheet, I customized the toolbars and added a SaveAs. I did a SaveAs with a different filename, then exited everything via the task manager. Then, I came and went normally which restored my toolbars. Finally, I opened my SaveAs’ed spreadsheet and disabled macros, cracked the password with Erlandsen tools, removed worksheet protection, and looked at the formula. –Sam

Reply | Quote

In the above post, I was running Win XP/XL2K.

I now have Win NT/XL97 up. I ran your VB project and was able to unprotect the sheet without saving it. VB got hung-up on the FindWindow line:

Sub ShowForm3()
    'Get Windows Handle of Form
    hwnd = FindWindow(vbNullString, "Form3")
    'Now show the window
    ShowWindow hwnd, SW_MAXIMIZE
    
End Sub

Reply | Quote

Arggg! Now, you really got even. Since Excel won’t close normally, my toolbars are hosed!

Reply | Quote

Sam,
I’ve made some adjustments as per the attached file. Since you were able in version 1 to gain access through the customizing of toolbars, I have replaced the Standard Menu with my own CustomMenuBar.

With this arrangement, on my machine (Win2K, xl2K) I am no longer able to access View>>Toolbars>>Customize or to Rt click a popup to do the same. Hence, I cant get access to the SaveAs (which is key to your method). Maybe this is still accessible on other configurations.

BTW if anyone has problems restoring toolbars, use this code:

Sub RestoreToolbars()

Toolbars(“Standard”).Visible = True
Toolbars(“Formatting”).Visible = True
Toolbars(“Drawing”).Visible = True
Toolbars(“Visual Basic”).Visible = True

‘etc

End Sub

Thanks
Paul

Reply | Quote

Paul,
With your new version, I can still right-click on the toolbars and customize that way. Or go into the VBE via Alt-f11 and run a SaveAs from there. Incidentally, the way I did it originally, since your tamper message worked for me at any rate, was to open the workbook via your program, then use Task Manager to end your app, leaving Excel running. I could then do what I needed without your tamper message doing anything.
FWIW.
PS I just noticed that the workbook produced by the SaveAs did not have the formulae in it, unless I killed the app first.

Reply | Quote

FWIW…

As long as your application relies on Excel’s protection schemes and on disabling commandbars etcetera, a power user will find a way to circumvent that simply by using code.

I stronlgy suspect the only really reliable way to protect your stuff is NOT to use Excel .

A good alternative is to produce a COM addin and put all relevant/critical functions in there. The user will be able to see the functions in the formulas in Excel, but will have no clue what they do.

Reply | Quote

I’ll just echo Rory’s & Jan Karel’s comments. I don’t see how you can prevent me from cracking the VBA password and writing VBA code to SaveAs. Anything that has intellectual value has to be written as a COM AddIn. Which BTW is one of the things that I’m working on for a living. But, (and I wish I could convince my boss of this) converting a bunch of sloppy-written macros, toolbars, & userforms into a COM addin is a very difficult task. Now, to see if I can recover my toolbars! –Sam

Reply | Quote

Thanks guys

Can you direct me to some good sources for learning more about COM addins?

Also in there a way to disable “Alt F11”?
Paul

Reply | Quote

See if we can get this thread back on track. To learn about Com addins, for me, the most useful book was David Broctor’s Microsoft Office 2000 Visual Basic for Applications Fundamentals. I also use Excel 2002 VBA Programmers Reference and Microsoft Office XP Developer’s Guide. I bought the XP references even though I still just writing for XL2K because they had more information and it was easy to skip over the new features. However, don’t expect too much from these books. Any information on Com is hard to find. You learn by doing, crying, and keeping a positive attitude. HTH –Sam

Reply | Quote

>> converting a bunch of sloppy-written macros, toolbars, & userforms into a COM addin is a very difficult task.

Even converting very well written macros to a COM Addin is not a trivial task. Add .Net, Office XP PIAs and C# to
the mix and going back to law school begins to look pretty good.

Reply | Quote

XL 97 SR2:

Opened the sheet in two ways:

– just opened the workbook
result: no formulas

– with your application
My toolbars were all still there
used this macro to unprotect the sheet:

Sub PasswordBreaker()
‘Breaks worksheet password protection.
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer

On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) _
& Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox “One usable password is ” & Chr(i) & Chr(j) _
& Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) _
& Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub

Then formulas were freely available.

Reply | Quote

Some sort of brute force technique here, but not much brute or force.

How does it work? (If you don’t mind)

Reply | Quote

I stumbled accross that one some time ago.

It seems that XL does not store the entire password but has some sort of method of saving the offsets of the characters in relation to the first character entered.

You’l find the macro if you do a google search on this string:

Excel password lost unprotect sheet code

(very first hit in the list)

Reply | Quote

There is a much easier way to break protected sheets- but depends on your version of Excel. This worked for me using Excel 2000, but I’m not quite sure the version of Excel the worksheet came from- see this post in the Excel forum

Reply | Quote

Hi Geoff,

Interesting!

Reply | Quote

Got them.

Just unprotected the sheet and bingo.

The first one is:

=14+(SUM(B5:B6)^3)

Reply | Quote

(Edited by charlotte on 24-Feb-03 19:41. )

Well,, Obviously I have some more work to do.

I would be interested in hearing how you were able to see the formulas without the “Tamper” message closing everything up.

Thanks

Reply | Quote

Just received an ad today from KDCalc . Looks like they are the way to go for protecting cell formulas. However,VBA code is not supported. Here is part of the reply I got back from them. It looks very interesting.
[indent]

---

KDCalc is very effective at protecting proprietary cell formulas. At runtime
formulas are read-only and non-visible.

KDCalc does not support macros, VBA code, or UI controls at this time. That is
something that would have to be hand coded in your calling application. You
would develop a UI in VB.net or C#, bind the UI to the KDCalc Engine, and
recode the macros as functions that are triggered by UI buttons or menu
commands. We do plan to support user defined functions in a future release.

---

[/indent]

Reply | Quote

Hey, Thanks Sam, I will go to this site and see how it would apply.
Appreciate your effort to recall my initial requirement.

Reply | Quote

Thanks Sam,
I checked out the site. It appears they are headed in the direction I want but are only part way there.
Excel combines several large operations or functions
1. A calculation engine that runs in the background
2. A UI for user input and feedback of results
3. A programming platform via the VBE using VBA
My app uses all three. I appears that KDCalc presently uses only the 1st above.

The second (UI) must be made in VB.net or another platform. I currently use VB6 as a shell to access excel. This could be transferred to .net, but I have a need for the user to “see” at any one time a range of cells approx 30w X 100h and to be able to input into these cells. the way I currently do this is with several worksheets in which I “hide” all but the needed rows and columns. I use approx 15 sheets for the various screens the user needs. Macro buttons are used for navigation to each sheet. Once the user gets to Excel he doesn’t go back to the VB shell until Closing the app where my code performs some close-down procedures.

It appears that with KDCalc the user “stays with” their app continuously, except for a brief reaching out to Excel for calculation (similar to OLE automation). this would work for me if KDCalc supported some kind of grid control (of the above stated size) to display the results of the xl calcs, as well as for additional user input. I interpret their ad to mean that this type of grid control is not supported. Basically, what I need is a series of small grids for I/O that would keep a hacker from access to the formulas. I thought OLE automation from VB to xl would be the answer but was extremely slow and limited in functionality.

The 3rd function of xl could be transported to vb is macros were supported. Looks like this is planned for a future release.

Thanks
Paul

Reply | Quote