How can I determine if the current selection belongs to any named ranges in my workbook?
	Why does Application.Offset() fail? I wish to use the XLM macro function instead of the VBA Offset() function.
	Why does the FillAcrossSheets method fail when I use xlFormulas as the parameter?
	How tough is it to write a custom VBA function to mirror the Excel 4.0 macro Offset() function?
	Does VBA have a function equivalent to the XLM Offset() function?
	Is there a better way to copy a range to all my worksheets than doing a Copy and Paste?
	How do I replace all occurrences of a string in a column?
	Does resizing a range overwrite values in other cells? What does the Resize method resize?
	How do you refer to a named range in another workbook? The Range method gives an error.
	How do I get the column letter of the rightmost column of a named range?
	How do I get the last row number of a named range?

How can I determine if the current selection belongs to any named ranges in my workbook?
Posted November 11, 1996
Keywords: Range Workbook

The following macro demonstrates how to loop the names collection and provide a list of all named ranges that the current selection falls within.

Sub FindNamesThatIncludeSelection()

    Dim sMessage As String
    Dim oName As Object
    Dim oTestRange As Range

    On Error Resume Next
    For Each oName In Names
        If Range(oName.Name).Parent.Name = ActiveSheet.Name Then
            Set oTestRange = Nothing
            Set oTestRange = Intersect(Selection, Range(oName.Name))
            If Not oTestRange Is Nothing Then
                If Selection.Address = oTestRange.Address Then
                    sMessage = sMessage & oName.Name & Chr(13)
                End If
            End If
        End If
    Next oName

    If sMessage = "" Then sMessage = "The selected range is not within any named ranges."
    MsgBox sMessage

End Sub
	

Why does Application.Offset() fail? I wish to use the XLM macro function instead of the VBA Offset() function.
Keywords: Offset Resize Application XLM
Posted October 10, 1996

To begin with, not all XLM functions are available as a method of the Application object. Usually, they are not available when there are equivalent functions in VBA.

There is no single function in VBA that does what the XLM Offset() does, but VBA has an Offset() function and a Resize() function. Together, they provide as much or more functionality as the XLM Offset() function. Example:

   Set MyRange = Worksheets(1).Range("$A$4:$B$7")
      ''' The following displays $C$5
   MsgBox MyRange.Offset(1, 2).Resize(1, 1).Address
			

It is possible to write a simple custom function in VBA that does what the XLM version does.

Why does the FillAcrossSheets method fail when I use xlFormulas as the parameter?
Keywords: Copy Cells Range Formats Formulas Values
Posted October 10, 1996

The help file wrongly lists xlFormulas as a valid value for the second argument instead of xlFormats. In fact, if you pass xlContents, the method copies formulas. Passing xlFormats expectedly copies the formats alone. There is no way to copy values using the method.

How tough is it to write a custom VBA function to mirror the Excel 4.0 macro Offset() function?
Keywords: Offset Resize XLM Custom
Posted October 10, 1996

How do you like this easy version:

   ''' Custom Offset() function that behaves like the
   ''' XLM OFFSET() function
Function Offset4(r As Object, Optional iRowOffset, _
      Optional iColOffset, Optional iRows, Optional iCols)
   Set Offset4 = r.Offset(iRowOffset, iColOffset).Resize(iRowOffset, iColOffset)
End Function

This works for most cases. To make it foolproof, you will have to use the IsMissing() function and check which arguments have not been passed, and take action accordingly.

Does VBA have a function equivalent to the XLM Offset() function?
Keywords: Offset Resize XLM
Posted October 10, 1996

No. In VBA, the Offset() function and Resize() functions allow you to achieve the same effect as follows.

   Range.Offset(x, y).Resize(iRows, iColumns)

If you wish you can easily write a custom function that takes the same arguments as the XLM version and converts them to the VBA syntax above.

Is there a better way to copy a range to all my worksheets than doing a Copy and Paste?
Keywords: Copy Cells Range Sheets Collection
Posted October 10, 1996

There is. The FillAcrossSheets method does this job for you. This is a method applicable to a collection of worksheets. It takes two arguments: the range to copy, and a constant that specifies what to copy (xlAll, xlContents, xlFormats). Example:

   ''' Copies formulas in Sheet1!B2:D4 to Sheet3 and Sheet4
Sub FillFormulasInSheets()
Dim vSheets
Dim rFormulas As Range

   Set rFormulas = Worksheets("Sheet1").Range("B2:D4")
   vSheets = Array("Sheet1", "Sheet3", "Sheet4")
   Worksheets(vSheets).FillAcrossSheets rFormulas, xlContents

End Sub

How do I replace all occurrences of a string in a column? I wrote a macro to replace "New York" with "New Yorker" using the code given in the on-line help example and the system hangs.
Keywords: Find Next Replace Substitute String
Posted October 14, 1996

The difference between your need and the on-line example is that your replacement string contains the original string.You need to make two changes to the code in order to handle this.

You need to pass the found cell as a parameter to the FindNext method so that it continues the search after this cell (otherwise it starts at the beginning and keeps hitting the same cell, looping forever -- appearing to hang). Also, since the FindNext method will loop around and find the first cell again after reaching the end of the range, you need to keep track of the first cell and quit the loop when you hit it again.

