Selecting All Unlocked Cells
Oddly, there is no direct way to quickly identify which cells are locked or unlocked. When the sheet is protected, you can use the Tab key to move among the unlocked cells, but it you would like to select them all you'll need a macro.
The macro listed below selects all unlocked cells on the active worksheet.
Sub SelectUnlockedCells() Dim WorkRange As Range Dim FoundCells As Range Dim Cell As Range Set WorkRange = ActiveSheet.UsedRange For Each Cell In WorkRange If Cell.Locked = False Then If FoundCells Is Nothing Then Set FoundCells = Cell Else Set FoundCells = Union(FoundCells, Cell) End If End If Next Cell If FoundCells Is Nothing Then MsgBox "All cells are locked." Else FoundCells.Select End If End Sub
Looping Through A Range Efficiently
A common type of macro involves looping through a range of cells, and performing an action based on the contents of each cell. For example, you may want to make the cell bold if the value in the cell is negative. This tip describes how to create such a macro. You should be able to adapt the technique described here to handle your own needs.
I list several macros, each increasingly more sophisticated.
Looping Through a Range: Take 1
The subroutine below demonstrates how to loop through all cells in the current selection. The routine checks the value of each cell in the range and adjusts the Bold property of the Font object accordingly. Notice that the subroutine starts by ensuring that the selection consists of a range.
Sub BoldNegative() If TypeName(Selection) <> "Range" Then Exit Sub For Each cell In Selection If cell.Value < 0 Then cell.Font.Bold = True Else Font.Bold = False Next cell End Sub
Looping Through a Range: Take 2
The previous subroutine works fine in most situations. But what if the selection consists of one or more entire rows or columns? Excel users are accustomed to selecting entire rows and columns, so your macro should be able to handle this type of situation. If you select an entire row before running the preceding macro, you'll find that it works, but it's not very efficient - and excruciatingly slow since it checks every cell in the selection. Ideally, the macro should just examine the non-empty cells. You can accomplish this by using the SpecialCells method of the Range object (refer to the online help for details).
The subroutine below improves upon the previous routine. It examines only the non-empty cells in the selection. It does this by first checking the cells with constants, and then the cells with formulas. Notice the use of the On Error statement. This is necessary because the SpecialCells method returns an error if no cells qualify. You'll find that this routine works equally fast even if the entire worksheet is selected.
Sub BoldNegative() If TypeName(Selection) <> "Range" Then Exit Sub On Error Resume Next ' Check the cells with constants For Each cell In Selection.SpecialCells(xlConstants, 23) If cell.Value < 0 Then cell.Font.Bold = True Else cell.Font.Bold = False Next cell ' Check the cells with formulas For Each cell In Selection.SpecialCells(xlFormulas, 23) If cell.Value < 0 Then cell.Font.Bold = True Else cell.Font.Bold = False Next cell End Sub
Looping Through a Range: Take 3
The preceding subroutine works fine, but it is not as efficient as it could be. You'll notice that a block of code is repeated. When code is repeated in a routine, you can often create a separate procedure, and then call the procedure rather than repeat your code. This is demonstrated in the two subroutines below. The CheckCells subroutine takes a Range object argument, and is called twice by the BoldNegative procedure.
Sub BoldNegative() If TypeName(Selection) <> "Range" Then Exit Sub On Error Resume Next ' Check the cells with constants Call CheckCells(Selection.SpecialCells(xlConstants, 23)) ' Check the cells with formulas Call CheckCells(Selection.SpecialCells(xlFormulas, 23)) End Sub Sub CheckCells(CurrRange As Range) For Each cell In CurrRange If cell.Value < 0 Then cell.Font.Bold = True Else cell.Font.Bold = False Next cell End Sub
Looping Through a Range: Take 4
If a single cell is selected when the preceding BoldNegative subroutine is executed, you'll find that all non-blank cells in the worksheet are examined. This may not be what you want. Therefore, it's necessary to make one additional check - to determine if the selection consists of a single cell. If so, only that cell is checked. The routine below incorporates this modification (the CheckCells routine is unchanged).
Sub BoldNegative() If TypeName(Selection) <> "Range" Then Exit Sub ' If one cell is selected, check it and exit If Selection.Count = 1 Then CheckCells (Selection) Exit Sub End If On Error Resume Next ' Check the cells with constants Call CheckCells(Selection.SpecialCells(xlConstants, 23)) ' Check the cells with formulas Call CheckCells(Selection.SpecialCells(xlFormulas, 23)) End Sub
Using Object Variables
Writing VBA procedures for Excel essentially involves manipulating objects. Excel itself is an object (called Application) and it contains more than 100 additional objects. Here's an example of using an object (in this case, a range object). This statement assigns the value in a cell to a variable named Dependents.
Dependents = Workbooks("Taxbook"). _ Worksheets("Sheet1").Range("A5").Value
In this example, Dependents is a "normal" variable (not an object variable). After this assignment is made, your code can use the Dependents variable anyway it chooses. However, if your code changes the value of the Dependents variable, the change will not be made to cell A5. An object variable is a variable that serves as a substitute for the actual object. To create an object variable, use the Set keyword. The statements below create an object variable for the Range object:
Dim Dependents as Range Set Dependents = Workbooks("Taxbook").Worksheets("Sheet1") _ .Range("A5")
After this assignment is made, you can use the Dependents variable as if it were the actual object. This includes modifying its properties and using its methods. For example, you can change the value stored in cell A5 with the following statement:
Dependents.Value = 3
Advantages of Using Object Variables
So what is the advantage of using object variables? There are two:
- Your code will be easier to understand and write
- Your code might run slightly faster
After all, typing:
is much faster than typing:
And, you'll probably agree that the code is much more meaningful. The speed advantage of using object variables may not be apparent, but if you perform timed tests you will find that working with object variables is almost always a bit faster than working with the objects themselves. One rule of thumb is to eliminate the number of "dots" processed. The first statement below contains three dot operators. The second statement contains only one dot.
Workbooks("Taxbook").Worksheets("Sheet1").Range("A5").Value = 3 Dependents.Value = 3
Referring To Ranges In Your VBA Code
Newcomers to VBA are often confused about how to refer to ranges on a worksheet. This confusion is somewhat justified, because VBA offers several different ways to refer to ranges. In this document I provide an overview of these techniques:
- Referring to ranges directly
- Referring to ranges by using the Cells method
- Referring to ranges by using the Offset method
Referring to ranges directly
Perhaps the most common way to refer to a range on a worksheet is to specify the range directly. Here's an example that assigns the value 1 to range A1:C12 on Sheet1 in a workbook named MyBook:
Workbooks("MyBook").Sheets("Sheet1").Range("A1:C12").Value = 1
Notice that this is a fully qualified reference. This statement will work regardless of which sheet is active. If Sheet1 is the active sheet, the statement can be simplified as:
Range("A1:C12").Value = 1
If the range has a name, you can use the name in your statement:
Range("myrange").Value = 1
You can also refer to a range by specifying the upper left and the lower right cell. Here's an example that assigns a value to all cells in the range A1:D12 on the active worksheet.
Range(Range("A1"), Range("D12")).Value = 99
Referring to ranges by using the Cells method
In Excel, the Range object has a method called Cells. Note that Cells is a method - not an object. When the Cells method is evaluated, it returns an object (specifically, a Range object).
The Cells method takes two arguments: the row and the column. The following statement assigns the value 1 to cell C2 on Sheet1:
Worksheets("Sheet1").Cells(2,3).Value = 1
You can also use the Cells method to refer to a larger range. The following statement assigns the value 1 to A1:J12 on the active worksheet:
Range(Cells(1,1), Cells(12,10)).Value = 1
In the preceding examples, the arguments for Cells were actual numbers. The advantage of using the Cells method becomes apparent when you use variables as the arguments. The subroutine below fills a 10X10 range (rowwise) with consecutive numbers from 1 to 100.
Sub FillRange() Num = 1 For Row = 1 To 10 For Col = 1 To 10 Sheets("Sheet1").Cells(Row, Col).Value = Num Num = Num + 1 Next Col Next Row End Sub
Referring to ranges by using the Offset method
The Offset method is another useful way to refer to ranges. The Offset method returns a Range object, and takes two arguments. The first argument represents the number of rows to offset; the second represents the number of columns to offset.
The following statement assigns the value 1 to the cell that is one row below cell C2 and two cells to the right of C2 (i.e., cell E3):
Range("C2").Offset(1,2).Value = 1
The Offset method is most useful when the arguments are variables, rather than numbers. The subroutine below fills a 10X10 range (rowwise) with consecutive numbers from 1 to 100.
Sub FillRange2() Num = 1 For Row = 0 To 9 For Col = 0 To 9 Sheets("Sheet1").Range("A1").Offset(Row,Col).Value = Num Num = Num + 1 Next Col Next Row End Sub
Understanding Object Parents
When working in VBA, it's important to understand the object model for the application you're using. More specifically, a good knowledge of how various objects relate to each other can often simplify your coding.
Excel's object model is a hierarchy - objects are contained in other objects. At the top of the hierarchy is the application objects (Excel itself). Excel contains other objects, and these objects contain other objects, and so on. The following depicts how a Range objects fits into this scheme.
Application Object (Excel) Workbook Object Worksheet Object Range Object
In the lingo of object-oriented programming, a Range object's parent is the Worksheet object that contains it. A Worksheet object's parent is the Workbook that contains the worksheet. And, a Workbook object's parent is the Application object.
The SheetName Function
How can this information be put to use? Examine the VBA function below. This function, which can be used in a worksheet formula, accepts a single argument (a range) and returns the name of the worksheet that contains the range. It uses the Parent property of the Range object. The Parent property returns an object -- the object that contains the range object.
Function SheetName(ref) As String SheetName = ref.Parent.Name End Function
The WorkbookName Function
The next function, WorkbookName, returns the name of the workbook. Notice that is uses the Parent property twice - the first Parent property returns a Worksheet object; the second Parent property returns a Workbook object.
Function WorkbookName(ref) As String WorkbookName = ref.Parent.Parent.Name End Function
The AppName Function
The function below carries this to the next logical level, accessing the Parent property three times. This function returns the name of the Application object.
Function AppName(ref) As String AppName = ref.Parent.Parent.Parent.Name End Function
I've come to the conclusion that a thorough understanding of Excel's object model is absolutely essential for anyone who needs to write non-trivial Excel macros. Study the online help, study VBA code that others have written, and explore the Object Browser. No doubt about it, things can be very confusing - but eventually the pieces fall into place.
And there's an excellent side benefit: Once you figure out how objects work,
you'll be able to apply your knowledge very easily to other applications that
support VBA. All you need to do is spend some time getting acquainted with the
apps's object model.
Working With Variable-Size Ranges
A companion file is available: Click here to download
In many situations, your VBA procedure needs to work with a range that can vary in size. For example, you may have a worksheet that holds weekly sales data. Every week you add a new row to it. Creating a VBA procedure to manipulate the data can be tricky, since the size of the range will vary from week to week. This document describes a number of VBA techniques that are useful when working with ranges that have an unknown size.
The CurrentRegion Property
If you need to work with an entire range of cells (such as a worksheet database, or list), the CurrentRegion property will be useful. This property returns a range object. To understand how this works, experiment with the Current Region option in the Go To Special dialog box (press F5, then click Special).
The statement below selects the current region for cell A1.
The End Property
You are probably familiar with Excel's keystrokes that let you move or select to the end of a row or column. For example, to move the cell pointer to the last nonblank cell in a column, you press Ctrl+Down Arrow (or, End followed by Down Arrow). To select cells from the active cell down to the first blank cell in the column, you press Ctrl+Shift+Down Arrow (or, End followed by Shift+Down Arrow).
If you record a VBA macro using these keystrokes, you may be surprised that the result isn't what you expect. Rather the record code to simulate these key combinations, the recorder simply records the actual cell addresses. However, VBA contains a useful property (End) that provides the equivalent of this type of action. The statement below selects cells from the active cell down to the last non-empty cell in the column.
In this case, xlDown is a built-in constant. As you may expect, there are three other constants to simulate key combinations in the other directions: xlUp, xlToLeft and xlToRight.
Following are VBA procedures that perform a wide variety of cell selections.
Sub SelectDown() ' Like Ctrl+Shift+Down Range(ActiveCell, ActiveCell.End(xlDown)).Select End Sub Sub SelectUp() ' Like Ctrl+Shift+Up Range(ActiveCell, ActiveCell.End(xlUp)).Select End Sub Sub SelectToRight() ' Like Ctrl+Shift+Right Range(ActiveCell, ActiveCell.End(xlToRight)).Select End Sub Sub SelectToLeft() ' Like Ctrl+Shift+Left Range(ActiveCell, ActiveCell.End(xlToLeft)).Select End Sub Sub SelectCurrentRegion() ' Like Ctrl+Shift+* ActiveCell.CurrentRegion.Select End Sub Sub SelectActiveArea() ' Like End, Home, Ctrl+Shift+Home Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select End Sub Sub SelectActiveColumn() ' Contiguous Cells in ActiveCell's Column If IsEmpty(ActiveCell) Then Exit Sub ' ignore error if activecell is in Row 1 On Error Resume Next If IsEmpty(ActiveCell.Offset(-1, 0)) Then Set TopCell = ActiveCell Else Set TopCell = ActiveCell.End(xlUp) End If If IsEmpty(ActiveCell.Offset(1, 0)) Then Set BottomCell = ActiveCell Else Set BottomCell = ActiveCell.End(xlDown) End If Range(TopCell, BottomCell).Select End Sub Sub SelectActiveRow() ' Contiguous Cells in ActiveCell's Row If IsEmpty(ActiveCell) Then Exit Sub ' ignore error if activecell is in Column A On Error Resume Next If IsEmpty(ActiveCell.Offset(0, -1)) Then Set LeftCell = ActiveCell Else Set LeftCell = ActiveCell.End(xlToLeft) End If If IsEmpty(ActiveCell.Offset(0, 1)) Then Set RightCell = ActiveCell Else Set RightCell = ActiveCell.End(xlToRight) End If Range(LeftCell, RightCell).Select End Sub Sub SelectEntireColumn() ' Like Ctrl+Spacebar Selection.EntireColumn.Select End Sub Sub SelectEntireRow() ' Like Shift+Spacebar Selection.EntireRow.Select End Sub Sub SelectEntireSheet() ' Like Ctrl+A Cells.Select End Sub Sub ActivateNextBlankDown() ' Next Blank Cell Below ActiveCell.Offset(1, 0).Select Do While Not IsEmpty(ActiveCell) ActiveCell.Offset(1, 0).Select Loop End Sub Sub ActivateNextBlankToRight() ' Next Blank Cell To the Right ActiveCell.Offset(0, 1).Select Do While Not IsEmpty(ActiveCell) ActiveCell.Offset(0, 1).Select Loop End Sub Sub SelectFirstToLastInRow() ' Select From the First NonBlank to the Last Nonblank in the Row Set LeftCell = Cells(ActiveCell.Row, 1) Set RightCell = Cells(ActiveCell.Row, Columns.Count) If IsEmpty(LeftCell) Then Set LeftCell = LeftCell.End(xlToRight) If IsEmpty(RightCell) Then Set RightCell = RightCell.End(xlToLeft) If LeftCell.Column = Columns.Count And RightCell.Column = 1 Then ActiveCell.Select Else Range(LeftCell, RightCell).Select End If End Sub Sub SelectFirstToLastInColumn() ' Select From the First NonBlank to the Last Nonblank in the Column Set TopCell = Cells(1, ActiveCell.Column) Set BottomCell = Cells(Rows.Count, ActiveCell.Column) If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown) If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp) If TopCell.Row = Rows.Count And BottomCell.Row = 1 Then ActiveCell.Select Else Range(TopCell, BottomCell).Select End If End Sub
VBA Debugging Tips
If you've worked with VBA for any length of time, you know that it's relatively easy to make errors. In fact, most VBA programmers can do so with very little effort on their part. The process of locating and correcting errors in your VBA code is known as debugging.
Types of Errors
There are basically three types of errors that can occur:
- Syntax errors:A variety of errors related to entering the code itself. These include incorrectly spelled keywords, mismatched parentheses, and a wide variety of other errors. Excel flags your syntax errors and you can't execute your code until they are correct.
- Run-time errors:
These are the errors that occur while your code is executing. There are many, many types of run-time errors. For example, if your code refers to an object that doesn't exist, you'll get a run-time error. Excel displays a message when there is a run-time error.
- Logical errors:
These are errors that occur through faulty programming. Logical errors may or may not cause a run-time error. In many cases they will simply produce incorrect results.
Debugging your code is the process of finding and correcting run-time errors and logical errors.
Eight Bug Reduction Tips
I can't help you completely eliminate bugs in your programs, but I can provide a few tips that will help you keep them to a minimum.
- Use an Option Explicit at the beginning of your module. Doing so will require that you define the data type for every variable that you use. It's a bit more work, but you'll avoid the common error of misspelling a variable name.. And there's a nice side benefit: Your routines will often run faster.
- Format your code with indentation. I've found that using indentation to delineate code segments is quite helpful. If you have several nested For...Next loops, for example, consistent indentation will make it much easier to keep track of them all.
- Be careful with On Error Resume Next. This statement causes Excel to ignore any errors and continue. In some cases, using this statement will cause Excel to ignore errors that shouldn't be ignored. Your may have bugs and not even realize it.
- Use lots of comments. Nothing is more frustrating than revisiting code that you wrote six months ago - and not having a clue as to how it works. Adding a few comments to describe your logic can save you lots of time down the road.
- Keep your subroutines and functions simple. Writing your code in smaller modules, each of which has a single, well-defined purpose, makes it much easier to debug them.
- Use the macro recorder to help you identify properties and methods. If I can't remember the name or syntax of a property or method, I've found that it's often quicker to simply record a macro and look at the recorded code.
- Consider a different approach. If you're having trouble getting a particular routine to work correctly, you might want to scrap the idea and try something completely different. In most cases, Excel offers several alternative methods of accomplishing the same thing.
- Understand Excel's debugger. Although it can be a bit daunting at first, you'll find that Excel's debugger is an excellent tool. Invest some time and get to know it. I used VBA for quite a while before I took the time to learn how the debugger works (it's well documented in the on-line help). I spent about an hour learning the details, and I estimate that it has saved me dozens of hours in wasted time.
Selecting The Maximum Value In A Range
This tip describes a simple utility that activates the worksheet cell that contains the maximum value. The VBA routine determines the maximum value in the selected range. If a single cell is selected, it determines the maximum value for the entire worksheet. Next, it use the Find method to locate the value and select the cell.
To use this subroutine:
- Copy the code below to a VBA module. You can also store it in your Personal Macro Workbook, or create an add-in.
- If you want to search for the maximum value in a specific range, select the range. Otherwise, select any single cell.
- Execute the GotoMax subroutine.
The GoToMax Subroutine
Sub GoToMax() ' Activates the cell with the largest value Dim WorkRange as Range ' Exit if a range is not selected If TypeName(Selection) <> "Range" Then Exit Sub ' If one cell is selected, search entire worksheet; ' Otherwise, search the selected range If Selection.Count = 1 Then Set Workrange = Cells Else Set Workrange = Selection End If ' Determine the maximum value MaxVal = Application.Max(Workrange) ' Find it and select it On Error Resume Next Workrange.Find(What:=MaxVal, _ After:=Workrange.Range("A1"), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False _ ).Select If Err <> 0 Then MsgBox "Max value was not found: " & MaxVal End Sub
A companion file is available: Click here to download
The number of permutations of a string is equal to the factorial of the length
of the string. For example, the word DOG has a length of three -- which means
that the letters can be rearranged in six different ways: DOG, DGO, ODG OGD, GDO,
and GOD. The number of permutations quickly gets unwieldy. The table below lists
the number of permutations for strings of various sizes.
This tip describes how to generate all permutations from a string. It uses a recursive subroutine to do the work. The source of this algorithm is not known (I was browsing through some old files on my hard drive and discovered it).
The GetString subroutine prompts the user for a string. If the length of the string is greater than 1 and less than 8, the GetPermutations subroutine is called --which then calls itself. The permutations are stored in column A of the worksheet.
Dim CurrentRow Sub GetString() Dim InString As String InString = InputBox("Enter text to permute:") If Len(InString) < 2 Then Exit Sub If Len(InString) >= 8 Then MsgBox "Too many permutations!" Exit Sub Else ActiveSheet.Columns(1).Clear CurrentRow = 1 Call GetPermutation("", InString) End If End Sub Sub GetPermutation(x As String, y As String) ' The source of this algorithm is unknown Dim i As Integer, j As Integer j = Len(y) If j < 2 Then Cells(CurrentRow, 1) = x & y CurrentRow = CurrentRow + 1 Else For i = 1 To j Call GetPermutation(x + Mid(y, i, 1), _ Left(y, i - 1) + Right(y, j - i)) Next End If End Sub
A companion file is available: Click here to download
This tip describes a useful technique that lets you display a series of help topics in an Excel workbook.
How it works
The technique described here uses a UserForm, and the UserForm has the following controls (see the Figure below):
- A DropDown control (which displays the current help topic title)
- A Label control that displays the help topic text
- A Frame control. The label is inside of the frame to allow scrolling if all of the text isn't visible.
- Three buttons: Previous, Next, and Exit
The help topics are contained on a worksheet, and the VBA code simply reads the text in the worksheet and transfers it to the UserForm. The user can select a help topic from the DropDown, or view the topics sequentially using the Previous and Next buttons.
You can easily adapt this technique to your own project:
- Export the UserForm and Import it to your project
- Set up the worksheet with the help topics and help text
- Create a simple macro to display the UserForm
Pros and Cons
This technique is very easy to set up, and requires no additional software or files. The VBA code is contained in the UserForm, so no additional programming is necessary. The primary disadvantage is that you cannot format the text that is displayed.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks