VBA Code Snippets

We can use Excel macros to automate the tasks we perform regularly.  Those tasks can be long and intricate or even quick and easy.  Macros are written using VBA code.  VBA stands for Visual Basic for Applications, which is the language used to control many applications.  Most of the actions we perform with a mouse or keyboard can also be automated with VBA.

There are lots of advantages to using VBA code.

  • Saves time – since VBA code operates at the speed of your computer, it can complete tasks much faster than you can manually.  The time-saving opportunity is massive; for example, a process that takes an hour to do manually might take only a few seconds with a macro.
  • Reduces errors – We make mistakes all the time; mistyping numbers, clicking the wrong cell, or deleting the wrong file.  But VBA is consistent.  If you provide the proper instructions, it will execute flawlessly every time.  Therefore, using macros can massively reduce the risk of human interaction.
  • Performs repetitive actions – Macros are perfect for performing repetitive tasks.  We can use a programming feature called looping, which allows us to run steps over and over again.
  • Integration with other applications – While we focus on Excel on this page, VBA is also included in Word, PowerPoint, Access, Outlook, and many other programs.  The best thing is that we can write macros in Excel which can control these other applications, which makes cross-application automation possible.

When you learn a foreign language, the fastest way to learn is to immerse yourself in that language.  Programming languages are no different; therefore, the best way to learn VBA is to immerse yourself and practice writing macros.  And much like dictionaries of phrases that exist for foreign languages, this page provides links to common VBA phrases which you can use to write your own macros.


Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address * First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.

VBA Code Library:

Application Settings and Methods

Control Custom Lists with VBA

Convert centimeters inches and pixels to points

Manipulating and changing scroll bars

Run a macro from macro (from another workbook)

Arrays

VBA Arrays – beginners guide

Sorting an array alphabetically with VBA

Auto Filter

Apply and control Auto Filter

Cells and Ranges

Add a prefix or suffix to every cell in selection

Apportion a value across cells

Cell and Range VBA properties and actions</

Convert Merged cells to Centre Across Selection

Draw rectangles around selected cells

Select all merged cells

Swap ranges, rows and columns

Charts

Charts and graphs

Comments

Insert, delete and control comments

Data Validation

Loop through each item in a Data Validation list with VBA

Files

Selecting a file using the File Dialog Box

Reading document properties

Find out if a file is already open by you or another user

Loop through all the files in a folder (and sub folders)

Copy, move, rename, delete and confirm existence of files

Zip and unzip files

Password protect an Excel file

Folders / Directories

Selecting a file using the File Dialog Box

Create, delete, rename and confirm existence of folders

Loop through all the files in a folder (and sub folders)

Zip and unzip folders

Form Controls

Form Control checkboxes

Loops

Loop through every worksheet or every workbook

Loop through all selected sheets

Loop through all the files in a folder (and sub folders)

Named Ranges

Named Ranges – VBA properties & actions

PDF

Save Excel sheets, charts and workbooks as PDF

Pictures

Insert, move, delete and control pictures

Pivot Tables

Excel VBA Code for Pivot Tables

PowerPoint

PowerPoint VBA Reference Library

Print settings

Control the print zoom settings

Security / Protection

Protect and unprotect worksheets

Protect and unprotect workbooks

Password protect an Excel file

Selection

Check what is currently selected in Excel

Shapes

Draw rectangles around selected cells

Tables

Control and manage Excel Tables

UserForms

UserForm zoom settings

Workbooks

Loop through every worksheet or every workbook

Workbook properties and actions

Protect and unprotect workbooks

Password protect an Excel file

Manipulating and changing scroll bars

Worksheets

Worksheet VBA properties and actions

Loop through every worksheet or every workbook

Loop through all selected sheets

Protect and unprotect sheets

Control the zoom settings

Zip and Unzip files and folders

VBA code to zip and unzipping files and folders


Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained a monkey could do.

Email Address * First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.