Advertisement

Programming the Microsoft Excel Range Object

  • Flavio Morgado
Chapter

Abstract

It can represent any number of cells, and in this chapter you will learn about the Range object and how to programmatically interact with it using VBA and its many properties and methods (it has no events), using some of the numerous Worksheet object methods to automate your worksheet and give it a professional look and feel.

Keywords

Object Property Event Fire Object Variable Function InStr Active Sheet 
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.

The Microsoft Excel Range object is where the real action of your worksheet applications takes place. It can represent any number of cells, and in this chapter you will learn about the Range object and how to programmatically interact with it using VBA and its many properties and methods (it has no events), using some of the numerous Worksheet object methods to automate your worksheet and give it a professional look and feel. You can obtain all the procedure code in this chapter by downloading the Chapter05.zip file from the book’s Apress.com product page, located at www.apress.com/9781484222041 , or from http://www.ProgrammingExcelWithVBA.4shared.com .

The Range Object

To the Microsoft Excel object model, a range is any number of worksheet cells, and since it is the basic worksheet unit, it is referenced by different Collection objects, returned as an argument from many Excel Worksheet, Workbook, and Application object events, properties, and methods. Table 5-1 shows some Excel Application properties and Workbook collections that return a Range object.
Table 5-1.

Excel Collection and Object Properties and Methods That Return a Range Object

Object

Value

Used to

Application .ActiveCell

Property

Returns a Range object representing the active cell

Application .Range

Property

Returns a Range object referenced by its address or name

Application .Selection

Property

Returns the selected object in the active window, which can be a Range object

Application .ThisCell

Property

Returns a Range object representing the cell from which the user-defined function is being called as a Range object

Range. Offset

Property

Returns another Range object offset from the current Range object

Workbook . Names

Collection

Stores workbook named ranges

Worksheet. Names

Collection

Stores worksheet named ranges

Worksheet.Cells

Property

Returns a Range object referenced by cell Row and Column numbers

Worksheet.Range

Property

Returns a Range object referenced by its address or name

To programmatically deal with the Range object using VBA, you must first declare a Range object variable, initialize it with the Set keyword, and then use it to deal with the Range object properties and methods, as follows:

Dim rg as Range
Set rg = Application .ActiveCell

Table 5-2 shows some of the most important properties of the Range object.
Table 5-2.

Some of the Most Important Microsoft Excel Range Object Properties

Range Object Property

Value

Used to

Address

String

Returns the range reference

AllowEdit

Boolean

Determines whether the range can be edited on a protected worksheet

Application

Application

Returns an Application object representing the Microsoft Excel windows

Cells

Range

Returns a Range object representing the cells in the specified range

Column

Long Integer

Returns the number of the first column in the first area of the range

Columns

Range

Returns a Range object representing all columns in the range

Count

Long Integer

Returns the number of cells in the range

CountLarge

Decimal

Returns the number of cells in the range for .xlsx workbooks

CurrentArray

Range

If the specified range is part of an array, returns a Range object representing the entire array

CurrentRegion

Range

Returns a Range object representing the current region

End

Range

Returns a Range object representing the cell at the end of the region that contains the source range

EntireColumn

Range

Returns a Range object representing the entire column (or columns) that contains the specified range

EntireRow

Range

Returns a Range object representing the entire row (or rows) that contains the specified range

Formula

String

Returns or sets the cell’s formula in A1-style

FormulaArray

Boolean

Returns or sets the array formula of a range

FormulaHidden

Boolean

Determines whether the formula will be hidden when the worksheet is protected

HasArray

Boolean

Determines whether the specified cell is part of an array formula

HasFormula

Boolean

Determines whether all cells in the range contain formulas

Hidden

Boolean

Determines whether the rows or columns are hidden

Item

Range

Returns a Range object representing a range at an offset from the specified range

ListHeaderRows

Long Integer

Returns the number of header rows for the specified range

Locked

Boolean

Determines whether the object is locked

MergeArea

Range

Returns a Range object representing the merged range containing the specified cell

MergeCells

Boolean

Determines whether the range or style contains merged cells

Name

String

Returns or sets the name of the referenced range

Offset

Range

Returns a Range object representing a range that’s offset from the specified range

Parent

Worksheet

Returns the Worksheet that is the parent of the specified range

Range

Range

Returns a Range object representing a range address

Resize

Range

Resizes the specified range (does not resize a named range)

Row

Long Integer

Returns the number of the first row of the first area in the range

Rows

Range

Returns a Range object representing the rows in the specified range

Value

Variant

Default property; returns or sets the value of the specified range

Value2

Variant

Returns or sets the cell value of the specified range; discards Currency and Data formatting options, returning the range pure value

Worksheet

Worksheet

Returns a Worksheet object representing the worksheet containing the specified range

Attention

Search the Internet with the keywords Range properties or Range methods to find a complete list of Excel Range object properties. Table 5-2 and 5-3 come from the following location on the Microsoft MSDN web site:
Table 5-3.

Some Important Microsoft Excel Range Object Methods

Range Object Method

Action Performed

Activate

Activates the upper-left cell of the range

AdvancedFilter

Filters or copies data from a list based on a criteria range

ApplyNames

Applies names to the cells in the specified range

AutoFilter

Filters a list using AutoFilter

AutoFit

Changes the width of the columns or the height of the rows in the range to achieve the best fit

AutoFormat

Automatically formats the specified range, using a predefined format

AutoOutline

Automatically creates an outline for the specified range

BorderAround

Adds a border to a range and sets the Color, LineStyle, and Weight properties for the new border

Calculate

Calculates a specified range of cells on a worksheet

Clear

Clears the entire range

ClearComments

Clears all cell comments from the specified range

ClearContents

Clears the formulas from the range

ClearFormats

Clears the formatting of the object

ClearHyperlinks

Removes all hyperlinks from the specified range

ClearNotes

Clears notes and sound notes from all the cells in the specified range

ClearOutline

Clears the outline for the specified range

Consolidate

Consolidates data from multiple ranges on multiple worksheets into a single range on a single worksheet

Copy

Copies the range to the specified range or to the clipboard

CreateNames

Creates names in the specified range, based on text labels in the sheet

Cut

Cuts the range values to the clipboard

Delete

Deletes the range values

Find

Finds specific information in a range and returns a Range object representing the first cell where that information is found

FindNext

Continues a search to the next cell that was begun with the Find method

FindPrevious

Continues a search to the previous cell that was begun with the Find method

ListNames

Pastes a list of all displayed names onto the worksheet, beginning with the first cell in the range

Merge

Creates a merged cell from the specified Range object

PasteSpecial

Pastes a range that was copied or cut from the clipboard into the specified range

Replace

Returns a Boolean indicating characters in cells within the specified range

Select

Selects the specified range

Sort

Sorts a range

SpecialCells

Returns a Range object that represents all the cells that match the specified type and value

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range_properties(v=office.15).aspx

Attention

The Range object has a lot of other formatting properties relating to the appearance of the worksheet cells and its contents. Use the MSDN web site to access all the Range object properties.

The Range object has also a lot of methods that you will use to perform actions using VBA code. Table 5-3 lists some of the most important Range object methods.

Using the Application .Range Property

The easiest way to access any cell value is using the Application object’s Range property. Since the Application object is the top-level object in the Microsoft Excel object hierarchy, you do not need to type it when using the Range property, which has this syntax:

Range (Cell1, Cell2)

In this code:
  • Cell1: This is required; it is the range address or name between double quotes. It can include the range operator (a colon), the intersection operator (a space), or the union operator (a comma). If it includes dollar signs, they will be ignored.

  • Cell2: This is optional; use it when both Cell1 and Cell2 are valid Range objects identifying the cell in the upper-left and lower-right corners of the desired range, respectively. Both Cell1 and Cell2 Range objects can contain a single cell, an entire column, or an entire row, or it can be a string that names a single cell.

The default property of the Range object is the Value property, so when you use the Application .Range property and pass it the address of the desired cell, you will receive the cell value. If you pass it a range of cells, you will receive a variant with an array of values that cannot be printed in the VBA Immediate window (which will return the code Error=13, “Type Mismatch”).

Attention

When used without an object qualifier, the Application .Range property is a shortcut for ActiveSheet.Range, returning a range from the active sheet. If the active sheet isn’t a worksheet, the property fails.

Also note that when applied to a Range object, the property is relative to the Range object. For example, if the selection is cell C3, then Selection.Range("B1") returns cell D3 because it’s relative to the Range object returned by the Selection property. On the other hand, the code ActiveSheet.Range("B1") always returns cell B1.

Figure 5-1 shows what happens when you use the Application .Range property (with or without the Application qualifier) and the Application .Selection property to return cell A1’s value, which contains the formula =Today( ), returning the current system date. Also note that the Range property returns the Value property of the range, and when you use the Value2 property, VBA does not return the expected formatted date value but the real number stored into the cell. In this case, this is the integer value that represents the date (days counting from 1-1-1900). You can also use other Range object properties, like HasFormula (which indicates whether the range has a formula), and use the Formula property to return its formula (if any).
Figure 5-1.

Use the Application .Range property or the Application .Selection property (with or without the Application qualifier) to recover the current cell value. Note that the Application .Range property is a shortcut to the ActiveSheet.Range property, meaning that if the selected sheet is a Chart sheet, the method will fail and VBA will raise an error

Attention

Use the Application .Selection property when you do need to know what is currently selected on the active sheet of Microsoft Excel . To know what is the cell address, use the Range. Address property (the ActiveSheet.Name property returns the worksheet name of the selected sheet).

Use Application .Range whenever you want to know the contents of a specific cell. To know a specific worksheet cell value, precede the cell address with the sheet name (tab name) between single quotes and an exclamation character (with everything inside double quotes), as follows (Figure 5-2):
Figure 5-2.

To verify a specific worksheet cell value, type a string that contains the sheet name inside single quotes, followed by an exclamation character and the cell address

?Range("'Sheet1'!A1")

Let’s see how to program some Range object methods and properties.

Using Range Object Properties and Methods

You can get the information from the selected cell range with VBA using two different sources: from the Application .Selection property or from the Target argument returned by some events raised by the Worksheet, Workbook, or Application objects.

They return the same information about what is selected inside Excel, differing only by the fact that the first is based on some user action and the last is gathered from the permanent state of the active sheet.

The Range Properties.xlsm macro-enabled workbook, which you can extract from Chapter05.zip, has the frmRange UserForm, which you can use to learn how to implement some Range object properties and methods using both the Application .Selection event and the Target argument from some Application object events.

When you open this workbook, the ThisWorkbook object’s Workbook _ Open ( ) event fires and loads frmRange in a nonmodal state (meaning that you can interact with the sheet tabs while the UserForm is opened, as shown in Figure 5-3).
Figure 5-3.

This is the frmRange UserForm from Range Properties.xlsm macro-enabled workbook, where you can learn how to implement some Range object properties and methods

Private Sub Workbook _Open ()
    frmRange .Show False
End Sub

Using the same programming technique described so far this book, the frmRange UserForm declares the module-level variable WithEvents mApp as Application and initializes it on the UserForm_Initialize ( ) event so it can catch cell range information whenever the user selects any cell range (using the Application .SheetSelectionChange( ) event) or selects another sheet tab (using the Application .SheetActivate( ) event) on any opened workbook.

Note that the frmRange UserForm interface has the range information updated with the aid of the Sub UpdateInterface( ) procedure (called from these three events):

Option Explicit
Dim WithEvents mApp As Application
Private Sub UserForm_Initialize ()
     Set mApp = Application
     Call UpdateInterface
End Sub
Private Sub mApp_SheetActivate(ByVal Sh As Object)
    Call UpdateInterface(Sh, Application .Selection)
End Sub
Private Sub mApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Call UpdateInterface(Sh, Target)
End Sub

Attention

Note that Sub UpdateInterface( ) receives two optional arguments used just by the mapp_SheetActivate( ) and mApp_SheetSelectionChange( ) events.

Once frmRange is loaded, whenever you select another cell’s range on any workbook sheet tab, a lot of range information is displayed on the frmRange UserForm, like the sheet tab name, range address, range values, selected cells, rows and columns count, rows number, and column letters of the range areas selected.

Figure 5-4 shows what happened to the frmRange UserForm interface when more than one noncontiguous cells range was selected by keeping the Ctrl key pressed while the mouse was dragged over the worksheet cells. Note that the Range Areas text box indicates the number of different ranges selected and that the lstAreas ListBox at its right shows the Area Index and Address information.
Figure 5-4.

When you select any cell range, the frmRange UserForm catches information of the selected cells, like the sheet tab name, the range address selected, how many range areas were selected, and describes each area index and address; how many cells, rows, and columns were selected; and the rows number and columns letters selected

Updating the UserForm Interface

The Private Sub UpdateInterface( ) procedure is responsible for synchronizing the information about the cell range selected in the Excel active sheet in the frmRange UserForm. It does this by executing this code:

Private Sub UpdateInterface(Optional Sh As Object, Optional Target As Range)
    Dim rg As Range
    Dim varItem As Variant
    Dim strRows As String
    Dim strColumns As String
    Dim strValues As String
    Dim intI As Integer
    Dim fComma As Boolean
    If Me .optEventTarget Then
        Set rg = Target
        Me .txtActiveSheet = Sh.Name
    Else
        Set rg = Application .Selection
        Me .txtActiveSheet = rg.Worksheet.Name
    End If
    Me .txtRowsSelected = ""
    Me .txtColumnsSelected = ""
    Me .txtValue = ""
    Me .txtAddress = rg.Address
    Me .txtCellsCount = rg.CountLarge
    Me .txtRowsCount = rg.Rows.CountLarge
    Me .txtColumnsCount = rg.Columns.CountLarge
    If rg.Cells.CountLarge > 1000 Then
        MsgBox "Too much cells selected!", vbCritical, "Select less cells"
    Else
        For Each varItem In rg.Rows
            If fComma Then
                strRows = strRows & ", "
            End If
            strRows = strRows & varItem.Row
            fComma = True
        Next
        fComma = False
        For Each varItem In rg.Columns
            If fComma Then
                strColumns = strColumns & ", "
            End If
            strColumns = strColumns & ColumnNumberToLetter(varItem.Column )
            fComma = True
        Next
        fComma = False
        For Each varItem In rg
            If fComma Then
                strValues = strValues & ", "
            End If
            strValues = strValues & varItem.Address (False, False) & "=" & varItem.Value
            fComma = True
        Next
        Me .txtRowsSelected = strRows
        Me .txtColumnsSelected = strColumns
        Me .txtValue = strValues
    End If
    Me .txtRangeAreas = rg.Areas .Count
    Me .cmdResize.Enabled  = (rg.Areas .Count = 1)
    Me .lstAreas.Clear
    For intI = 1 To Selection.Areas .Count
        Me .lstAreas.AddItem intI
        Me .lstAreas.Column (1, lstAreas.ListCount - 1) = rg.Areas (intI).Address
    Next
End Sub

Note that the procedure may receive two optional arguments (Sh as Object and Target as Range) so it can receive information about the worksheet and cell range affected by the user action whenever the mApp_SheetActivation( ) and mapp_SheetSelectionChange( ) events fire, or it can use the Application .Selection property, which also returns a Range object that reflects the cells selected on the active sheet.

To show that both objects represent the same thing, the procedure begins by declaring the rg as Range variable to represent the selected range.

Private Sub UpdateInterface(Optional Sh As Object, Optional Target As Range)
    Dim rg As Range

If the “Event Target argument” option is selected on the UserForm bottom (optEventTarget OptionButton), the procedure uses the Target argument and the source of the selected range and defines the txtActiveSheet text box using the Name property of the Sh object.

If Me .optEventTarget Then
    Set rg = Target
    Me .txtActiveSheet = Sh.Name

But if the Application .Selection option is selected, the procedure uses the Application .Selection property as the source to the selected range, and once the range is defined, it uses its Worksheet.Name property to recover the sheet tab name.

Else
    Set rg = Application .Selection
    Me .txtActiveSheet = rg.Worksheet.Name
End If

Once it has a reference to the selected range, it clears the txtRowsSelected, txtColumnsSelected, and txtValues text boxes.

Me .txtRowsSelected = ""
Me .txtColumnsSelected = ""
Me .txtValue = ""

And then it recovers the Range. Address property, which indicates the cells currently selected.

Me .txtAddress = rg.Address

The Range. Address property has this syntax:

