Excel VBA

Microsoft Excel is a deep program rich in features and functionality. One of the most powerful features of Excel is the ability to write programs in Visual Basic for Applications that run "behind" your worksheets to turn Excel into a calculation-oriented development platform for creating special purpose spreadsheets which can function as applications in their own right.

Service Pack
A service pack (in short SP) is a collection of updates, fixes or enhancements to a software program delivered in the form of a single installable package.

A service pack can be incremental, which means it only contains the updates that were not present in the previous service packs or, it can be cumulative, which means it includes the contents of all its predecessors. In the case of Microsoft's products, an incremental update was called a service release. For example, Office 2000 must be upgraded to service release 1 (SR-1) before one can install SP2.

Macro Security
Macro security settings are located in the Trust Center. However, if you work in an organization, your system administrator might have changed the default settings to prevent anyone from changing any settings and execute macros.

Macro Recording
A great way of learning about Excel VBA is using its macro recording function. With the function, you tell Excel to start recording, then perform various steps as if you were working without a macro recorder, and finally, tell Excel to stop recording. VBA code corresponding to what you did using Excel GUI has been recorded by Excel. While the code often cannot be meaningfully used without a modification, by starting from it and modifying it you can save a lot of time that would otherwise be spent reading the VBA documentation.

Menu paths:
 * Excel 2000, 2003: Tools > Macro > Record New Macro.
 * Excel 2007: View (tab) > Macros (group) > down-pointing triangle below Macros button > Record Macro
 * Excel 2007: Developer (tab) > Code (group) > Record Macro

Links:
 * Use Microsoft Office Excel 2007 macros to speed up work at microsoft.com
 * Create a macro (Excel 2003) at microsoft.com
 * Record and use Excel macros (Excel 2000) at microsoft.com

Enabling Developer tab
Developer tab allows you to insert various user interface controls such as buttons. In order to use it, you first have to enable it.

Menu paths to enable the tab:
 * Excel 2007: round Office button > Excel Options (button at bottom) > Popular > Show Developer tab in the Ribbon (check box)
 * Excel 2010: File (tab) > Options (button) > Customize Ribbon (button) > Developer (check box)

Links:
 * How to: Show the Developer Tab on the Ribbon for Excel 2007 at microsoft.com
 * How to: Show the Developer Tab on the Ribbon for Excel 2010 at microsoft.com

Making an XLA

 * Works on: |Microsoft Excel 2002 SP-2, |Microsoft Excel 2000 SR-1

An XLA is one way to make a VBA code-library. It's basically just a normal spreadsheet(.xls file) but has its worksheets hidden. Here's how to create a new one:
 * New Workbook
 * Save-As ...Name it whatever
 * Hit Alt-F11
 * In the Project-tree, select VBAProject(whatever.xls)/ThisWorkbook
 * Hit F4 to get Properties View
 * Find Property IsAddin and set to True
 * Hit Save
 * Close Excel
 * Rename whatever.xls to whatever.xla

Alternatively, you can use Save As / Excel AddIn.

Accessing the Registry

 * Works on: Microsoft Excel 2002 SP-2

This recipe is for reading/writing keys local to your application—this is to provide persistent settings for your VBA app. It does not cover arbitrary access to the Registry (i.e. looking at any key).

The VBA sub/functions are  and. You can type the following into the Immediate window to get a feel for how these work:

If you want to iterate over all keys in a given section you can do so as follows:

You can also delete a registry key as follows: DeleteSetting "MyApplicationName", "MyConfigSection", "MyKeyName"

FYI: Excel/VBA sticks this in the following registry location: MyComputer\HKEY_CURRENT_USER\Software\VB and VBA Program Settings\MyApplicationName\MyConfigSection ...where  and   are whatever you specified in your   call.

They end up in HKEY_CURRENT_USER\Software\VB and VBA Program Settings\MyApplicationName\MyConfigSection.

Prevent Confirmation Popups In Excel

 * Works on: Microsoft Excel 2002 SP-2

Make the following call from VBA:

Making Cells Read-Only

 * Works on: Microsoft Excel 2002 SP-2

Finding Non-Empty Portion of Worksheet
A Worksheet has a maximum size of 65536 rows by 256 columns. However if you want to iterate over all cells you probably don't want to visit all of the empty ones. For this purpose the Worksheet provides the UsedRange property. For example: ActiveSheet.UsedRange.Rows.Count tells you how many non-empty rows are in the given worksheet. Empty rows which are in between the first and last used row are counted as well. Example: If a given Worksheet has entries in cells A7 and B16, used range is considered to be A7:B16, which counts for a total of 10 rows.

