none
VBA Word - Open to Specific Sub in VBE Editor RRS feed

  • Question

  • hi friends,

    how do i go to a specific sub in a vba module in a word document ?

    That is go to a sub in the VBE editor.

    Sub GotoTest()

        Application.VBE.MainWindow.Visible = True

        ThisDocument.VBProject.VBComponents("Test1").Activate
    End Sub


    Sub Test1()

    MsgBox "Hello"
    End Sub

    This says subscript out of range

    I have looked everywhere but can't find this small detail amiss

    any ideas?

        

    Cheers Dan :)



    • Edited by Dan_CS Wednesday, September 7, 2016 11:21 PM
    Wednesday, September 7, 2016 11:15 PM

Answers

  • Here is a second example that uses Find. I have included an InputBox. However, you need to understand that Find will find the text anywhere and not necessarily a Sub name. That is the reason for the InputBox because where I hard coded the string to find, the code found it where it was assigned to the variable. As per the InputBox Prompt, I have instructed the user to include Sub in the string to find.

    Code has been edited since initial posting due to InputBox used in Excel does not work the same in Word.

    Sub SearchCodeModule()
        Dim vbProj As VBProject
        Dim vbComp As VBComponent
        Dim cdeMod As CodeModule
        Dim strProcToFind As Variant
        Dim SL As Long ' start line
        Dim EL As Long ' end line
        Dim SC As Long ' start column
        Dim EC As Long ' end column
        Dim Found As Boolean
       
        'No validation in this example for InputBox
        strProcToFind = InputBox("Enter name of procedure to find. (Include Sub)")
       
        If strProcToFind = "" Then
            MsgBox "User cancelled. Processing terminated"
            Exit Sub
        End If
       
        Application.VBE.MainWindow.Visible = True
       
        Set vbProj = ActiveDocument.VBProject

        For Each vbComp In vbProj.VBComponents
            vbComp.Activate
       
            Set cdeMod = vbComp.CodeModule
            With cdeMod
                SL = 1
                EL = .CountOfLines
                SC = 1
                EC = 255
                Found = False
               
                'Note: Following line WholeWord set to False
                Found = .Find(Target:=strProcToFind, StartLine:=SL, StartColumn:=SC, _
                    EndLine:=EL, EndColumn:=EC, _
                    WholeWord:=False, MatchCase:=False, patternsearch:=False)
                   
                'The variables SL, SC, EL,EC get reset with the find.
                'EL is the end of the line and EC is the end of the string being searched
                If Found Then
                    'Following highlights entire line.
                    'Change EL to EC to highlight from zero to end of strToFind
                    Application.VBE.ActiveCodePane.SetSelection SL, 1, SL, EL
                    Exit Sub
                End If
               
            End With
        Next vbComp
       
        If Found = False Then
            MsgBox "The string '" & strProcToFind & "' not found."
        End If
           
    End Sub


    Regards, OssieMac



    • Edited by OssieMac Thursday, September 8, 2016 7:21 AM Compatibility problem between Excel and Word
    • Marked as answer by Dan_CS Thursday, September 8, 2016 11:43 AM
    Thursday, September 8, 2016 7:04 AM

