VBA - really no solution for effectively debug locked code?

[7ohm7]

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? 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

 

[Fluff]

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]

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]

Ok, I have it e.g. ThisWorkbook.VBProject.VBComponents(..).CodeModule.Lines
Good one. Thank you Fluff, for confirmation that a solution exists.

 

[Fluff]

Glad you sorted it & thanks for the feedback

 

[Kyle123]

You don't have to add line numbers manually, just use an addin, something like https://www.mztools.com/v8/features.aspx

 

[7ohm7]

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:

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]

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.