Using Events

 * Works on: Microsoft Excel 2002 SP-2

Consider the following class definition—Assume its a class called CMyClass:

The main ideas here are:
 * By declaring mySheet WithEvents, you're saying that CMyClass is listening to mySheet's events.
 * By declaring the member sub mySheet_SelectionChange you're indicating how an instance of CMyClass should react if mySheet experiences a selection change (i.e. the user selects a new cell or range of cells); The general pattern for events is sub memberVarName_EventName(params).
 * You can disconnect the eventing between the given worksheet and CMyClass by setting mySheet = nothing;
 * You can create classes that throw events of your design using:
 * You would declare at the top of the class: Public Event SomeNiceEventName(YourFavoriteParam1 as WhateverType, etc...),
 * You could then raise that event (i.e. firing it to whatever listeners your class has) using RaiseEvent SomeNiceEvent("Some nice event happened.");
 * VBA in Excel doesn't like the letters r or c used as variables. Those letters mean 'row' and 'column' elsewhere.

A little more detail is given here:

Caveat: Uncaught Exceptions
Caveat: Uncaught exceptions in event-handlers cause VBE to mysteriously reset. If you are causing an uncaught exception in an event-handler, you probably won't get an error popup. Instead, VBE will just reset. For this reason you should make sure you're catching exceptions in all of your event handlers.

Caveat: Online-Help Typos
Some versions of Excel can have typos in the F1-help. Here's an example of a Click handler with the correct parameters:

Iterating Over MultiSelected Cells

 * Works on: Microsoft Excel 2002 SP-2

The following code-snippet writes "YAY!" in each cell selected by the user:

Exporting VBA Code

 * Works on Microsoft Excel 2002 SP-2

The following code provides a very primitive routine to write serializes the VBA code in your modules to files:

Resize a Named Range
Note that the Resize property of Range objects does not change the size of the range object. It returns a new anonymous Range object. Easiest way to do this is to set the .Name property of the Resized range:
 * Excel 2003

Creating a Named Range

 * Excel 2002

Named Ranges allow the user to refer to a cell or range of cells by name instead of by their cell address. This name can be used in other cell formulas as well as in VBA (e.g. using the SomeName ). There are two sorts of named ranges: Workbook Names and Worksheet Names.

To create a Workbook Name you can select the cell you want to name, pull down Insert-->Name-->Define... This will bring up the "Define Name" dialog. Here you can enter the new name for your cell.

The create a Worksheet Name you follow the same procedure but precede the name with Sheetname, e.g.  to create a named-range only visible from within worksheet.

When there are two variables with the same name, one local (Worksheet Name) and one global (Workbook Name), the spreadsheet uses the local variable.

There is no way to visualize a named range. The closest thing is to pull down again Insert-->Name-->Define..., but this method does not show if a variable is a local Worksheet Name or a global Workbook Name.

The named range can be a single cell, part of a row, part of a column or a rectangular group of cells. Each one behaves differently:
 * A single cell can be referenced anywhere in the worksheet or, if it's defined globally (workbook name), anywhere in any worksheet.
 * A group of cells composing part of a row can only be referenced in a parallel row. For example, if the named variable is mass and it spans the cells C5:L5, then a reference to mass' at cell E8 (say, a formula like =mass * (3e8)^2) would take the value at C8, but a reference to mass'' at cell M9 would return an error
 * Similarly, a group of cells composing part of a column can only be referenced in a parallel column. Cells outside the range will return an error
 * A group of cells that define a rectangular array of sides greater than one is only useful to be referenced in other worksheets - so, there's no point in defining them locally (worksheet name). For example, if covmatrix are the cells Sheet1!B2:D4, then if cell Sheet2!C3 has the formula =1/sqrt(covmatrix), then it will return 1/sqrt(Sheet1!C3).

Reading a File
Reading a file line by line AKA for each line of a file:

Links:
 * Open at Visual Basic for Applications Reference, msdn.microsoft.com
 * Close at Visual Basic for Applications Reference, msdn.microsoft.com
 * Line Input at Visual Basic for Applications Reference, msdn.microsoft.com

Writing to a File
Writing to a file:

Writing the tab-separated content of the current worksheet to a text file, disregarding some of cell content formatting such as percentages:

Links:
 * Open at Visual Basic for Applications Reference, msdn.microsoft.com
 * Close at Visual Basic for Applications Reference, msdn.microsoft.com
 * Print # at Visual Basic for Applications Reference, msdn.microsoft.com

File Existence
Testing whether a file exists:

Directories
Making a directory:

Removing a directory:

