Security Tip: Learn How To Protect Your Code

VBA code stored in worksheets (document modules) can be read from workbooks with unprotected structure (Review > Protect Workbook), even if the VBA project is password locked or unviewable. This hack can be demonstrated with the following simple steps:

• Store event code in a worksheet
• Lock the VBA project with a password (VBE > Tools > VBAProject Properties > Protection)
• Copy the worksheet with the event code to a new workbook
  
  

The new workbook has its own VBA project, which is unlocked at the time of creation. Unfortunately, the properties of the source VBA project, including its password, cannot be transferred to the target workbook. Thus, the code embedded in the newly copied worksheet will be visible, even if the source VBA project was password locked or custom protected as unviewable or Unviewable+!

The workaround for this problem involves 2 steps:

• Protect the workbook's structure:
  File > Info > Protect Workbook > Protect Workbook Structure or
  Review > Protect Workbook > [Protect workbook for structure] to prevent worksheet copying.
  
  
• Include only minimal code in the worksheets. Place the bulk of any such code in a standard module and call on it from the worksheet.

How to protect Workbook Structure

Move/Copy button shown disabled when
Workbook Strucutre is protected

'USE:
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     Call UserKeyPress(Target) 'procedure stored in a standard module
End Sub
 
'INSTEAD OF...
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
    On Error Resume Next
   
    Application.EnableEvents = False
    
    'Condition added to prevent further moves after game is won - PB
    If Not ThisWorkbook.Sheets("Game").Shapes("YouWin").Visible Then
        If Not Intersect(Target, [key.left]) Is Nothing Then LeftArrowPressed
        If Not Intersect(Target, [key.right]) Is Nothing Then RightArrowPressed
        If Not Intersect(Target, [key.up]) Is Nothing Then UpArrowPressed
        If Not Intersect(Target, [key.down]) Is Nothing Then DownArrowPressed
    End If
    ThisWorkbook.Sheets("Game").Range("Active").Select
    
    Application.EnableEvents = True
  
End Sub

A second alternative is to use a Class Module with Events, instead of using event code in a document (sheet) module.

---

Protect VBA Code With Unviewable+

What if you didn’t need to worry about others modifying or even stealing your code? With the Unviewable+ VBA project, this thought is now a reality!

Clicjk here to learn about Unviewable+ VBA

---

How To Copy Worksheets Without Embedded Code

There are times when worksheets with VBA code may have to be copied (manually or by VBA) and saved to a new workbook:

Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs ...

An error or unexpected results could occur, if the event code is triggered in the new workbook, besides the fact that the code would be readable without a VBE password, as discussed earlier.

Here are several options, which could be used to exclude the VBA event code from being included in the new workbook:

• Save new workbook in a macro-free format, when using Excel 2007 or later version (xlOpenXMLWorkbook, see XlFileFormat Enumeration)
  
  
• Copy the data range (not the source sheet) to a new macro-free (document module) worksheet
  
  
• Delete all worksheet event code in the destination worksheet using the VBA extensibility library. 
  The function shown below deletes code inside Excel document modules:
  
  

Option Explicit
 
Function DeleteEventVBACode(sDocument As String) As Long
     
    ' Add reference to VBA Extensibility library, if early binding is used
    ' ThisWorkbook.oVBProjectect.References.AddFromGuid _
    '                                    GUID:="{0002E157-0000-0000-C000-000000000046}", _
    '                                    Major:=5,Minor:=3
    Const vbext_pp_none = 0
    Const vbext_ct_Document = 100
 
    Dim oVBProject As Object
    Dim oVBComponent As Object
    Dim oCodeModule As Object
    Dim lLineCount As Long
    
    On Error GoTo ERR_HANDLER
     
    Set oVBProject = ActiveWorkbook.oVBProjectect
 
    If oVBProject.Protection = vbext_pp_none Then
         
        For Each oVBComponent In oVBProject.oVBComponentonents
         
            If oVBComponent.Type = vbext_ct_Document And _
               oVBComponent.Name = sDocument Then
                 
                Set oCodeModule = oVBComponent.oCodeModuleule
                With oCodeModule
                    lLineCount = .CountOfLines
                    .DeleteLines 1, lLineCount
                End With
            End If
        Next oVBComponent
         
        DeleteEventVBACode = lLineCount
    Else
        DeleteEventVBACode = 0
    End If
Exit Function

ERR_HANDLER:
    DeleteEventVBACode = -1
End Function

---

comments powered by Disqus

---

Home  »   Articles  »   Security tip: Sheet VBA code can be read, even if VBE is locked