|
Friday, June 12, 2009 - 12:51 PM
Public Function TestLen(inString As Variant) As Boolean
'returns false when len = 0, an input is missing, or an variable is empty, instead of throwing an error
'returns true when input is an error (usually caused by a cell with an error, not an empty cell)
On Error Resume Next
If IsMissing(inString) Then Exit Function
If IsError(inString) Then TestLen = True: Exit Function
If (Len(CStr(inString)) > 0&) Then TestLen = True
End Function
Public Function myUBound&(inArray, Optional Dimension& = 1)
'returns 0 instead of an error if array is empty
On Error Resume Next
myUBound = Application.Max(UBound(inArray, Dimension), 0)
End Function
Public Function myMatch&(inValue As Variant, inArray)
'returns 0 instead of error if match not found
On Error Resume Next
myMatch = Application.Match(inValue, inArray, 0)
End Function
Public Function ColumnLetter$(ColNum&)
If (ColNum > 26&) Then
ColumnLetter = Chr(Int((ColNum - 1) / 26) + 64) & Chr(((ColNum - 1) Mod 26) + 65)
Else
ColumnLetter = Chr(ColNum + 64)
End If
End Function
Public Function StringReference$(StartRow&, StartCol&, Optional EndRow&, Optional EndCol&)
'convert a numeric reference to a string reference to obviate (unhiding and) selecting the ws containing the range before manipulating the range
'uses ColumnLetter function
StringReference = ColumnLetter(StartCol) & CStr(StartRow)
If IsMissing(EndRow) And IsMissing(EndCol) Then Exit Function
If IsMissing(EndRow) Then EndRow = StartRow: If IsMissing(EndCol) Then EndCol = StartCol
StringReference = StringReference & ":" & ColumnLetter(EndCol) & CStr(EndRow)
End Function
Public Function SheetExists(WSName$) As Boolean
On Error Resume Next
If TestLen(Sheets(WSName).Name) Then SheetExists = True
End Function
Public Function NameExists(RangeName$) As Boolean
'test if range name exists in active wb
On Error Resume Next
NameExists = TestLen(Range(RangeName).Address)
End Function
Public Function WindowIsOpen(WindowName$) As Boolean
On Error GoTo ExitNow
If (Windows(WindowName).Index > 0) Then WindowIsOpen = True
ExitNow:
End Function
|
|
| << Navigate to Friday, June 12, 2009 |
Add New Comment |