Changing to a directory:

Changing the current drive:

Listing the content of a directory, using a custom filter encompassing two file extensions:

Links:
 * ChDir at Visual Basic for Applications Reference, msdn.microsoft.com
 * ChDrive at Visual Basic for Applications Reference, msdn.microsoft.com
 * Dir at Visual Basic for Applications Reference, msdn.microsoft.com
 * MkDir at Visual Basic for Applications Reference, msdn.microsoft.com
 * RmDir at Visual Basic for Applications Reference, msdn.microsoft.com

Grep
Searching for a regular expression in the lines of the files of a directory aka grepping:

Clipboard
Prerequisites: Accessing the clipboard from an Excel sheet requires that a reference to MSForms (Microsoft Forms Object Library) is set in the sheet. You can set the reference by adding and subsequent removing of a user form, via Insert > UserForm in a pop-up menu. To check the presence of a reference, see Tools > References menu.

Placing text on the clipboard:

Getting text from the clipboard:

Links:
 * DataObject Class at msdn.microsoft.com; contains a section on Visual Basic, whose applicability to Excel VBA is unclear

Range
A range is a set of cells. The cells in a range do not need to be adjacent. However, the cells in a single range do need to belong to a single worksheet.

Defining a new range:

Iterating a range AKA for each cell in a range:

Iterating rows and iterating columns AKA for each row of a range and for each column of a range, even if discontiguous:

Making an union (a range subsuming both) or intersection (a range covering only cells in common) of two ranges:

Selecting a range:

Activating a cell:

Finding out about a range AKA learning about a range, including the number of cells AKA cell count, the first row, the last row, the first column, the last column, row count, and column count:

Links:
 * Range Collection at Excel 2003 VBA Language Reference at msdn
 * Referring to Multiple Ranges at Excel 2003 VBA Language Reference at msdn
 * End Property at Excel 2003 VBA Language Reference at msdn
 * Intersect Method at Excel 2003 VBA Language Reference at msdn
 * Union Method at Excel 2003 VBA Language Reference at msdn

Worksheet
To create, access, or delete worksheets, you can use the methods of Worksheet objects. Examples follow.

Obtaining an existing sheet by name or creating it if it does not exist:

Links:
 * Worksheet Object at Excel 2003 VBA Language Reference at mdsn
 * Sheets Collection Object at Excel 2003 VBA Language Reference at mdsn

Searching
You can search for values in a sheet as follows:

If you want to have a substring match, drop "LookAt:=xlWhole" or use "LookAt:=xlPart".

Links:
 * Find Method (Excel 2003 VBA Language Reference) at msdn

Cell Formatting
You can format cells including text color, background color, font properties and border, but also formatting as a number, percent or text from VBA as follows:

Links:
 * Font Object (Excel 2003 VBA Language Reference) at msdn
 * Borders Collection (Excel 2003 VBA Language Reference) at msdn
 * LineStyle Property (Excel 2003 VBA Language Reference) at msdn
 * Weight Property (Excel 2003 VBA Language Reference) at msdn
 * NumberFormat Property (Excel 2003 VBA Language Reference) at msdn

Color
In Excel VBA, RGB colors are plain numbers rather than objects. Some color examples are listed in.

Some examples:

Setting the background color of the cells of the 1st column to the row number color index:

Color index in the default palette:
 * 0 - Automatic
 * 1 - Black
 * 2 - White
 * 3 - Red
 * 4 - Green
 * 5 - Blue
 * 6 - Yellow
 * 7 - Magenta
 * 8 - Cyan
 * 9 - Dark red
 * 10 - Dark green
 * 11 - Dark blue
 * ... etc., through 56

Finding all cells whose text color approaches green:

Links:
 * Color Property at Excel 2003 VBA Language Reference at msdn
 * ColorIndex Property at Excel 2003 VBA Language Reference at msdn
 * Colors Property at Excel 2003 VBA Language Reference at msdn
 * ColorType Property at Excel 2003 VBA Language Reference at msdn
 * PatternColor Property at Excel 2003 VBA Language Reference at msdn
 * PatternColorIndex Property at Excel 2003 VBA Language Reference at msdn

Visibility
Hiding a row (hide a row, hide row):

Hiding several rows at once:

Hiding the rows of the currently selected cells:

Looping over rows that are visible AKA shown AKA not hidden:

Toggling the visibility of rows:

Hiding a column (hide a column, hide column):

Hiding several columns at once:

Hiding the columns of the currently selected cells:

Other tricks pertaining to visibility of columns work in a direct analogy to the examples for rows above.

