Salakhetdinov Shamil
mcp2004 at mail.ru
Tue Sep 23 16:23:25 CDT 2014
Brad -- You can use free Visual Studio Express. If you have to develop mainly code for "data crunching/reformatting" from "a wide variety of formats" into MS Excel readable (CSV?) files then using VB.NET/C# together with .NET Framework built-in features + many open sources libs would be a snap comparing to the usual VBA development. If you still prefer to use VBA try first to make a test project with 80 modules to see how well it will work: the difficulties of handling large Excel 2003/VBA project (actually getting into a "dead end state" after a couple of month of work) are comparable IMO to a learning curve for VB.NET. -- Shamil Tue, 23 Sep 2014 20:09:29 +0000 from Brad Marks <bradm at blackforestltd.com>: >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 >