Monday, February 22, 2010

Back to XL VBA : A Way to create Utility functions to increase reusability

Hi there!

I normally create a Util Module every time I work on an Office Automation Project. I thought, people may be benefitted with it. Moreover, I will not have to create it again. I will copy it from here only ;)


Public Function SheetExists(wkb As Workbook, shtName As String) As Boolean
Dim sht As Worksheet

Set sht = Nothing
On Error GoTo errHandler
Set sht = wkb.Worksheets(shtName)
SheetExists = True
Exit Function

errHandler:
SheetExists = False
End Function

Public Function GetSheet(wkb As Workbook, shtName As String) As Worksheet
Dim sht As Worksheet

Set sht = Nothing
If (SheetExists(wkb, shtName)) Then
Set sht = wkb.Worksheets(shtName)
End If

Set GetSheet = sht
End Function


Public Function RangeExists(wkSht As Worksheet, rngName As String) As Boolean
Dim rng As Range

Set rng = Nothing
On Error GoTo errHandler
Set rng = wkSht.Range(rngName)
RangeExists = True
Exit Function

errHandler:
RangeExists = False
End Function

Public Function GetRange(wkSht As Worksheet, rngName As String) As Range
Dim rng As Range

Set rng = Nothing

If (RangeExists(wkSht, rngName)) Then
Set rng = wkSht.Range(rngName)
End If

Set GetRange = rng
End Function


Public Function GetRows(wkSht As Worksheet, Optional columnName As String = "A") As Long
Dim rowCounter As Long
Dim runLoop As Boolean
Dim startRange As Range

rowCounter = 0
runLoop = True

Set startRange = wkSht.Range(columnName & "1")

While runLoop
If (Len(startRange.Offset(rowCounter, 0).Value) > 0) Then
rowCounter = rowCounter + 1
Else
runLoop = False
End If
Wend

GetRows = rowCounter

End Function

Public Function GetColumns(wkSht As Worksheet, Optional rowNum As String = "1") As Long
Dim colCounter As Long
Dim runLoop As Boolean
Dim startRange As Range

colCounter = 0
runLoop = True

Set startRange = wkSht.Range("A" & rowNum)

While runLoop
If (Len(startRange.Offset(0, colCounter).Value) > 0) Then
colCounter = colCounter + 1
Else
runLoop = False
End If
Wend

GetColumns = colCounter

End Function




Thanks,
Vikas