Functions to get range properties

[Nick Hebb]

I wrote the following utility functions for dealing with ranges. I
often want the first column number or label; last column number or
label; next column label; first row; last row, etc.

What I want to know is whether there is a better way to get this info
with built-in functions. Any ideas?

Here are the utility functions:


' Constants
Private Const ZEE As Integer = 26
Private Const MINCOL As Integer = 1
Private Const MAXCOL As Integer = 256
Private Const ASCII64 As Integer = 64
'
'

Public Function GetFirstColumnNumber(ByVal A1Address As String) As
Integer

Dim rng As Range

Set rng = Application.ActiveWorkbook.Sheets(1).Range(A1Address)
GetFirstColumnNumber = rng.Columns.Item(1).Column
Set rng = Nothing

End Function


Public Function GetLastColumnNumber(ByVal A1Address As String) As
Integer

Dim rng As Range
Dim iFirst As Integer
Dim iCount As Integer

iFirst = GetFirstColumnNumber(A1Address)

Set rng = Application.ActiveWorkbook.Sheets(1).Range(A1Address)
iCount = rng.Columns.Count
Set rng = Nothing

GetLastColumnNumber = iFirst + iCount - 1

End Function


Public Function GetFirstColumnLabel(ByVal A1Address As String) As
String

Dim iCol As Integer

iCol = GetFirstColumnNumber(A1Address)
GetFirstColumnLabel = ColumnNumberToLabel(iCol)

End Function


Public Function GetLastColumnLabel(ByVal A1Address As String) As String

Dim iCol As Integer

iCol = GetLastColumnNumber(A1Address)
GetLastColumnLabel = ColumnNumberToLabel(iCol)

End Function


Public Function GetFirstRowNumber(ByVal A1Address As String) As Integer

Dim rng As Range

Set rng = Application.ActiveWorkbook.Sheets(1).Range(A1Address)
GetFirstRowNumber = rng.Rows.Item(1).Row
Set rng = Nothing

End Function


Public Function GetLastRowNumber(ByVal A1Address As String) As Integer

Dim rng As Range
Dim iFirst As Integer
Dim iCount As Integer

Set rng = Application.ActiveWorkbook.Sheets(1).Range(A1Address)
iCount = rng.Rows.Count
Set rng = Nothing

GetLastRowNumber = GetFirstRowNumber(A1Address) + iCount - 1

End Function


Public Function ExtractCellAddress(ByVal FullAddress As String) As
String

If InStr(FullAddress, "!") > 0 Then
FullAddress = Right(FullAddress, Len(FullAddress) - pos)
End If

ExtractCellAddress = FullAddress

End Function


Public Function ColumnNumberToLabel(ByVal ColumnNumber As Integer) As
String

Dim ret As String

If ColumnNumber < MINCOL Or ColumnNumber > MAXCOL Then
ret = "!OutOfRangeError"
ElseIf ColumnNumber > ZEE Then
ret = Chr(Int(ColumnNumber / ZEE) + ASCII64) &
Chr((ColumnNumber Mod ZEE) + ASCII64)
Else
ret = Chr(ColumnNumber + ASCII64)
End If

ColumnNumberToLabel = ret

End Function


Public Function GetNextColumnLabel(ByVal ColumnLabel As String) As
String

Dim char1 As String
Dim char2 As String
Dim iCol As Integer
Dim ret As String

If Len(ColumnLabel) = 1 Then
If ColumnLabel <> "Z" Then
ret = Chr(Asc(ColumnLabel) + 1)
Else
ret = "AA" ' special case "Z" rolls over to "AA"
End If
ElseIf Len(ColumnLabel) = 2 Then
char1 = Left(ColumnLabel, 1)
char2 = Right(ColumnLabel, 1)
iCol = (Asc(char1) - ASCII64) * ZEE + Asc(char2) - ASCII64 + 1
ret = ColumnNumberToLabel(iCol)
Else
ret = "!OutOfRangeError"
End If

GetNextColumnLabel = ret

End Function

 

[Bernie Deitrick]

Nick,

No need to fool around with declaring range variables, etc. For example, for
your first two functions, you could use:

Public Function GetFirstColumnNumber(ByVal A1Address As String) As Integer
GetFirstColumnNumber = Range(A1Address).Item(1).Column
End Function

Public Function GetLastColumnNumber(ByVal A1Address As String) As Integer
GetLastColumnNumber =
Range(A1Address).Cells(Range(A1Address).Cells.Count).Column
End Function

or you could use the worksheet functions - Array entered using
Ctrl-Shift-Enter (for consistency). For example:

=MIN(COLUMN(C1:F1))
=MAX(COLUMN(C1:F1))

HTH,
Bernie
MS Excel MVP

 

[Nick Hebb]

That works as a cell formula, but I need it in VBA. So if I do
something like:

Application.WorksheetFunction.Min()

Then the Min() function expects as series of arguments [arg1], [arg2],
[arg3], etc. I don't know how to turn COLUMN(C1:F1) into an array or a
series of arguments without a lot of extra code.

Any tips would be great. I hate writing and using code that re-invents
the wheel, and I have a suspicion that I have.