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
Click to Subscribe.
Click here to learn to program
In most browsers, you can press Ctrl+F, or Edit >
Find, to search this page for keywords.
Our Programming Style
Moving around the
Getting Values from
Cell and Sheet addresses
Delete selected row
The Current Region Property
The Used Range Property
Worksheet Function from inside a Macro
Macro to Copy and Paste
Working with the
Clipboard in Excel Code
Speed Up Excel Macros
Asking the User for a
Asking the User to find a File, and then Opening it
We use two-character indention. If you don't like this, ask
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:
Integer index (type integer or long)
Single (Real Number)
Double (Real Number)
Where there isn't a type-declaration suffix, we use the usual Polish
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
click here to start learning to program Excel or
e-mail us about buying the Excel Programming Course.
Here are our code examples:
Move current cell down by one cell:
Move 3 cells left:
Select - End - Up:
These display the value that is in the current cell:
InputBox(ActiveCell.Text) ' Let user change text
This puts the contents of a string variable (as edited above with
InputBox) into the current cell:
This gets a formula from a named range (created with Insert > Name >
Tmp$ = Range("SomeName").Formula
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:
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
For I% = 1 To Sheets().Count
Sheets(I%).Visible = True
Sub BurySheet(I%) '
Hide sheet I% with xlVeryHidden:
Sheets(I%).Visible = xlVeryHidden
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:
Returns a Range object that represents the used range on the
specified worksheet. Read-only.
In this case, there is no "Proper Case" function in VB, so we call
Strg$ = Application.WorksheetFunction.Proper(Strg$)
' Macro to Replace Formula with its answer: 03/10/2001 by Rick
' Keyboard Shortcut: Ctrl+q
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
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.
Dim MyData As DataObject
Private Sub cmdTest_Click()
Set MyData = New DataObject
Private Sub UserForm_Initialize()
txtData1.Text = "Move this data to DataObject, to the
Clipboard, then to txtData2!"
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
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
Dim Title$, Fiter$, Prefix$, Fil$
Prefix$ = "GSA"
Title$ = "Find " & Prefix$ & " file for sample " & gSample$
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:"
There is also a GetSaveAsFilename method for displaying that dialog
xlDialogOpen opens the file the user selects (but does not return
the file name):
' 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
MsgBox "User Cancelled!"
This is part of a poorly-documented, but potentially very useful
collection called xlDialogs:
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
For some built-in dialog boxes (the Open
dialog box, for example), you can set initial values using
..., 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):
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,
Can anyone shed more light on this? I've tried (for
bringing up the Edit > Links dialog):
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
I did too. You could use
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!
Would you like to add a tip of your own (due acknowledgement will be
click here to send tip.
Excel Programming Primer,
Excel Spreadsheet Tips,
Microsoft Access Tips,
Tips on Windows and
other Windows Programs,
Free Software for