Hyperlink
Opening or following a hyperlink (open a hyperlink, follow a hyperlink, open hyperlink, follow hyperlink):

Opening the Wikipedia article for the article title found in the single active cell by opening a put-together URL:

Opening a Wikipedia article per article title in any of the currently selected cells:

Opening a local hyperlink, with a possible pop-up asking for confirmation for security reasons:

Links:
 * FollowHyperlink Method (Excel 2003 VBA Language Reference) at msdn

Temporary file
Getting a temporary file, with unclear robustness of the following method, which uses random numbers and tests for file existence:

Links:
 * How To Use GetTempFileName API to Create a Unique Temporary File at Microsoft.com

Command Output
If you do not mind a popping up console window, here is a way of obtaining output of a command from Excel VBA:

If having a console window popping up is not acceptable and you are okay with creating a temporary file, here is another way of obtaining output of a command from Excel VBA:

Using cmd /c, you can run chains of commands connected using & or | as well:

Links:
 * Capture output value from a shell command in VBA? at stackoverflow

Dimensions
Row height and column width:

Links:
 * RowHeight Property at Excel 2003 VBA Language Reference at msdn
 * ColumnWidth Property at Excel 2003 VBA Language Reference at msdn
 * StandardHeight Property at Excel 2003 VBA Language Reference at msdn
 * StandardWidth Property at Excel 2003 VBA Language Reference at msdn

Comment
Working with comment aka note:

Collecting all comments of the sheet into a string:

Links:
 * Comment Object at Excel 2003 VBA Language Reference at msdn
 * Shape Object at Excel 2003 VBA Language Reference at msdn

IsEmpty
Approximately speaking, tells whether a variable has been initialized and not yet written to.

Can be used to tell whether a cell is empty; the presence of a comment attached to the cell or formatting of the cell does not make a cell non-empty.

Examples:

Links:
 * IsEmpty at Excel 2010 VBA Language Reference at msdn

IsNull
Tells whether an expression is Null, which is distinct from Empty.

Null can be assigned to variant variables; it cannot be assigned to variables dimensioned as strings or integers. Null cannot be assigned to objects, unlike Nothing.

Examples:

Links:
 * IsNull at Excel 2013 VBA Language Reference at msdn

Add-Ins at Startup
Controlling what add-ins Excel opens at startup:

Microsoft Excel 2003: Configure loaded add-ins via Tools -> Add-ins. Their list is mirrored in the following Registry key, but there's no advantage in editing the Registry: HKCU\Software\Microsoft\Office\11.0\Excel\Init Commands

Microsoft Excel 2002 SP-2:When you start Excel, it may automatically load addins (i.e. those you added from Tools -> Add-Ins). The list of add-ins that get loaded come from the following registry key: HKCU\Software\Microsoft\Office\10.0\Excel\Options Under this key you may find a list of string-variables: The values of these variables is the name of the add-in. Excel, on start-up, will attempt to load the add-in in string-variable OPEN first, then OPEN1 (if present), onward until it runs out of such string-variables. It seems like Excel will automatically renumber the keys if they are not consecutive (i.e. OPEN1, OPEN3, OPEN4 will become OPEN1, OPEN2, OPEN3).
 * OPEN
 * OPEN1
 * OPEN2
 * etc...

Note also the list of add-ins presented when you do Tools -> Add-Ons is, in part, populated by the contents of the following key: HKCU\Software\Microsoft\Office\10.0\Excel\Addin Manager

See also the following MS KB article: How to Remove Entries from Add-Ins Dialog Box.

Chart direct from VBA array data
Charts need not be based on the values in cells of a spreadsheet, but can also be made directly in VBA from an array. The following code makes a chart of the relative frequency of characters in a string, in percent of the total, or normalized to a maximum of unity. There is also an option to sort the display and the content can be changed by modifying the content or sequence of the vRef array. Procedures to remove charts and test the functions are included.

Nuking Retained State

 * Works on: Microsoft Excel 2002 SP-2
 * OS: Windows XP

Nuking Excel's Retained State: Excel remembers all sorts of things between runs: What addins to load, what buttons and menus to display, etc. Sometime you'll want to clean all of this stuff up and bring Excel back to its factory state.

Nuking Excel Checklist:
 * 1) Make sure the following directories are empty:
 * C:\Program Files\Microsoft Office\Office10\xlstart
 * C:\apps\xp\application data\Microsoft\xlstart
 * 1) Nuke the auto-open keys from the registry(as shown below);
 * 2) Nuke all .xlbs – For example check here:
 * C:\apps\xp\application data\Microsoft\Excel

Related Wikibooks

 * Visual Basic