Microsoft Excel Programming Tips, with Sample Code

The tips that follow are hard-won knowledge that we didn't want to lose, that may also be useful to other Excel Programmers.  If you like them please feel free to send us your tips in exchange.
There's a series of more elementary tips in our free monthly newsletter: Click to Subscribe.
Click here to learn to program Excel.
In most browsers, you can press Ctrl+F, or Edit > Find, to search this page for keywords.

Contents:

Our Programming Style

We use two-character indention.  If you don't like this, ask for our free indention program to fix it.

Where a type suffix exists, we use it for variable names instead of Polish notation.  The big advantage of this is that you know for sure that a variable named (say) File$ is a string, whereas strFile could be any type, depending on how disciplined the programmer was.  The type-declaration suffixes we use are:

Where there isn't a type-declaration suffix, we use the usual Polish prefixes.

Sorry if our conventions offend strict Polish notation devotees!

In what follows, we assume that you are conversant with the Visual Basic language*, and focus on how you get your VB code to talk to the Excel spreadsheet. 
*If not, click here to start learning to program Excel or e-mail us about buying the Excel Programming Course.

Here are our code examples:

Moving around the Spreadsheet

Move current cell down by one cell:
ActiveCell.Offset(1, 0).Select

Move 3 cells left:
ActiveCell.Offset(0, -3).Select

Select - End - Up:
Range(Selection, Selection.End(xlUp)).Select

Getting Values from the Spreadsheet

These display the value that is in the current cell:
MsgBox ActiveCell.Value
Tmp$ = InputBox(ActiveCell.Text)    ' Let user change text

This puts the contents of a string variable (as edited above with InputBox) into the current cell:
ActiveCell.Formula = Tmp$

This gets a formula from a named range (created with Insert > Name > Define):
Tmp$ = Range("SomeName").Formula

Cell and Sheet addresses:

To get the active cell's address, copy and paste the following line into the immediate Window (Gtrl+G), then press Enter:
? "Sheet " & ActiveSheet.Index & ": " & ActiveSheet.Name & "!" & Replace$(ActiveCell.Address, "$", "")

To refer to a cell on a specific sheet in code:
Replace$(Sheets(Sh&).Cells(Ro&, Co&).Address, "$", "")
Sheets("Sheet1").Cells(1, 2).Formula = 27    ' Put value into cell B1

To get the name of a sheet if the number is known:
MsgBox Sheets(1).Name

Delete selected row:

Rows(ActiveCell.Row).Delete Shift:=xlUp

Visible Property

True if the object is visible, false if hidden. For a chart or worksheet, this property can be set in code to xlVeryHidden. This hides the object so deep that the user cannot make the object visible: The only way to make it visible again is by setting this property to True in code, e.g.:

Sub UnhideAll() ' Unhide All sheets in Workbook including xlVeryHidden:
' Macro 15/01/2001 by Rick Raubenheimer

  Dim I%
  For I% = 1 To Sheets().Count
    Sheets(I%).Visible = True
  Next I%
End Sub

Sub BurySheet(I%) ' Hide sheet I% with xlVeryHidden:
  Sheets(I%).Visible = xlVeryHidden
End Sub

The Current Region Property

The current region is a range bounded by any combination of blank rows and blank columns (i.e. a "block" of data).  This property is useful for many operations that automatically expand the selection to include the entire current region:
ActiveCell.CurrentRegion.Select

The Used Range Property

Returns a Range object that represents the used range on the specified worksheet. Read-only.
UsedRange.Activate

To Call a Worksheet Function from inside a Macro:

In this case, there is no "Proper Case" function in VB, so we call Excel's:
Strg$ = Application.WorksheetFunction.Proper(Strg$)

Macro to Copy and  Paste Values

Sub CopyPasteValue()
' Macro to Replace Formula with its answer: 03/10/2001 by Rick Raubenheimer
'
' Keyboard Shortcut: Ctrl+q
'
  Selection.Copy
  Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
End Sub

Working with the Clipboard in Excel Code

The following example demonstrates data movement from a TextBox to a DataObject, from a DataObject to the Clipboard, and from the Clipboard to another TextBox. The PutInClipboard method transfers the data from a DataObject to the Clipboard. The SetText and Paste methods are also used. To use this example, copy this sample code to the Declarations portion of a form. On the form, create two TextBox controls named txtData1 and txtData2, and a CommandButton named cmdTest.

Option Explicit
Dim MyData As DataObject

