VBA Code Generators and Tools

Save time, improve code quality, get the work done!

Explore the code generation features below. Note that code preview allows you to see and adjust code before it is actually inserted. Check the additional coding tools overview further down.

Code builders overview

In the above slider presentation most examples concerned generic code generation, either directly from the cascading menu or else, in case there are more choices for the user, with a generic dialog allowing you specify what variables to use or to add to your code, which enumerated constant, whether a parameter is optional, etcetera. There is a description of the code involved at the top, the Help button at the bottom brings you to the official VBA documentation. Technically, these dialogs build on deep knowledge of the Office programs type libraries.

The following features custom-made code generator dialogs and when they are used.

example generic dialog

Custom-made code generator dialogs

Inputbox Builder

In the above slides, the MsgBox builder was presented. Similarly, the Inputbox Builder adds code to extend the basic Inputbox behaviour of obtaining input from a user with validation of the obtained data. If for example a number is required on receiving a non-numeric it will tell the user what is the required type and ask him to enter a new value.

inputbox builder

UserForm Builder

A more comprehensive kind of user interaction kan be achived with User Forms - mostly in Excel and Word. The UserForm Builder makes it easy to create a User Form with control of chosen types (text, combo, checkboxes). Also, if the user enters a value of the wrong type in the control, pressing the OK button results in a message to the user that a value for that field is incorrect and sets the cursor back to that field for the user to correct it.

UserForm Builder

If and Select Case builders & Loops

Builders to support code flow give can still save some time. First, using Code VBA intellisense Shift-SPACE after If, a menu pops up where you may select an appropriate set of lines. Second, if you selected the If Builder, when thinking about what conditions statements are most appropriate one may decide Select Case here is more appropriate. Regarding loops, that works similarly, after Do.

start if menu using intellisense select case builder

Multiline String Builder

Coding a string expression that combines variables with strings requires you to keep track of where strings begin and end. The Multiline String Builder simplifies the coding of such strings. It knows what variables are available for you to select and add and to interpret going teh the next lines as vbNewLine.

Multiline String  builder

SQL VBA builder

The SQL VBA builder lets you convert an SQL statement in a string you can use in VBA. Using the strSQL variable assembled in parts makes the code easier to read when it concerns multiple lines and helps you solve conflicts between any double quotes in your SQL and the quotes surrounding the string.

SQL VBA builder

MS Access Recordset Builder

The Recordset Builder makes it very easy to create code to add, read, update or browse data in your MS Access database using DAO (or ADO). Depending on your requirements it proposes code to add, edit or delete a selected record, or to iterate a collection of records.

DAO and ADO Recordset Builder

Class Builder

A sophisticed way to improve your code is by organizing it using custom classes. If you adopt this approach, the Class Builder is a very convenient way to create a class with properties. Once created you add the required methods. You can also use the Property builder to add additional classes. Access users also will benefit of the generate class from table feature.

Class Builder

Folder Builder

The Folder Builder helps to create code specifying the directory to store files in, optionally using variables in the file creation process. Possibly, the folder you want to use does not yet exist. To ensure it does you may use the VBA MkDir function, or, if there may be a case of multiple levels in subfolders, create those folders using the MakeDirMulti function which is included in Code VBA module modVBAFolderProcedures which is automatically added to your project if required.

Folder Builder

Writing your own Code Builders

Code VBA relies for most part on the VBA language and what the Office application offers in terms of features based on its class type library. Consider in Excel how many calculation methods are already available through the WorksheetFunction class! Still, we also found it convenient to extend VBA with common functions, e.g. a set of string manipulation functions. These are placed in a subfolder of Documents named VBA Code. Visit Creating and using code fragments for more information.

example fragment based code builder

Additional coding tools

Code Explorer

The Code Explorer is a replacement for the Project Explorer. It shows all procedures in your project and allows you to move to the procedure you need to work on quickly using double-click selection. In addition it allows you to insert selected procedure into the current position in the code, comment code, select or remove procedures, tidy the code indentation, drag procedures to other modules (move) or projects (copy).

Code formatting / Tidy

Tidy VBA code indentation makes it much more readable and easier to debug. When Code VBA inserts code its placement is determined by the current position of the cursor, it does not take the rest of the procedure in account. For this we have the Code VBA 'Tidy indentation' feature. With Code VBA 'Tidy indentation' feature you can have your code formatted automatically or only when you explicitly ask for it.

Commenting code

If you want to have certain lines of code not run, but you don't want to delete them, you can comment them. For this, each line of code needs to get a single quote added at the beginning. Doing this for more lines is distracting. Code VBA line commenting allows you to this for selected lines or for a selected procedure from the Code Explorer.

Inserting names easily

A special Names menu let's you pick up names of controls, labels, files more easily saving time in finding and copy-pasting them.

Extended use of Immediate window

Learn what procedures do by trying them out in the Immediate window before including them in your macro. Code VBA lets you insert custom or predefined procedures and prefills them with example strings and numbers for trying them.

Office automation

Use automation to have Office applications work together, e.g. customized emails (Outlook) from Access database. References are set automatically, both early and late binding are supported.