Codematic Excel developers logo
Codematic spreadsheet-excel development image

Rapid Application Development / Advanced Excel Development

 
Products for Excel
Commercial Products:
  - Classic Ribbon
  - Alt-FileSearch
  - Password Remover
Spreadsheet Quality Products
Free Products
Excel Development
Excel Development Index
Excel VBA
- VBA IDE (editor)
- VBA Training
- VBA Best Practice
- VBA Performance
- COM Performance
- VBA Security
Excel and Databases
Excel and Pivot Tables
Excel Add-ins
Worksheet Functions
Excel and xlls
Excel (in)security
Excel testing
Excel and .net
Excel External Links
Excel Developer Types
Professional Excel Development
Excel 2007
Excel 2010
Excel Development Archive
Spreadsheet Services
Spreadsheet Development
Spreadsheet Migration
Spreadsheet Maintenance
Spreadsheet Review
Spreadsheet Management
Resources
Excel User Confs
Consultant Profile
Book Reviews
Links
Other
Site Map

Working with Excel and VBA in the VBA Editor

VBA Integrated Development Environment

You can set up the IDE to support the way you work. Some people like to have all the main tool windows open all the time. Others like to just have one or two open. Note though once you have them docked like above you quickly learn not to change it as it is so hard to re-dock them the same way.

VBA Environment

Unfortunately the default environment settings are not geared towards high quality development, they are set to make it easy for beginners to get productive quickly.

These are our preferred settings for professional quality development (Tools>>Options)

The default is to have those top 2 reversed.

You always get Auto Syntax Check (line goes red), removing the tick stops the disruptive modal error message boxes from constantly popping up every time you move off a line to copy something:

If the advice were a bit more helpful it might be worthwhile, but even beginners would struggle to get any value out of the above example. And it isn't going to get better as VBA is end of lined. Advice: Turn off Auto Syntax Check.

Require Variable Declaration puts an 'Option Explicit' at the top of each new code resource you open (module, class, form etc) (note it is not retrospective, hence the need to set it ASAP). Not using option explicit is just sloppy and is sure to lead to hard to spot errors in any significant coding.

if you want to use a variable called x (you may be able to think of a more meaningful name) OE forces you to 'Dim' it first, and optionally to decide what data type it should be. If OE is missing VB will implicitly Dim the variable as a variant the first time you use it, if you later mistype the variable name VB will create another new variable, rather than warn you 'Variable not defined' which would be more useful.

Another bizarre default setting is the way the IDE does not show one of the most important toolbars as standard. Luckily you can right click in the menu area and show the Edit toolbar.

The 2 very useful commands here are the ones with blue lines, the first one comments out a line, the one with the blue arrow un-comments it.

Also of note is the option to show the MZ-Tools toolbar. This is an excellent free add-in that is vital to serious Excel VBA development. MZ Tools is available from here.

Note these settings will be reflected in other Office applications.

Much more info is included in these slides.

Please contact us with any questions.

 

Upcoming Events:

25 January 2012 - UK Excel Developer Conference - London


Products for sale:

AltFileSearch

Office 2007 FileSearch replacement logo

New information about the missing FileSearch feature in Office 2007 and details of our pragmatic solution (Current price GBP 30.00)


wsUnprotector

worksheet password remover logo

Instant Excel worksheet protection remover and password recovery (Current price GBP 15.00)


Classic Ribbon Tab

classic ribbon for office 2007 logo

Add Excel 97/2000/2002/2003 compatible menu structure to Excel 2007
(Current Price GBP 10.00)


 

Products coming soon:

Link Manager

(Find and control external links in Excel Workbooks)

Due by Q1 2111.

XLAnalyst Pro

(Excel VBA based spreadsheet auditing tool)

Due before the end of 2111.

  ;-)
This page was last reviewed on December 21, 2011

©Codematic Ltd 1999-2011