VBA Code Snippets

This page contains sections of common sections of code, which you can learn from, copy straight into your VBA projects, or just to remind you of the syntax.

Contents:

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

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

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 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

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

Protect and unprotect worksheets

Control the zoom settings

Zip and Unzip files and folders

VBA code to zip and unzipping files and folders