Code Library


AutoVBA's Code Library contains over 230+ ready-to-use VBA code fragments

vba code module

Each piece of code is easily accessible via the menu within the Visual Basic Editor. Simply click and the code will insert directly into your code module.

vba code module

Keyboard Shortcuts

Each code fragment is also accessible via ALT keyboard shortcuts. Simply press and release the ALT key, followed by the underlined letter from your desired menu:

vba code module

Save Your Own Code

AutoVBA gives users the ability to create their own code library. You can add up to 10 custom code folders in the Custom Code Library Toolbar:

custom code library

Click this icon to create a folder:

custom code library new folder

Then from within the folder you can add or delete code and create subfolders.

custom code library menus

The Edit Custom Code Menu allows you to edit any piece of code:

vba edit code

custom code library edit

or edit the file structure by right-clicking in the Custom Code Explorer:

custom code library edit2

Collaborating and Organizing - Custom Code

AutoVBA stores these custom code fragments in an XML file on your hard drive. The directory location can be changed in the Edit Form:

vba edit code

vba edit code location

These code fragments can be shared across teams by placing the Custom Code Folder on a shared network drive.

You can also export the XML file to a specific folder. Use this to backup your code or to send to colleagues. Use the Restore button to import the saved XML file's code.

Code Library Contents

Settings

vba code library settings

The settings menu contains VBA and Excel settings including:

  • ScreenUpdating
  • Automatic Calculations
  • Events
  • Error Handling
  • and much more
  • Pro-Tip: Even if you know the syntax, make use of the keyboard hotkeys and save some keystrokes

Objects

Most Excel VBA work is manipulating objects like Ranges, Sheets, and Workbooks. You manipulate these objects by either applying a method (an action like Copy or Paste) or a property (cell value or cell color).

With a click of the mouse in AutoVBA you can apply a method or property to an object.

Columns and Rows

vba code library columns rows

Ranges

vba code library ranges

Sheets

vba code library sheets

Workbooks

vba code library workbooks

Files

vba code library files

  • Functions to work with files: Does a file exist?, Get file name from path, and unzip files.
  • The Files menu contains code to manipulate files
  • The Folders menu contains code to work with folders
  • SpecialFolders provides code to access MyDocuments and other special folders
  • The TextFiles menu contains code to work with text files allowing you to load them into VBA's memory, edit them, and more.

Functions

vba code library functions

Ready-to-use functions to add to your toolbox.

  • Does a specific Sheet & Range exist? TRUE / FALSE
  • Does a file exist? TRUE / FALSE
  • Standard start & end procedure declarations

Ifs and Selects

Examples of logical commands: Ifs and Selects.

vba code library ifs selects

Fors and Loops

vba code library fors loops

Fors and Loops are essential to automating repetitive processes. AutoVBA allows you to easily insert five of the most common types of loops:

  • For Each Cell in Range
  • Do Until
  • For i = 1 to 5
  • Loop Through a String
  • For Each WS in Worksheets

MessageBox and Inputbox

MessageBoxes and InputBoxes allow you to easily communicate information to users or to receiving information from the user.

vba code library messageboxes inputboxes

Text, Dates, and Time

VBA contains many tools for working with text, dates, and times.. AutoVBA makes it easy to find the correct tool.

Text

vba code library text

Date and Time

vba code library date time

VBA Object Variables - Intro

This is a brief introduction to object variables in VBA. If you're already familiar, skip to the next section for information about how AutoVBA handles objects.

Excel VBA code often references workbooks, worksheets, and ranges. This looks like:

Range("A1").value = 1
Sheets("Sheet1").Delete
Workbooks("timecard.xlsm").Close

Notice the first code example above:

Range("A1").value = 1

Only the range is defined. The workbook and worksheet are not. When the workbook and/or sheet are not explicitly defined the currently active workbook or sheet is used. In other words:

Range("A1").value = 1

is the same as

Activesheet.Range("A1").value = 1

and the same as

ActiveworkbookActivesheet.Range("A1").value = 1

Instead, it's possible to specify a workbook or worksheet:

Workbooks("timecard.xlsm").Sheets("Sheet1").Range("A1").value = 1

As you can see, this is a lot to write. Imagine writing this out over and over again. Instead workbook, worksheet, and range objects can be declared as variables:

dim ws as worksheet
set ws = Workbooks("timecard.xlsm").Sheets("Sheet1")

ws.range("A1").value = 1

Now, ws can be used to reference Sheet1 of Timecard.xlsm. This is a huge time-saver and can make code easier to read. It's also necessary when working with many Loops.

Continue to the next section to learn more.

Settings - Objects

Many code fragments in AutoVBA reference workbook, worksheet, or range objects. You can choose whether AutoVBA refers to object variables or detects the currently active range/sheet/workbook and hard-codes those names .

This setting is defined here:

vba code module

vba code module

Option 1 (Default): Read the currently active workbook, worksheets, and/or range and hard-code them into the code fragment.

Examples:

Range("A1").value = 1

Sheets("Sheet1").Delete

Workbooks("timecard.xlsm").Close

Workbooks("timecard.xlsm").Sheets("Sheet1").Range("A1").value = 1

Option 2: Use object variables when referring to workbooks, worksheets, and ranges.

rng.value = 1

ws.Delete

wb.Close

wb.ws.rng.value = 1

To activate option 2 click the checkbox:

vba code module

The Object Variable names are editable. To edit them, just click within the table:

vba code module