Microsoft Excel Programming Recipes

From Wikibooks, the open-content textbooks collection

Current revision (unreviewed)

A Wikibookian suggests that this book or chapter be merged into Excel.
Please discuss whether or not this merge should happen on the discussion page.

[edit] Introduction

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

[edit] Note to Co-Contributors

I'm starting this Wiki-Book because I have notes squirrelled away in various places where I record Excel-programming recipes as I discover them and figure exposing this to the general Wiki-Book community will:

Provide free corrections and improvements to these recipes;
May be helpful for people who are coming up the Excel programming learning curve;

I encourage the structure to be flexible: If someone has an epiphany of how better to organize this page, go for it. However the general concept of a collection of simple atomic recipes for common tasks, I think, is a good one. I suggest every recipe should include at a minimum:

The version of Excel it is known to work on, and Operating System (if applicable);
A little introductory text explaining what specifically the recipe aims to accomplish;
copy-pasteable code (if applicable);
(anything else?)

Also, I encourage improving the code-samples! These are mostly very sparse and were written mostly for the sake of jogging ones memory on how to do something. Turning these code-snippets into nicely written commented subs and functions would be a great contribution.

When writing code samples it is not necessary to enclose them in <pre>..</pre> tags, just indent them with a couple of spaces (don't forget to indent blank lines as well or there will be breaks in the display). Please also properly indent the code so that it is easily readable.

[edit] VBA Recipes

[edit] 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;

or you could just do "Save As / Excel AddIn

[edit] 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 SaveSetting and GetSetting. You can type the following into the Immediate window to get a feel for how these work:

 SaveSetting "MyApplicationName", "MyConfigSection", "MyKeyName", "Hello World"
 MsgBox GetSetting("MyApplicationName", "MyConfigSection", "MyKeyName")

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

 sub ShowAllKeys()
     Dim v As Variant
     v = GetAllSettings("MyApplicationName", "MyConfigSection")
     If Not IsEmpty(v) Then
        Dim counter As Integer
        For counter = LBound(v) To UBound(v)
            Dim keyname As String: keyname = v(counter, 0)
            Dim keyval As String: keyval = v(counter, 1)
            MsgBox keyname & "=" & keyval
        Next
     End If
 end sub

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 MyApplication and MyConfigSection are whatever you specified in your SaveSettings call.

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

[edit] Prevent Confirmation Popups In Excel

Works on: Microsoft Excel 2002 SP-2

Make the following call from VBA:

Application.DisplayAlerts = False

[edit] Making Cells Read-Only

Works on: Microsoft Excel 2002 SP-2

 Sub ProtectMe()
 Range("A1:IV65536").Locked = False
 Range("A1").Locked = True
 ActiveSheet.Protect Password:="Test"
 End Sub

[edit] 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.

[edit] Using Events

Works on: Microsoft Excel 2002 SP-2

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

option explicit
Dim WithEvents mySheet as Worksheet
 
Public Sub Init(aWS as Worksheet)
   set mySheet = aWS
End Sub
 
Private Sub mySheet_SelectionChange(ByVal Target As range)
   dim rg as range
   for each rg in Target
     Debug.Print CStr(rg)
   next
end Sub

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: [[1]]

[edit] 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.

[edit] Caveat : Online-Help Typos

I discovered the F1-help in my version of Excel had a typo re: the Click event. Here's an example of a Click handler with the correct parameters:

Private Sub clicksrc_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
   MsgBox "I got a click!"
end sub

-ravee.k

[edit] Iterating Over MultiSelected Cells

Works on: Microsoft Excel 2002 SP-2

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

  dim c as range
  For each c in Selection
     'same row as c + 1 column
     c.Offset(0,1).value = "YAY!"
     'same row as c + 3 column
     c.Offset(0,3).IndentLevel = 2
     ' etc.
  Next

[edit] 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:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Dim objModule As Object
  On Error Resume Next
  
  For Each objModule In ThisWorkbook.VBProject.VBComponents
      DoEvents
      If objModule.CodeModule.CountOfLines > 0& Then
         objModule.Export objModule.Name & ".bas"
      End If
  Next objModule
  
End Sub

[edit] Resize a Named Range

Excel 2003

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:

 Sub ResizeRange(ByVal RangeName As String, _
                 Optional NewRowCount As Long = 0, _
                 Optional NewColumnCount As Long = 0)
   
   Dim oRange As Range
   Set oRange = Range(RangeName)
   If NewRowCount = 0 Then
     NewRowCount = oRange.Rows.Count
   End If
   If NewColumnCount = 0 Then
     NewColumnCount = oRange.Columns.Count
   End If
 
   oRange.Resize(NewRowCount, NewColumnCount).Name = RangeName
 
 End Sub

[edit] 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. Sheet1!InitialValue to create a named-range only visible from within worksheet Sheet1.

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

[edit] C++ Recipes

[edit] Useful links

Here's a summary of various Office-development articles online at MSDN: [[2]]
Here's one such document with some good example code: [[3]]

[edit] Environment Tweaks

[edit] Controlling What Addins Excel Opens At Startup

Works on: Microsoft Excel 2002 SP-2

When you start Excel, it may automatically load addins (i.e. those you added from Tools-->Addins). The list of addins 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:

OPEN
OPEN1
OPEN2
etc...

The values of these variables is the name of the addin. Excel, on start-up, will attempt to load the addin in string-variable OPEN first, then OPEN1 (if present), onward until it runs out of such string-variables.

Note also the list of addins presented when you do Tools-->Addins is, in part, populated by the contents of the following key:

HKCU\Software\Microsoft\Office\10.0\Excel\Addin Manager

[edit] Notes:

I don't know what happens if you skip a key: i.e. OPEN, OPEN1, OPEN3. Maybe a nice experiment for someone with some free time ;)
Check the following MS-article: [[4]]

I have found that somehow the keys get consecutive even if you have entered them non consecutively. In my situation there was no open key and I added OPEN1. I saved the registery and open excel and closed the excel and once again opened the registry to find that the OPEN1 key has been renamed to OPEN.

[edit] Nuking Excel's Retained State

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

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:

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

[edit] Other Excel Resources on the Web

The Mr. Excel Forum: This is a great super-active forum where Excel Power-users can often be found. It's currently located here: [5]

Other Active Boards include : Woody's Lounge J Rubin's ExcelTip.com OzGrid Express

Clever people active with with Excel: Chip Pearson Ron de Bruin

BygSoftware.com: This site has loads of practical examples showing Excel's potential [[6]]

Aspose.Cells for .NET/Java: This is a component (available for both, .NET and Java platforms) that can be used by developers to create and manipulate Excel files without the need for MS Office to be installed on the system. Download it from here: [[7]]

Excel Statistics Lab Manual is written in Excel.