This function will list out all the procedures and functions that exist in an MS Access module.
Public Function AllProcsInThisModule(ByVal strModulename As String, acc As Access.Application)
Dim mdl As Module
Dim lngCount As Long
Dim lngCountDecl As Long
Dim lngI As Long
Dim strProcname As String
Dim astrProcNames() As String
Dim intI As Integer
Dim strMsg As String
Dim lngR As Long
' Open specified Module object.
'acc.DoCmd.OpenModule strModuleName
' Return reference to Module object.
Set mdl = acc.Modules(strModulename)
' Count lines in module.
lngCount = mdl.CountOfLines
' Count lines in Declaration section in module.
lngCountDecl = mdl.CountOfDeclarationLines
' Determine name of first procedure.
strProcname = mdl.ProcOfLine(lngCountDecl + 1, lngR)
' Initialize counter variable.
intI = 0
' Redimension array.
ReDim Preserve astrProcNames(intI)
' Store name of first procedure in array.
astrProcNames(intI) = strProcname
' Determine procedure name for each line after declarations.
For lngI = lngCountDecl + 1 To lngCount
' Compare procedure name with ProcOfLine property value.
If strProcname <> mdl.ProcOfLine(lngI, lngR) Then
' Increment counter.
intI = intI + 1
strProcname = mdl.ProcOfLine(lngI, lngR)
ReDim Preserve astrProcNames(intI)
' Assign unique procedure names to array.
astrProcNames(intI) = strProcname
End If
Next lngI
'strMsg = "Procedures in module '" & strModuleName & "': " & vbCrLf & vbCrLf
For intI = 0 To UBound(astrProcNames)
strMsg = strMsg & intI + 1 & ". " & astrProcNames(intI) & vbCrLf
Next intI
' Message box listing all procedures in module.
AllProcsInThisModule = strMsg
End Function
This is a complimentary article written by the MAARS team for the MAARS user community. Code in this article drives the operation of MAARS (MS Access Application wRiting Software). MAARS is an intelligent automation program that speeds up MS Access Application Development by 10x, 20x or 100x times. To learn more about MAARS, click here.
Disclaimer:
Some information included in this article may have been sourced from other publicly available websites and blogs. In such cases, credit goes to those authors for the original ideas and thoughts, but we do take credit for putting valuable information together and improve the efficiency of other office developers.
hi, i would like to know if it is possible to execute a function of a module, having the module name in a variable. thank you. Lorenzo
Hi Lorenzo:
Excellent question. As far as I know, the call function expects a static name (a module), not a variable. If you try to construct a call statement using variable names for the module and the function, unfortunately, it will not recognize the statement. I am assuming you are using the same function name in 2 different modules and that’s why you are asking the question – is this correct? What is it that you are trying to achieve -I may have other suggestion.
Regards,
Bashir
Hello Bashir.
You guessed!
I am purposely designing mask to use with the MVC pattern. I am writing as generic code as possible. So for each new class I implement I will have to change the code as little as possible manually.
It is not too expensive a thing.
It was just to take away a curiosity.
I find your site really interesting.
Well done.
Lorenzo
Thanks Lorenzo for your comment on the site. The reason I love software engineering-it allows me to be creative. Let me know if you have found a way to this.
Regards,
Bashir
In Excel you could use Application.Run to get what you want, e.g.:
Application.Run “A.foo”
Application.Run “B.foo”
where A and B are module names. (If the foo’s are Functions, Run returns what each foo returns.)
Access also defines Application.Run with the same signature, but it fails when you try to do this. 🙁
Multiple classes that implement the same interface will give you polymorphic behavior over the interface, if that helps.