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.
The Procedure Builder is started from the Code VBA toolbar. It inserts boilerplace code depending on your choice. If required it even adds error handler code that allows the user to send an email to you giving details on what happened.
The Code VBA toolbar gives logical access to the VBA language elements such as variables, condition and loop structure. Extensive support is available for the different kind of user interactions such as message and inputbox, file, folder and date picker dialogs under menu Dialog.
Context sensitive intellisense Shift-SPACE opens a menu with all expressions that return the required type, here String. This could be available variables, built-in functions or custom functions available in the code library...
The Property Call Builder for setting a variable, here a worksheet, is started from an available parent, here ThisWorkbook. This can be done from the Object menu on the toolbar which is aware of all variables locally available.
Alternatively, you can insert the name of the object variable and start insertion using intellisense Shift-SPACE. Move down the menu hierarchy to select code for your task, here looping through the rows in the worksheet.
Complete lines, including setting of enumerated value, can be written this way. Commonly used properties are put at the top of the menu. The description summarizes what the property is about. Press F1 to get directly into online documentation.
The Application menu - here Excel - is a main entry point giving common code at the top, commonly used methods and properties
and the major classes of the application. Bottom left shows the Code Builder for Workbooks.Open
after the menu item was clicked.
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.
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.
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.
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
.
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
.
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.
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.
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.
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.
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.
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.