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!

 

Details
Parent Category: 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.

 

Details
Parent Category: VBA - Visual Basic for Applications

Determining the last row actually containing information in an Excel worksheet can be an annoying task, if you are relying on the Excel API to do the trick for you. Especially when you are not exatcly in control over the handed files you are dealing with and people are allowed to enter information anywhere it is not as easy as it should be.

Source Code automating Excel search function

Below function is determining the last row containing data in provided worksheet using the Excel search function.
This approach works only, if no cells in given sheet are merged.
Therefore merged cells are removed from a handed workbook before determining its last row containing data.

Details
Parent Category: VBA - Visual Basic for Applications

  1. Copy Paste Special Values for UsedRange
  2. Excel VBA - Create a new worksheet
  3. Excel VBA Properties file writer
  4. Has an Excel workbook been saved

Subcategories

Excel VBA Article Count:  30

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.

MS Office and VBA Article Count:  11

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

Power Point VBA Article Count:  1

Access VBA Article Count:  7

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

Access VBA DAO Article Count:  2