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.

Whenever you have to interact with users there is information you would like to store outsie the lifetime of an application in order for it to be available at next startup.

The below class can assist in writing and reading property files into hidden temp directories like AppData in Windows systems. This way you can for example store user settings without having to deal with protecting and hiding Excel sheets. I think it is bad style to store information that only rarely is touched by a user in a workbook, which is used to process data. Unless Excel is not used as an interface to configure a lot of different parameters in an application, I use property files.

However the main advantage is that you can use a Scripting.Dictionary internally in the below class in order to ease getting and setting properties by name. This includes the following advantages compared to storing information in Excel files.

  1. No duplicates possible.
  2. No handling of indices in Excel sheets necessary.
  3. Property files are more reliable because Excel sheets are more likely to be modified by users.

Source Code

If you often have to convert values from Excel files into SQL filter lists for investigation purposes, this VBA function will save you a lot of time.

Thanks for this function go to Sascha Hombach a highly skilled IT Consultant, who initially developed it for HP ALM filter Strings. I only adjusted it for the use case described above.

Source Code

The following function checks, if a worksheet is existing under a given name in a provided workbook object.

Source Code

' @Author - Alexander Bolte
' @Description - Checking, if a worksheet is existing in given workbook under given name.
' @Param wrk - an object of type Workbook.
' @Param name - a String holding the name of the worksheet existance should be checked for.
' @Returns - true, if worksheet exists under given name, else false.
Public Function worksheetExists(ByRef wrk As Workbook, ByVal name As String) As Boolean
Dim ret As Boolean
Dim tmp As Worksheet

On Error GoTo errHandle:

Set tmp = wrk.Worksheets(name)
ret = Not (tmp Is Nothing)

errHandle:
If Err.Number <> 0 Then
Err.Clear
ret = False
End If

Set tmp = Nothing
worksheetExists = ret
End Function