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)