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)
