Question about blank lines in modules

[greaseman]

This might sound crazy, but.... does anyone know of a way, using code, to go through a module, looking for blank lines, and when finding a blank line to see if there are spaces in the line? Next, if there are spaces in the blank line, remove them, so you end up with a completely blank line that has nothing more than a "carriage return / line feed?"

If anyone has a sample, could it be posted with your reply?


I enjoy and appreciate this forum..... it has already helped me greatly. Thank you!!

 

[Mile-O]

Why?

 

[greaseman]

Lines in modules

Hi, Mile-o-phile.....

The reason is related to an "intermittent" error we receive at my job. The error is basically, "VBA332.DLL has caused an invalid page fault," which causes instant ACCESS death. After hours of researching the VBA332.DLL headache, which Microsoft says is usually due to either a corrupt db or to using a reserved word in VBA code (not so in our cases), and of replacing the VBA332.DLL and of even reloading Microsoft Office, and of decompiling, and making an empty db and copying in all tables, forms, queries, etc, we found something interesting:

We copied our modules out to text files, and using Word, saw that an empty line (or two) appeared to contain spaces, instead of the usual CHR$(10) and CHR$(13) right at the beginning of the empty line. (Incidentally, the empty lines were placed into the code for readability.) When the "spaces" were removed manually from the "blank" lines, and then the fixed code was placed back into the modules, all worked wonderfully..... compiles succeeded and the VBA332.DLL kamikaze disappeared when the applications were processed.

So..... that is why we are interested in suggestions for something that can examine, find and replace as follows: (1) Read a module (2) Determine if a line is blank. (3) If the line is blank, determine if it contains spaces, as opposed to just a Linefeed / Carriage Return. (4) If it contains spaces, remove the spaces, leaving just the Carriage Return / Line Feed. (5) Replace the old line with the newly altered line. (6) Save the module.

Looking forward to your replay.......

 

[Mile-O]

Here you go, and don't say I'm not good to you.

Sub CheckSpacesInModules()

    On Error GoTo Err_CheckSpacesInModules

    Dim lngCounterA As Long, lngCounterB As Long
    Dim modModule As Module

    For lngCounterA = 0 To Modules.Count - 1
        Set modModule = Modules.Item(lngCounterA)
        With modModule
            For lngCounterB = 1 To .CountOfLines
                If Trim(.Lines(lngCounterB, 1)) = "" Then
                    .ReplaceLine lngCounterB, ""
                End If
            Next lngCounterB
        End With
    Next lngCounterA
    
Exit_CheckSpacesInModules:
    Exit Sub
    
Err_CheckSpacesInModules:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_CheckSpacesInModules
    
End Sub

This code will go through all of your modules and check each line for blank spaces only - if it finds any it will just clear the spaces. If you want to delete the line you can change the .ReplaceLine part with DeleteLine. InsertLine is also a possibility.

It won't remove tabs.

 

[greaseman]

Thank you....I'll give it a try ASAP. One "other little thing...." If a line of code is idented (such as in the code you used in your reply), will your code remove the spaces in front of the indenting, or leave that line alone? Apologies for not mentioning that in my earlier pleas.

Thanks again!! That was a fast response!

 

[Mile-O]

If there is text after the indenting, it should leave the line alone.

 

[greaseman]

Thank you again!! What took so long to reply?? I am coding you suggestions "as we speak" and will let you know how I did.

 

[greaseman]

It works!!

It worked!! Thank you so much!! That will save us a lot of grief and aggravation!! I had searched and searched on the various forums and did not find anything related to the problem we encountered, so your response was really appreciated.

 

[Mile-O]

No probs.