VBA Code Generator and Coding Tools

Affiliate Disclosure

This Post focuses on AutoMacro, one of my favorite VBA-related tools. In the following sections, I:

• Describe the main reasons that explain why I find AutoMacro useful and believe most macro/VBA users have the potential to benefit from AutoMacro; and
• Explain/introduce AutoMacro's main tools/features.

Use the following Table of Contents to navigate to the section you're interested in.

Why (in my opinion) AutoMacro is useful

My main objective with Power Spreadsheets is to share what I've learned and continue to learn about Excel. Visual Basic for Applications (VBA) is one of my main areas of interest/focus.

For most Excel users (including me), one of the main reasons to learn about macros and VBA is to automate repetitive or recurring tasks.

When you create an appropriate macro, Excel can carry out some repetitive or recurring tasks automatically. The degree of automation you can achieve depends on a variety of factors. These include, for example:

• The specific characteristics of the task at hand; and
• Your level of knowledge about macros and VBA.

When the appropriate circumstances exist and your knowledge of macros and VBA is enough, you can fully or partially automate tasks. In such cases, those tasks are executed with very little or none input from you.

Creating macros and working with VBA code, however, can generate a new set of challenges.

How to achieve your objectives with macros and VBA

During the years I've spent working (and helping thousands of people automate Excel) with macros and VBA, I've noticed that almost all macro and VBA users face similar challenges.

For example: Despite having a good understanding of macros and VBA, users may have issues/challenges:

• Creating/structuring VBA procedures; or
• Identifying the appropriate VBA constructs/structures/statements/syntax to achieve a particular result/objective.

In other words, most macro and VBA users don't always know the exact VBA constructs/structures/statements/syntax they must use to achieve their objectives. This (commonly) results in these users spending countless hours searching for, and adjusting, VBA code examples/templates.

Improving the VBA user experience

Even (very) advanced macro and VBA users face challenges of their own.

For example, after a while working with macros and VBA, you may start to notice that (to a certain extent) you constantly/repeatedly use the same VBA code snippets/templates/structures (or basic variations of those snippets/templates/structures) across different contexts.

There are other more subtle challenges that virtually all macro and VBA users face. These include, for example, questions/issues on how to improve the user experience around the following topics/processes:

• Navigating complex/large VBA Projects.
• Appropriately formatting VBA code.
• Debugging/reviewing Excel modules and VBA code.

A brief introduction to AutoMacro

AutoMacro:

• Is an add-in for VBA; and
• Installs directly into the Visual Basic Editor (VBE).

Depending on the version, AutoMacro comes loaded with the following tools/features:

• Code generators;
• An extensive code library;
• The ability to create your own code library; and
• Advanced coding tools.

These tools/features help (to a certain/substantial extent) address/mitigate the issues/challenges I describe in the previous sections.

I was originally introduced to AutoMacro by Steve (from Automate Excel), who created AutoMacro. Being aware of some of the challenges I describe in the previous sections, Steve thought there had to be a better way.

Since learning about AutoMacro, AutoMacro has quickly become one of my favorite VBA-related tools. Several members of the Power Spreadsheets Community successfully use AutoMacro to, among others, save countless hours when working with macros and VBA.

---

I use the developer’s automacro and thrilled to see it in action. Do a lot with custom coding. Great tool, indispensable.

Dan M., USA

---

VBA Code Generators

AutoMacro's VBA Code Generators:

• Build code from scratch via visual interfaces;
• Require none or minimal VBA coding knowledge; and
• Are massive time-savers for more experienced programmers.

The following 3 VBA Code Generators are available in all AutoMacro versions:

1. Procedure Builder.
2. Loop Builder.
3. Copy Paste Builder

All other Code Generators are available in the Developer and Developer+ versions of AutoMacro.

Loop Builder

The term “looping” (broadly) refers to the execution of certain statements several times. Therefore, when you want/need to repeat an action (or set of actions) while working with VBA, you usually use loops.

Use the Loop Builder to build VBA code that does the following:

1. Loops through objects/items.
2. Performs actions with/on some/all of those objects/items.

Step #1: Objects/items you can loop through

The Loop Builder allows you to loop through any of the following:

• Objects;
• Array items; and
• Numbers.

The following are some of the objects you can loop through:

• Cells, which loops through all cells in a cell range.
• Sheets, which loops through all sheets in a workbook.
• Workbooks, which loops through all open workbooks.
• Shapes, charts, Pivot Tables or Tables, which loops through all shapes, charts, Pivot Tables or Tables in either:
  • A worksheet; or
  • All worksheets in a workbook.