Range.Address ((RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)

In this code:
  • RowAbsolute: This is optional; it is a Boolean value indicating whether the row part of the reference must be an absolute reference. The default value is True.

  • ColumnAbsolute: This is optional; it is a Boolean value indicating whether the column part of the reference must be an absolute reference. The default value is True.

  • ReferenceStyle: This is optional; it indicates the reference style to be used: xlA1 or xlR1C1. The default value is xlA1.

  • External: This is optional; it indicates whether the reference must be local or external. Use True to return an external reference or False to a local reference. The default value is False.

  • RelativeTo: This is optional; it must be a Range object that defines the starting point to relative references when RowAbsolute and ColumnAbsolute are False and when ReferenceStyle=xlR1C1.

So, whenever the procedure uses rg. Address , it returns the range address of the selected cells using absolute references (look to the Range Address text box of Figures 5-3 and 5-4).

To return how many cells are used by the selected range, you can use the Count or CountLarge property of the Range object.

Me .txtCellsCount = rg.CountLarge

To return how many rows and columns are selected, use the Rows or Columns collection’s Count or CountLarge property of the Range object.

Me .txtRowsCount = rg.Rows.CountLarge
Me .txtColumnsCount = rg.Columns.CountLarge

Attention

The CountLarge property was introduced in the Excel object model for the .xlsx file of Excel 2007 or newer versions, which has 1,048,576 rows x 1,024 columns = 1,073,741,824 possible cells (compared to the 65,536 rows x 256 columns = 16,777,216 possible cells of Excel 2003 or older versions). Excel 2003 and older versions don’t recognize the CountLarge property, while Excel 2007 or newer versions will raise an error if you use the Count property to select more rows, columns, and cells than available to Excel 2003.

Since UpdateInterface( ) returns the selected range values, it has a provision to avoid selecting more than 1,000 cells using the CountLarge property (since the user can select the gray square at the right of column A header—above the row 1 header—to select all worksheet cells).

If rg.Cells.CountLarge > 1000 Then
    MsgBox "Too much cells selected!", vbCritical, "Select less cells"
Else

Getting the Rows Used by the Selected Range

If fewer than 1,000 cells are selected, every row number selected is retrieved using the Range.Row property and stored in the strRows String variable. The procedure uses a For Each…Next loop to run through all selected rows inside the Range.Rows property (which behaves like a collection).

For Each varItem In rg.Rows

Inside the For Each… Next loop, the fComma Boolean variable is used to verify the need to add a comma to the strRows String variable, which is needed before the second row number is added. Note that fComma becomes true after the first row is processed.

    If fComma Then
       strRows = strRows & ", "
    End If
    strRows = strRows & varItem.Row
    fComma = True
Next

Getting the Columns Used by the Selected Range

When the first For Each…Next loop ends, the procedure turns fComma = False and begins a second For Each… Next loop through the Range.Columns property (which also behaves like a collection) to retrieve all the columns used by the selected range.

fComma = False
For Each varItem In rg.Columns
    If fComma Then
       strColumns = strColumns & ", "
    End If

The Range. Column property returns the column number, and you need to turn this value into a column letter, which is made by the Function ColumnNumberToLetter( ) procedure before storing it into the strColumns string variable:

    strColumns = strColumns & ColumnNumberToLetter(varItem. Column )
    fComma = True
Next

Changing a Column Number to a Letter

There are a lot of algorithms on the Internet destined to transform the Range. Column Integer value to the associated column letter, but you don’t need them. Use the Application .Cells( ) property instead, which returns a Range object for a given row and column number and has this syntax:

Application .Cells (RowIndex, ColumnIndex)

In this code:
  • RowIndex: This is required; it is a long integer positive value indicating the row number of the reference.

  • ColumnIndex: This is required; it is a long integer positive value indicating the column number of the reference.

Since the Cells( ) property returns a range object, use the Range object’s Address property to return the cell address for row = 1 and the desired column number, using this syntax:

strColumn = Cells(1, rg.Column ).Address (False, False)

Once the address is returned, just take off the 1 row number at the end of the address and you will get the column letters. Note that the Address property is using False for its RowAbsolute and ColumnAbsolute arguments, forcing the address to be returned as a relative reference.

This is the code used by the Function ColumnNumberToLetter( ) of the basColumnNumberToLetter module:

Public Function ColumnNumberToLetter(Optional ColumnNumber As Variant) As String
    Dim strColumn As String
    If IsEmpty(ColumnNumber) Then
        ColumnNumber = Application .Selection.Column
    End If
    strColumn = Application .Cells(1, ColumnNumber).Address (False, False)
    ColumnNumberToLetter = Left (strColumn, Len(strColumn) - 1)
End Function

Did you get it? The ColumnNumberToLetter( ) Function declares the optional ColumnNumber as Variant argument. If the argument is missing, it receives the Application .Selection. Column property, which returns the column number of the selected cell in the Excel interface.

    If IsEmpty(ColumnNumber) Then
        ColumnNumber = Application .Selection.Column
    End If

It then uses the Application .Cells( ) property for the first row and desired column, which returns a Range object, and then uses the Range. Address property to return the relative reference to the desired cell.

The address returned will be relative to row 1, so the next instruction uses the VBA Left ( ) and Len( ) functions to get just the column letters, which are the value returned by the function.

    ColumnNumberToLetter = Left (strColumn, Len(strColumn) - 1)
End Function

Considerations About the Range Rows and Columns Properties

Before we continue, you must be aware of some discrepancies about the Range Rows and Columns properties, which behave like a collection.
  • The Count property of both the Rows and Columns properties return the row/column count for the first range, if more than one noncontiguous ranges are selected.

  • Both Rows and Columns properties may return repeated values of row and column numbers used by the selected ranges if you select more than one noncontiguous range that uses the same rows and columns.

Look again to Figures 5-3 and 5-4 and see for yourself. Figure 5-3 selects just the cells in the range A5:B6, while Figure 5-4 selects many other cell ranges. But the count value of both the Range.Rows and Columns properties continue to refer to cells A5:B6, while the selected row numbers and column letters have many duplicates.

Getting a Cell’s Address and Values for the Selected Range

Use the Range. Address (False, False) and Range.Value properties to get the relative cell addresses and values of the selected range, performing a For Each…Next loop through all cells of the Range object (note that the varItem Variant variable retrieves each cell in the range, which is also a Range object).

fComma = False
For Each varItem In rg
    If fComma Then
       strValues = strValues & ", "
    End If
    strValues = strValues & varItem. Address (False, False) & "=" & varItem.Value
    fComma = True
Next

And once all row numbers, column letters, and cell addresses and values have been retrieved, update the UserForm interface.

    Me .txtRowsSelected = strRows
    Me .txtColumnsSelected = strColumns
    Me .txtValue = strValues
End If

Getting Selected Range Areas

The UpdateInterFace( ) procedure ends by using the Range. Areas collection to know how many different noncontiguous ranges have been selected and the addresses of each one.

The Range. Areas .Count property is used to inform the Areas .Count property and to enable the cmdResize button (which can just be used when a single contiguous range is selected).

Me .txtRangeAreas = rg. Areas .Count
Me .cmdResize.Enabled  = (rg. Areas .Count = 1)

The lstAreas ListBox is used to show each Range. Area Index value and its associated Address property. So, it first clears the lstAreas ListBox with its Clear method and then performs a For…Next loop through all areas of the Range. Areas collection using intI as the Areas counting.

Me .lstAreas.Clear
For intI = 1 To Selection.Areas .Count

The lstAreas value has two columns. The Area Index (represented by the intI variable) is added to the lstAreas item value using its AddItem method.

Me .lstAreas.AddItem intI

The Areas . Address property is added to the second column of the same item of the lstAreas ListBox using intI to reference the desired area and using the lstAreas.ListCount-1 property to correctly reference the last added item.

        Me .lstAreas.Column (1, lstAreas.ListCount - 1) = rg.Areas (intI).Address
    Next
End Sub

Resizing the Selected Range

Use the Range. Resize property to add or delete cells rows and/or columns selected by the range. It has this syntax:

Range.Resize (RowSize, ColumnSize)

In this code:
  • RowSize: This is optional; it is a long integer positive value indicating the number of rows on the new range. If omitted, the number of rows remains the same.

  • ColumnSize: This is optional; it is a long integer positive value indicating the number of columns on the new range. If omitted, the number of rows remains the same.

When you select just one range and click the Resize CommandButton (cmdResize) of the frmRange UserForm, you will accept the values defined on the resized Rows and Columns text boxes (txtAddRows and txtAddColumns, usually = 1) to resize the selected range, executing this code:

Private Sub cmdResize_Click()
    Dim intRows As Integer
    Dim intCols As Integer
    intRows = Selection.Rows.Count + CInt(Me .txtAddRows)
    intCols = Selection.Columns.Count + CInt(Me .txtAddColumns)
    If intCols <= 0 Then intCols = 1
    Selection.Resize (intRows, intCols).Select
End Sub

Note that the procedure declares the intRows and intColumns variables to hold the new range row and column values. It uses the Application .Selection property (without the Application qualifier) to return the selected range and uses the Range.Rows.Count property to return how many rows are selected. It then adds the txtAddRows value to the row count selected, using the VBA CInt( ) function to convert the text box String value to Integer:

intRows = Selection.Rows.Count + CInt(Me .txtAddRows)

Since the txtAddRows value can be negative, the procedure verifies whether the intRows value is equal to or lower than zero. If it is, it is turned to 1 (meaning that the resized range will have at least one row).

If intRows <= 0 Then intRows = 1

The same steps are repeated to the intCols variable, and the resize method is executed. Note that when this is done, the new Range object returned by the Selection. Resize property is resized, but it is not selected on the worksheet. So, the Range.Selection property is also executed, so the new defined range is selected.

Selection.Resize (intRows, intCols).Select

When this is done, the “cascading event” phenomenon happens again, firing the Application _SheetSelectionChange( ) event, captured by the mApp module-level variable, which will call the UpdateInterface( ) procedure and update the UserForm interface (Figure 5-5).
Figure 5-5.

Use the Range.Resize property to resize the selection (or any other Range variable). Remember that after the resize operation, if you want to see the new range selected on the worksheet, you must use the Range.Select property (you can use negative values on the Rows and Columns text boxes at the right of the button)

Private Sub mApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Call UpdateInterface(Sh, Target)
End Sub

Attention

If Worksheet_SelectionChange ( ) or ThisWorkbook_SheetSelectionChange( ) are programmed, they will also fire at this moment.

Changing the Range Reference

By default the frmRange UserForm uses the Application .Selection property to return the selected range. This is necessary because when frmRange is opened, it fires the UserForm_Initialize ( ) event, which doesn’t have any argument and needs to catch the Selection range to update its interface.

Private Sub mApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Call UpdateInterface(Sh, Target)
End Sub

When you click the “Event Target argument” (optEventTarget) option, the optEventTarget_Click( ) event fires, executing this code:

Private Sub optEventTarget_Click()
    Dim rg As Range
    Set rg = Selection
    Application .EnableEvents = False
        Range("A1").Select
    Application .EnableEvents = True
    rg.Select
End Sub

Note that the code declares and uses the rg as Range variable to hold the current selection range, returned by the Application .Selection property.

Private Sub optEventTarget_Click()
    Dim rg As Range
    Set rg = Selection

Now that the selected range is stored, to force the Application _SheetSelectionChange( ) event to fire you must make a fake selection and then select again what is stored on the rg variable. To avoid that the cascade event fires twice, the code uses the Application .Enabled Event property to disable Excel events, selects cell A1, enables the property again, and then reselects the current range, firing the cascading event.

Application .EnableEvents = False
   Range("A1").Select
Application .EnableEvents = True
rg.Select

When mApp_SheetSelectionChange( ) fires, it will pass the Sh and Target arguments to the UpdateInterface( ) procedure, which will use them to synchronize the UserForm interface.

Private Sub mApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Call UpdateInterface(Sh, Target)
End Sub

Using the Names Collection

Great worksheet applications need named ranges that you set using the Excel interface or VBA. All named ranges are stored on the Names collection and exist as different entities for both the Workbook and Worksheet objects: application scope named ranges must be stored on the Workbooks. Names collection, while worksheet scope named ranges must be stored on the Worksheets . Names collection. Each collection must have unique named ranges, but a Workbook named range can exist with the same name on any Worksheet. Names collection, representing different cell ranges.

Each named range must follow this syntax:
  • The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.

  • You cannot use the uppercase and lowercase characters C, c, R, or r as a defined name because they are all used as shorthand for selecting a row or column for the currently selected cell when you enter them in the Excel Name or Go To text box.

  • Names cannot be the same as a cell reference, such as A$1 or R1C1.

  • Spaces are not valid. Use the underscore character (_) or period (.) as word separators instead.

  • The name length can contain up to 255 characters.

Using the Excel interface, you create a workbook scope named range by first selecting the cell range and then entering the range name in the Excel Names ListBox . To create a worksheet scope named range, precede the name with the sheet tab and an exclamation point (like Sheet1!Test, as shown in Figure 5-6).
Figure 5-6.

Use the Excel Names ListBox to create workbook or worksheet scope named ranges. To create a worksheet named range, precede the name with the sheet tab and an exclamation point

Note that Figure 5-6 shows you can create names with different scopes that point to the same cells (both WorkbookRange and SheetRange point to cells A1:B2). Whenever you do this and select these cells range on the sheet tab, Excel will always refer to the worksheet name.

To create a named range with VBA code, use the Workbook or Worksheet object’s Names .Add method, which has this syntax:

Object.Add (Name, RefersTo , Visible, MacroType, ShortcutKey, Category, NameLocal, RefersToLocal, CategoryLocal, RefersToR1C1, RefersToR1C1Local)

In this code:
  • Name: This is required; it is the range name to be created.

  • RefersTo : This is required; it is the range address that the name refers to, using A1-style notation, if the RefersToLocal, RefersToR1C1, and RefersToR1C1Local parameters are not specified.

  • Visible: This is optional; it is the Boolean value that specifies whether the name is visible (hidden names do not appear in the Define Name, Paste Name, or Goto dialog box). The default value is True.

  • MacroType: This is optional ; it associates the name with a macro using the following values:
    • 1: User-defined function (Function procedure).

    • 2: Macro (Sub procedure).

    • 3 or omitted: This is the default value; the name does not refer to a user-defined function or macro.

  • ShortcutKey: This is optional; it specifies the macro shortcut key. It must be a single letter, such as z or Z. This applies only for command macros.

  • Category: This is optional; it is the category of the macro or function if the MacroType argument is defined to 1 or 2. The category is used in the Function Wizard.

  • NameLocal: This is optional; it specifies the localized text to use as the name if the Name parameter is not specified.

  • RefersToLocal: This is optional; it describes what the name refers to, in localized text, using A1-style notation, if the RefersTo , RefersToR1C1, and RefersToR1C1Local parameters are not specified.

  • CategoryLocal: This is optional; it specifies the localized text that identifies the category of a custom function if the Category parameter is not specified.

  • RefersToR1C1: This is optional; it describes what the name refers to, in English using R1C1-style notation, if the RefersTo , RefersToLocal, and RefersToR1C1Local parameters are not specified.

  • RefersToR1C1Local: This is optional; it describes what the name refers to, in localized text using R1C1-style notation, if the RefersTo , RefersToLocal, and RefersToR1C1 parameters are not specified.

The Names .Add method returns a Name object referencing the newly added named range. And although it has a lot of arguments, you need to use just the Name and RefersTo arguments to create or edit an existing name.

You can use the Names collection’s Add method to add or modify an existing range name. If the name is new, Excel will create it; if it already exists, Excel will change the RefersTo property to whatever you type in the Names .Add RefersTo argument.

To add (or modify) the WorkbookRange range name indicated in Figure 5-6 (a workbook scope range name) associated to Sheet1 cells A1:B2, use one of these syntaxes in the VBA Immediate window . Here’s an example:

?ThisWorkbook.Names .Add(“WorkbookRange”, “=A1:B2”)

Here’s the other example:

?Names .Add(“WorkbookRange”, “=A1:B2”)

To add (or modify) the SheetRange range name indicated in Figure 5-6 (a worksheet scope range name) associated to cells A1:B2, use this syntax in the VBA Immediate window:

?Sheet1.Names .Add(“SheetRange”, “=A1:B2”)

Or use this syntax:

?Names .Add(“Sheet1!SheetRange”, “=A1:B2”)

Did you notice that you can use just the Names collection’s Add method to insert both workbook or worksheet range names? To add or modify any worksheet scope name, just precede its name with the sheet name followed by an exclamation point and Excel will add it to the desired sheet Names collection.

Attention

Always precede the cell reference by the equal sign so Excel can interpret it as a cell range.

If the worksheet name contains the space character, you must enclose it in single quotes before using the Names .Add method (like you must do using the Excel Name box). For example, to insert the SheetRange named range into the My Sheet worksheet’s Names collection pointing to cells A1:B2, use this syntax:

?Names .Add("'My Sheet'!SheetRange", "=A1:B2")

Hiding Named Ranges

The same way Excel does with the Worksheet object , the Name object has a Visible property, which is also an argument of the Names .Add collection, that you can set to False to hide the named range from the user view. There is no way to do this operation using the Excel interface.

Once you have created a range name, you can change the Visible property using the VBA Immediate window with this syntax (note that you should not use the ? character in the VBA Immediate window so the Name.Visible property can be changed).

Names (“WorkbookRange”).Visible = False

And once you do this, the name does not appear anymore in the VBA Name box or the Name Manager dialog box (Figure 5-7).
Figure 5-7.

Use the Visible property of the Name object to hide/show any named range in the Excel interface, either in its Name box or in the Name Manager dialog box

To hide or show all named ranges used by your Excel application from user eyes, create a single procedure that receives two arguments: the sheet name and a Boolean argument that indicates what you want to do, such as the Sub HideRangeNames ( ) procedure from the basHideRangeNames.bas file that you can also extract from the Chapter02.zip file.

Public Function HideRangeNames (strWorksheet As String, fShow As Boolean)
    Dim nm As Name
    For Each nm In Worksheets (strWorksheet).Names
        nm.Visible = fShow
    Next
End Function

To hide all Sheet1 worksheet names, use the VBA Immediate window this way (don’t use the ? character):

HideRangeNames (“Sheet1”, False)

To show again all Sheet1 range names, call the procedure again using True for the second argument.

HideRangeNames (“Sheet1”, True)

Resizing Named Ranges

Another operation that you will need to perform on your Excel applications is to resize an existing range name, inserting or deleting rows and columns on its RefersTo property. And you can do this in two different ways.
  • Using the Names .Add method to re-create the existing range name with a new cell range reference

  • Using the Range. Resize property to resize a given range and then using the Range object Name property to attribute a name to the resized range

The Range. Resize property has this syntax:

Range.Resize (RowSize, ColumnSize)

In this code:
  • RowSize : This is optional; it is the number of rows in the resized range. If this argument is omitted, the number of rows in the range remains the same.

  • ColumnSize: This is optional; it is the number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same.

Note that the Range. Resize property specifies the total number of rows and columns that the range must have. It will resize the desired range regarding its top-left cell as the first range cell.

The next operation resizes the range returned by the WorkbookRange named range created in Figure 5-6 that begins on cell A1, so it now has five rows and ten columns. Since it returns a Range object, you cannot perform this in the VBA Immediate window.

Range(“WorkbookRange”).Resize (5,10)

The last instruction does not change the WorkbookRange named range reference. Just the range returned by this operation has changed, resizing it to five rows and ten columns. To see this resizing operation in action, use the Range.Select method, typing this syntax in the VBA Immediate window (note again that it does not use the ? print character, as shown in Figure 5-8):
Figure 5-8.

Use the Range.Resize property to resize the address returned by a given named range to an absolute number of rows and columns (keeping the original top-left cell range). To select the resized range, use the Range.Select method

Range(“WorkbookRange”).Resize (5,10).Select

Most operations you will do to change an existing named range size will be made by adding or deleting one or more rows or columns in the current reference. To do this, you need to specify the Range. Resize method’s RowSize and ColumnSize arguments using the range’s Rows.Count and Columns. Count properties as default values that must be added by a positive or negative number of rows/columns.

The next instruction will change the range returned by the WorkbookRange named range by adding just one row to its current reference, keeping it with the same column count (note that it uses the Range. Resize method’s RowSize argument, as shown in Figure 5-9):
Figure 5-9.

To resize the address returned by a given named range by adding to it a defined number of rows/columns, use the Range.Rows.Count or Range.Columns.Count property to return the current number of rows/columns, adding or subtracting an integer to resize them to the desired size. This figure shows how to add one row to the range address returned by the WorkbookRange named range created in Figure 5-6

Range(“WorkbookRange”). Resize (Range(“WorkbookRange”).Rows.Count + 1).Select

To change any named range address by adding to it any number of rows or columns, use the Range. Resize operation to resize it and then use the Range.Name property to name the new resized range to the desired saved range.

The next instruction resizes the WorkbookRange named range by adding to it two rows, keeping it as the same column counting (use the Excel Name box to see the result, as shown in Figure 5-10):
Figure 5-10.

To really resize a saved range, use the Range.Resize method to resize the address and then use the Range.Name property to name the resized range. Once the operation is done, use the Excel Name box to select the range and watch the result. This figure shows how to resize the WorkbookRange (A1.B2) created in Figure 5-6 by adding two more rows (resulting in A1:B4)

Range(“WorkbookRange”). Resize (Range(“WorkbookRange”).Rows.Count + 2).Name = “WorkbookRange”

Now that you already have a good understanding of how to do some specific Name object operations, let’s see all these Name object properties and methods in action!

Using Name Object Properties and Methods

Microsoft Excel 2007 or newer versions use the Name Manager command of the “Define names” area of the Formula tab of the ribbon to control the range names you create on any workbook. This command raises the Name Manager dialog box (which is like a UserForm object), which is where you can add, edit, or delete range names; filter range names by scope; or change the selected range name address (Figure 5-11).
Figure 5-11.

This is the Excel Name Manager dialog box that uses the New Name dialog box to insert/edit range names of any opened workbook. It does not have the ability to hide range names

To add or edit range name details, the Name Manager counts, with the aid of the New/Edit Name dialog box (another UserForm), and allows you to select the name scope and add a comment to any range name.

Note the interface behavior. When you click the New/Edit CommandButton of the Name Manager dialog box, it disappears from the screen and loads the New Name dialog box. And if you insert a new name or edit an existing one, when you close the New Name dialog box, the inserted/edited name is selected in the Name Manager dialog box, which shows most of its properties.

Attention

Both the Name Manager and New Name dialog box don’t offer any means by which you can hide a range name. And once a range name is created, you also cannot change its scope from workbook to worksheet, and vice versa. To do such operations, you must delete and re-create the range name.

The aim of this section is to try to duplicate the Name Manager behavior with some improvements that can help you to manage the range names of your workbook application, as follows:
  • Hide/show one or more range names

  • Change the range name scope

  • Apply the same comment to one or more range names at once

Attention

Before you begin to read this section, I want reiterate that what you are about to study was not result of chance. It took hard work, study, practice, and experimentation of Excel VBA programming, using a trial-and-error approach that took me many days to finish. When you begin to create your own solutions, remember that to build solid, good, and reliable software, you must access many different knowledge sources and strive to perfection to achieve the desired results.

To see the Excel Names collection and Name object in action, extract the Names Collection.xlsm macro-enabled workbook from the Chapter05.zip file, which uses two UserForms to improve the Excel Name Manager dialog box: frmNames (to mimic the Excel Name Manager) and frmEditName (to mimic the New/Edit Name dialog box), as shown in Figure 5-12.
Figure 5-12.

The Names Collection.xlsm macro-enabled workbook has two UserForms: frmNames to mimic the Name Manager dialog box and frmEditName to mimic the New Name dialog box

Whenever you need to open two successive modal UserForms, the first UserForm must hide itself using the Userform.Hide method before showing the second modal UserForm, which will stop the code at this point until the second modal UserForm can be unloaded, and when this happens, the first UserForm must apply to itself to the UserForm.Show method to show again its interface.

To keep the frmNames and frmEditName UserForm interfaces synchronized, you must use Public Properties declared on both UserForms so one can synchronize the other interface to mimic the way the Excel Name Manager and New/Edit Name dialog boxes behave.
  • The frmNames .NameSelected and frmEditName .NamesSelected properties set the connection between the two UserForms regarding the Name object that is being created or edited.

  • The frmEditName .NewName property is used to signal the insertion of a new name.

Whenever you open the Names Collection.xlsm macro-enabled workbook, the frmNames UserForm is shown to you, ready to deal with most Excel Name object properties and methods. It synchronizes its interface using two main Sub procedures.
  • Sub FilllstNames( ): To fill the lstNames ListBox with current Name object information

  • Sub DefineControls( ): To synchronize the Enabled property of most frmNames controls regarding what is currently selected in the lstNames ListBox

Figure 5-13 shows how frmNames should look when you open the Names Collection.xlsm workbook for the first time and no name has been created.
Figure 5-13.

When you open the Names Collection.xlsm macro-enabled workbook, frmNames is shown by the ThisWorkbook.Workbook _Open event

Once more, frmNames is automatically opened by the This Workbook.Workbook _ Open ( ) event, which has this code:

Private Sub Workbook _Open ()
    frmNames .Show
End Sub

When the UserForm opens, its UserForm_Initialize ( ) event fires, adding two main options to the cboObjects ComboBox (“All objects” and “Workbook ”) and the name of each sheet tab of the active workbook using a For Each…Next loop to run through all objects of the Application . Worksheets collection.

Private Sub UserForm_Initialize ()
    Dim varItem As Variant
    mintLastColumn = 1
    Me .cboObjects.AddItem "All objects"
    Me .cboObjects.AddItem "Workbook "
    For Each varItem In Worksheets
        Me .cboObjects.AddItem varItem.Name
    Next
    Me .Height = mconHeight1
    Me .cboObjects.ListIndex = 0
End Sub

You should note three important things that happen on the frmNames UserForm_Initialize ( ) event.
  • It Height property is changed to the mconHeight1 constant, making it become a small vertical dimension and hiding its Details section (compared to frmNames in Figures 5-12 and 5-13).

  • The last instruction, which sets cboObject.ListIndex = 0, will cascade-fire the cboObjects_Change( ) event, filling the lstNames ListBox with all range name information by calling the Sub FilllstNames( ) procedure.

  • After the cboObjects_Change( ) event is fired, the UserForm interface is synchronized, and most controls become unavailable because of the action’s Sub EnableControls ( ) procedure.

The cboObjects ComboBox is responsible for defining the scope of the range names that the frmNames must show: The “All objects” option will return all range names from the Application . Names collections (meaning all Workbook and Worksheet scope range names); the “Workbook ” option will return just Workbook scope range names, and by selecting any sheet tab name in the list, only the selected Worksheet object range names will be show.

Whenever the user—or the code—changes the cboObjects ComboBox value, the cboObjects_Change( ) event will fire, executing this code:

Private Sub cboObjects_Change()
    Call FilllstNames
    Call lstNames_Change
End Sub

Recovering Name Object Properties

Whenever another name scope is selected on the cboObjects ComboBox, a new Name object is inserted, or any Name property is changed, the code needs to call Sub FilllstNames( ) to clear and fill lstNames ListBox with the current Name object information. To keep this procedure as short as possible, it uses Function GetNameValue ( ) to return the ListBox ’s Value column using the Excel Name Manager style, which also uses Function EvaluateRange ( ) to eventually return a string with the associated error code exhibited by any formula or cell (Figure 5-14).
Figure 5-14.

To keep the Sub FilllstNames( ) as short as possible, it was divided by two other specialized Function procedures. GetNameValue ( ) must return the name value using the same style of Excel Name Manager dialog box, while EvaluateRange ( ) is used to return any formula or cell value as is, including a string with an associated error code (if any)

This is Public Sub FilllstNames( ) procedure code, executed to clear and fill the lstNames ListBox with Name object properties (since it is a Public procedure, it is considered as a frmNames method):

Public Sub FilllstNames()
    Dim nm As Name
    Dim intIndex As Integer
    Dim intPos As Integer
    Const conWorkbook = 1
    intIndex = Me .cboObjects.ListIndex
    With Me .lstNames
        .Clear
        For Each nm In Names
            intPos = InStr (1, nm.Name, "!")
            If (intIndex < conWorkbook) Or _
               ((intIndex = conWorkbook) And (intPos = 0)) Or _
               ((intIndex > conWorkbook) And (nm.Parent.Name = Me .cboObjects)) Then
                .AddItem nm.Name
                .Column (1, .ListCount - 1) = Mid (nm.Name, intPos + 1)
                .Column (2, .ListCount - 1) = GetNameValue (nm)
                .Column (3, .ListCount - 1) = nm.RefersTo
                .Column (4, .ListCount - 1) = IIf(nm.Parent.Name = ThisWorkbook.Name, "Workbook ", nm.Parent.Name)
                .Column (5, .ListCount - 1) = nm.Comment
                .Column (6, .ListCount - 1) = IIf(nm.Visible, "Yes", "No")
            End If
        Next
    End With
End Sub

Sub FilllstNames( ) declares all the variables it needs plus the Const conWorkbook = 1 constant to avoid the appearance of a “magic number” inside the code.

Private Sub FilllstNames(Optional varListIndex As Variant)
   ...
    Const conWorkbook = 1

It then uses the intIndex Integer variable to reference the cboObjects.ListIndex property just once (the item selected on the cboObjects ComboBox), defines a With Me .lstName…End With loop to also reference the lstName ListBox only once, and clears the ListBox by calling the Clear method.

    intIndex = Me .cboObjects.ListIndex
    With Me .lstNames
        .Clear

Once the lstNames ListBox is cleared, it begins a For Each nm in Names …Next loop to run through all names stored in the Application . Names collection, using the nm as Name object variable to easily reference each Name object.

    Dim nm as Name
    ...
        For Each nm In Names

Since any worksheet name is preceded by the sheet name and an exclamation character, FilllstNames( ) verifies the current Name object scope by searching for an exclamation character (!) on its Name property using VBA InStr ( ) function.

            intPos = InStr (1, nm.Name, "!")

Once this is done, the intPos Integer variable will receive a 0 as an indication of the absence of the ! character (meaning the workbook name scope) or the position of the ! inside the Name property (worksheet named scope).

Then it must make the decision to insert the name in the lstNames ListBox according to three possible scopes selected on the cboObjects ListBox : all objects, workbook names, or any sheet tab names. The name must be inserted if:
  1. 1.

    cboObjects = "All objects", meaning that all names must be inserted. Since this is the first option of the cboObjects ComboBox (ListIndex = 0) and conWorkbook = 1, it makes this test:

    If (intIndex < conWorkbook) Or _

     
  2. 2.

    cboObjects = " Workbook " option, meaning that just workbook scope Name objects should be inserted. Since this is the second option of cboObjects (ListIndex = 1 = conWorkbook), the Name object properties must be inserted if it also does not contain a ! character on its name (intPos = 0):

    ((intIndex = conWorkbook) And (intPos = 0)) Or _

     
  3. 3.

    cboObjects = <SheetTabName> option, meaning that just the selected sheet tab names must be inserted (ListIndex > 1 > conWorkbook) and the selected sheet tab name on cboObjects equals the Worksheet object that the name belongs to, which is given by the Name.Parent.Name property.

                   ((intIndex > conWorkbook) And (nm.Parent.Name = Me .cboObjects)) Then

     

Look at the VBA properties window and note that the lstNames ListBox was defined with seven columns (ColumnCount = 7) and that its first column is hidden (ColumnWidths = 0 pt;85 pt;…). The first (hidden) column must receive the Name object’s Name property as it is: with or without the preceding sheet name for worksheet named scope. The next six columns must show six different Name object properties or information, as their names imply: Name, Value, Refers To, Scope, Comment, and Visible (as indicated in Figure 5-12).

So, the next procedure instruction uses the ListBox AddItem method to add the Name object’s Name property to the first lstNames column (hidden):

                .AddItem nm.Name

Using the ListBox Column property, the procedure begins to add another Name object property to the last item inserted in the list. The second lstNames ListBox column (Column =1) receives the Name.Name property without the sheet name that precedes it, if the name has a worksheet scope. It uses the VBA Mid ( ) function to extract the name from the position after the ! character (intPos+1) to the end of the name.

                .Column (1, .ListCount - 1) = Mid (nm.Name, intPos + 1)

Attention

If intPos = 0, meaning that no ! character was found on the name, the Mid (nm.Name, intPos+1) function will return all name characters beginning from the first one.

The second lstNames column must receive the Name object value, or some of it first values if it represents more than one cell, using the same style of the Name Manager dialog box (see Figure 5-11), which lead us to two different situations:
  • Name objects that represent a value, like a single cell range or a formula that must be evaluated

  • Name objects that represent a range of cells must show values inside braces ({}); each value must be inside double quotes (“”) separated by semicolons; row breaks are identified by a backslash (\); the values must reflect what is seen on the worksheet

To keep the procedure smaller, it uses Function GetNameValue ( ) to recover the current Name object value.

.Column (2, .ListCount - 1) = GetNameValue (nm)

Recovering Name Values with GetNameValue ( )

The function GetNameValue ( ) receives a Name object as an argument and executes this code:

Private Function GetNameValue (nm As Name) As String
    Dim rg As Range
    Dim strItem As String
    Dim intI As Integer
    Dim intJ As Integer
    Dim intK As Integer
    Const conMaxItens = 6
    On Error Resume Next
    If Not IsArray (nm.RefersToRange) Then
        GetNameValue = EvaluateRange (nm.RefersTo )
    Else
        Set rg = nm.RefersToRange
        strItem = "{"
        For intI = 1 To rg.Rows.Count
            strItem = strItem & IIf(intI > 1, "\", "")
            For intJ = 1 To rg.Columns.Count
                strItem = strItem & IIf(intJ > 1, ";", "")
                strItem = strItem & Chr(34) & EvaluateRange (rg.Cells(intI, intJ)) & Chr(34)
                intK = intK + 1
                'Provision to not add more than conMaxItens itens
                If intK >= conMaxItens Then
                    Exit For
                End If
            Next
            If intK >= conMaxItens Then
                Exit For
            End If
        Next
        If intK <= conMaxItens Then
            strItem = strItem & "}"
        Else
            strItem = strItem & "…"
        End If
        GetNameValue = strItem
    End If
End Function

After declaring its variables, the procedure disables any VBA raised error using an On Error Resume Next instruction and then uses the VBA IsArray ( ) function to verify whether the Name.RefersToRange property (which returns a Range object) does not refers to multiple cells. If this is true, the returned range refers to a single cell or to a Name constant formula (which does not return a Range object, raising a VBA error), and Name.RefersToProperty (which returns a string) is passed to Function EvaluateRange ( ) to evaluate the reference and see whether it returns any Excel error.

    On Error Resume Next
    If Not IsArray (nm.RefersToRange) Then
        GetNameValue = EvaluateRange (nm. RefersTo )

Evaluating Excel Values with the Function EvaluateRange ( )

Excel cells can represent a wide range of values, including text, numbers, dates, hours, formulas that return any of these values, and…errors!

So, to correctly use the Application .Evaluate( ) method to evaluate any cell value and show it on the lstNames ListBox , you must evaluate the formula or range the name represents and verify whether it returns any Excel error. If this is true, the error must be displayed in the lstNames Value column as is.

Table 5-4 shows all possible Excel errors, the error code, its VBA constants, and its meaning.
Table 5-4.

Excel Error Constants , Error Types, and Values

Excel Error

Error Code

Error Constant

Error Type

#DIV/0!

2007

xlErrDiv0

Division by zero

#N/A

2042

xlErrNA

Not available

#NAME?

2029

xlErrName

Name does not exist

#NULL!

2000

xlErrNull

A NULL value

#NUM!

2036

xlErrNum

Number is expected

#REF!

2023

xlErrRef

Range or reference is wrong

#VALUE

2015

xlErrValue

Value is missing

To verify whether a cell address or formula evaluation returns an error, you must compare its value with the Excel CVErr ( ) function, which has this syntax:

CVErr (Expression)

In this code:
  • Expression: This is an error code or application constant associated with the error you want to generate.

As Table 5-4 specifies, CVErr ( ) receives any Excel error code or error constant as an argument and returns the associated Excel error. For example, to generate the famous #DIV/0! Excel error as a returned value in any VBA procedure, use CVErr ( ) this way:

CVErr (xlErrDiv0)

If you want to know whether cell A1 is returning a #DIV/0! error, use this syntax:

If Range(“A1”) = CVErr (xErrDiv0) then

This is the Function EvaluateRange ( ) code, which expects to receive a formula or single cell reference and returns its expected value, including error codes as a string:

Private Function EvaluateRange (varValue As Variant) As String
    If VarType(varValue) = vbString Then
        varValue = (Evaluate(varValue))
    End If
    If IsError(varValue) Then
        Select Case varValue
            Case CVErr (xlErrDiv0)
                varValue = "#DIV/0!"
            Case CVErr (xlErrNA)
                varValue = "#N/A"
            Case CVErr (xlErrName)
                varValue = "#NAME?"
            Case CVErr (xlErrNull)
                varValue = "#NULL!"
            Case CVErr (xlErrNum)
                varValue = "#NUM!"
            Case CVErr (xlErrRef)
                varValue = "#REF!"
            Case xlErrValue
                varValue = "#VALUE!"
            Case Else
                varValue = "#VALUE!"
        End Select
    End If
    EvaluateRange = varValue
End Function

Since EvaluateRange ( ) receives the varValue as Variant argument, you can pass it the Name. RefersTo string (which is always a formula that must be evaluated) or the Name.RefersToRange property, which returns the associated Range object (which is a value already evaluated).

Once varValue is received, EvaluateRange ( ) uses the VBA VarType( ) function to verify whether the varValue is a text string. If it is true, the Name. RefersTo property string was passed and must be evaluated using the Excel Application .Evaluation method.

    If VarType(varValue) = vbString Then
        varValue = Evaluate(varValue)
    End If

At this point, varValue was evaluated to a value or any Excel error, which is tested by the VBA IsError( ) function. If IsError(varValue)=True, a Select Case statement comparing the varValue error with the value returned by the Excel CVErr ( ) function for each possible constant error using a Select Case statement.

The first comparison uses the xlErrDiv0 constant, and if the comparison is true, the formula or cell has or returns a #DIV/0! error, and the "#DIV/0!" string is stored into varValue and returned as the function result.

If IsError(varValue) Then
        Select Case varValue
            Case CVErr (xlErrDiv0)
                varValue = "#DIV/0!"
                ...
        End Select
    End If
    EvaluateRange = varValue
End Function

Getting Back to GetNewName ( )…
You must return to the GetNameValue ( ) procedure and continue with the Else clause of the If Not IsArray (nm.RefersToRange) Then... instruction to see how a Name object that returns multiple cell values is processed.

The Excel Name Manager uses a particular way to show a multiple-cell range name in its interface: its values are shown inside braces ({}), with each value inside double quotes ("") and separated by semicolons. Row breaks are identified by a backslash (\), and the values must reflect what is seen on the worksheet (including Excel errors).

This time you will need to run across all rows and columns of the range name, evaluating each cell value and returning them with the expected format. To select each cell used by the cell range, you must use the Range.Cells property inside two nested For…Next loops: an outer loop to process each range row and an inner loop to process each range column.

But before we dive into the code technique, you must be aware that a range can have an excessive number of cells, so the procedure must put a limit on what can be seen in the lstNames Value column. That is why GetNameValue ( ) declares so many Integer variables and the Const conMaxItens = 6 constant: to execute the two For…Next loops and limit the list to no more than six individual cell values.

Private Function GetNameValue (nm As Name) As String
    Dim rg As Range
    Dim strItem As String
    Dim intI As Integer
    Dim intJ As Integer
    Dim intK As Integer
    Const conMaxItens = 6

The GetNameValue ( ) procedure will use the strItem string variable to compound the list of the first range values. Since the list be enclosed by brace characters, an open brace ({) is added to strItem before starting to loop through all rows of the range name using the intI Integer variable as the row counting. To visually separate each row from the next, strItem will receive a backslash (\) character after the first row is entirely processed (which happens when intI>1).

Else
    Set rg = nm.RefersToRange
    strItem = "{"
    For intI = 1 To rg.Rows.Count
        strItem = strItem & IIf(intI > 1, "\", "")

Another For…Next loop is initiated to run through all columns of each range row and return its cells values, using the intJ Integer variable as the column counting. Note that after the first item is added to strItem (inJ>1), the procedure adds a colon to separate each item from the next.

For intJ = 1 To rg.Columns.Count
    strItem = strItem & IIf(intJ > 1, ";", "")

At this point, the procedure is positioned on the cell range represented by (intI, intJ) coordinates, which is perfect for being used by the Range.Cells property to return the cell value, which also must be sent to the EvaluateRange ( ) procedure to verify whether it returns any Excel error. Since the Excel Name Manager encloses each cell value in double quotes, the procedure adds a Chr(34) (") character before and after the cell value.

strItem = strItem & Chr(34) & EvaluateRange (rg.Cells(intI, intJ)) & Chr(34)

Attention

Whenever you need to add a single or a double quote as part of a String value, use the VBA function Chr(34) = " (double quote) or CHR(39) = ' (single quote) to concatenate it into the string without generating an Excel error, regarding the string close character.

The next steps use the intK counter to count how many values have been inserted on the strItem variable. When intK >= conMaxItens, the procedure uses an Exit For instruction to interrupt the inner loop.

intK = intK + 1
'Provision to not add more than conMaxItens itens
    If intK >= conMaxItens Then
       Exit For
    End If
Next

This will force the code to exit to the outer loop, which verifies again whether intK >= conMaxItens. If it is, all items have already been inserted on the strItem variable, and the outer loop must also end with another Exit For instruction.

    Next
    If intK >= conMaxItens Then
       Exit For
    End If
Next

All desired items have been inserted. If they count equal to or less than conMaxItens, strItem receives a closing brace character (}). If the strItem variable does not hold all range values, it receives a reticence.

        If intK <= conMaxItens Then
            strItem = strItem & "}"
        Else
            strItem = strItem & "…"
        End If
        GetNameValue = strItem
    End If
End Function

Getting Back Again to FilllstNames( )…

We now need to get back again to the FilllstNames( ) procedure, which at this time has already defined its third Value column. The next operations to add Name object properties to lstName columns for its last-inserted item are easy to understand. Take a look at the next instructions:

                .Column (3, .ListCount - 1) = nm.RefersTo
                .Column (4, .ListCount - 1) = IIf(nm.Parent.Name = ThisWorkbook.Name, "Workbook ", nm.Parent.Name)
                .Column (5, .ListCount - 1) = nm.Comment
                .Column (6, .ListCount - 1) = IIf(nm.Visible, "Yes", "No")
            End If
        Next
    End With
End Sub

Note that the fifth lstNames column’s Scope value is added using a IIF( ) instruction to verify whether the Name object’s Parent.Name property equals the ThisWorkbook.Name property, meaning a workbook scope range name. If it isn’t, it adds the Parent.Name property to this column.

And once all desired Name object properties have been recovered by the For Each nm in Names loop, the procedure ends and returns to the cboObject_Change( ) event, which will call the lstNames_Change( ) event to synchronize the UserForm interface.

Private Sub cboObjects_Change()
    Call FilllstNames
    Call lstNames_Change
End Sub

Attention

The lstNames_Change( ) event will be analyzed in the section “Selecting a Name on lstNames ListBox ” later in this chapter.

Adding a New Name Object

Now that you have a good understanding about how the FilllstNames( ) procedure works (calling GetNameValue ( ) to evaluate each Name object’s RefersTo property, which calls EvaluateRange ( ) to evaluate each cell value), to see frmNames in action you need to add some range names by clicking the New ControlButton, which will fire the cmdNew_Click( ) event and execute this code:

Note the technique: VBA errors are disabled using an On Error Resume Next instruction and then use the UserForm.Hide method to hide frmNames from the Excel interface and allow frmEditName to be loaded. Then it uses the With frmEditName …End With loop to reference frmEditName just once and sets three properties: NewName, NameSelected, and NameFilter.

When the code uses the With frmEditName instruction to reference the UserForm, VBA will immediately load it into memory, firing the UserForm_Initialize ( ) event and executing this code:

Private Sub UserForm_Initialize ()
    Dim varITem As Variant
    Me .cboObjects.AddItem "Workbook "
    For Each varITem In ThisWorkbook.Worksheets
        Me .cboObjects.AddItem varITem.Name
    Next
    Me .cboObjects.ListIndex = 0
    Me .txtRefersTo = "='" & ActiveSheet.Name & "'!" & Selection.Address
End Sub

As you can see, frmEditName fills the cboObjects ComboBox with the word Workbook and the name of each workbook sheet tab and defines Workbook as the default selection and the txtReferTo text box to the address of what is currently selected on the active worksheet. Note that to deal with sheet names that have spaces, it uses a single quote after the = sign, concatenates the ActiveSheet.Name property, concatenates another single quote and an exclamation point, and finally concatenates the Selection. Address property.

    Me .txtRefersTo = "='" & ActiveSheet.Name & "'!" & Selection.Address

When the frmEditName Initialize( ) event finishes executing, the code returns to the first instruction of the With frmEditName instruction on the frmNames code module, setting the frmEditName .NewName property to True to indicate to the UserForm that it must create a new name.

This will fire the frmEditName Public Property Let NewName( ) procedure to execute, storing the True value into the mbolNewName module-level variable and setting the UserForm Caption property to New Name.
When the Property Let NewName( ) procedure ends, the code returns to the next With frmEditName instruction, this time trying to set the frmEditName .NameSelected property to the name selected on the frmNames lstNames ListBox .

Since no name has been inserted on the Names Collection.xlsm workbook and the lstNames ListBox has no name selected, the mcolItemsSelected(1) instruction on the right will raise an error (Error = 5, “Invalid procedure call or argument”), which will be ignored because of the On Error Resume Next instruction executed on the procedure beginning, and the frmEditName Public Property Let NameSelected( ) procedure will not be executed!

Attention

The mcolItemsSelected(1) instruction will be explained in the section “Using Collection Variables” later in this chapter.

When the frmEditName .Show method is executed, the frmEditName UserForm has its property ShowModal = True, the window is showed modally, and the cmdNew_Click( ) code stops on this instruction until frmEditName is closed by the user action (Figure 5-15).
Figure 5-15.

When you click the New CommandButton to create a new name, frmEditName is loaded while frmNames is unloaded from memory

Let’s try to insert a new name that returns a constant with a #DIV/0! error so you can try the frmNames FilllstNames( ) procedure. In the Range Name text box (txtName) of frmEditName , type Div0Constant, and in the “Refers to” text box (txtRefersTo), type this formula (Figure 5-16):
Figure 5-16.

Insert a Name constant that raises an Excel #DIV/0! error to see how the frmNames Sub FilllstNames( ) procedure performs when such a range name is created

=2/0

Validating Names

After you type the desired name in the txtName text box and press Enter, the txtName_BeforeUpdate( ) event fires, executing this code:

Private Sub txtName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(Me .txtName) Then
        Me .txtName = FixName(Me .txtName)
    End If
End Sub

Note that like any other Before event, it passes a Cancel argument, meaning that it can be canceled by the VBA code. The event uses the VBA Len( ) function to verify whether anything was typed in the txtName text box, and if this is true, it passes the name typed to the Function FixName( ) procedure of basFixName code module, which takes out any invalid characters from the name before executing.

The function FixName( ) procedure executes this code:

Public Function FixName(ByVal strName As String)
    'Invalid characters inside range names: @#$%&()+∼`"':;,.|!?_-/\*[]{}
    Dim strInvalidChars As String
    Dim strChar As String
    Dim intI As Integer
    'Search for invalid characters
    strInvalidChars = "@#$%&()+∼`´':;,.|!?-/\*[]{}" & """"
    For intI = 1 To Len(strInvalidChars)
        'Get each invalid character and take it out
        strChar = Mid (strInvalidChars, intI, 1)
        strName = Replace (strName, strChar, "")
    Next
    'Now change spaces to underscores
    strName = Replace (strName, " ", "_")
    FixName = strName
End Function

The search technique is quite simple; it stores all invalid characters on a string variable and uses the VBA Replace ( ) function to substitute them, which has this syntax:

Replace (Expression, Find, Replacement, [Start], [Count], [Compare]) As String

In this code:
  • Expression: This is required; it is the string expression containing the substring to replace.

  • Find: This is required; it is the substring being searched for.

  • Replacement: This is required; it is the replacement substring.

  • Start: This is optional; it is the position within the expression where the substring search is to begin. If omitted, 1 is assumed.

  • Count: This is optional; it is the number of substring substitutions to perform. If omitted, the default value is –1, which means “make all possible substitutions.”

  • Compare: This is optional; it is the numeric value indicating the kind of comparison to use.
    • Binary: This performs a binary comparison (case sensitive).

    • Text: This performs a textual comparison.

The FixName( ) procedure works this way: it stores all invalid characters to be extracted from the strName argument on the strInvalidChars String variable.

'Search for invalid characters
strInvalidChars = "@#$%&()+∼`´':;,.|!?-/\*[]{}" & """"

A For…Next loop runs through all invalid characters extracting them one by one to the strChar variable and using the VBA Mid ( ) function, and it uses the VBA Replace ( ) function to search and replace it with an empty string ("").

For intI = 1 To Len(strInvalidChars)
    'Get each invalid character and take it out
    strChar = Mid (strInvalidChars, intI, 1)
    strName = Replace (strName, strChar, "")
Next

The VBA Replace ( ) function is used again to change any space to an underscore character (_) and returns the fixed name.

'Now change spaces to underscores
strName = Replace (strName, " ", "_")
FixName = strName

Using Names Collection Add Method

Since you are now inserting a new constant name that returns a #DIV/0! Excel error, when you click the OK ControlButton, the cmdOk_Click( ) event fires, executing this code:

Private Sub cmdOK_Click()
    Dim nm As Name
    Dim strName As String
    On Error Resume Next
    If Len(Me .txtName) = 0 Then
        MsgBox "Type the range name", vbCritical, "Range name?"
        Exit Sub
    End If
    If Len(Me .txtRefersTo) = 0 Then
        MsgBox "Define range address", vbCritical, "Range address?"
        Exit Sub
    End If
    strName = Me .txtName
    If Me .cboObjects <> "Workbook " Then
        strName = "'" & Me .cboObjects & "'!" & strName
    End If
    If Not Me .NewName Then
        Call FixNameChange
        Me .NameSelected.Delete
    End If
    Set nm = Names .Add(strName, Me .txtRefersTo, Me .chkVisible)
    nm.Comment = Me .txtComment & ""
    Set mName = nm
    Unload Me
End Sub

As you can see, the cmdOK_Click( ) event procedure begins using an On Error Resume Next to disable any VBA raised errors and then verifies with the VBA Len( ) function if any text was typed in the txtName or txtRefersTo text box (note that it doesn’t care if you type an invalid reference on txtRefersTo).

Once you have typed the name and it references the formula, the procedure stores the desired name into the strName String variable and verifies the name scope. If cboObject = " Workbook ", the name is inserted as is, but if you select a sheet name, the sheet name is enclosed by single quotes, suffixed by a ! character and used as a prefix to the name.

On Error Resume Next
strName = Me .txtName
If Me .cboObjects <> "Workbook " Then
   strName = "'" & Me .cboObjects & "'!" & strName
End If

Next the code uses frmEditName .NewName property to verify whether it is inserting a new Name by verifying it. If this is true, the Name object stored on the frmEditName .NameSelected property will be deleted.

If Not Me .NewName Then
    Me .NameSelected.Delete
End If

And the name is added to the Application . Names collection using the Names .Add method with strName for the Name.Name property, txtRefersTo to the Name. RefersTo property, and chkVisible refers to the Name.Visible property, storing a reference to it into the nm as Name object procedure variable.

Set nm = Names .Add(strName, Me .txtRefersTo, Me .chkVisible)

Since the Name.Comment property can’t be set by the Names .Add method, it is defined on the nm variable that represents the new added name.

nm.Comment = Me .txtComment & ""

The local nm object variable is then associated to the mName object variable, and the frmEditName UserForm is unloaded from memory using the VBA Unload method.

    Set mName = nm
    Unload Me
End Sub

When frmEditName is unloaded, the UserForm_Terminate ( ) event fires, executing this code:

Before frmEditName unloads from memory, it uses a With frmNames …End With instruction to reference frmNames only once and updates the lstNames ListBox to reflect any Name object changes, calling the frmNames .FilllstNames method.

With frmNames
    .FilllstNames

Remember that the frmEditName cmdOK_Click( ) event stored the nm object variable reference to the new name on its mName module-level variable? This reference is now used when the procedure calls the Property Get NameSelected( ) procedure and compares it to Nothing.
Since a new name has been added, (Not ( Me .NameSelected) is Nothing) = True, this new name is used to define the frmNames .NameSelected property, which will execute the frmEdifName Property Get NameSelected( ) on the right side of the equation, while executing frmNames Property Let NameSelected( ) on the left side.

The code for frmNames Property Let NameSelected( ) is quite simple: it first verifies whether the nm as Name argument has some Name object reference. If it does, it uses a For…Next loop to run through all lstNames items, using the Names collection to compare each Name object’s Name property with the recently added nm object Name property (note that it uses lstNames. Column (0), intI) to return each Name.Name property).

For intI = 0 To Me .lstNames.ListCount - 1
    If Names (Me .lstNames.Column (0, intI)).Name = nm.Name Then

When it finds a match, it selects the item using the lstNames ListBox .Selected property and makes the list scroll to the selected item by setting the ListIndex property before exiting the For…Next loop and uses the frmNames .Show method to show the interface on the screen.

And, when this happens, the lstNames.Change( ) event cascade-fires because another item was selected in the lstNames ListBox , synchronizing the frmNames interface to the selected item.

Attention

You will learn about the lstNames_Change( ) event in the next section.

When the frmEditName UserForm_Terminate ( ) event ends, it returns code control to the cmdOK_Click( ) event, which also returns code control to the frmNames cmdNew_Click( ) event that executes the frmNames .Show method, showing frmNames with a synchronized interface.
Figure 5-17 shows how frmNames appears in Excel after the Div0Constant Name object was added using frmEditName . Note that the frmNames CommandButtons are now enabled, since the new added name is selected in the lstNames ListBox
Figure 5-17.

This is frmNames after the Div0Constant Name object was added. Since it produces a division by zero, the lstNames Value column shows a #DIV/0! Excel error

Inserting a New Name by Selecting a Range Address

To see how frmNames performs, you need to add a few more names to the Names Collection.xlsm workbook. Since the first Name object (Div0Constant) was associated to a constant value (the #DIV/0! Excel error), let’s insert one name that has just a range of valid numbers. Click again the frmNames New ControlButton, type MyData in the txtName text box, keep the Workbook scope, and click cmdRefersTo (the small ControlButton at the right of the txtRefersTo text box). frmEditNames will hide itself and show Application .Inputbox, which is where you can click and drag the desired cell addresses to associate the range name. Select cells A7:B9 from Sheet1 (Figure 5-18).
Figure 5-18.

Use frmNames to create a new name associated with just valid numbers (with no error cells), like MyData, with workbook scope, associated to cells A7.B9

This is the code behind the frmEditName cmdRefersTo_Click( ) event, which allows the selection of the cell’s range that will be associated to the range name:

Private Sub cmdRefersTo_Click()
    Dim varRange As Variant
    Dim intPos As Integer
    Const conFormula = 0
    Me .Hide
    varRange = Application .InputBox("Cells selected:", "Select range cells", Me .txtRefersTo, , , , , conFormula)
    If varRange <> False Then
        varRange = Application .ConvertFormula(varRange, xlR1C1, xlA1)
        'Search for Workbook reference
        intPos = InStr (1, varRange, "]")
        If intPos > 0 Then
            varRange = "'" & Mid (varRange, intPos + 1)
        End If
        'Search for Sheet name
        intPos = InStr (1, varRange, "!")
        If intPos = 0 Then
            varRange = "'" & ActiveSheet.Name & "'!" & Mid (varRange, 2)
        End If
        'Search for "='
        If Left (varRange, 1) <> "=" Then
            varRange = "=" & varRange
        End If
        Me .txtRefersTo = varRange
    End If
    Me .Show
End Sub

When you click the cmdRefersTo ControlButton, the first instruction executed by its Click( ) event is to hide itself by calling the UserForm.Hide method. It then calls the Application .Inputbox method using the txtRefersTo value for the InputBox Default argument (what is currently selected on the active sheet; see the frmEditName UserForm_Initialize ( ) event code in section “Adding a New Name” earlier in this chapter). For its last Type argument, the constant conFormula = 0 to avoid the appearance of any magic number on the code.

Private Sub cmdRefersTo_Click()
    Dim varRange As Variant
    Dim intPos As Integer
    Const conFormula = 0
     Me .Hide
    varRange = Application .InputBox("Cells selected:", "Select range cells", Me .txtRefersTo, , , , , conFormula)

If the user clicks the InputBox Cancel button, varRange will receive False, and the procedure will call the UserForm.Show method and end normally. But if the InputBox OK button is selected, the next code instruction uses the Application .ConvertFormula method to change the range selected from R1C1 to A1 style.

    If varRange <> False Then
        varRange = Application .ConvertFormula(varRange, xlR1C1, xlA1)

Attention

There is no indication in the Application .InputBox method that whenever it uses Type=0 to get a range address by dragging the mouse over any sheet cells, the formula returned will use the R1C1 style. But Excel does this, and you must convert it to A1 style so it appears like most users expect to see it.

If the range you are trying to select belongs to a sheet tab that is different from the active sheet, the Application .InputBox method will also return on the formula the workbook name inside double braces, like this:

='[Names Collection.xlsm]Sheet1'!$A$7:$B$9

So, you need to search the varRange variable for a closing brace (]) using the VBA InStr ( ) function, and if it’s found, you take it out from the selected range using the VBA Mid ( ) function.

'Search for Workbook reference
intPos = InStr (1, varRange, "]")
If intPos > 0 Then
   varRange = "'" & Mid (varRange, intPos + 1)
End If

The next instruction will verify whether the returned address is already prefixed by the sheet tab name using again the VBA InStr ( ) function to search for a ! character. If it does not exist (which happens when you select any range on the active sheet), it must be inserted in the formula. The new formula is composed by the active sheet name enclosed by single quotes, an exclamation character (!), and the current address without its first = character, which is extracted by the VBA Mid ( ) function.

        'Search for Sheet name
        intPos = InStr (1, varRange, "!")
        If intPos = 0 Then
            varRange = "'" & ActiveSheet.Name & "'!" & Mid (varRange, 2)
        End If

The code then searches for the = character that must be at the very first position of the returned address, and if it is not there, it is added again, and frmEditName txtRefersTo receives the selected range with the appropriate format.

'Search for "='
If Left (varRange, 1) <> "=" Then
   varRange = "=" & varRange
End If
Me .txtRefersTo = varRange

The Sub cmdRefersTo_Click( ) event finishes by using the UserForm.Show method to show the frmEditName Userform interface again.

    End If
     Me .Show
End Sub

When you click the frmEditName cmdOK ControlButton, the MyData range name will be added to the workbook Names collection, and frmNames will rebuild its lstNames ListBox with the newly added name selected in its interface (Figure 5-19).
Figure 5-19.

This is frmNames after you have inserted the MyData range name, with workbook scope, associated to cells A7.B9 to return just valid numbers

To continue with the next sections of this book, please insert some more range names with different scopes. Table 5-5 shows the range names that will be used in the next figures of this chapter.
Table 5-5.

Using frmNames and frmEditName , Insert These Range Names

Range Name

Scope

Refers To

SumMyDataq

Sheet1

=Sum(MyData)

DataWithError

Sheet1

=Sheet1!$A$6:$B$9

RangeSheet2

Sheet2

=Sheet2!$A$1

Figure 5-20 shows frmNames with all five range names created so far in this book section.
Figure 5-20.

This is the frmNames interface after all the proposed range names of Table 5-5 are inserted in the Names Collection.xslm macro-enabled workbook

Attention

Note that the SumMyData range name correctly sums all MyData range name values.

Selecting Items in the lstNames ListBox

The lstNames ListBox of the frmNames UserForm was set to allow multiple selections in the lstNames ListBox by setting the MultiSelect property to 2 - frmMultiSelectExtend, meaning that you can click and drag the mouse over the list or use the Ctrl or Shift key to select any combination of items.

Whenever a VBA UserForm ListBox is defined to allow multiselection, it does not return a Value property anymore; the lstNames.Value will now return Null.

Since the VBA UserForm ListBox does not have an ItemsSelected property to indicate which items are selected (like Microsoft Access ListBox does), you need to use a loop to run through all ListBox items verifying whether the Selected property is True, and then take the desired action, as follows:

For intI = 0 to Me .lstNames.ListCount - 1
   If Me .lstNames(intI).Selected = True Then
      ‘ Do something here!
   End If
Next

Now you need a way to store all ListBox -selected items in a variable so you can easily process multiple names at once (changing the Visible property, for example), call the FilllstNames( ) procedure to fill lstNames with the new Name object properties, and reselect them after the process is completed. This time you will use the VBA Collection object to hold the items selected.

Using Collection Variables

VBA offers the Collection object as a way to group and manage related objects. It has been widely used to collect object references created with Class modules, and you see it in action every time you use the Excel Workbooks, Worksheets, or Names collections that hold references to different types of Excel objects.

The Collection object offers the Add and Remove methods to manage items, a Count property to indicate how many items it currently holds, and an Item property as an easy way to instantly recover any collected data inside the Collection object.

To be useful, a Collection variable must be declared as a Public or Private variable in the Declaration section of a code module so it can be accessed by all its procedures. To test how a Collection object variable works, you can declare it as a Public variable of the ThisWorkbook code module on any Excel workbook, using the VBA New keyword, by typing the next instruction in its Declaration section (note that the variable name was prefixed with mcol, which is a common way to identify code module collection variables).

Option Explicit
Public mcolMyCollection as New Collection

Attention

If you did not use the VBA New keyword to declare an object variable (like mcolMyCollection as New Collection), you need to use the VBA Set instruction to instantiate it or add some item with the Add method before trying the Count property.

From this point on, the ThisWorkbook.mcolMyCollection Collection variable can be easily accessed from any part of your code or from the VBA Immediate window, and the mcolMyCollection.Count property will return zero items since no one has already been inserted on it (Figure 5-21)!
Figure 5-21.

Once a code module has a Public Collection variable declared with the New keyword, you can test it using the VBA Immediate window. Note that when you evoke the mcolMyCollection.Count property, the object variable is automatically instantiated by VBA

Adding Collection Items

To add a new item to a Collection object variable, use the Add method, which has this syntax:

Object.Add Item, Key, Before, After

In this code:
  • Object: This is the name of the object variable declared as Collection.

  • Item: This is required; it is an expression that specifies an object reference or any type of value that represents the member to add to the collection.

  • Key: This is optional; it is a string expression that specifies an identification key that can be used, instead of a positional index, to access a member of the collection and return its Item property.

  • Before, After: This is optional; it is an expression that specifies an existing member position in the collection where the new member should be placed before or after (you can specify a before position or an after position, but not both). If a numeric expression, Before must be a number from 1 to the value of the collection’s Count property. If a string expression, Before or After must correspond to the Key specified for the desired existing member.

Supposing you want to add the A Item to the mcolMyCollection variable identified by the ItemA Key, type this instruction in the VBA Immediate window:

ThisWorkbook.mcolMyCollection.Add “A”, “ItemA”

Note that A is used as the Item argument of the Collection.Add method (meaning Item value) while ItemA is used as Key argument. Figure 5-22 shows what happened after the mcolMyCollection object variable received item values A, B, and C, identified by the ItemA, ItemB, and ItemC keys, respectively, using the VBA Immediate window. Note that the mcolMyCollection.Count property returns three items.
Figure 5-22.

Using the VBA Immediate window, you can add, remove, and count items inserted on any Public Collection variable. This figure shows that three items have been added to the ThisWorkbook mcolMyCollection variable, using the Key argument to identify each item

Recovering Collection Items

To recover any collection Item, you can use its 1-based Index position or its Key (if any). That is why you may use for the Key argument of the Collection.Add method unique identifiers: to easily recover any desired item.

To recover the first mcolMyCollection Item using the numerical Index position, type this instruction in the VBA Immediate window:

?ThisWorkbook.mcolMyCollection(1)
A

To recover the first mcolMyCollection Item using it Key, type this instruction in the VBA Immediate window:

?ThisWorkbook.mcolMyCollection(“ItemA”)
A

In both cases, VBA will print in the Immediate window the desired Item value.

Removing Collection Items

To remove any collection, use the Remove method, which has this syntax:

object.Remove({Index | Key})

In this code:
  • Object: This is the name of the object variable declared as Collection.

  • Index, Key: This is the index position (1-based) or the key string associated to the item to delete.

So, to delete the first item of the mcolMyCollection variable, use one of the next two syntaxes with the Index or Key string associated to the item you want to remove in the VBA Immediate window. Here’s an example:

?ThisWorkbook.mcolMyCollection.Remove(1)
Here’s another example:?ThisWorkbook.mcolMyCollection.Remove(“ItemA”)

When you do this, every item remaining in the Collection variable will be re-indexed, but the Key value will remain the same, and that is the best reason to associate a unique Key to each Collection item.

Clearing a Collection

The VBA Collection object does not have a Clear method. In fact, you don’t need it! To clear any Collection variable of all its items, use the VBA Set and New keywords to instantiate it again. The next instruction will automatically release all items of the mcolMyCollection variable when you type it in the VBA Immediate window (or execute it on any code procedure):

Set mcolMyCollection = New Collection

It can’t be easier than that!

Attention

Some web sites advise you to associate Nothing to the Collection variable as a way to clear its items, as follows:

Set mcolMyCollection = Nothing

Although this works well to remove all items from the Collection variable, it also destroys the association of the object variable with the Collection object. If the variable was not declared with the New keyword, if you try to use its Count property immediately before this operation, instead of returning zero items, VBA will return an error since the mcolMyCollection variable is still not instantiated.

Using a Collection Variable to Store ListBox Selected Items

The frmNames UserForm has the ability to process multiple Name objects selected in the lstNames ListBox at once to delete them or change the Visible and Comment properties. Since the FilllstNames( ) procedure always shows the current properties of each Name object, you need to hold the selected lstNames items using a Collection object variable, process them, call FilllstNames( ) again to update its new properties, and reselect them again in the ListBox .

That is why frmNames has the Private mcolItemsSelected as Collection object variable declared in its Declaration section.

Option Explicit
Dim mcolItemsSelected As Collection

Note that the Collection variable was not declared with the New VBA keyword, meaning that it must be instantiated to associate it to a VBA Collection object.

Whenever you select one or more items on the lstNames multiselect ListBox , its Change( ) event fires, executing this code:

Private Sub lstNames_Change()
    Dim strNames As String
    Dim intI As Integer
    Dim bolVisible As Boolean
    Dim bolHidden As Boolean
    If mbolCancelEvent Then
        mbolCancelEvent = False
        Exit Sub
    End If
    Set mcolItemsSelected = New Collection
    With Me .lstNames
        For intI = 0 To .ListCount - 1
            If .Selected(intI) Then
                mcolItemsSelected.Add intI
                If .Column (6, intI) = "Yes" Then
                    bolVisible = True
                Else
                    bolHidden = True
                End If
            End If
        Next
    End With
    strNames = intI & " range names"
    strNames = strNames & IIf(mcolItemsSelected.Count > 0, " (" & mcolItemsSelected.Count & " selected)", "")
    Me .lblNamesCount.Caption = strNames
    If mcolItemsSelected.Count = 1 Then
        Call ShowNameProperties (mcolItemsSelected(1))
    Else
        Call ClearNameProperties
        Me .chkVisible = IIf(bolVisible And bolHidden, Null, bolVisible = True)
    End If
    Call EnableControls ((mcolItemsSelected.Count = 1))
End Sub

After declaring the variables it needs, the code performs the now famous trick to avoid a cascading event. It verifies whether the module-level variable mbolCancelEvent = True, and if it does it turns it false and exits the event code.

If mbolCancelEvent Then
    mbolCancelEvent = False
    Exit Sub
End If

The mcolItemsSelected Collection variable is then instantiated or cleared from all its items.

Set mcolItemsSelected = New Collection

Attention

Every time another item is selected in the lstNames ListBox , the Change( ) event fires, destroying and re-creating the mcolItemsSelected Collection variable.

The procedure begins a With lstNames…End With instruction to reference the lstNames ListBox only once and then begins a For…Next loop through all the items.

With Me .lstNames
    For intI = 0 To .ListCount - 1

If any lstNames item’s Selected property is True, the item is selected and added to the mcolItemsSelected Collection variable using the ListIndex value (represented by the intI Integer variable) as the Item value.

If .Selected(intI) Then
    mcolItemsSelected.Add intI

Since the user can select an undefined number of Name objects on the lstNames ListBox , they can have different Visible properties. The code then tries to recover each Name.Visible property analyzing lstNames seventh column value (Column =6, Visible). If it has a Yes, Name.Visible = True and the bolVisible variable also becomes True. If Name.Visibe = "No", the bolHidden variable becomes True:

If .Column (6, intI) = "Yes" Then
    bolVisible = True
Else
    bolHidden = True
End If

When all lstNames items have been processed, strNames receives how many Name objects are listed in the ListBox (using intI as the Name counting) and how many are selected (using the mcolItemsSelected.Count property), and the value is associated to the lblNamesCount.Caption property.

    strNames = intI & " range names"
    strNames = strNames & IIf(mcolItemsSelected.Count > 0, " (" & mcolItemsSelected.Count & " selected)", "")
    Me .lblNamesCount.Caption = strNames

There are now two different possibilities: just one item was selected on lstNames or more than one was selected.

If just one item is selected, the selected Name object properties must be shown in the frmNames Detail section. This is made by calling the ShowNameProperties ( ) procedure, which receives as an argument the selected Name ListIndex (represented by mcolItemsSelected(1), the first and only Collection variable Item):

If mcolItemsSelected.Count = 1 Then
    Call ShowNameProperties (mcolItemsSelected(1))

Attention

The ShowNameProperties ( ) procedure will be analyzed in the “Showing Name Properties” section later in this chapter.

If more than one item is selected, the frmNames Detail section must have its controls cleared because of the impossibility of showing ambiguous values, by calling the ClearNameProperties( ) procedure.

Else
    Call ClearNameProperties

Attention

The ClearNameProperties( ) procedure will be analyzed in the “Clearing Name Properties” section later in this chapter.

The frmNames Detail section has the chkVisible check box (see Figure 5-12), which now must reflect the Visible property of all selected Name objects on the lstNames ListBox .

For your information, any check box can have three different states: Checked (=True), Unchecked (= False), and Undetermined (= Null, becoming gray). If both bolVisible and bolHidden variables are True, it means that both visible and hidden names have been selected in the lstNames ListBox . Otherwise, they are all visible or all hidden, and the chkVisible value must be set accordingly, by verifying just whether bolVisible=True.

    Me .chkVisible = IIf(bolVisible And bolHidden, Null, bolVisible = True)
End If

The lstNames_Change( ) event ends up making a call to the EnableControls ( ) procedure, which receives a Boolean argument (True/False) to enable/disable UserForm controls. Note that it receives a Boolean comparison against the mcolItemsSelected.Count property; the frmNames controls will be available if just one item is selected on lstNames ListBox .

    Call EnableControls ((mcolItemsSelected.Count = 1))
End Sub

Now that you already know that lstNames ListBox items selected are held by the mcolSelectedItems Collection variable, let’s play for a while with the lstNames selection and its interface.

Attention

Now that you know that lstNames ListBox selected items are held by the mcolItemsSelected Collection variable, return to the section “Adding a New Name Object” and take a look at the cmdNew_Click( ) event procedure and the way it uses mcolItemsSelected(1) to retrieve the selected item and return the Name object that must be set on the frmEditName .NameSelected property.

Showing Name Properties

Most times we use the frmNames UserForm to select and edit one Name object on its lstNames ListBox . Figure 5-23 shows what happens when the first lstNames item in Figure 5-20 is selected and you click the Show Details ControlButton (cmdDetails) to show the frmNames Detail section.
Figure 5-23.

Whenever you select just one item in the lstNames ListBox , the lstNames_Change( ) event fires, calling the ShowNameProperties ( ) procedure to show the selected Name properties and the EnableControls (True) procedure to enable all the controls

Showing and Hiding the UserForm Detail Section

When you click, the cmdDetails_Click( ) event fires, executing this code:

Const mconHeight1 = 268
Const mconHeight2 = 338
Private Sub cmdDetails_Click()
    Static sbolExended As Boolean
    Me .Height = IIf(sbolExended, mconHeight1, mconHeight2)
    Me .cmdDetails.Caption = IIf(sbolExended, "Show Details", "Hide Details")
    sbolExended = Not sbolExended
End Sub

The cmdDetails_Click( ) event uses the sbolExtended Static Boolean variable to hold the last state of the UserForm Height property (a Static variable does not lose its value between procedure calls).

If sbolExtended = True, the frmNames Detail section is shown and must be hidden, and vice versa. So, the UserForm Height property is changed according to the module-level constants mconHeigh1 and mconHeight2, defined by trial and error by dragging the frmNames bottom margin down and up in the VBA interface and noting the Height property.

Me .Height = IIf(sbolExended, mconHeight1, mconHeight2)

The same is made to the cmdDetails.Caption property: the sbolExtended value is used to determine whether the frmNames Details section is visible or hidden, alternating its caption from “Show Details” to “Hide Details”:

Me .cmdDetails.Caption = IIf(sbolExended, "Show Details", "Hide Details")

And the sbolExtended variable value alternates its value between True and False each time cmdDetails_Click( ) fires.

    sbolExended = Not sbolExended
End Sub

Quite simple, huh?

Showing Selected Name Properties

To show the selected Name object properties, the lstNames_Change( ) event calls the ShowNameProperties ( ) procedure, which executes this code:

Private Sub ShowNameProperties (intIndex)
    Dim rg As Range
    Dim strRefersTo As String
    Dim intPos As Integer
    With Me .lstNames
        Me .txtName = .Column (0, intIndex)
        Me .txtRefersTo = .Column (3, intIndex)
        Me .txtComment = .Column (5, intIndex)
        Me .chkVisible = (.Column (6, intIndex) = "Yes")
    End With
    If Me .chkSelectRangeName Then
        strRefersTo = Mid (txtRefersTo, 2)
        On Error Resume Next
        Set rg = Range(strRefersTo)
        If Err = 0 Then
            intPos = InStr (1, strRefersTo, "!")
            Worksheets (Left (strRefersTo, intPos - 1)).Activate
            Range(strRefersTo).Select
        Else
            Worksheets (1).Activate
            Range("A1").Select
        End If
    End If
End Sub

After declaring its variables, a With lstNames…End With loop is used to reference lstNames only once, and the txtName, txtRefersTo, txtComment, and chkVisible controls of the frmNames Detail section receive the associated Name object properties, using the appropriate lstNames ListBox column.

With Me .lstNames
     Me .txtName = .Column (0, intIndex)
     Me .txtRefersTo = .Column (3, intIndex)
     Me .txtComment = .Column (5, intIndex)
     Me .chkVisible = (.Column (6, intIndex) = "Yes")
End With

Did you note that frmNames has a “Select range name on workbook” check box at the bottom of the lstNames ListBox (chkSelectRangeName)? If chkSelectRangeName is checked, it means that whenever you select any Name object on lstNames, its cell range must be selected on the worksheet it belongs to. So if chkSelectRangeName=True, the procedure uses the VBA Mid ( ) function to remove the = character that precedes the Name. RefersTo property and stores the result in the strRefersTo variable.

If Me .chkSelectRangeName Then
    strRefersTo = Mid (txtRefersTo, 2)

An On Error Resume Next statement is executed before the code tries to set a reference to the cells range it represents.

On Error Resume Next
Set rg = Range(strRefersTo)

If the Name object is associated to any cell range, no error will be raised, and the sheet name must be extracted from strRefersTo so the desired worksheet can be activated before the range is selected. The code stores the position of the ! character that suffixes the sheet name on the intPos Integer variable, uses the VBA Left ( ) function to extract the sheet name, and uses it to reference it on the Worksheets collection before calling the Activate method.

If Err = 0 Then
    intPos = InStr (1, strRefersTo, "!")
    Worksheets ( Left (strRefersTo, intPos - 1)).Activate

And once the worksheet is activated, the cell range is selected.

Range(strRefersTo).Select

But if the Name object is associated to a constant formula, VBA will raise an error (Error: 1004: “method ‘Range’ of object ‘_Global’ failed”), and you make the decision to select cell A1 of the first sheet tab whenever this happens. Note that this time I use Worksheets (1) to reference the first sheet tab:

        Else
            Worksheets (1).Activate
            Range("A1").Select
        End If
    End If
End Sub

Figure 5-24 shows the selection of the DataWithError Name object range address (Sheet1!A6:B9) when chkSelectRangeName is checked.
Figure 5-24.

Whenever you check chkSelectRangeName, the Click( ) event will fire, and the cell range associated to the Name object will be selected on the sheet it belongs to. If the Name is associated to a constant formula, cell A1 of the first sheet tab will be selected instead

Attention

Whenever you click the chkSelectRangeName check box, the Click ( ) event fires, executing this simple code:

Private Sub chkSelectRangeName_Click()
    Call ShowNameProperties (mcolItemsSelected(1))
End Sub

Enable/Disable UserForm Controls

The last procedure called by the lstNames_Change( ) event, whenever one or more names are selected on the lstNames ListBox , is the EnableControls ( ) procedure.

The EnableControls ( ) procedure uses a quite useful, popular, and interesting technique to enable/disable controls on the frmNames UserForm interface. Every VBA control (and I believe that almost any possible control and object) has a Tag property, which is a read/write text string value that you can use to store anything you want, using up to 2,048 characters.

So, each frmNames control that I want to synchronize receives a special value on its Tag property (1 or 2) according to the type of synchronization it is performing.
  • Tag = 1 is used for every control that must be enabled when only one item is selected on the lstNames ListBox (or disabled when no item is selected).

  • Tag = 2 is used on every control that can be used when more than one item is selected on the lstNames ListBox (or disabled when any item is selected), as you can see in Figure 5-25.
    Figure 5-25.

    The frmNames UserForm uses the Tag property of all controls it wants to enable/disable to synchronize its interface. Whenever any control Tag = 1, this means that the control can be enabled/disabled by the UserForm EnableControls ( ) procedure

The EnableControls ( ) procedure executes this code:

Private Sub EnableControls (bolEnabled  As Boolean)
    Dim intI As Integer
    For intI = 0 To Me .Controls.Count - 1
        Select Case Me .Controls(intI).Tag
            Case "1"
                Me .Controls(intI).Enabled  = bolEnabled
            Case "2"
                Me .Controls(intI).Enabled  = (bolEnabled  Or mcolItemsSelected.Count > 0)
        End Select
    Next
End Sub

The EnableControls ( ) procedure uses the frmNames Controls collection to run through all its controls using a For int=0 to Controls.Count-1…Next loop. At each loop passage, it takes the current control Tag property and verifies its value using a Select Case statement.

For intI = 0 To Me .Controls.Count - 1
    Select Case Me .Controls(intI).Tag

If the control Tag = "1" (the Tag property is a String value), just one item was selected on the lstNames ListBox , and the control Enabled property receives the bolEnabled argument, enabling or disabling the control.

Case "1"
     Me .Controls(intI).Enabled  = bolEnabled

But if the control Tag = "2", you have more than one item selected in the lstNames ListBox , so the control must be enabled if bolEnabled = True or mcolItemsSelected.Count > 1 (to disable all controls that must be available for just a single selected item).

            Case "2"
                Me .Controls(intI).Enabled  = (bolEnabled  Or mcolItemsSelected.Count > 0)
        End Select
    Next
End Sub

Attention

Note that whenever bolEnabled  = False and mcolItemsSelected.Count = 0, all tagged controls will be disabled in the UserForm interface, which will happen whenever you open frmNames or delete a Name object and no item is selected in the lstNames ListBox .

Figure 5-26 shows EnableControls ( ) in action selecting just controls with Tag = "2" whenever more than one item is selected in the lstNames ListBox .
Figure 5-26.

Whenever the lstNames ListBox has more than one item selected, just the controls that can be used to operate on one or more Name objects becomes enabled (the ones with property Tag= "2")

Clearing frmNames Detail Section

To clear all the frmNames Detail controls whenever more than one item is selected in the lstNames ListBox , the frmNames _Change( ) event calls the ClearNameProperties( ) procedure, which executes this code:

Private Sub ClearNameProperties()
    Me .txtName = ""
    Me .txtRefersTo = ""
    Me .txtComment = ""
    Me .chkVisible = False
End Sub

Attention

Whenever you have cleaned up an undefined number of controls, consider using the same technique described in the previous section: use the controls’ Tag property to specifically tag them and clear all controls at once using a For…Next loop that runs through the UserForm Controls collection. Since text box and check box controls are cleared using different values ("" for text boxes and 0 for check boxes), you can add different Tag property values to each control type or use the VBA TypeOf(Controls(intI)) function to verify the control type and act accordingly.

Now that you know how many different procedures and VBA instructions run whenever you select one or more lstNames ListBox items, you must think about this unquestioning truth: computers are really fast!

Editing an Existing Name Object

Try to select any lstName ListBox item alone (like the last one) and click the Edit (cmdEdit) ControlButton to edit it in the frmEditName UserForm (Figure 5-27).
Figure 5-27.

Whenever you click any lstNames item and click the frmNames Edit ControlButton, the cmdEdit_Click( ) event fires and passes the selected Name object reference to the frmEditName .NameSelected property, which gets all it basic properties (Parent, RefersTo , Comment and Visible) and shows it in the frmEditName interface

This happens because the cmdEdit_Click( ) event fires, executing this code:

Private Sub cmdEdit_Click()
    Me .Hide
    If mcolItemsSelected.Count > 0 Then
        With frmEditName
            .NameSelected = Names ( Me .lstNames. Column (0, mcolItemsSelected(1)))
            .Show
        End With
    End If  
    Me .Show
End Sub

The bold instruction indicates that cmdEdit_Click( ) gets the Index of the selected lstNames item from the mcolItemsSelected(1) item collection and uses this item to get the Name.Name property (stored on the hidden lstNames. Column (0) column), uses the Application . Names collection to return the selected Name object reference, and stores it in the frmEditName .NameSelected property.

With frmEditName
     .NameFilter = Me .cboObjects
     .NameSelected = Names (Me .lstNames.Column (0, mcolItemsSelected(1)))

One single code line for so much activity!

When this happens, the frmEditName .NameSelected property’s Let( ) event fires and executes this code:

Public Property Let NameSelected(ByVal nm As Name)
    Dim intPos As Integer
    Set mName = nm
    If Not Me .NewName Then
        intPos = InStr (1, nm.Name, "!")
        If intPos = 0 Then
            Me .txtName = nm.Name
            Me .cboObjects = "Workbook "
        Else
            Me .txtName = Mid (nm.Name, intPos + 1)
            Me .cboObjects = nm.Parent.Name
        End If
        Me .txtComment = nm.Comment
        Me .txtRefersTo = nm.RefersTo
        Me .chkVisible = nm.Visible
    End If
End Property

Note that frmEditName Property Let NameSelected( ) receives the Name object reference on its nm As Name argument and passes this object reference to the frmEditName module-level object variable mName, so it can be used by the cmdOK and cmdCancel Click events.

Public Property Let NameSelected(ByVal nm As Name)
    Dim intPos As Integer
    Set mName = nm

The code verifies the frmEditName .NewName property, testing Not Me .NewName, which will run the frmEditName .NewName property’s Get( ) procedure. Since Not Me .NewName = True, the Name object properties must be retrieved and exhibited on the UserForm interface.

To know the Name object scope, the code searches the Name.Name property for the ! character used to separate the sheet tab name from the Name.Name property using the VBA InStr ( ) function and stores the result into the intPos Integer variable.

If there is no such character on the Name.Name property, the Name object has workbook scope, and the cboObject ComboBox is set accordingly.

intPos = InStr (1, nm.Name, "!")
If intPos = 0 Then
    Me .txtName = nm.Name
    Me .cboObjects = "Workbook "

If the Name object has a worksheet scope and txtName receives just the Name.Name property (without the preceding sheet name), using the VBA Mid ( ) function to extract it, and cboObject ComboBox has its value set by the Name.Parent.Name property, which returns the worksheet object sheet tab name.

        Else
            Me .txtName = Mid (nm.Name, intPos + 1)
            Me .cboObjects = nm.Parent.Name
        End If

The procedure finishes defining other Name object properties to the appropriate frmEditName controls.

        Me .txtComment = nm.Comment
        Me .txtRefersTo = nm.RefersTo
        Me .chkVisible = nm.Visible
    End If
End Property

Editing the Name Object

Since the desired Name object reference is set to the frmEditName module-level mName variable, you can change any of its properties, including its scope, which the Excel Edit Name dialog box doesn’t allow.

The Microsoft Excel New/Edit Name dialog box does not allow you to change any Name object scope but allows you to change the Name object’s Name property if the selected name is not used by any other Name object’s RefersTo property.

Let’s see a special case. The MyData range name is used by the SumMyData range name on its RefersTo property constant formula. You want to use Excel Edit Name dialog box to change the MyData name to anything else, like the MyNewData name, but Excel will not allow you to do that because this name is used by other Name. RefersTo property. Neither allows you to change the MyData scope, from Workbook to Sheet1 (Figure 5-28).
Figure 5-28.

The Excel Name Manager and Edit Name dialog box don’t allow you to change any Name object’s Name property when it is used by any other Name object (which happens on the MyData range name used by the SumMyData range name constant formula). In addition, they don’t allow you to change the name scope (Edit Name dialog box’s Scope ComboBox is disabled)

My vision of frmNames and frmEditName is to allow such interesting and desirable changes both on any Name object’s Name property and on its scope, fixing any possible formula conflict that may arise.

Supposing that you want to change the MyData Name object to MyNewData and its scope from Workbook to Sheet1 using frmNames , double-click the name to show it on frmEditName , type the new name in the txtName text box, select Sheet1 in the cboObjects ComboBox, and press the frmEditName OK button, which will run the cmdOK_Click( ) event (already explored in “Adding a New Name Object” earlier on this chapter), executing this partial code (where the first validating instructions were removed):

Private Sub cmdOK_Click()
    Dim nm As Name
    Dim strName As String
   ...
    If Not Me .NewName Then
        Call FixNameChange
        Me .NameSelected.Delete
    End If
    Set nm = Names .Add(strName, Me .txtRefersTo, Me .chkVisible)
    nm.Comment = Me .txtComment & ""
    Set mName = nm
    Unload Me
End Sub

You now have opened another great “parenthesis” on the frmEditName cmdOK_Click( ) event to explain how Sub FixNameChange ( ) works to change the proposed name change on any Excel formula of the entire workbook.

Searching and Replacing Formula Content

Since you are editing an existing Name object, frmEditName .NewName = False. The code first calls the Sub FixNameChange ( ) procedure to replace the old name with the new name inside any formula used on the workbook.

    If Not Me .NewName Then
        Call FixNameChange

The Sub FixNameChange ( ) procedure executes this code:

Private Sub FixNameChange ()
    Dim nm As Name
    Dim ws As Worksheet
    Dim rg As Range
    Dim rgInitial As Range
    Dim strName As String
    Dim strRefersTo As String
    strName = mName.Name
    strName = Mid (strName, InStr (1, strName, "!") + 1)
    If strName <> Me .txtName Then
        'Change Name references
        For Each nm In Names
            strRefersTo = nm.RefersTo
            If InStr (1, strRefersTo, strName) Then
                strRefersTo = Replace (strRefersTo, strName, Me .txtName)
                nm.RefersTo = strRefersTo
            End If
        Next
        'Change cells formulas references
        For Each ws In ActiveWorkbook.Worksheets
            Set rg = ws.Cells.Find(strName, , xlFormulas, xlPart)
            If Not rg Is Nothing Then
                Set rgInitial = rg
                Do
                    rg.Formula = Replace (rg.Formula, strName, Me .txtName)
                    Set rg = ws.Cells.FindNext (rg)
                    If rg Is Nothing Then Exit Do
                Loop While (rg.Address <> rgInitial.Address )
                Set rgInitial = Nothing
            End If
        Next
    End If
End Sub

Whenever you change the Name.Name property, you must make this change in two different places in any Excel workbook: inside any Name. RefersTo property and inside any worksheet cell formula.

Since any worksheet scope Name object has its Name property prefixed with the sheet name, the procedure first searches for the sheet name existence and removes it, storing just the name on the strName variable.

strName = mName.Name
strName = Mid (strName, InStr (1, strName, "!") + 1)

Then FixNameChange ( ) must first verify whether the Name object you want to save suffers any change on its Name property, comparing the module-level object variable mName.Name property to frmEditName txtName text box value.

If strName <> Me .txtName Then

If both name strings don’t match, you are proposing a Name property change, and the procedure will first use a For Each….Next loop to run through the Names collection searching for the current Name property in any Name. RefersTo property. Note that strRefersTo holds the Name. RefersTo property, and the VBA InStr ( ) function is used to find the current Name property stored in the strName variable.

For Each nm In Names
    strRefersTo = nm.RefersTo
    If InStr (1, strRefersTo, strName) Then

Since InStr ( ) returns the initial position of strRefersTo inside strName, whenever InStr ( ) > 0 (strRefersTo found inside strName), the code uses the VBA Replace ( ) function to make the desired change and stores the replaced property again in the Name. RefersTo property.

strRefersTo = Replace (strRefersTo, strName, Me .txtName)
nm.RefersTo = strRefersTo

The VBA Replace ( ) function is quite useful because it can quickly search and replace any substring inside a desired string, returning another string with the desired replacement. It has this syntax:

Replace (Expression, Substring, Replacement, [Start, [Count, [Compare]]] )

In this code:
  • Expression: This is required; it is the string expression containing the substring to replace.

  • Substring: This is required; it is the substring being searched for.

  • Replacement: This is required; it is the replacement string.

  • Start: This is optional; it is the position in Expression where the substring search is to begin. If omitted, 1 is assumed.

  • Count: This is optional; it is the number of substring substitutions to perform. If omitted, the default value is –1, meaning “all possible substitutions.”

  • Compare: This is optional; it is the type of comparison to use when evaluating substrings.
    • Vbbinarycompare: This makes a binary comparison (case sensitive).

    • Vbtextcompare: This makes a textual comparison (case insensitive).

And once the For...Next loop ends, all possible formulas inside any workbook Name object will be correctly replaced by the new proposed Name property.

And once this is made, it is time to search all workbook cells formulas and make the same substitution, and this process is made by the Range. Find method.

Using the Range.Find Method

Microsoft Excel has the Find and Replace dialog box, which is used to search and replace items inside a single worksheet or on the entire workbook. This dialog box uses the Excel Range object’s Find method, which has this syntax:

Expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

In this code:
  • Expression: This is an object variable that represents a Range object.

  • What: This is required; it is the data to search for.

  • After: This is optional; it is a single cell after which you want the search to begin. It corresponds to the position of the active cell when a search is done from the user interface. The search will begin after this cell, which will not be searched until the method wraps back around to it. If you don’t specify this argument, the search starts after the cell in the upper-left corner of the range (the default is the current selection).

  • LookIn: This is optional; it is where the information will be searched (values, formulas, or comments) and can be set to xlValue (default), xlFormula, or xlComments.

  • LookAt: This is optional; it is the scope to be searched in LookIn, all or part, and can be set to the following XlLookAt constants: xlWhole (default) or xlPart.

  • SearchOrder: This is optional; it is the search order, by row or column. It can be set to one of the following XlSearchOrder constants: xlByRows (default) or xlByColumns.

  • SearchDirection: This is optional; it is the search direction to be made and can be set to the following XlSearchDirection constants: xlNext (default) or xlPrevious.

  • MatchCase: This is optional; it allows the search to be case sensitive. The default value is False.

  • MatchByte: This is optional; it is used only if you’ve selected or installed double-byte language support. Set it toTrue to have double-byte characters match only double-byte characters. Set it to False (default) to have double-byte characters match their single-byte equivalents.

  • SearchFormat: This is optional; it is for making search use specific character formatting options.

Attention

All Range. Find arguments are used by the Excel Find and Replace dialog box, and every change you make to one of them will be shown in the Excel Find and Replace interface.

The Range. Find method returns Nothing if any cell is found or if a Range object representing a single cell was found. You can continue to search more cells with the same settings using the Range. FindNext or Range.FindPrevious method.

Note that when the search reaches the end of the specified search range, it wraps around again to the beginning of the range. So, to stop a search when this wraparound occurs, save the address of the first found cell and then test each successive found-cell address against this saved address.

The Range. Find is method is quite interesting because it searches any range size. Since any Worksheet object has a Cells property, which returns a Range object with all worksheet cells, any time you use Cells.Find you are in fact searching all active sheet cells!

So, the procedure needs to search every Worksheet object inside the workbook using a For…Each loop to run through the Worksheets collection and then use the Worksheet object’s Cells property to return a range object and apply its the Range. Find method, as follows:

'Change cells formulas references
For Each ws In ActiveWorkbook.Worksheets
    Set rg = ws.Cells.Find(strName, , xlFormulas, xlPart)

This last instruction will search all cells of a single Worksheet object ( represented by the ws variable), looking in the cell formula (xlFormulas) for any part (xlPart) that has what is stored in the strName variable (which holds the current Name.Name property).

If no cell is found having strName inside any part of its formula, the rg as Range variable will hold Nothing, and the procedure will end the If…End If instruction, selecting another Worksheet object in the Worksheets collection.

For Each ws In ActiveWorkbook.Worksheets
    Set rg = ws.Cells.Find(strName, , xlFormulas, xlPart)
    If Not rg Is Nothing Then
       ...
    End If
Next

But if any cell is found having strName inside any formula part, the rg variable will have a reference to it, and in this case, you must store the reference of this first cell found in the rgInitial variable.

If Not rg Is Nothing Then
   Set rgInitial = rg

And a Do...Loop is initiated to change this first cell formula using the VBA Replace ( ) function to change any current Name.Name property (strName) by the new proposed name ( Me .txtName).

Do
  rg.Formula = Replace (rg.Formula, strName, Me .txtName)

Now is the interesting part. You must continue the search through all other worksheet cells using the Range. FindNext method, which has this syntax:

Expression .FindNext (After)

In this code:
  • Expression: This is an object variable that represents a Range object.

  • After: This is optional; it is a single cell after which you want to search (the position of the active cell when a search is done from the user interface). The search will begin after this cell, meaning that it will not be searched until the method wraps back around to it. If this argument is not specified, the search starts after the cell in the upper-left corner of the range.

Attention

The Range.FindPrevious method obeys the same rules of Range. FindNext when searching backward.

So, you must continue to search the current worksheet after the last cell found using the Range. FindNext method, passing to its After argument a reference to the last found cell.

Set rg = ws.Cells.FindNext (rg)

And when the Range. FindNext must be executed, one of these conditions may occur: no other cell is found (rg = Nothing) or the first found cell searches again. And you need to make a double comparison.

Whenever you need to verify whether an object variable is Nothing (doesn’t point to any object), you must make this test alone. Let me put this in other words: you cannot test on a single instruction if (rg = Nothing) or (rg. Address = rgInitial. Address ) because if rg = Nothing, rg. Address returns a VBA error. rg points to no Range object, so it cannot have an Address property! So, the next row verifies whether the Range. FindNext method finds another cell. If it is equal to Nothing, you can stop searching on this Worksheet object and the Exit Do instruction will be executed.

Set rg = ws.Cells.FindNext (rg)
If rg Is Nothing Then Exit Do

Otherwise, another cell is found, and the Do…Loop must continue changing its Formula property. Note that this time the Address property is used to compare the initial range object with the new found one as a condition to end the Do...Loop.

Do
  rg.Formula = Replace (rg.Formula, strName, Me .txtName)
  Set rg = ws.Cells.FindNext (rg)
  If rg Is Nothing Then Exit Do
Loop While (rg. Address <> rgInitial. Address )

Attention

The Range.Value property (the default property) returns the range value, which can be either a single value or an array of values, so you cannot compare range values directly. The same applies to the Range.Name property, which just exists for any individual named cell. So, since Range. Find and Range. FindNext return a single cell reference, you can surely use the Range. Address property to compare the initial and found ranges.

If the last found cell is the same cell processed (rg. Address = rgInitial.Adress), it can’t be processed again, because if you are changing a name just by adding a suffix (like changing the MyData name to MyData2), when the search returns to the first cell, it will find again the MyData prefix inside the now changed MyData2 formula and will substitute it again and again, until you end up with a range name of MyData222222… (with 2 repetitions enough to fill up the maximum 256 characters formula limit), and Excel will raise an error.

So, when the code returns to the first changed cell, the Do…Loop ends, the rgInitial variable is set to Nothing, and another worksheet object is processed again, until all worksheet cell formulas are correctly processed for the proposed name change.

        Loop While (rg.Address <> rgInitial.Address )
        Set rgInitial = Nothing
    End If
Next

Attention

Go back to Sub FixNameChange ( ) and note how easy it is to use the Range. Find method to search and replace any workbook cell value using VBA, the same way the Excel Find and Replace dialog box does!

Now that you know how FixNameChange ( ) works, so let’s return to the frmEditName cmdOK_Click( ) event and continue the VBA process of editing an existing Name object.

Getting Back to the frmEditName cmdOK_Click Event

After all the workbook formulas have been adequate processed, the cmdOK_Click( ) event must delete the currently edited Name object, because if you change the Name.Name property or the Name. RefersTo property by changing just its scope (from Workbook to any sheet name, and vice versa), you end up with two names (two different Name objects, having different Name properties with the same Name property but different scopes).

Private Sub cmdOK_Click()
    Dim nm As Name
    Dim strName As String
   ...
    If Not Me .NewName Then
        Call FixNameChange
         Me .NameSelected.Delete
    End If

And once the Name object is deleted, it is re-created with the new desired Name properties, the same way you created a new Name.

    Set nm = Names .Add(strName, Me .txtRefersTo, Me .chkVisible)
    nm.Comment = Me .txtComment & ""

After the Name object is re-created, the frmEditName mn object module-level variable (which returns the frmEditName .NameSelected property value) is associated to the new added name, and the UserForm is unloaded from memory.

    Set mName = nm
    Unload Me
End Sub

Attention

According to the Microsoft Excel documentation, you do not need to delete a name to change its properties. Just use again the Names collection’s Add method to re-create it. But you must delete it before using the Add method whenever you want to change its Name property or its scope.

Canceling Name Editing

If you decide not to make changes to the edited Name object, you can press the keyboard Esc key or click the frmEditName Cancel ControlButton (cmdCancel, which has its Cancel property set to True), and this code will be executed:

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Synchronizing the frmEditName and frmNames Interfaces

Whenever you click the frmEditName cmdOK of cmdCancel control buttons, the UserForm is unloaded from memory and its UserForm_Terminate ( ) event fires, executing this code:

Private Sub UserForm_Terminate ()
     With frmNames
        .cboObjects = Me .NameFilter
        If Not (Me .NameSelected Is Nothing) Then
            .NameSelected = Me .NameSelected
        End If
        .Show
    End With
End Sub

Note that frmEditName .NameFilter (what was selected on the frmNames cboObjects ComboBox) is used to define again the frmNames .cboObjects value (which will cascade-fire the cboObjects_Change( ) event).

     With frmNames
        .cboObjects = Me .NameFilter

Next, the code verifies frmEditName .NameSelected has any name associated with it (which will not happen whenever frmNames has no item selected). If this is true, the frmNames .NameSelected Property Let( ) procedure receives the frmEditName .NameSelected Property Get( ) procedure, selecting the appropriate item on in frmNames lstNames ListBox , which is shown by frmNames whenever the Show method finally executes.

        If Not (Me .NameSelected Is Nothing) Then
            .NameSelected = Me .NameSelected
        End If
        .Show
    End With
End Sub

Attention

To see a Name.Name property change in action, you must create one cell formula that references the name (like =Sum(MyData)) and then copy and paste this cell to other multiple cells of any sheet tab and then perform the name change. You will see that VBA will change all Name objects and cell formula references to the new desired name, keeping everything working as it should on the workbook. Try it!

Resizing an Existing Name Object

One common operation on most Excel applications is to resize a given Name object whose RefersTo property is associated with a contiguous worksheet cell addresses by adding or deleting one or more rows or columns to/from it. This operation is performed by the Range. Resize property, which has this syntax:

Expression .Resize (RowSize, ColumnSize)

In this code:
  • Expression: This is required; it is an expression that returns a Range object to be resized.

  • RowSize: This is optional; it is the total number of rows in the new range. If omitted, the number of rows in the range remains the same.

  • ColumnSize: This is optional; it is the number of columns in the new range. If omitted, the number of columns in the range remains the same.

So, whenever you want to resize a given range name, you must use the Range Rows and/or Columns collection’s Count property to retrieve the current number of rows/columns and then add/subtract to these values to/from the desired final range dimensions.

The next syntax resizes a hypothetical Name object Range1 address, by adding to it one more row:

Dim rg as Range
Set rg = Range(“Range1”).Resize (Range(“Range1”).Rows.Count + 1)

Note that since the second resize argument is missing, the Range1 Name object will keep its current column number. This operation can be shortened by first setting the desired Name object to a Range object variable, as follows:

Dim rg as Range
Set rg = Range(“Range1”)  ‘ Range1 is the Name.Name property
Set rg = rg.Resize (rg.Rows.Count +1)

As you can see, the final operation ends with an rg object variable with the desired dimensions, but no change had been made to the Name object’s RefersTo property: it remains the same!

To really resize a Name object by adding/subtracting it to/from one or more rows/columns, you must use the Range.Name property to name the new range with the Name.Name property. The next instructions really change the Range1 Name object, adding one more row to its RefersTo property:

Dim rg as Range
Set rg = Range(“Range1”)  ‘ Range1 is the Name.Name property
rg.Resize (rg.Rows.Count +1).Name = “Range1”

You saw that? You must perform a double operation: rg. Resize returns a new Range object with the desired dimensions, which has a Name property, which is set to the desired Name object, effectively resizing it!

Now let’s return to frmNames . Whenever you select a valid Name object in the lstNames ListBox (one that is associated to any cell address, not a constant formula) and click the Resize ControlButton (cmdResize), the cmdResize_Click( ) event fires, executing this code:

Private Sub cmdResize_Click()
    Dim nm As Name
    Dim rg As Range
    Dim intIndex As Integer
    On Error Resume Next
    intIndex = mcolItemsSelected(1)
    Set nm = Names (Me .lstNames.Column (0, intIndex))
    Set rg = Range(nm.RefersTo )
    If Err = 0 Then
        rg.Resize (rg.Rows.Count + Me .txtAddRows, rg.Columns.Count + Me .txtAddColumns).Name = nm.Name
        Call FilllstNames
        Call SelectItems
        Call chkSelectRangeName_Click
    End If
End Sub

Since cmdResize will be enabled only when just one item is selected in the lstNames ListBox , after declaring the variable it needs, the code begins by disabling VBA errors, stores the selected lstName.Index value from the mcolItemsSelected(1) collection on the intIndex variable, and uses this value to set a reference to the selected Name object:

On Error Resume Next
intIndex = mcolItemsSelected(1)
Set nm = Names (Me .lstNames.Column (0, intIndex))

Once the selected Name object is retrieved, the code tries to set a Range object reference to it, using the Name.Name property.

Set rg = Range(nm.RefersTo )

Since the selected Name object can be associated to a constant formula, if you select such types of Name, VBA will raise an error (Error = 1004, “method ‘Range’ of object ‘_Global’ failed”). So, the next instruction verifies whether there is any error setting the Range object reference, and if it is true, the procedure ends doing nothing.

    If Err = 0 Then
       ...
    End If
End Sub

But if the Range object reference is adequately set, the Name is associated to a valid range address: the Range. Resize property is evoked, adding to the Range. Address property the default values of the txtAddRows (1) and txtAddcolumns (0) text boxes (the ones below cmdResize in the frmNames interface).

This will resize the Range object, which is then named using the Name.Name property, effectively resizing the Name object address.

rg.Resize (rg.Rows.Count + Me .txtAddRows, rg.Columns.Count + Me .txtAddColumns).Name = nm.Name

And once this operation is done, the frmNames interface must be synchronized by calling FilllstNames( ) (to update lstNames ListBox ), SelectedItems( ) (to reselect the resized name on lstNames), and chkSelectRangeName_Click( ) events (to select the new dimensions of the Name object on the worksheet it belongs to if chkSelectRangeName is checked).

Call FilllstNames
Call SelectItems
Call chkSelectRangeName_Click

This is simple code used by the Sub SelectItems( ) procedure to reselect the same lstNames items after any change is made to one or more Name object properties.

Private Sub SelectItems()
    Dim varItem As Variant
    'Reselect names on ListBox
    Me .lstNames.ListIndex = mcolItemsSelected(1)
    For Each varItem In mcolItemsSelected
        mbolCancelEvent = True
        Me .lstNames.Selected(varItem) = True
    Next
End Sub

As you can see, the code begins by selecting the first Collection item in the ListBox , changes the ListIndex property, and then performs a For Each…Next loop through all the mcolItemsSelected collection items, setting mbolCancelEvent = True to avoid cascading the lstNames_Change( ) event and then reselecting the desired items on the lstNames ListBox .

Attention

If the chkSelectRangeName check box is checked before you click the frmNames Resize ControlButton, you will see the new name cells selected on the worksheet it belongs to. The chkSelectRangeName_Click( ) event calls the Sub ShowNameProperties ( ) procedure, analyzed in the section “Show Selected Name Properties” earlier in this chapter.

Note that you can use negative values on the txtAddRows and txtAddColumns text boxes to shrink any range name.

Figure 5-29 shows how the MyData Name object has the RefersTo property resized by clicking the cmdResize button, accepting the frmNames default resizing values (adding to it one row).
Figure 5-29.

When you select a valid Name object (one associated to any range address) and click the frmNames Resize ControlButton, the cmdResize_Click( ) event fires and adds the specified number of rows and columns to the Name.RefersTo property. This time the MyData Name was added by one row, which can be seen on the worksheet since the “Select range name on workbook” option is checked

Performing Multiple Name Properties Changes

The frmNames UserForm allows you to delete one or more range names or change the Visible and Comment properties at once: a desired Name object operation not performed by the Excel Name Manager dialog box (which allows multiple deletions).

Since all selected Name objects are stored in the mcolItemsSelected collection, whenever desired you can run through all collection items and perform the same operations on all of them. Let’s see this in action!

Changing the Name .Visible Property

Although every Name object has a Visible property, Excel doesn’t allow the user to change it, unless you use VBA code to do it. When any name has Visible = False, it doesn’t appears anymore in the Excel Name box or Excel Name Manager, which is quite desirable for most Excel applications to hide development details from users’ eyes.

To change any Name object’s Visible property using the frmNames interface, follow these steps:
  1. 1.

    Select the desired Name objects on the lstNames ListBox .

     
  2. 2.

    Click the frmNames Show Details ControlButton.

     
  3. 3.

    Click the Visible check box.

     

Whenever you click the chkVisible check box, the chkVisible_AfterUpdate( ) event fires, executing this simple code:

Private Sub chkVisible_AfterUpdate()
    Dim varItem As Variant
    For Each varItem In mcolItemsSelected
        Names (CStr(Me .lstNames.Column (0, varItem))).Visible = Me .chkVisible
    Next
    Call FilllstNames
    Call SelectItems
End Sub

Quite simple, huh? A For Each…Next loop is performed through the mcolItemsSelected collection, and each selected Name object has the Visible property changed to the chkVisible state, effectively making it hidden/visible each time the chkVisible check box has its value changed.

Note that Sub FilllstNames( ) is called to update the frmNames interface with the new Name object properties, and Sub SelectItems( ) is called to select again the same lstNames items.

Attention

The chkVisible_AfterUpdate( ) event does not cascade-fire whenever the chkVisible value is changed, which happens whenever one or more items is selected in the lstName ListBox .

Changing the Name.Comment Property

This is also quite simple. Whenever you want to add the same Comment property to more than one Name object, just select the desired items in the lstNames ListBox , click the frmNames Show Details ControlButton, type the desired comment in txtComment, and press Enter.

The txtComment_ AfterUpdate ( ) event will fire, executing this code:

Private Sub txtComment_AfterUpdate()
    Dim varItem As Variant
    Dim strMsg As String
    Dim strTitle As String
    If mcolItemsSelected.Count > 1 Then
        strMsg = "Apply the same comment to all " & mcolItemsSelected.Count & " selected names?"
        strTitle = "Comment all selected names?"
        If MsgBox (strMsg, vbYesNo + vbDefaultButton2 + vbCritical, strTitle) = vbNo Then
            Exit Sub
        End If
    End If
    Call FilllstNames
    Call SelectItems
End Sub

Note that this time a warning is raised every time there is more than one item selected on the lstName ListBox .

    If mcolItemsSelected.Count > 1 Then
        strMsg = "Apply the same comment to all " & mcolItemsSelected.Count & " selected names?"
        strTitle = "Comment all selected names?"
        If MsgBox (strMsg, vbYesNo + vbDefaultButton2 + vbCritical, strTitle) = vbNo Then
            Exit Sub
        End If
    End If

If the MsgBox ( ) Yes button is selected, all selected Name objects will receive the same comment, executing a For Each…Next loop through all the mcolItemsSelected collection items and changing the Comment property—the same way you changed one or more Name.Visible properties.

    For Each varItem In mcolItemsSelected
        Names (CStr(Me .lstNames.Column (0, varItem))).Comment = Me .txtComment & ""
    Next

Figure 5-30 shows how easy it is to change one or more Name objects’ Visible property, hiding it from being seen in the Excel Name box or Name Manager dialog box, as well as associating the same comment to all of them.
Figure 5-30.

If you select one or more Name objects in the lstNames ListBox, you can change its Visible and Comment properties at once. A hidden range name can’t be seen in the Excel Name box or Name Manager dialog box

Deleting Name Objects

To delete one or more name objects, select the desired names and click the frmNames Delete button, which will fire the cmdDelete_Click( ) event, executing this simple code:

Private Sub cmdDelete_Click()
    Dim varItem As Variant
    Dim strMsg As String
    Dim strTitle As String
    If mcolItemsSelected.Count = 1 Then
        strMsg = "Confirm deletion of selected name?"
        strTitle = "Delete Name?"
    Else
        strMsg = "Confirm deletion of all " & mcolItemsSelected.Count & " names selected?"
        strTitle = "Delete selected names?"
    End If
    If MsgBox (strMsg, vbYesNo + vbDefaultButton2 + vbCritical, strTitle) = vbYes Then
        For Each varItem In mcolItemsSelected
            Names (CStr(Me .lstNames.Column (0, varItem))).Delete
        Next
        Set mcolItemsSelected = New Collection
        Call FilllstNames
        Call ClearNameProperties
        Call EnableControls (False)
    End If
End Sub

Since one or more Name object is about to be deleted—and this operation can be undone, unless you close the workbook without saving it and reopen it again—the code asks for a confirmation before proceeding.

If mcolItemsSelected.Count = 1 Then
    strMsg = "Confirm deletion of selected name?"
    strTitle = "Delete Name?"
Else
    strMsg = "Confirm deletion of all " & mcolItemsSelected.Count & " names selected?"
    strTitle = "Delete selected names?"
End If
If MsgBox (strMsg, vbYesNo + vbDefaultButton2 + vbCritical, strTitle) = vbYes Then

If the MsgBox ( ) Yes button is selected, a For Each…Next loop runs through all the mcolItemsSeleted collection items and performs the Names collection’s Delete method, removing all selected Name objects from the workbook.

For Each varItem In mcolItemsSelected
    Names (CStr(Me .lstNames.Column (0, varItem))).Delete
Next

And then it clears the mcolItemsSelected collection by attributing to it a New Collection (with zero items selected).

Set mcolItemsSelected = New Collection

Changing the Name.RefersTo Property

The last VBA technique you will see performed by the frmNames UserForm mimics the way the Excel Name Manager dialog box allows the addition of the Name. RefersTo property using a text box control and some command buttons.

Whenever you select any Name object on frmNames and click the Details ControlButton, you will see its properties in the UserForm Details section. Note that the txtRefersTo text box is enabled, and on the right side you can see three CommandButtons mimicking how the Excel Name Manager does it (Figure 5-31).
Figure 5-31.

The frmNames UserForm mimics the way the Excel Name Manager dialog box allows changes to the Name.RefersTo property, using a TextBox control (txtRefersTo) along with three CommandButtons (from left to right, cmdDiscard, cmdAccept, and cmdRefersTo)

Note in Figure 5-31 that cmdRefersTo is the only enabled CommandButton. If you click it, the cmdRefersTo_Click( ) event will fire and execute this code:

Private Sub cmdRefersTo_Click()
    Dim varRange As Variant
    Dim intPos As Integer
    Const conFormula = 0
    varRange = Application .InputBox("Cells selected:", "Select range cells", Me .txtRefersTo, , , , , conFormula)
    If varRange <> False Then
        varRange = Application .ConvertFormula(varRange, xlR1C1, xlA1)
        'Search for Workbook reference
        intPos = InStr (1, varRange, "]")
        If intPos > 0 Then
            varRange = "'" & Mid (varRange, intPos + 1)
        End If
        'Search for Sheet name
        intPos = InStr (1, varRange, "!")
        If intPos = 0 Then
            varRange = "'" & ActiveSheet.Name & "'!" & Mid (varRange, 2)
        End If
        'Search for "='
        If Left (varRange, 1) <> "=" Then
            varRange = "=" & varRange
        End If
        Me .txtRefersTo = varRange
        Call EnableEditing(True)
    End If
End Sub

If you look at the section “Inserting a New Name by Selecting a Range Address” earlier in this chapter, you will see that this code is quite similar to that used on the frmEditName cmdRefersTo CommandButton (it uses the Application .Inputbox method to allow the selection of worksheet cells), except with its last instruction (bold in the last listing), which is used to enable/disable the frmName UserForm controls whenever you try to change any Name. RefersTo property.

The Sub EnableEditing( ) procedure executes this code:

Private Sub EnableEditing(bolEnabled  As Boolean)
    Dim intI As Integer
    For intI = 0 To Me .Controls.Count - 1
        If Me .Controls(intI).Name <> "txtRefersTo" Then
            If Len(Me .Controls(intI).Tag) > 0 Then
                Me .Controls(intI).Enabled  = Not bolEnabled
            End If
        End If
    Next
    Me .cmdAccept.Enabled  = bolEnabled
    Me .cmdDiscard.Enabled  = bolEnabled
End Sub

Note that it uses a For…Next loop to run through the frmNames .Controls collection testing if the control Name property is different from txtRefersTo:

For intI = 0 To Me .Controls.Count - 1
    If Me .Controls(intI).Name <> "txtRefersTo" Then

It verifies whether the control Tag property has something inside using the VBA Len( ) function. If this is true, the control is enabled/disabled, according to the procedure bolEnabled argument value.

If Len(Me .Controls(intI).Tag) > 0 Then
    Me .Controls(intI).Enabled  = Not bolEnabled
End If

And when the loop finishes, it changes the Enabled property of both cmdAccept and cmdDiscard ControlButtons.

    Me .cmdAccept.Enabled  = bolEnabled
    Me .cmdDiscard.Enabled  = bolEnabled
End Sub

This operation has the effect of disabling all controls except txtRefersTo and lstNames, whenever bolEnabled = True, and enabling them otherwise.

But the frmNames cmdRefersTo ControlButton is not the only way to change the Name. RefersTo property. You can also type any value to the txtRefersTo property to change the value. And whenever anything is typed in the txtRefersTo property, three different key events fire:
  • KeyDown( ), which fires when any printable key is pressed

  • KeyUp( ), which fires when any printable key is released

  • KeyPress( ), which fires when any keyboard key is pressed, including Control, Alt, and Tab, and distinguishes the keys from the numeric keypad on the left of any keyboard

In other words, the KeyDown and KeyUp events report the exact physical state of the keyboard: pressed or released, respectively. The KeyPress event does not report if the keyboard state for the key is up or down; it simply supplies the character that any key represents.

Attention

Although the txtRefersTo_KeyPress( ) event will fire whenever you press the Shift, Ctrl, or Alt keys, the text box content will not be changed, so you don’t use this event to catch text box changes.

Both the KeyDown( ) and KeyUp( ) events receive two “by value” arguments: KeyCode and Shift.
  1. 1.

    KeyCode means the printable ASCII code key, which can be verified in the VBA Immediate window using the VBA Chr( ) function when the code is in Break mode , this way:

    ?Chr(KeyCode)

     
  2. 2.

    Shift means the pressing state of Shift, Ctrl, and Alt keys, where Shift = 1, Ctrl = 2, and Alt = 4, and by combining the values, you know which keys were pressed along with any other keyboard key. (For example, if the KeyDown( ) event argument Shift=3, it means that the Shift+Ctrl keys were pressed.)

     

Attention

Yes, there is a VBA Asc( ) function that does the opposite. Given a key string like A, it returns the associated ASCII code.

The Backspace key is part of the ANSI character set, but the Delete key isn’t. If you delete a character in a control by using the Backspace key, you cause a KeyPress event; if you use the Delete key, you don’t.

The KeyPress( ) event receives just one argument, KeyAscii, which returns the associated ASCII code of any keyboard pressed (including keys Esc, Tab, Shift, Ctrl, Alt, Enter, and so on).

Whenever you type anything in the frmNames txtRefersTo text box, the KeyDown( ) event fires, executing this code:

Private Sub txtRefersTo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If Not mbolEditRefersTo Then
        mbolEditRefersTo = True
        Call EnableEditing(True)
    End If
End Sub

As you can see, the code uses the mbolEditRefersTo module-level variable to verify whether txtRefers to is in editing mode. If mbolReferTo = False, then Not mbolRefersTo = True, meaning that it was the first key pressed to change the txtRefersTo value. So, mbolReferTo becomes True, and the procedure makes a call to Sub EnableEditing( ) just for the first key pressed.

Figure 5-32 shows what happens to the DataWithError Name object when the RefersTo property is changed from Sheet1!$B$6:$B$9 (see Figure 5-31) to Sheet1!$B$6:$B$91 (note that all UserForm controls were disabled except the txtRefersTo and lstNames ListBoxes )!
Figure 5-32.

Whenever you click cmdRefersTo and select another cell range or type anything inside txtRefersTo to change the selected Name.RefersTo property, Sub Enabled Editing( ) is executed, disabling most UserForm controls, except the txtRefersTo and lstNames ListBoxes , while the cmdDiscard and cmdAccept command buttons become enabled !

Whenever you make any change to the txtRefersTo text box, you have three possibilities:
  • Discard the change by clicking cmdDiscard CommandButton

  • Accept the change by clicking cmdAccept CommandButton

  • Click the New command button or in the lstNames ListBox

When you click cmdDiscard, the Click( ) event fires, executing this code:

Private Sub cmdDiscard_Click()
    mbolEditRefersTo = False
    Me .txtRefersTo = Me .lstNames.Column (3, Me .lstNames.ListIndex)
    Call EnableEditing(False)
End Sub

Quite simple, huh? It sets mbolEditRefersTo = False, as a clear indication that txtRefersTo is not in edit mode anymore, updates the txtRefersTo value to the current Name. RefersTo property (which is stored in lstNames. Column (3)), and calls Sub EnableEditing(False), which will revert the frmNames control’s state.

When you click cmdAccept, you want to make changes to the Name.txtRefersTo property, so the cmdAccept_Click( ) event must execute this code:

Private Sub cmdAccept_Click()
    Dim nm As Name
    mbolEditRefersTo = False
    Set nm = Names (Me .lstNames.Column (0, mcolItemsSelected(1)))
    nm.RefersTo = Me .txtRefersTo
    Call EnableEditing(False)
    Call FilllstNames
    Call SelectItems
End Sub

This time, the Name. RefersTo property is updated according to the new value typed in the txtRefersTo text box, Sub EnableEditing(False) is called to return frmNames controls to their default state, Sub FilllstNames( ) is called to update the lstNames ListBox , and Sub SelectItems( ) is called to reselect the edited Name object on lstNames. Easy, huh?

The last special case happens when the user tries to abandon the txtRefers to change by clicking the frmNames Edit CommandButton or lstNames ListBox . If the user clicks the Edit button, you must do nothing, because frmNames will be unloaded, while frmEditName is opened.

But if the user clicks the lstNames ListBox , it is trying to abandon the txtRefersTo new value, so the code uses the lstNames_Enter( ) event to raise a MsgBox ( ) with the same message used by the Excel Name Manager, executing this code:

Private Sub lstNames_Enter()
    Dim strMsg As String
    Dim strTitle As String
    If mbolEditRefersTo Then
        strMsg = "Do you want to save the changes you made to the name reference?"
        strTitle = "Change name reference?"
        Select Case MsgBox (strMsg, vbYesNo + vbDefaultButton2 + vbCritical, strTitle)
            Case vbYes
                Call cmdAccept_Click
            Case Else
                Call cmdDiscard_Click
        End Select
    End If
End Sub

This time, the MsgBox ( ) offers two options: accept or discard the change (discard is the default option, by clicking No). Whichever decision the user makes, cmdAccept or cmdDiscard Click( ) events are called to accept or discard the Name. RefersTo editing! Figure 5-33 shows the message received whenever an attempt is made to abandon the txtRefersTo editing.
Figure 5-33.

This message is issued by the lstNames_Enter( ) event procedure that warns the user that it is about to abandon the changes made to the selected Name.RefersTo property

Attention

If you are wondering why I used the lstNames_Enter( ) event procedure instead of any txtRefersTo event to detect any txtRefersTo change, it is because if I used any txtRefersTo event, the user could not click cmdDiscard or cmdAccept without firing the event. By transferring the event to the only control it can click (ListNames), I use exactly the same programming technique employed by the Excel Name Manager. The user can change the txtRefersTo content, click cmdAccept or cmdDiscard, or be warned that Excel doesn’t make such decisions after attempt to change the Name. RefersTo property.

Chapter Summary

In this chapter, you learned about the following:
  • How to use two VBA UserForm objects ( frmNames and frmEdit) to mimic the way Excel Name Manager behaves

  • How to use Excel Name object properties and methods to create and edit any Name object

  • How to use an Excel Names collection to select a given Name object

  • That Excel Names collection allows the insertion of different scope Name objects (workbook or sheet scope)

  • That you can use the Application .InputBox method to select a valid range address to any Name object

  • That to change a Name address by adding or removing rows/columns, you must successively use the Range. Resize and Range.Name properties

  • That when Name.Visible property is set to False, you can hide the Name object on Excel Name box or Excel Name Manager dialog box

  • That to change the Name.Visible property you must use VBA code

  • How to use VBA Collection object variable to hold programming data

  • How to use the Range. Find method to search for worksheet information

  • How to use the Excel Replace ( ) function to easily find and replace data on any string variable

  • Many different VBA programming techniques to master VBA programming and worksheet applications development

In the next chapter, you will learn about special Range object methods and properties that can be used to enhance the data management of your worksheet applications.

Copyright information

© © The Editor(s) (if applicable) and The Author(s) 2016

Authors and Affiliations

  • Flavio Morgado
    • 1
  1. 1.Rio de JaneiroBrazil

Personalised recommendations