The sample function below illustrates how to rewrite the procedure. Notice the use of the Substitute worksheet function in order to replace all occurrences within a cell. 

   ''' Replaces all occurrences of "New York" in column B with
   ''' "New Yorker". Handles multiple occurrences within a cell.
Sub ReplaceNewYork()
Dim rFound As Range
Dim szFirst As String
Dim iCount As Integer

   ThisWorkbook.Worksheets(1).Activate
   Set rFound = Columns(2).Find("New York")
   iCount = 0
   Do While Not rFound Is Nothing
         ''' Store address of first occurrence
      If szFirst = "" Then
         szFirst = rFound.Address
      ElseIf rFound.Address = szFirst Then
         Exit Do   ''' If we have looped around, quit
      End If
      rFound.Value = Application.Substitute(rFound.Value, _
                        "New York", "New Yorker")
      iCount = iCount + 1
      Set rFound = Columns(2).Cells.FindNext(rFound)
   Loop
   MsgBox "Replaced occurrences in " & iCount & " cells."
End Sub
	

Does resizing a range overwrite values in other cells? What does the Resize method resize?
Keywords: Resize Range
Posted October 10, 1996

Resize resizes a specified range and returns a reference to the resized range. You can assign this reference to a variable and manipulate it, or, directly pass it to any function that accepts a range reference variable. Resizing does not change the values in any cell, nor does it change the range object to which it is applied.

Paste the code snippet below in a module in a workbook containing at least one worksheet and run it.  

Sub TestResize()
Dim rOld As Range
Dim rNew As Range

   Set rOld = Worksheets(1).Cells(4, 4)   ''' $D$4
   MsgBox "Old address is " & rOld.Address & "."
   Set rNew = rOld.Resize(2, 3)         ''' $D$4:$F$5"
   MsgBox "Old address is " & rOld.Address & chr$(10) & _
      "New address is " & rNew.Address & "."
      ''' How to access cell $G$7, knowing $D$4:$F$5
   MsgBox "Resized rNew address is " & rNew.Offset(3, 3).Resize(1, 1).Address
End Sub

How do you refer to a named range in another workbook? The Range method gives an error.
Keywords: Range method
Posted July 6, 1996

The Range method applies to the Application object and to the Worksheet object. When used without a qualification, like, 

  Range("SomeName")
			

Excel reads it as 

  Application.Range("SomeName")
			

and assumes that this range is contained in the active workbook. This is give an error if the active workbook does not contain this name. To refer to a name in a specific workbook, use 

  Workbooks("Book2").Worksheets("Sheet1").Range("SomeName")
			

An alternate approach, useful when you don't know the name of the sheet, is to activate the workbook and then use the shorter syntax as follows. 

  Dim r As Range
   Workbooks("Book2").Activate
   Set r = Range("SomeName")
			

How do I get the column letter of the rightmost column of a named range?
Keywords: Column Range Object Count
Posted July 6, 1996

The Column property of a range object gives the column number of the first column of the range. The Columns method can be used to return the collection of all columns in the range. Applying the Count property on this collection will give us the number of columns in the range. We can thus calculate the column number of the last column, and then convert it to a letter using the Chr$() function 

  With Range("SomeName")
      MsgBox "The last column is " & Chr$(.Column + .Columns.Count + 64)
   End With
			

This is much simpler than parsing the range address string. Also, this approach can be easily extended to handle discontiguous ranges as follows. 

Sub test()
   MsgBox Chr$(GetLastColumnNumber(Range("SomeName")) + 64)
End Sub
   ''' Returns the column number of the rightmost column in
   ''' a range. Handles discontiguous ranges correctly.
Function GetLastColumnNumber(r As Range) As Integer
Dim rArea As Range
Dim iLastColumnInArea As Integer
Dim iLastColumn As Integer
   iLastColumn = 0
   For Each rArea In r
      iLastColumnInArea = rArea.Column + rArea.Columns.Count - 1
      If iLastColumnInArea > iLastColumn Then
         iLastColumn = iLastColumnInArea
      End If
   Next rArea
   GetLastColumnNumber = iLastColumn
End Function
			

How do I get the last row number of a named range?
Keywords: Row Range Object Count
Posted July 6, 1996

Use something like 

  With Range("SomeName")
      iRowNumber = .Row + .Rows.Count - 1
   End With
			

Explanation: The Row property of a range object gives the row number of the first row of the range. We then use the Rows method without any parameter to return the collection of all rows in the range. The Count property returns the number of items in a collection. Discontiguous ranges require more work. We need to iterate through its areas and remember the bottommost row encountered. The function below does that job.  

Sub test()
   MsgBox GetLastRowNumber(Range("SomeRange"))
End Sub
   ''' Returns the row number of the bottommost row in
   ''' a range. Handles discontiguous ranges correctly.
Function GetLastRowNumber(r As Range) As Integer
Dim rArea As Range
Dim iLastRowInArea As Integer
Dim iLastRow As Integer
   iLastRow = 0
   For Each rArea In r
      iLastRowInArea = rArea.Row + rArea.Rows.Count - 1
      If iLastRowInArea > iLastRow Then iLastRow = iLastRowInArea
   Next rArea
   GetLastRowNumber = iLastRow
End Function
			

Back to Top

Back to the FAQ Table of Contents

RAM disk is *not* an installation procedure.