VBA - Visual Basic for Applications

With this collection of articles I want to build a wiki for myself as well as others.

The goal is to build not only a little framework for Excel VBA, but also a collection of general tips and hints on issues I experienced working with Excel and Visual Basic for Excel.

Once completed, I am guessing it never will be, this will serve me well. Like my wife always says, I am "Mr. Forgetful" and a "Lazy Bum".

Yes dear, I love you too.

And it's true, good programmers are lazy!

 

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

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

Subcategories

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.

This category holds articles regarding general things in MS Office VBA independent from the MS Office application.  

This category holds articles regarding Access VBA, but also general things I come accross Access and its usage in companies.