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

Salakhetdinov Shamil mcp2004 at mail.ru
Wed Sep 24 03:33:41 CDT 2014


 Hi Darryl --

Thank you for your remark.
Yes, I have just shared my experience with MS Excel 2003 VBA development which was very smooth till I have reached the "concrete wall" of frequent Excel/VBA project crashing with my loosing the results of code edits, no way to restore them without frequent export to text files/reimport into a new Excel/VBA project. That was a bad luck probably and using the "weak part" of MS Excel: I didn't know about 64KB module text size limitation, AFAIKR I have used rather small modules but that were mainly class modules - and some WithEvents - that could have been another undocumented "feature" that MS Excel/VBA Engine are weak in handling this stuff...

I'd be interested to know for what kind of reporting solutions.do you and your customer use MS Excel? 
Are these all kinds of reports or just a (limited) subset of business report types?
When developing reporting solutions are you doing a lot of custom VBA development?
If yes, is that development mainly consisting of MS Excel Automation/MS Excel reports worksheets formatting or it has also a lot of general purpose "data crunching": talking to the external databases / web services, reformatting their outputs, merging results...

Thank you.

-- Shamil


Wed, 24 Sep 2014 09:44:21 +1000 from Darryl Collins <darryl at whittleconsulting.com.au>:
>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.
<<< skipped >>>
>


More information about the AccessD mailing list