Bill Benson
bensonforums at gmail.com
Tue Sep 23 16:14:52 CDT 2014
There is no terrible harm splitting an excel application into two or three. On Sep 23, 2014 4:12 PM, "Brad Marks" <bradm at blackforestltd.com> wrote: > Shamil, > > Thanks for the advice and thanks for the code. > > This is my first attempt to use Excel to build a large application. I am a > little concerned about pushing Excel beyond what it can do. > > I work part time for a small manufacturing firm with a very limited > budget. I currently do not have access to Visual Studio. > > My only tools as MS Access, MS Excel, and some open source (free) things. > > The Excel project that I have started to work on is being built to > simplify how "outside" Excel spreadsheets are handled. These Excel files > are sent to us in a wide variety of formats. Currently there is a labor > intensive manual process to make these spreadsheets into an Excel file with > a common definition. This will be replaced by a new "semi automated" > process using an Excel "Menu" form and numerous VBA modules. > > So far progress has been good, but I have a long ways to go. > > Thanks again for the help, > Brad > > > > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com [mailto: > accessd-bounces at databaseadvisors.com] On Behalf Of Salakhetdinov Shamil > Sent: Tuesday, September 23, 2014 12:59 PM > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >