Need help to build a solid security system for a workbook

garmont · Mar 15, 2006

I am developing an application that i want to keep as secure as possible from piracy. I've built in user id and password. i utilize macros wherever possible, but have relied on a couple of workbook events to handle a lot of the security features. i just found a couple of ways to defeat these events and open the workbook to saves without locking away all the good stuff....
does anyone know how to really lock up a workbook and keep it from being copied when sheets are supposed to get hidden upon closing, along with other security features like time checks, id's, and lockout 'flags'???
thanks,
garmont

NdNoviceHlp · Mar 18, 2006

Maybe not the next decade? I liked Tom's idea and I have a link for you. Have a nice day. Dave
http://www.vbaexpress.com/forum/showthread.php?t=7508

erik.van.geit · Mar 18, 2006

Maybe not the next decade? I liked Tom's idea and I have a link for you. Have a nice day. Dave
http://www.vbaexpress.com/forum/showthread.php?t=7508

OK next week

thanks
hoping this will also be useful for garmont (original poster)

Legacy 98055 · Mar 18, 2006

The trick is not to only integrate the workbook into your exe, but to also keep all of your code out of the workbook and in the exe.

garmont · Mar 19, 2006

You all have left me in the dust, but have given me a lot of links and good ideas to pursue. Many thanks for all your responses.
Any suggestions on how best to learn "up to" what you're dealing with are most welcome.

garmont · Mar 23, 2006

After hours reviewing past posts (about my totally unoriginal excel security question) I think i get it....
I'd like to be certain about the same password issues with addin's: I would assume that the encrypted file open password (available in the tools menu under SaveAs...) is susceptible to the same near-instantaneous pw breaking algorithms that open the workbook and VBA passwords.
Am I correct?

My application can use all of its macros from an addin or other linked resource. I am still looking for ways to use excel workbook for the user interface, not too worried about its security since no features will work without the macros. So if i can do the rest in an addin that can be encrypted, i might be ok.

Does anyone have experience with Visual Baler sw re: these security problems?

Legacy 98055 · Mar 23, 2006

Why don't you simply compile your code into a DLL?

Legacy 98055 · Mar 25, 2006

In answer to PM's by garmont and shajueasow and also the surprise I had of so little information on how to place VBA code into an ActiveX dll and then use it from VBA. This is a very oversimplified example because there are just too many little points to try and cover. If you guys want to post your code that you wish to place in a dll, I'll try and help you more specifically. I use several custom activex dll's in place of Personal.xls for several reasons. The code runs much faster, is only loaded as needed, and contains functionality available in VB that is not available in VBA. Especially forms development without all of the API calls. In many cases, I have found that creating a COM add-in is too bloated for what I needed and, therefore, simply placed the code into an undressed dll.

This is the most basic example to show you how easy it is. Well, how easy it can be until your try to distribute it.

Using this method, you will not need to register or install anything. The only prerequisite on the target machines would be the VB runtime and the dll file remaining in the same or relative path to the workbook.

<table border="1" bgcolor="White"><caption ALIGN=left>Example VBA Code:</caption><tr><td> Option Explicit

 '****************
 ' Main Function *
 '****************

 Function SpellNumber(ByVal MyNumber)
 Dim Dollars, Cents, Temp
 Dim DecimalPlace, Count

 ReDim Place(9) As String
 Place(2) = " Thousand "
 Place(3) = " Million "
 Place(4) = " Billion "
 Place(5) = " Trillion "

 ' String representation of amount.
 MyNumber = Trim(Str(MyNumber))

 ' Position of decimal place 0 if none.
 DecimalPlace = InStr(MyNumber, ".")
 ' Convert cents and set MyNumber to dollar amount.
 If DecimalPlace > 0 Then
 Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
 "00", 2))
 MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
 End If

 Count = 1
 Do While MyNumber <> ""
 Temp = GetHundreds(Right(MyNumber, 3))
 If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
 If Len(MyNumber) > 3 Then
 MyNumber = Left(MyNumber, Len(MyNumber) - 3)
 Else
 MyNumber = ""
 End If
 Count = Count + 1
 Loop

 Select Case Dollars
 Case ""
 Dollars = "No Dollars"
 Case "One"
 Dollars = "One Dollar"
 Case Else
 Dollars = Dollars & " Dollars"
 End Select

 Select Case Cents
 Case ""
 Cents = " and No Cents"
 Case "One"
 Cents = " and One Cent"
 Case Else
 Cents = " and " & Cents & " Cents"
 End Select

 SpellNumber = Dollars & Cents
 End Function



 '*******************************************
 ' Converts a number from 100-999 into text *
 '*******************************************

 Private Function GetHundreds(ByVal MyNumber)
 Dim Result As String

 If Val(MyNumber) = 0 Then Exit Function
 MyNumber = Right("000" & MyNumber, 3)

 ' Convert the hundreds place.
 If Mid(MyNumber, 1, 1) <> "0" Then
 Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
 End If

 ' Convert the tens and ones place.
 If Mid(MyNumber, 2, 1) <> "0" Then
 Result = Result & GetTens(Mid(MyNumber, 2))
 Else
 Result = Result & GetDigit(Mid(MyNumber, 3))
 End If

 GetHundreds = Result
 End Function

 '*********************************************
 ' Converts a number from 10 to 99 into text. *
 '*********************************************

 Private Function GetTens(TensText)
 Dim Result As String

 Result = "" ' Null out the temporary function value.
 If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
 Select Case Val(TensText)
 Case 10: Result = "Ten"
 Case 11: Result = "Eleven"
 Case 12: Result = "Twelve"
 Case 13: Result = "Thirteen"
 Case 14: Result = "Fourteen"
 Case 15: Result = "Fifteen"
 Case 16: Result = "Sixteen"
 Case 17: Result = "Seventeen"
 Case 18: Result = "Eighteen"
 Case 19: Result = "Nineteen"
 Case Else
 End Select
 Else ' If value between 20-99...
 Select Case Val(Left(TensText, 1))
 Case 2: Result = "Twenty "
 Case 3: Result = "Thirty "
 Case 4: Result = "Forty "
 Case 5: Result = "Fifty "
 Case 6: Result = "Sixty "
 Case 7: Result = "Seventy "
 Case 8: Result = "Eighty "
 Case 9: Result = "Ninety "
 Case Else
 End Select
 Result = Result & GetDigit _
 (Right(TensText, 1)) ' Retrieve ones place.
 End If
 GetTens = Result
 End Function


 '*******************************************
 ' Converts a number from 1 to 9 into text. *
 '*******************************************

 Private Function GetDigit(Digit)
 Select Case Val(Digit)
 Case 1: GetDigit = "One"
 Case 2: GetDigit = "Two"
 Case 3: GetDigit = "Three"
 Case 4: GetDigit = "Four"
 Case 5: GetDigit = "Five"
 Case 6: GetDigit = "Six"
 Case 7: GetDigit = "Seven"
 Case 8: GetDigit = "Eight"
 Case 9: GetDigit = "Nine"
 Case Else: GetDigit = ""
 End Select
 End Function

