VBA - Visual Basic for Applications

Whenever I have to apply more complex  text operations I tend to use regular expressions.

If you want to remove HTML tags from a String, nothing is faster and easier than using regular expressions to do so.

Source Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
' @Author - Alexander Bolte
' @ChangeDate - 2014-11-06
' @Description - Removing HTML tags from a provided HTML text.
' @Param htmlText - a String holding HTML text.
' @Remarks - Using a regular expression to remove HTML tags from a provided String.
' Regular expression is as follows.
' "<([A-Z][A-Z0-9]*)\b[^>]*>|<\/([A-Z][A-Z0-9]*)>"
Public Function replaceHTMLTags(ByVal htmlText As String) As String
    Dim regEx As New RegExp
    Const cHtmlPattern As String = "<([A-Z][A-Z0-9]*)\b[^>]*>|<\/([A-Z][A-Z0-9]*)>"
    
    regEx.Global = True
    regEx.IgnoreCase = True
    regEx.pattern = cHtmlPattern
    htmlText = regEx.Replace(htmlText, "")
    
    replaceHTMLTags = htmlText
End Function

Referenced APIs

This code references the following API, which is a VBScript API.

"Microsoft VBScript Regular Expression Reference 5.5"

In order to reference this API in Excel VBA go to Options -> References (In German "Extras" -> "Verweise") and tick the API above.

 

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

It may seem trivial, but even the smallest things have to be handled carefully when programming.

Therefore I have some standard functions, which handle the creation of new worksheets in my VBA projects. So far I never had trouble with them plus they emphazise NOT to use existing worksheets for your coding. Instead it is better to always create new worksheets. This way you have full control over your programm.

If you start coupling Excel files with source code, you start a war that you cannot win. If your source code relies on a certain status in Excel files, it will fail by time and you spend a lot of time handling errors, which can easily be avoided.

However, here the source code for creating a new worksheet safely.

' @Author - Alexander Bolte
' @ChangeDate - 2014-05-31
' @Description - checks, if a worksheet exists under given name in given workbook.
' @Param myBook - an initialized Excel Workbook, which should be searched for given sheet name.
' @Param sheetName - a String holding the sheets name, which should be searched in given workbook.
' @Returns true, if the sheet exists, else false.
Public Function worksheetExists(ByRef myBook As Workbook, ByVal sheetName As String) As Boolean
Dim ret As Boolean
Dim tmpS As Worksheet

On Error GoTo errHandler:
Set tmpS = myBook.Worksheets(sheetName)
ret = True
Set tmpS = Nothing

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

worksheetExists = ret
End Function


' @Author - Alexander Bolte
' @ChangeDate - 2014-05-31
' @Description - creates a new worksheet with given name in given workbook. If a worksheet already exists under given name, it is deleted and replaced with an empty worksheet.
' @Param myBook - an initialized Excel Workbook, which should be searched for given sheet name.
' @Param sheetName - a String holding the sheets name, which should be created in given workbook.
' @Returns a Worksheet object referencing a newly created worksheet in given workbook.
Public Function createWorksheet(ByRef myBook As Workbook, ByVal sheetName As String) As Worksheet
Dim tmpS As Worksheet

' ### delete the worksheet, if already existing ###
If worksheetExists(myBook, sheetName) Then
myBook.Worksheets(sheetName).Delete
End If
' ### add a new worksheet ###
Set tmpS = myBook.Worksheets.Add
tmpS.Name = sheetName
tmpS.Move myBook.Worksheets(1)

Set createWorksheet = tmpS
End Function

If you do not want Excel to display warning Messages, you can switch them off using the DsiplayAlerts property of the Application object.

' Disable all alerts.
Application.DisplayAlerts = False

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.