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: 7885

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

Last Updated: 09 October 2014

Hits: 6951

Actually you can list all worksheet names in a workbook using the object path Workbook.Worksheets(index).Name.

I needed to have the names as strings in a VBA.Collection, so I developed a little function collecting the names.

' @Author - Alexander Bolte
' @Change Date - 26.12.2013
' @Description - Creates a list of all sheet names in given workbook.
' @Param wrk - Excel workbook the sheet name collection should be created from.
' @Returns - VBA.Collection holding all sheet names from given Microsoft Excel workbook.
Public Function getSheetNamesFromWorkbook(ByRef wrk As Workbook) As VBA.Collection
    Dim i As Integer
    Dim sheetNames As New VBA.Collection
    
    On Error GoTo errHandle:
    
    For i = 1 To wrk.Worksheets.Count
        sheetNames.Add wrk.Worksheets(i).Name
    Next i
    
errHandle:
    If Err.Number <> 0 Then
        Err.Clear
    End If
    
    Set getSheetNamesFromWorkbook = sheetNames
End Function

Last Updated: 07 June 2014

Hits: 7716