VBA - really no solution for effectively debug locked code?

7ohm7

New Member
Joined
Oct 31, 2019
Messages
9
Dear VBA experts and fans,

I created an excel system that is full of VBA code and we give it to the client - with the code locked for viewing. Of course, there was soon a request that the client encountered an error, so I put "bookmarks" into the whole code - I divided the code into parts (parts smaller than procedures) and at the beginning of each part is assigned a simple global string variable and this is logged in Excel sheet together with the error code in case of error, so I know which was the last part before the error.

But it's still not enough debug solution, as it would be in the case of unlocked code, where VBE nicely marks which line caused an error.

Should I take it as a sad fact, that locked VBA/VBE simply cannot give me the text nor any other identification of the line that caused the error while the code is locked? :eek: Can't a line of code be considered an object I can work with?

I do not consider as solution:
- numbering lines or error handlers https://stackoverflow.com/questions/36553813/find-error-line-number-in-vba - because it is extremely laborious to find the exact line
- or my "bookmark" solution above, which is basically just another error handler

Thank you in advance.

Mark
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,131
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
I don't understand why you are not willing to consider using line numbers, I have used them on many occasions & find it very helpful, as you can report the exact line the code failed on, along with the error number & message.
 

7ohm7

New Member
Joined
Oct 31, 2019
Messages
9
Hi & welcome to MrExcel.
I don't understand why you are not willing to consider using line numbers, I have used them on many occasions & find it very helpful, as you can report the exact line the code failed on, along with the error number & message.

Thank you Fluff,

I was reluctant to imagine that I should number 5000 lines of my code (yes I should do it in full code, because today there is a bug in one module but tomorrow may be in another). And especially what to do when adding new lines of code - it will take me time to always update the line numbers when I enter something.

But I'm starting to accept it, especially if the code can be numbered programmatically. Meanwhile, I have the idea to process directly with the VBA cleaning meta-code, which would do this:
- took module code as string
- delete the numbers at the beginning of the lines (CR/LF) if they were already
- add numbers at the beginning of the lines based on counter of CR/LF in previous part of string - this should ensure that they are all unique

It could work. But if this is the case, is there any way to extract the entire function code as a string object? Something to export module?

Thank you in advance
 

7ohm7

New Member
Joined
Oct 31, 2019
Messages
9
Ok, I have it :) e.g. ThisWorkbook.VBProject.VBComponents(..).CodeModule.Lines
Good one. Thank you Fluff, for confirmation that a solution exists.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,131
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad you sorted it & thanks for the feedback
 

7ohm7

New Member
Joined
Oct 31, 2019
Messages
9
You don't have to add line numbers manually, just use an addin, something like https://www.mztools.com/v8/features.aspx

Thank you Kyle, I see that M-Z Tools has many great features, I shall think about buying. But in the meantime I've already done a simple code for adding line numbers programmatically:

Code:
Option Explicit

Public Sub AddLineNumbers(sModuleName As String)
'adds/refresh numbers in the begining of line for purpose of debugging locked code

Dim sLineOld As String, sLineNew As String
Dim i As Long
Dim bProtected As Boolean

With ThisWorkbook.VBProject.VBComponents(sModuleName).CodeModule
    For i = 1 To .CountOfLines
        sLineOld = .Lines(i, 1)
        bProtected = ( _
            .ProcOfLine(i, vbext_pk_Proc) = vbNullString _
            Or bContains(sLineOld, "Sub") _
            Or bContains(sLineOld, "Function") _
            Or sLineOld = vbNullString _
            Or Strings.Left(sLineOld, 1) = "'")

        If Not bProtected Then
            sLineNew = CStr(i) & " " & IIf(IsNumeric(Strings.Left(sLineOld, 1)), sAfter(sLineOld, " "), sLineOld)
            Call .ReplaceLine(i, sLineNew)
        End If
    Next i
End With

End Sub
 

7ohm7

New Member
Joined
Oct 31, 2019
Messages
9
Sorry, one explanation is missing - bContains is my function, which is simple check if Strings.InStr(sWhere, sWhat) = 0
Please feel free to correct me, I'm just getting started with VBA.
 

Forum statistics

Threads
1,136,768
Messages
5,677,624
Members
419,707
Latest member
Anna vib

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top