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?
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
