2 Replies - 418 Views - Last Post: 18 July 2017 - 10:06 PM Rate Topic: -----

#1 M.Y.H  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 18-July 17

VBA errors in many excel documents sheets

Posted 18 July 2017 - 05:40 AM

hello everyone,

I need some help in Visual Basic if someone can help.

I have many excel documents that contains error in their vba code, i developed a VB code that will fix this problem by find and

replace the text that is making this problem.

My problem is that i want to replace a whole function with a new one and my code will replace only a single line and i didn't find

a way to replace multi lines.

Dim wb As Workbook
Dim strPath As String, strfile As String
Dim strToReplaceWith As String, strToReplace As String
Dim i As Long, j As Long

Dim VBE As Object

strPath = TextBox1.Text & "\"

strfile = Dir(strPath)

While strfile <> ""
    Set wb = Workbooks.Open(strPath & strfile)

    Set VBE = ActiveWorkbook.VBProject

    If VBE.VBComponents.Item(1).Properties("HasPassword").Value = False Then
        If VBE.VBComponents.Count > 0 Then
            For i = 1 To VBE.VBComponents.Count
                VBE.VBComponents.Item(i).Activate

                If VBE.VBE.CodePanes.Item(i).CodeModule.CountOfLines > 0 Then
                    For j = 1 To VBE.VBE.CodePanes.Item(i).CodeModule.CountOfLines
                        If InStr(1, VBE.VBE.CodePanes.Item(i).CodeModule.Lines(j, 1), TextBox2.Text, vbTextCompare) Then
                            strToReplace = VBE.VBE.CodePanes.Item(i).CodeModule.Lines(j, 1)
                            strToReplaceWith = Replace(strToReplace, TextBox2.Text, TextBox3.Text, 1, 1, vbTextCompare)
                            VBE.VBE.CodePanes.Item(i).CodeModule.ReplaceLine j, strToReplaceWith
                        End If
                    Next
                End If
            Next i
        End If
    End If

    wb.Close True

    strfile = Dir
Wend


Is This A Good Question/Topic? 0
  • +

Replies To: VBA errors in many excel documents sheets

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 5074
  • View blog
  • Posts: 13,697
  • Joined: 18-April 07

Re: VBA errors in many excel documents sheets

Posted 18 July 2017 - 05:50 PM

Without knowing more about what this VBA is doing, from what I can tell, you are comparing line by line for a match. What you need to do is determine when the function starts and ends that contains the error and delete all lines, putting all the lines of the replacement in its place. This is a little more complex than simply going through each line.

My guess is that the offending line is in the middle of the function so you would need to possibly back up a few lines until you found the beginning of the function right? Or are all the function signatures the same? If they are the same then it is just a matter of finding that line to get the start of the function and finding its corresponding "End function" line to know the range of lines that need to be replaced.

Hopefully you get what I mean.
Was This Post Helpful? 0
  • +
  • -

#3 M.Y.H  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 18-July 17

Re: VBA errors in many excel documents sheets

Posted 18 July 2017 - 10:06 PM

like u said, i'm comparing line by line i need to find the whole text that i want to change i couldn't find a way

@Martyr2, can you send me some examples to see how this can be done?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1