IT Services & Technology Solution Services

IT Services YittBox
Create an account and receive a discount code for any future services!
Get Discount Code Now
Access – VBE – Retrieve Procedure Text Question
                                        Here’s a bit of an odd ball, but I was helping out in a forum discussion in which the user needed to be able to display the code behind procedures. He was trying to create some type of teaching tool.

Now there are different ways to approach such a thing and he could have copy/pasted each procedure into a table, but that creates duplication (which is never a good thing), creates maintenance since he’ll have to update entries as code is change, …

I found the question intriguing and set out to find a way to simply read the information from the VBA project.  Anyone have a solution already?
                                    
  • 1

  • 1

Answers (1)

October 17, 2019
Hello. I found a post of similar question and the solution for it. Credit for Daniel Pineault from devhut.net.

'---------------------------------------------------------------------------------------
' Procedure : VBE_GetProc
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return a VBA proc's text - a way to extract vba procedures
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None, uses Late Binding
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sModuleName : Name of the Module that contains the procedure to navigate to
' sProcName   : Name of the procedure to return the text of
' bInclHeader : True/False - whether to include the procedure header in the returned
'                   string
'
' Usage:
' ~~~~~~
' ? VBE_GetProc("Module1", "fOSUserName")
' ? VBE_GetProc("Module1", "fOSUserName", False)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2018-12-28              Initial Release, forum help
' 2         2018-12-28              Swicthed to Late Binding
' 3         2018-12-31              Added bInclHeader to include/exclude the proc header
'                                   Minor code optimization
'---------------------------------------------------------------------------------------
Public Function VBE_GetProc(ByVal sModuleName As String, _
                            ByVal sProcName As String, _
                            Optional bInclHeader As Boolean = True)
    Dim oModule               As Object  'CodeModule
    Dim lProcStart            As Long
    Dim lProcBodyStart        As Long
    Dim lProcNoLines          As Long
    Const vbext_pk_Proc = 0 'Req'd for Late Binding
 
    On Error GoTo Error_Handler
 
    Set oModule = Application.VBE.ActiveVBProject.VBComponents(sModuleName).CodeModule
    lProcStart = oModule.ProcStartLine(sProcName, vbext_pk_Proc)
    lProcBodyStart = oModule.ProcBodyLine(sProcName, vbext_pk_Proc)
    lProcNoLines = oModule.ProcCountLines(sProcName, vbext_pk_Proc)
    If bInclHeader = True Then
        VBE_GetProc = oModule.Lines(lProcStart, lProcNoLines)
    Else
        lProcNoLines = lProcNoLines - (lProcBodyStart - lProcStart)
        VBE_GetProc = oModule.Lines(lProcBodyStart, lProcNoLines)
    End If
 
Error_Handler_Exit:
    On Error Resume Next
    If Not oModule Is Nothing Then Set oModule = Nothing
    Exit Function
 
Error_Handler:
    'Err 35 is raiised if proc not found
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: VBE_GetProc" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function
 *
 *
 *
 

Leave a Reply

Your email address will not be published. Required fields are marked

 *
 *
 *
 
 
Let’s Work together

Start your Project

Loading