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

Darryl Collins darryl at whittleconsulting.com.au
Tue Sep 23 18:44:21 CDT 2014


Hi Brad and Shamil,

On the other side of the coin, I have built many Excel workbooks with 80+ 
modules that have been extensively used in the past without issue.  Indeed I 
usually find Excel a lot more stable and well behaved than Access in the 
long term - but that is just my experience and I am in no way discounting 
Shamil's comments here.  I understand he has gobs of experience and 
knowledge and I respect his opinion.  Occasionally Excel will go corrupt and 
you need to rebuild it - but that is unusual.

There is an undocumented issue with VBA modules that if they are a certain 
size (being roughly 64 KB in size when exported to text) - this can cause 
Excel to behave strangely and/or crash - So you are better off having many 
smaller modules than a few big ones.  Indeed I tend to have a lot of modules 
as I generally put each logical step or function into a single module - this 
is part of the reason I end up with a lot of modules, on the flip side - 
this makes them easy to reuse in other workbooks and can speed up 
development times considerably.

As for working with Excel VBA vs Visual Studio.  Whilst VS is wonderfully 
elegant and more advanced to use than VBA - it is often a step too far for 
the average small business.  These are folks who grew up with Lotus 123 
macros and generally even basic users know how to record and use a VBA code 
module.  Most of them don't want to know of options outside of MS Office - 
even SQL Server Express (and usually MS Access) is too complicated for many 
of them.

As weird as it seems to folks like us, many small businesses run their whole 
operations on Excel, often as a defacto database.

Anyway, being an Excel guy I would say "Go for it".  The real trick is to 
use Excel for it strengths (that is as a reporting tool, number masher and 
scenario outcomes) and not as a primary datastore.  That is what a database 
if for.  I personally never use forms in Excel either - I find there is no 
need for it.  Just enter the data directly into the worksheet - faster, 
easier and more stable.

Anyway, if you need any Excel help feel free to ask here, offline on the 
Excel-L list.

Cheers
Darryl.


-----Original Message----- 
From: Salakhetdinov Shamil
Sent: Wednesday, September 24, 2014 3:58 AM
To: Access Developers discussion and problem solving
Subject: Re: [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 >>>
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com 



More information about the AccessD mailing list