Error Handling in locked code

[Graham Charlton]

I am creating workbook containing VBA procedures which will be distributed to many users on different sites. I have password protected the code to prevent people seeing/altering/stealing it but this is causing me problems supporting the workbook. I have covered foreseeable errors with appropriate error trapping but occasionally errors occur related to the users local environment etc that I cannot predict when writing the code.

I there any way that when an error occurs that the offending row of code can be displayed in a message box so that I can identify the cause of the error without having to give the user access to my code.

Thanks
Graham

 

[Fluff]

You can put line numbers on the code like

Sub Graham_Charlton()
         Dim Cl As Range, Fnd As Range
         Dim Ky As Variant
         Dim Ws1 As Worksheet, Ws2 As Worksheet
         
         On Error GoTo oops
10       Set Ws1 = Sheets("Data")
20       Set Ws2 = Sheets("Sheet2")
30       With CreateObject("Scripting.dictionary")
40          For Each Cl In Ws1.Range("E2", Ws1.Range("E" & Rows.Count).End(xlUp))
50             If Not .Exists(Cl.Value) Then
60                .Add Cl.Value, Cl.Offset(, -4).Resize(, 3)
70             Else
80                Set .item(Cl.Value) = Union(.item(Cl.Value), Cl.Offset(, -4).Resize(, 3))
90             End If
100         Next Cl
110         For Each Ky In .Keys
120            Set Fnd = Ws2.Range("4:4").Find(Ky, , , xlWhole, , , False, , False)
130            If Not Fnd Is Nothing Then
140               .item(Ky).Copy Fnd.End(xlDown).Offset(1)
150            End If
160         Next Ky
170      End With
      Exit Sub
oops:
      MsgBox "an error occurred on line " & Erl
End Sub

and use Erl to report what line the error occurred on

 

[Graham Charlton]

Perfect! If only I had known that before writing all my code... could take me a while to number all the rows.

Thanks for your help

 

[jmacleary]

Hi Graham

Not the answer, but a hint regarding numbering. Create a blank spreadsheet, put 10,20 in A1 and A2, drag down loads (i.e. create the numbers 10, 20, 30 etc.). Then copy each subroutines code into column B. In column C put (for C1) =A1&" "&B1 and drag down. Then copy column C back over your code.

 

[Fluff]

You're welcome & thanks for the feedback.

There are add-ins available that will number the code for you.
I use an old version of MZ-Tools