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

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

Source Code

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

Last Updated: 25 July 2015

Hits: 6573

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