Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Verifying a Macro Exists (Word XP)

    Hi. I've been searching all morning and can't find an answer to my problem.
    I would like to be able, via code, to verify if a module/procedure exists before calling it from another procedure. (Actually, there is a VB "front-end" that needs to call a home-made VBA command that I wrote: it's in a module called Utilities and the procedure is SetCustomProtocolProperties().)
    I set up a reference to Microsoft Visual Basic for Applications Extensibility and got the following code to work (simplified for this example):

    Dim mTemplate As Template
    Dim mColl As New Collection
    Dim vbComp As VBComponent

    Set mTemplate = ActiveDocument.AttachedTemplate

    For Each vbComp In mTemplate.VBProject.VBComponents
    mColl.Add vbComp
    Debug.Print vbComp.Name
    Next vbComp

    I
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Verifying a Macro Exists (Word XP)

    Here are a couple of procedures I use to build a list of Procedures/Functions in a .xls file. See if it will get you started.

    <pre>Public Function BuildProcList(wbCode As Workbook) As Long
    Dim I As Long, J As Long, lDLines As Long, lCLines As Long, lMLines As Long
    Dim lPStart As Long, iComponentType As Integer
    Dim obComponent As Object, obCode As Object
    Dim strComponentName As String, strCurComponentName As String
    Dim strProc As String, strCurProc As String

    I = 0
    J = 4
    For Each obComponent In wbCode.VBProject.VBComponents
    lMLines = 0
    strComponentName = obComponent.Name
    iComponentType = obComponent.Type
    Set obCode = obComponent.CodeModule
    lDLines = obCode.CountOfDeclarationLines
    lCLines = obCode.CountOfLines
    If lDLines > 0 Then
    J = J + 1
    Call StoreProc(strComponentName, "Declarations", 1, lDLines, iComponentType, J)
    End If
    If lCLines > lDLines Then
    strCurProc = obCode.ProcOfLine(lDLines + 1, vbext_pk_Proc)
    lPStart = lDLines + 1
    End If
    For I = lDLines + 1 To lCLines
    strProc = obCode.ProcOfLine(I, vbext_pk_Proc)
    If strProc <> strCurProc Then
    J = J + 1
    Call StoreProc(strComponentName, strCurProc, lPStart, lMLines, _
    iComponentType, J)
    lMLines = 0
    lPStart = I
    strCurProc = strProc
    End If
    lMLines = lMLines + 1
    Next I
    If lMLines > 0 Then
    J = J + 1
    Call StoreProc(strComponentName, strProc, lPStart, lMLines, iComponentType, J)
    lPStart = 0
    lMLines = 0
    End If
    Next obComponent
    BuildProcList = J
    End Function

    Public Sub StoreProc(strCName As String, strMName As String, lLine As Long, lCnt As Long, _
    iCType As Integer, lN As Long)
    ThisWorkbook.Worksheets(1).Cells(lN, 1) = strCName
    ThisWorkbook.Worksheets(1).Cells(lN, 2) = strMName
    ThisWorkbook.Worksheets(1).Cells(lN, 3) = lLine
    ThisWorkbook.Worksheets(1).Cells(lN, 4) = lCnt
    ThisWorkbook.Worksheets(1).Cells(lN, 5) = iCType
    End Sub
    </pre>

    Legare Coleman

  3. #3
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Verifying a Macro Exists (Word XP)

    This function may serve your purposes.

    The line that starts "lngX =" triggers Error 35 if the procedure doesn't exist in the module, the error handler resumes execution at the next line, lngX remains 0, and so ProcedureExists returns False.

    This is stripped down from a different procedure of mine, and for this purpose could be stripped down even further. If you want, you could change the ErrorHandler line "Resume Next" to "Resume ExitLabel" and take out the "If lngX > 0 Then" line and the corresponding "End If" line (while leaving in the ProcedureExists = True line, which won't execute if the procedure isn't found).

    Function ProcedureExists(oDoc As Word.Document, _
    strModule As String, strProcedure As String) As Boolean
    On Error GoTo ErrorHandler

    Dim vbc As VBIDE.VBComponent
    Dim lngX As Long

    Set vbc = oDoc.VBProject.VBComponents(strModule)

    lngX = vbc.CodeModule.ProcBodyLine(strProcedure, vbext_pk_Proc)
    If lngX > 0 Then
    ProcedureExists = True
    End If

    ExitLabel:
    Set vbc = Nothing
    Exit Function

    ErrorHandler:
    Select Case Err.Number
    Case 35
    Resume Next
    End Select
    'Put your normal error-handling code here.
    Resume ExitLabel

    End Function

  4. #4
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Verifying a Macro Exists (Word XP)

    Oops. Correction to my earlier post:

    If the module (rather than the procedure) doesn't exist, error 9 (not 35) is triggered, and in that case you'll definitely want to GoTo ExitLabel rather than Resume Next. So here's the corrected version of my function:

    Function ProcedureExists(oDoc As Word.Document, _
    strModule As String, strProcedure As String) As Boolean
    On Error GoTo ErrorHandler

    Dim vbc As VBIDE.VBComponent
    Dim lngX As Long

    Set vbc = oDoc.VBProject.VBComponents(strModule)

    lngX = vbc.CodeModule.ProcBodyLine(strProcedure, vbext_pk_Proc)
    ProcedureExists = True

    ExitLabel:
    Set vbc = Nothing
    Exit Function

    ErrorHandler:
    Select Case Err.Number
    Case 9, 35
    GoTo ExitLabel
    End Select
    'Put your normal error-handling code here.
    Resume ExitLabel

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •