Excel VBA

This category will hold articles regarding developement in Excel VBA. It will serve as a wiki and an Excel VBA Framework for myself.

Some development tasks reoccur for every customer. Since I am a lazy bum it will be nice to have a central source where I can reuse source code from.

If you have to delete a worksheet from a workbook, you can use below source code.

Source Code

1
2
3
4
5
6
7
8
9
10
' @Author - Alexander Bolte
' @ChangeDate - 2014-10-08
' @Description - Deleting a worksheet named like sheetName from given target Workbook, if a worksheet under given sheetName exists.
' @Param trg - an initialized workbook object.
' @Param sheetName - a String holding a unique worksheet name.
Public Function deleteWorksheet(ByRef trg As Workbook, ByVal sheetName As String)
    If worksheetExists(trg, sheetName) Then
        Call trg.Worksheets(sheetName).Delete
    End If
End Function

Referenced Functions

The following functions are referenced.

 Check if a worksheet exists

 

If you have to search an exact match for a String in an Excel worksheets row, the following function might be of help.

Source Code

The following method returns a Range object referneced by a NamedRange in an Excel worksheet. Motivation for this article is the fact that VBA does not handle possible errors in a NamedRange. If a referenced range contains errors even in formulas, the simple calll to RefersToRange of a NamedRange will fail.