Excel VBA Revisited

Split String By Comma

2015-02-19T07:02:00.003-08:00

The following routine splits the comma-separated contents of an active cell into strings and prints each result below the active cell. It will overwrite the cells below the active cell.

Sub SplitByComma()

Dim arr As Variant
Dim row As Integer
Dim col As Integer

row = ActiveCell.row
col = ActiveCell.Column

arr = VBA.split(ActiveCell.Value, ",")
For i = 0 To UBound(arr)
    Cells(row + 1 + i, col).Value = Trim(arr(i))
Next i

End Sub

2014-10-26T10:09:00.003-07:00

EndsWith and StartsWith For VBA

2013-02-15T12:23:00.000-08:00

Here are listings for 2 functions that are missing in VBA: string starts with and string ends with. The functions are case-insensitive. They use a simple equality check which will probably make them unsuitable for non-ASCII text.

Public Function EndsWith(str As String, ending As String) As Boolean
     Dim endingLen As Integer
     endingLen = Len(ending)
     EndsWith = (Right(Trim(UCase(str)), endingLen) = UCase(ending))
End Function

Public Function StartsWith(str As String, start As String) As Boolean
     Dim startLen As Integer
     startLen = Len(start)
     StartsWith = (Left(Trim(UCase(str)), startLen) = UCase(start))
End Function

Add Hyperlinks

2012-06-15T08:23:00.001-07:00

The following procedure converts a url to a hyperlink in range:

Sub AddHyperlinks(rng As Range)
    Dim c As Range
    For Each c In rng
        If StartsWith(c.Value, "http") Then
                c.Hyperlinks.Add Anchor:=c, Address:=c.Value
        End If
    Next
End Sub

It uses the StartsWith function from the previous post.

Some Excel Range Functions

2012-06-07T06:55:00.000-07:00

Here I will list code for some useful VBA functions that deal with an Excel range. I think the function descriptions are self-explanatory.

Determine Last Row in a Range

Function GetLastRow(rng As Range)
    GetLastRow = rng.Rows.Count + rng.Row - 1
End Function

Return an Array of Row Numbers from a Range

Function RowsFromRange(rng As Range)

Dim c As Range
Dim i As Integer
Dim arr() As Integer
ReDim arr(rng.Rows.Count - 1)

For Each c In rng.Rows
    arr(i) = c.Row
    i = i + 1
Next

RowsFromRange = arr

End Function

Initialize VBA Array

2012-06-07T06:24:00.000-07:00

Declare an array as Variant:

Dim arr as Variant

Use the VBA Array function to initialize the array:

arr = Array(154, 155, 156, 157)

Check if Worksheet Exists

2012-06-06T11:53:00.000-07:00

This is a VBA function that checks if a Worksheet exists. It accepts a worksheet name as a parameter. It loops through worksheet names and stops if it finds the one you are looking for.
Otherwise, it goes through all of them and returns false. Also, it is case-insensitive.

Private Function WorksheetExists(wsName As String) As Boolean
        Dim ws As Worksheet
        Dim ret As Boolean
        ret = False
        wsName = UCase(wsName)
        For Each ws In ThisWorkbook.Sheets
            If UCase(ws.Name) = wsName Then
                ret = True
                Exit For
            End If
        Next
        WorksheetExists = ret
End Function