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