Friday, June 15, 2012

Add Hyperlinks

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.

Thursday, June 7, 2012

Some Excel Range Functions

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

Declare an array as Variant:

Dim arr as Variant

Use the VBA Array function to initialize the array:

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

Wednesday, June 6, 2012

Check if Worksheet Exists

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