When looping through numbers, the loop does this based on the following parameters (which you specify):

• A start number.
• An end number.
• A loop counter increment.

Step #2: Specify conditions/criteria for actions to be performed

The VBA Builder allows you to specify with/on which objects/items (in the loop) the applicable actions are performed.

In other words, you can specify that the applicable actions are performed with/on either of the following:

• All objects/items in the loop; or
• Only the individual objects/items that meet certain conditions/criteria.

Additionally, you can apply more complex/sophisticated conditional logic by matching certain actions to certain criteria. When matching an action to a criterion, the VBA code does the following:

• If condition/criterion #1 is met, action #1 is performed.
• If condition/criterion #1 isn't met, the other applicable conditions/criteria are tested in sequential order.
• If a condition/criterion (condition/criterion #n) is met, the applicable action (action #n) is performed.

In other words, when you match an action to a specific condition/criterion, the VBA code executes the actions matched to the first condition/criterion that's met.

You can increase the flexibility of your VBA code further by working with the following wildcards when specifying conditions/criteria:

• Question mark (?), which represents any single character.
• Asterisk (*), which represents any sequence of characters.

File Processing Wizard

Use the File Processing Wizard to build VBA code that does the following:

1. Loops through all files in a folder.
2. Performs actions with/on some/all of those files.

The File Processing Wizard is, to a certain extent, similar to the Loop Builder (which I explain above). More precisely, the File Processing Wizard (also) creates a loop that allows you to repeat an action (or set of actions).

Additional options when working with files with the File Processing Wizard

The File Processing Wizard allows you to specify the following:

• Whether the VBA code should loop through all files in the sub-folders of the (main) folder your VBA code works with.
• Whether the applicable actions are performed with/on either of the following:
  • All files; or
  • Only the individual files that meet certain conditions/criteria.

You can increase the flexibility of your VBA code further by working with the following wildcards when specifying conditions/criteria:

• Question mark (?), which represents any single character.
• Asterisk (*), which represents any sequence of characters.

Common uses of the File Processing Wizard

The following are some of the most common/useful actions you can perform with the File Processing Wizard:

• Open all files in a folder.
• Merge all files in a folder.
• Create a list of the files in a folder.
• Delete, move or copy the files.

When the File Processing Wizard generates code to open all files in a folder, you can also choose what to do after a file is processed. For example, you can specify that the file should be:

• Kept open.
• Closed without saving.
• Saved and closed.

One of the most common and, in my opinion, useful uses of the File Processing Wizard is to merge all files in a folder (for example, multiple CSV files). This option results in VBA code that carries out a simple file-merge process where the contents of each applicable/specified sheet in each applicable file are copied into the destination sheet in the applicable workbook.

Message Box and Input Box Builder

Use the Message Box and Input Box Builder to easily create good looking professional:

• Message Boxes; and
• Input Boxes

Message Boxes

A message box (generally) does the following:

1. Displays a message (in the message box).
2. Waits for the user to click a button.
3. Returns a value. This value indicates the button of the message box clicked by the user.

When working with VBA, you can create a variety of different message boxes.

AutoMacro allows you to, for example, specify the following characteristics/attributes associated with the message box's buttons:

• Number of buttons.
• Type of buttons.
• Default button.

AutoMacro also allows you to specify the icon style used by the message box. The following are the 4 icon styles you can usually work with:

• Critical: Critical message icon.
• Question: Warning query icon.
• Exclamation: Warning message icon.
• Information: Information message icon.

The Message Box builder contains the following two useful sections:

1. Preview, which displays a preview of the currently-configured Message Box.
2. Generated Code, which displays the VBA code generated by AutoMacro.

Both sections are automatically updated according to the message box characteristics/attributes you specify.

As I explain above, a message box usually returns a value indicating the button of the message box clicked by the user.

In practice, you'll (usually) do something with the value returned by the message box. In other words, your VBA code will perform a certain action depending on the button of the message box clicked by the user.

AutoMacro considers this. The code generated by the Message Box Builder usually includes a Select Case statement.

The Select Case statement allows you to model certain decision-making and conditional-testing processes. In the case of the Message Box Builder, the Select Case allows your code to appropriately handle the value that indicates which button in the message box was clicked by the user.

Input Boxes

An input box (generally) does the following:

1. Displays an input box.
2. Waits for the user to either:
   1. Input text and click the OK button (or press the Enter key); or
   2. Click the Cancel button (or press the Esc key).
3. Returns the contents of the text box in the input box (when the user clicks OK or presses Enter).

In practice, you'll (usually) do something with the contents returned by the input box. In other words, your VBA code will (usually) use the contents entered in the text box of the input box.

AutoMacro considers this. The code generated by AutoMacro usually assigns the contents returned by the input box to a variable. This allows you to easily store and manipulate (at different points in your procedure) the data entered by the user in the input box.

AutoMacro allows you to (additionally) specify the 2 following characteristics/attributes of the input box:

• Input Type, which allows you to specify the type of input to expect from the user.

• Default value, which is the value displayed in the input box's text box when the input box is initially displayed. This is, in other words, the default response.

Copy & Paste Builder

Use the Copy & Paste Builder to build VBA code that does the following:

• Copy or cut; and
• Paste.

You can carry out copy/cut and paste operations in any worksheet in any open workbook.

AutoMacro (also) allows you to create VBA code that carries out Paste Special operations. This allows you to further specify how the pasting operation is carried out by, for example, specifying the following:

• The part of the cell range to be pasted. You can, among others, choose to paste:
  • All;
  • Formulas;
  • Values;
  • Formats;
  • Comments; and
  • So on.
• Whether an arithmetic operation is performed by carrying out one of the following operations:
  • Add the copied data to the value in the destination cell.
  • Divide the value in the destination cell by the copied data.
  • Multiple the value in the destination cell by the copied data.
  • Subtract the copied data from the value in the destination cell.
• Whether blank cells in the cell range are pasted (or not) to the destination cell range.
• Whether rows/columns are transposed when the cell range is pasted.

Procedure Builder

Use the Procedure Builder to create procedures with certain specific/desired settings. In other words, the Procedure Builder quickly:

1. Generates procedures based on your specified/desired settings; and
2. Inserts that procedure into the active VBA module.

The Procedure Builder can be particularly helpful for beginners, who aren't familiar with some frequently-used settings/statements in VBA procedures.

More precisely, you can use the Procedure Builder to do the following:

• Toggle between:
  • Sub procedures; and
  • Function procedures.
• Specify procedure names.
• Add descriptive comments.
• Define whether a procedure is:
  • Public; or
  • Hidden/private.
• Set the following Application-level settings:
  • Disable screen updating, which results in the screen not updating while the procedure runs. This (usually) speeds up your macro code.
  • Disable automatic calculations, which results in turning automatic calculations (where Excel controls recalculations) off. This (usually) speeds up your macro code.
  • Disable alerts, which results in the suppression of prompts and alerts messages while the macro is running. When a message requires a response, Excel chooses the default response.
  • Disable events, which results in disabling events. The main reason to disable events is to prevent an infinite loop of cascading events. An infinite loop of cascading events occurs when an event-handler procedure triggers the event that (in turn) triggers the event-handler procedure itself.
• Display a message box after the completing the applicable tasks. This is useful when a procedure takes more than a few seconds to run. The message box informs the user that the procedure has finished running.
• Specify error-handling settings. Although sometimes useful (for example, when debugging), run-time errors can be disruptive (from a user's perspective). Therefore, there are situations in which you want to appropriately handle/trap errors to avoid the disruption caused by unhandled run-time errors. The Procedure Builder helps you work with different versions/variations of the On Error statement. The On Error statement is the basic error-handling construct.
  • Default (On Error GoTo 0), which disables any enabled error handler in the procedure.
  • On Error Resume Next, which specifies that when a run-time error occurs:
    • Control goes to the statement immediately following the statement where the error occurred; and
    • Execution continues.
  • On Error GoTo Error Handler and On Error GoTo End, which enable an error-handling routine.

The Procedure Builder also allows you to specify the default settings to be used each time you load the Procedure Builder. In other words, you can:

1. Research/specify your desired settings once; and
2. The Procedure Builder will use those settings when generating procedures in the future.

Array Builder

Use the Array Builder to create, and work with:

• Arrays;
• Dictionaries; and
• Collections.

The Array Builder contains some quick access buttons you can use to insert VBA code to perform certain commonly-used actions.

Class and Property Builder

Use the Class and Property Builder to create:

• Classes; and
• Properties.

Format Builder

Use the Format Build to create and preview number formats.

When specifying number formats, you can choose from the following:

• Named formats.

• Custom Formats. AutoMacro is loaded with many commonly-used custom formats. Additionally, you can either:
  • Edit the pre-loaded custom formats; or
  • Create your own custom formats from scratch.

Code Library

AutoMacro's Code Library contains hundreds of commonly-used code examples:

• The Beginner version contains 100 ready-to-use code examples.
• The Developer and Developer+ versions contain 230+ ready-to-use code examples.

You can insert these code fragments into your VBA code with:

• The click of a button; or
• Keyboard shortcuts.

Simply select the code from AutoMacro's menu and it will be inserted directly into your module.

Object Code Library

When working with Excel, you probably spend a substantial amount of time dealing with a few important/foundational objects. These usually include, for example:

• Workbooks;
• Sheets; and
• Cell ranges, including columns/rows.

These important/foundational objects continue to be important when working with VBA.

AutoMacro's Object Code Library allows you to:

• Interact with the objects that compose the Excel Application; and
• Learn about the hierarchy in which the objects that compose the Excel Application are organized (known as the Excel Object Model).

After identifying an object, you do something with/to that object. You can (generally) manipulate objects in two ways:

1. You can change an object's characteristics/attributes by working with properties.
2. You can carry out an action with/on the object by working with methods.

AutoMacro allows you to easily do the following:

1. Refer to an object; and
2. Do something with/to that object by working with a property/method.

The following are some of the objects you can work with:

• Columns and rows.

• Cell ranges.

• Sheets.

• Workbooks.

Settings Code Library

Use the Settings Code Library to work with several commonly-used settings, including the following:

• Screen updating, which allows you to control whether screen updating is turned on or off.
• Automatic calculations, which allows you to specify Excel's calculation mode (for example, automatic or manual).
• Events, which allows you to disable events.
• Error handling, which allows you to specify how potential run-time errors are handled.

The Settings Code Library can be helpful for both beginner and advanced VBA users:

• If you're a beginner VBA user, use the Settings Code Library to become familiar with frequently-used settings.
• If you're an advanced VBA user, use the Settings Code Library to save time by, for example, working with AutoMacro's keyboard hotkeys.

Files Code Library

The Files Code Library contains code for working with:

• Files; and
• Folders.

More precisely, the Files Code Library contains the following:

• Code to work with/on files.
• Code to work with/on folders.
• Code to access certain special folders (for example, Documents).
• Code to work with text files, which allows you to (for example):
  • Load the files into VBA's memory; and
  • Edit the files.
• Functions to work with files, as follows:
  • Confirm whether a file exists.
  • Obtain a file name from a path.
  • Unzip files.

Other Code Libraries

AutoMacro also contains code fragments for working with the following:

• Functions, including functions that do the following:
  • Confirm whether a specific sheet/range exist.
  • Confirm whether a specific file exists.
  • Add standard start/end procedure declarations

• Conditional logic:
  • If… Then… Else statements; or
  • Select Case statements.

• Loops, which allow you to repeat an action (or set of actions). AutoMacro allows you to insert the following 5 commonly-used loops:
  • For Each Cell in Range.
  • Do Until.
  • For i = 1 to 5.
  • Loop Through String.
  • For Each WS in Worksheets.

• Message Boxes and Input Boxes.

• Text.
• Dates.
• Times.

Custom Code Library

With AutoMacro's Developer and Developer+ versions, you can do the following:

• Create your own Custom Code Library.
• Organize your code into folders.
• Access your custom code via:
  • User-created menus; or
  • Keyboard shortcuts.
• Share your custom code with friends and colleagues. Your Custom Code Library can be synced across multiple computers by using a shared directory.

Coding Tools

AutoMacro's (Developer and Developer+ versions) Coding Tools:

• Improve the Visual Basic Editor (VBE); and
• Make coding in VBA easier.

Use the Coding Tools to, among others:

• Navigate;
• Edit;
• Format; and
• Debug;

Your VBA code.

Formatting Tools

Appropriately formatting/organizing your VBA code is essential to, among others, achieve the following:

• Keep your code:
  • Readable; and
  • Organized.
• Reduce the risk of errors.
• Improve your efficiency when working with VBA.

AutoMacro contains several VBA code formatting tools:

• Comment/uncomment multiple lines of code, which:
  • Allows you to quickly comment/uncomment multiple lines of code at once.
  • Identifies when some lines of code are commented and others aren't. This allows you to easily standardize the commenting in a block of partially commented VBA code.

• Auto-Indent, which adds proper code indentation to the active module.

• Remove excess line-breaks, which does the following:
  • Scans your VBA code; and
  • Deletes line-breaks when more than two consecutive blank lines are found.

Navigation Tools

As the size of your VBA Projects increases, quick/easy navigation through the different components of the VBA Project may become more challenging.

AutoMacro contains several tools to help you quickly/easily navigate VBA Projects:

• Back and Forward buttons, which do the following:
  • The Back button allows you to go back to the previous code location.
  • The Forward button returns to the original (before pressing the Back button) code location.
  • Store your code position history. This allows you to jump back/forward several code positions.

• Bookmarks, which allow you to:
  • Save code positions in a VBA Project; and
  • Quickly/easily navigate to those code positions using keyboard shortcuts.

• Close all windows, which closes either of the following:
  • All windows; or
  • All windows except the active window.

Miscellaneous Utilities

AutoMacro contains several additional tools/features that don't fit neatly/clearly into the categories I describe in the previous sections.

• Display line numbers, which allows you to toggle the display of line numbers in the active module. This tool/feature is useful (for example) when reviewing/debugging VBA code.

• Sort code, which allows you to quickly rearrange your code without copying/cutting/pasting, but rather:
  1. Opening the Sort Codes menu;
  2. Select the appropriate code element; and
  3. Click the up/down arrow buttons to move/organize the selected code element.

• Mass import/export of modules, which allows you to mass import/export modules/UserForms.

• Copy UserForm controls and code, which allows you to copy and paste both:
  • UserForm controls; and
  • The code associated to the UserForm control.

Excel/VBA Model Debugger

The main objective of the Excel/VBA Model Debugger is to help you review/debug Excel/VBA models. The Excel/VBA Model Debugger achieves this by creating the following 5 reports:

• Excel Named Ranges Report, which lists all named ranges in the workbook and does the following:
  • Specifies the named range's scope (workbook or worksheet).
  • Identifies the worksheet in which the named range is located.
  • Creates a link to the named range.
  • Warns whether there are ambiguously-named ranges. Ambiguously-named ranges occur when two named ranges:
    • Have different scopes (one workbook, one worksheet); but
    • Have the same name.

• VBA Range Report, which carries out the following scans:
  • Scans the VBA code and lists:
    • All used ranges; and
    • Their location.
  • Scans the used ranges and identifies potential problems. These potential problems include, for example:
    • References to named ranges that don't exist.
    • Hard-coded cell range references.

• Shape Procedures Report, which lists:
  • All shapes in the workbook that call procedures; and
  • The procedures called by those shapes.

• Procedures List Report, which lists:
  • All procedures in the VBA Project;
  • The module in which the procedure is stored;
  • The entire procedure code;
  • Whether the procedure is called by either of the following:
    • Another procedure.
    • A shape.

• Formula Error Report, which does the following:
  • Scans the workbook; and
  • Identifies worksheets containing formulas that either:
    • Result in errors; or
    • Contain errors as one of their arguments.

Join the Hundreds of Professionals Who Use AutoMacro!

AutoMacro is used by advanced VBA users to save countless hours by inserting pre-made code (vs. typing from scratch).

With AutoMacro, beginner and intermediate VBA users may also learn VBA faster. AutoMacro:

• Makes it easy to see what's important to learn; and
• Shows you what VBA is capable of.

In other words: Regardless of your current VBA skill level is, I believe you have the potential to benefit from AutoMacro.

But you don't have take my word for it. The following are some of the things AutoMacro users have to say:

---

AutoMacro is a great resource for someone like myself who isn't writing VBA code on a daily basis, but, does write some fairly in-depth macros to automate tasks for myself and coworkers. I get rusty on exact syntax required, and AutoMacro is much handier than repeated Google searches!

Henson R.
USA

---

The program is very easy to understand and allows me to concentrate more on writing concise and efficient code and not having to stop to look up the exact syntax. This is giving me the time to start doing more advanced coding which I really appreciate!

Dave S.
USA

---

I've used AutoMacro and it has saved me allot of time navigating the complexities and options that VBA presents. I get the code done faster and in a more reliable fashion than ever before. Being able to include my own code snippets in the library for recall into the editor is a big plus for me and saves even more time. If i need to enhance something in the library, that's easy too. EXCELLENT TOOL. You'd be making a mistake not to get it.

Brent M.
Canada

---

AutoMacro is backed by a 30-day no-questions-asked 100% money back guarantee.