Selecting All Unlocked Cells

Category: General VBA | [Item URL]

As you probably know, when a worksheet is protected, only the unlocked cells can be changed. You may want to apply different formatting to the unlocked cells, or simply ensure that the correct cells are indeed unlocked.

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

Category: General VBA | [Item URL]

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

Category: General VBA | [Item URL]

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:

  1. Your code will be easier to understand and write
  2. Your code might run slightly faster

After all, typing:

  Dependents.Value

is much faster than typing:

  Workbooks("Taxbook").Worksheets("Sheet1").Range("A5").Value

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

Category: General VBA | [Item URL]

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

Category: General VBA | [Item URL]

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

Learning More

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

Category: General VBA | [Item URL]

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.

Range("A1").CurrentRegion.Select 

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.

Range(ActiveCell, ActiveCell.End(xlDown)).Select 

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.

VBA Code

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

Category: General VBA | [Item URL]

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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. 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

Category: General VBA | [Item URL]

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:

  1. Copy the code below to a VBA module. You can also store it in your Personal Macro Workbook, or create an add-in.
  2. If you want to search for the maximum value in a specific range, select the range. Otherwise, select any single cell.
  3. 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 


Generating Permutations

Category: General VBA | [Item URL]

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.

Characters Permutations
1 1
2 2
3 6
4 24
5 120
6 720
7 5,040
8 40,320
9 362,880
10 3,628,800
11 39,916,800
12 479,001,600

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

VBA Listing

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


Displaying Help

Category: General VBA / UserForms | [Item URL]

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:

  1. Export the UserForm and Import it to your project
  2. Set up the worksheet with the help topics and help text
  3. 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.



Page 4 of 17 pages
[Previous page]   [Next page]

Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

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

© Copyright 2017, J-Walk & Associates, Inc.
Privacy Policy