Useful VBA FunctionI cannot live without

Standard

Here are 3 VBA functions that I always have in the VBA projects I built.

 

  • Count occurrence of substring in string

This function allows you to count the occurrence of substring in string, various type of data can be passed to the function and case-sensitivity can be adjusted.

Function countstr(ByVal keyword As Variant, ByVal text As Variant, Optional ByVal case_sensitive = True) As Integer
  keyword = CStr(keyword): text = CStr(text)
  countstr = (Len(text) - Len(Replace(text, keyword, vbNullString, , , IIf(case_sensitive, vbBinaryCompare, vbTextCompare)))) / Len(keyword)
End Function

examples:
countstr("a","aaa") 'return 3
countstr("a","AAA") 'return 0
countstr("a","AAA", False) 'return 3
countstr(1, 123) 'return 1
countstr("1",123) ' rrtuen 1
  • Find last used row of a column/sheet
Function lastrow(ws As Worksheet, Optional ByVal column As Variant = "") As Integer
With ws
    If column = "" Then
        lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    Else
        lastrow = .Cells(.Rows.Count, column).End(xlUp).Row
    End If
End With
End Function

examples:
'Assume Sheets(1) is empty
Sheets(1).Cells(3, 3) = 1
Sheets(1).Cells(2, 2) = 1

Debug.Print lastrow(Sheets(1), 3) 'return 3
Debug.Print lastrow(Sheets(1), "B") 'return 2
Debug.Print lastrow(Sheets(1), "A") 'return 1
Debug.Print lastrow(Sheets(1))  'return 3

  • Check if worksheet exists in a workbook
Function ws_exist(sheet_name As String, Optional ByRef wb As Workbook) As Boolean
On Error Resume Next
If wb Is Nothing Then Set wb = ThisWorkbook
ws_exist = (UCase(wb.Sheets(sheet_name).Name) = UCase(sheet_name))
End Function

examples:
'Assume only Sheet1 exists in ThisWorkBook
ws_exist("Sheet1") 'return True
ws_exist("SHEET1") 'return True
ws_exist("Sheet2") 'return False

'You may also check if worksheet exists in other workbooks if you have created a reference

ws_exist("Sheet1",wb)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s