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.

 

This function copies used range of handed worksheet and replaces it with values only.

Source Code

1
2
3
4
5
6
7
8
9
10
11
12
' @Author - Alexander Bolte
' @ChangeDate - 2014-10-09
' @Description - Copy / Pasting contents of handed Worksheets UsedRange
' as values into the same range.
' @Param trg - a Worksheet object holding data.
' @Remarks -
' UsedRange of worksheet will be overwritten by itself pasting only values and nothing else.
' Formulas will be lost.
Public Function copyPasteWorksheetValues(ByRef trg As Worksheet)
    trg.UsedRange.Copy
    trg.UsedRange.PasteSpecial xlPasteValues
End Function

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

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.