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

' @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.

Last Updated: 25 July 2015

Hits: 6301

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.

Last Updated: 19 August 2015

Hits: 6127

If you need to reference a workbook, which might already be opened but all you have ready is the file path the following function will do the trick for you.

If the Excel workbook is not opened oran opened workbook with the same name is not saved under given file path, Nothing will be returned.

' @Author - Alexander Bolte
' @Change Date - 2013-12
' @Description - Returns a workbook object referencing an Excel file
' available under given file path, if the Excel file was already opened before calling this function.
' @Param filePath - a path to an Excel file.
' @Returns - a workbook object referencing an Excel file available under given file path.
Public Function getOpenedWorkbook(ByVal filePath As String) As Workbook
    Dim ret As Workbook
    Dim wrkName As String
    Dim sep As String
    Dim splitted() As String
    
    On Error GoTo errHandle:
    
    ' Get the separator for path strings on operating system.
    sep = Application.PathSeparator
    splitted = Split(filePath, sep)
    wrkName = splitted(UBound(splitted))
    Set ret = Application.Workbooks(wrkName)
    If ret.path <> filePath Then
        ret = Nothing
    End If
    
errHandle:
    If Err.Number <> 0 Then
        Err.Clear
    End If
    
    Set getOpenedWorkbook = ret
End Function

Last Updated: 07 June 2014

Hits: 6290