Private Sub cmdTest_Click()
  Set MyData = New DataObject

  MyData.SetText txtData1.Text
  MyData.PutInClipboard

  txtData2.Paste
End Sub

Private Sub UserForm_Initialize()
  txtData1.Text = "Move this data to DataObject, to the Clipboard, then to txtData2!"
End Sub

To Speed Up Excel Macros:

  Application.ScreenUpdating = False
  Application.EnableEvents = False

These commands turn off screen updating, which can slow down a running macro, and disable events. This last line is included so that changes done by the macro in your worksheet won't trigger Excel's recalculation routines. If your macro is making a lot of changes in the data in the worksheet, and a full recalculation is triggered after each change, then with such a large workbook, lots of time can be spent just doing the recalculation. At the end of your macro, you reverse the effect of the two lines you added:

  Application.EnableEvents = True
  Application.ScreenUpdating = True
End Sub

Asking the User for a File Name:

GetOpenFilename gets the file name without opening the file (as xlDialogOpen does, see directly below):  It always looks by default on the current drive and folder, which is why we do a ChDrive and ChDir first if necessary:

Sub Test2()
  Dim Title$, Fiter$, Prefix$, Fil$
  Prefix$ = "GSA"
  Title$ = "Find " & Prefix$ & " file for sample " & gSample$
  ChDrive "P"
  ChDir "P:\Civlab\" & Prefix$
  Fiter$ = Prefix$ & " Excel Files (*.xls),*.xls"
  Fiter$ = Fiter$ & ",Excel Files (*.xls),*.xls"
  Fiter$ = Fiter$ & ",All Files (*.*),*.*"
  Fil$ = Application.GetOpenFilename(Fiter$, , Title$)
  MsgBox Fil$, vbInformation, "Open:"
End Sub

There is also a GetSaveAsFilename method for displaying that dialog box.

Asking the User to find a File, and then Opening it:

xlDialogOpen opens the file the user selects (but does not return the file name):

Sub OpenAny()
  ' This will Open a file specified by the user (name unknown):
  Dim dlgAnswer As Boolean
  dlgAnswer = Application.Dialogs(xlDialogOpen).Show
  If dlgAnswer Then    ' True = it was opened
    MsgBox "Opened File " & ActiveWorkbook.Name
  Else
    MsgBox "User Cancelled!"
  End If
End Sub

This is part of a poorly-documented, but potentially very useful collection called xlDialogs:

Excel Dialogs

You can use a single dialog box to change many properties at the same time. For example, you can use the Format Cells dialog box to change all the properties of the Font object.

For some built-in dialog boxes (the Open dialog box, for example), you can set initial values using arg1, arg2, ..., arg30. To find the arguments to set, locate the corresponding dialog box constant in "Built-In Dialog Box Argument Lists" in Excel Help. For example, search for the xlDialogOpen constant to find the arguments for the Open dialog box. For a little information about built-in dialog boxes, see the Dialogs collection in Excel Help.

This example displays the Open dialog box and selects the Read-Only option (3rd argument):
Application.Dialogs.Item(xlDialogOpen).Show arg3:=True
Arguments for xlDialogOpen: file_text, update_links, read_only, Format, prot_pwd, write_res_pwd, ignore_rorec, file_origin, custom_delimit, add_logical, editable, file_access, notify_logical, converter

Can anyone shed more light on this?  I've tried (for bringing up the Edit > Links dialog):
  Application.Dialogs(xlDialogChangeLink).Show
which produces Error 1004: Show method of Dialog class failed.  Microsoft's Help on the subject is, of course, useless.

Tip contributed by Christopher Wilkinson, 12 April 2007:

"You said that you got a runtime error using the xlDialogChangeLinks Dialog.
I did too. You could use
   Application.CommandBars.FindControl(Id:=759).Execute
to bring up the dialog instead."

Comment: This works a charm, provided you do not run the macro from a VB button that (by default) has the property "take focus on click" set true. Edit > Links is greyed out when the focus is on a button! Ask for our spreadsheet "LinksDialog.xls" if you want a demonstration.  Thanks for the tip, Chris!

Contributed Tips

Would you like to add a tip of your own (due acknowledgement will be given!) – click here to send tip.

See also:  Excel Programming Primer, Excel Spreadsheet Tips, Microsoft Word Tips, Microsoft Access Tips, Maximizer Tips, Tips on Windows and other Windows Programs, Free Software for Programmers.