</td></tr></table>

Take the above function from MS. This is a UDF that you would place in a standard module within a workbook. You would then call it from some cell as such:

=SpellNumber(A1)

What if you wanted a bit more security than is offered by VBA? You could place the code into a dll.

From VB. New project, ActiveX Dll. Name the class "Functions". Name the project "MyFunctions". Paste the above UDF into your class module named "Functions". Still from VB... File menu, Make "MyFunctions.dll..", save to the same folder as your workbook. This is not neccesary but for ease in this example.

Now from VBA. Tools, references, Browse. Locate the dll you just created and set a reference to it.

Delete the SpellNumber UDF in the workbook. Replace it with this code:

<table border="1" bgcolor="White"><caption ALIGN=left>Example VBA Code:</caption><tr><td> Function SpellNumber(ByVal MyNumber)
 Dim mf As New MyFunctions.Functions
 SpellNumber = mf.SpellNumber(MyNumber)
 End Function
</td></tr></table>

Now your code is hidden and not as accessible, will run faster in most cases, and the workbook will generally behave better without the code bloat. There are other things to consider as well. Good and bad.

Briefly. Distributing is easy in this case as long as Excel can find the dll in the expected path. Excel will register the file for you.

To debug these types of projects, set a reference to the VBProjectfile, "MyFunctions.vbp", run it, and then call it from Excel. You can step through the code between VBA and VB just as you would in VBA alone. Note that both devlopement evironments need to be open at the same time. There are tricks and troubles but you can just ask if you have any problems.

Also, note that we did not use anything in Excel's object model in the above example. Most of your code will doubtless use many objects within Excel's and Office's libraries. Converting your VBA to VB code is usually as simple as adding a reference to Excel and then fully qualifying your objects. You will need to distribute the lib and exp files along with your dll as well.

ActiveX Dll Example.zip

The download example will extract all of the files into several folders including the vb project files...

Edit: If you do need to register your dll...

Hi Gary.

No. Your users do not need VB installed. They will need the VB6 runtime files, but very, very rare is the windows system that does not have these files. In fact, the only way a computer that is updated periodically would not have these files is if somebody purposely did not want them there. Make sure the reference to your dll is valid on the target PC. If we need to register the dll you can add some simple code to register it. We should not have to register the file, but I always do register the dll's and place them in the system folder when I distribute an application. Here is some sample code that will register the dll if there is a certain error...

Assumes an activex dll named "MyFunctions.dll" located in the workbook's path. An installation procedure might be a simple VB Script file that would move the dll to the target system's system folder, register it, and update the regerence in your workbook. Very easy. If you need any help, just reply...

<table border="1" bgcolor="White"><caption ALIGN=left>Example VBA Code:</caption><tr><td> Function SpellNumber(ByVal MyNumber)
On Error GoTo Err_SpellNumber
Dim mf As New MyFunctions.Functions
Dim RegServerFailed As Boolean

SpellNumber = mf.SpellNumber(MyNumber)

Exit Function
Err_SpellNumber:
If Err.Number = 429 Then
If RegServerFailed Then Exit Function
Call RegServer(Chr(34) & ThisWorkbook.Path & "\MyFunctions.dll")
RegServerFailed = True
Resume
End If
End Function

Private Sub RegServer(DllPath As String)
Call Shell("REGSVR32.EXE /s " & Chr(34) & ThisWorkbook.Path & "\MyFunctions.dll")
End Sub

</td></tr></table>

You may simply want to register the dll on workbook open.

<table border="1" bgcolor="White"><caption ALIGN=left>Example VBA Code:</caption><tr><td> Private Sub Workbook_Open()
Call Shell("REGSVR32.EXE /s " & Chr(34) & ThisWorkbook.Path & "\MyFunctions.dll")
End Sub
</td></tr></table>

Need help to build a solid security system for a workbook

garmont

New Member

NdNoviceHlp

Well-known Member

Excel Facts

erik.van.geit

MrExcel MVP

Legacy 98055

Guest

garmont

New Member

garmont

New Member

Legacy 98055

Guest

Legacy 98055

Guest

Similar threads

Forum statistics

Share this page

Need help to build a solid security system for a workbook

garmont

New Member

NdNoviceHlp

Well-known Member

Excel Facts

erik.van.geit

MrExcel MVP

Legacy 98055

Guest

garmont

New Member

garmont

New Member

Legacy 98055

Guest

Legacy 98055

Guest

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock