[AccessD] DoCmd.OpenModule for Access - Something similar for Excel ?

 Brad --

1. Regarding your question - in P.S. of this posting you'll find a code sample to start with. Watch line wraps! I have used it with Excel 13, 'Option Explicit' and I have set a reference to 

Microsoft Visual Basic for Applications Extensibility 5.3 ->
C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB You can remove 'Option Explicit' and use late binding with Object variables - the code sample should still work well.

2. Regarding 80 modules in MS Excel VBA application: last time (10 years ago) when I used large MS Excel VBA project it had 20-30 modules and it was MS Excel 2003, and I found it loading very slowly, and then "one fine day" it started to fall down on load (opening MS Excel file with 20-30 modules) - the only "solution" was to export all modules into text files and import them back. But after a few edits/saves, the "fall down story" repeated. Be careful. But I must note I have used quite a few class modules not just ordinary vba modules. 

3. Honestly I was surprised that there exists still a demand on so intensive Excel/VBA development, why not consider using Visual Studio VB.NET/C# for MS Excel Automation? When I converted my Excel/VBA project with 20-30 modules into a VB.NET project's COM add-in it started to work very smoothly, and of course large VB.NET (Visual Studio) projects do not fall down (as often as) large Excel/VBA project do.

4. If you're brave enough to anyway go with 80 modules for your MS Excel VBA project I'd note that your case would be a good candidate for one or more Susan Harkin's articles in 'Tech.Republic" and I and/or other AccessD members might help you with coding your utility (VBA modules managing) procedures accompanying these articles :)

Thank you.

-- Shamil


P.S.

Option Explicit
Public Sub TestLocateVbaProcedure()
Dim projectFileName As String
Dim projectVbaModuleName As String
Dim vbaProcedureName As String

projectFileName = "Book1.xlsm"
projectVbaModuleName = "ThisWorkbook" ' "Sheet1"
vbaProcedureName = "myTestProcedure2"
LocateVbaProcedure projectFileName, projectVbaModuleName, vbaProcedureName
End Sub
Public Sub LocateVbaProcedure( _
ByVal projectFileName As String, _
ByVal projectVbaModuleName As String, _
ByVal vbaProcedureName As String)

Dim vbeProjectInstance As VBIDE.VBProject
Dim vbaCodeModule As VBIDE.CodeModule
Dim vbaProcedureStartLineNimber As Integer
Dim vbaCodePane As VBIDE.codePane

Set vbeProjectInstance = getVBProjectInstanceByFileName(projectFileName)
Set vbaCodeModule = vbeProjectInstance.VBComponents(projectVbaModuleName).CodeModule
vbaProcedureStartLineNimber = vbaCodeModule.ProcStartLine(vbaProcedureName, 0)
Set vbaCodePane = vbaCodeModule.codePane
vbaCodePane.Show
vbaCodePane.SetSelection vbaProcedureStartLineNimber, 1, vbaProcedureStartLineNimber, 1
End Sub
Private Function getVBProjectInstanceByFileName(ByVal projectFileName As String) As VBIDE.VBProject
Dim vbeProjectInstance As VBIDE.VBProject
For Each vbeProjectInstance In Excel.Application.VBE.VBProjects
If (VBA.InStr(vbeProjectInstance.Filename, projectFileName)) Then
Set getVBProjectInstanceByFileName = vbeProjectInstance
Exit Function
End If
Next vbeProjectInstance
End Function
 


Fri, 19 Sep 2014 14:54:04 +0000 from Brad Marks <bradm at blackforestltd.com>:
>Charlotte,
>
>I have an Access application that has 80+ VBA modules.  When I was developing this application, I found it very handy to have an Access form that had buttons that would make the VBA code visible via the 
>DoCmd.OpenModule command.  I used this approach a lot because it made my work easier.
>
>Now, I would like to do the same thing with a large Excel application, but the DoCmd.OpenModule command is not available in Excel. 
>
>Thanks,
>Brad
<<< skipped >>>
>