All replies

  • VBComponents("Test1") will only refers to the Module "Text1", not a sub within a module.

    Rod Gill
    Author of the one and only Project VBA Book and VBA developer.
    www.project-systems.co.nz

    Thursday, September 8, 2016 2:21 AM
  • AFAIK you cannot directly activate a sub by its name. However, you can locate the sub and then select the line where it is found but it requires the module to be activated first.

    The following loops through the modules and searches for the sub name and then jumps to the line number. The line number for the sub actually starts where you see the line between subs on the screen and therefore the cursor might be positioned above the sub if you have several blank lines between subs.

    I have not included any code for "Not found".

    There may be other methods of using Find to locate the sub and then select the required line number but I have not tested. The code is adapted from code at the following link so have a look at it and you might find a better method.

    http://www.cpearson.com/excel/vbe.aspx

    Sub GotoTest()
        Dim vbProj As VBProject
        Dim vbComp As VBComponent
        Dim cdeMod As CodeModule
        Dim strProcToFind As String
        Dim lngLineNum As Long
        Dim ProcName As String
        Dim ProcKind As vbext_ProcKind
       
        strProcToFind = "test2"     'Edit to Sub or Function to find
       
        Application.VBE.MainWindow.Visible = True
       
        Set vbProj = ActiveDocument.VBProject       'Use ActiveWorkbook for Excel

        For Each vbComp In vbProj.VBComponents
            vbComp.Activate
           
            Set cdeMod = vbComp.CodeModule
           
            With cdeMod
                lngLineNum = .CountOfDeclarationLines + 1
                Do Until lngLineNum >= .CountOfLines
                    ProcName = .ProcOfLine(lngLineNum, ProcKind)
                    If UCase(ProcName) = UCase(strProcToFind) Then      'Case sensitive so use UCase
                        Application.VBE.ActiveCodePane.SetSelection lngLineNum, 1, lngLineNum, 1
                        Exit Sub
                    End If
                    'Increment Line number to past end of sub or function
                    lngLineNum = .ProcStartLine(ProcName, ProcKind) + _
                            .ProcCountLines(ProcName, ProcKind) + 1
                Loop
            End With
        Next vbComp
     End Sub


    Regards, OssieMac

    Thursday, September 8, 2016 4:31 AM
  • Here is a second example that uses Find. I have included an InputBox. However, you need to understand that Find will find the text anywhere and not necessarily a Sub name. That is the reason for the InputBox because where I hard coded the string to find, the code found it where it was assigned to the variable. As per the InputBox Prompt, I have instructed the user to include Sub in the string to find.

    Code has been edited since initial posting due to InputBox used in Excel does not work the same in Word.

    Sub SearchCodeModule()
        Dim vbProj As VBProject
        Dim vbComp As VBComponent
        Dim cdeMod As CodeModule
        Dim strProcToFind As Variant
        Dim SL As Long ' start line
        Dim EL As Long ' end line
        Dim SC As Long ' start column
        Dim EC As Long ' end column
        Dim Found As Boolean
       
        'No validation in this example for InputBox
        strProcToFind = InputBox("Enter name of procedure to find. (Include Sub)")
       
        If strProcToFind = "" Then
            MsgBox "User cancelled. Processing terminated"
            Exit Sub
        End If
       
        Application.VBE.MainWindow.Visible = True
       
        Set vbProj = ActiveDocument.VBProject

        For Each vbComp In vbProj.VBComponents
            vbComp.Activate
       
            Set cdeMod = vbComp.CodeModule
            With cdeMod
                SL = 1
                EL = .CountOfLines
                SC = 1
                EC = 255
                Found = False
               
                'Note: Following line WholeWord set to False
                Found = .Find(Target:=strProcToFind, StartLine:=SL, StartColumn:=SC, _
                    EndLine:=EL, EndColumn:=EC, _
                    WholeWord:=False, MatchCase:=False, patternsearch:=False)
                   
                'The variables SL, SC, EL,EC get reset with the find.
                'EL is the end of the line and EC is the end of the string being searched
                If Found Then
                    'Following highlights entire line.
                    'Change EL to EC to highlight from zero to end of strToFind
                    Application.VBE.ActiveCodePane.SetSelection SL, 1, SL, EL
                    Exit Sub
                End If
               
            End With
        Next vbComp
       
        If Found = False Then
            MsgBox "The string '" & strProcToFind & "' not found."
        End If
           
    End Sub


    Regards, OssieMac



    • Edited by OssieMac Thursday, September 8, 2016 7:21 AM Compatibility problem between Excel and Word
    • Marked as answer by Dan_CS Thursday, September 8, 2016 11:43 AM
    Thursday, September 8, 2016 7:04 AM
  • The code in my previous post has been edited since initial posting to get it to work satisfactorily in word. I initially developed it in Excel and the InputBox works differently in Word.

    This post to force a notification because you do not get a notification when edits are made.


    Regards, OssieMac

    Thursday, September 8, 2016 7:23 AM
  • Thank you for the answers, I wasn't aware it was this complex.

    I had a small snippet of code for excel which always worked so i assumed same for word

    Sub GoTo_Test1()

    Application.VBE.MainWindow.Visible = True

      With Application
           .GoTo Test1
        End With
    End Sub

    But for word its more complex


    Cheers Dan :)

    Thursday, September 8, 2016 11:45 AM