Written by Allen Wyatt (last updated February 22, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
When you work with worksheets—particularly those from other people—you may be looking for a way count the number of characters in a worksheet. The following macro is very handy in that regard. It counts the number of characters in an entire workbook, including any characters in any text boxes inserted in the various worksheets.
Sub CountCharacters() Dim wks As Worksheet Dim rng As Range Dim rCell As Range Dim shp As Shape Dim bPossibleError As Boolean Dim bSkipMe As Boolean Dim lTotal As Long Dim lTotal2 As Long Dim lConstants As Long Dim lFormulas As Long Dim lFormulaValues As Long Dim lTxtBox As Long Dim sMsg As String On Error GoTo ErrHandler Application.ScreenUpdating = False lTotal = 0 lTotal2 = 0 lConstants = 0 lFormulas = 0 lFormulaValues = 0 lTxtBox = 0 bPossibleError = False bSkipMe = False sMsg = "" For Each wks In ActiveWorkbook.Worksheets ' Count characters in text boxes For Each shp In wks.Shapes If TypeName(shp) <> "GroupObject" Then lTxtBox = lTxtBox + shp.TextFrame.Characters.Count End If Next shp ' Count characters in cells containing constants bPossibleError = True Set rng = wks.UsedRange.SpecialCells(xlCellTypeConstants) If bSkipMe Then bSkipMe = False Else For Each rCell In rng lConstants = lConstants + Len(rCell.Value) Next rCell End If ' Count characters in cells containing formulas bPossibleError = True Set rng = wks.UsedRange.SpecialCells(xlCellTypeFormulas) If bSkipMe Then bSkipMe = False Else For Each rCell In rng lFormulaValues = lFormulaValues + Len(rCell.Value) lFormulas = lFormulas + Len(rCell.Formula) Next rCell End If Next wks sMsg = Format(lTxtBox, "#,##0") & _ " Characters in text boxes" & vbCrLf sMsg = sMsg & Format(lConstants, "#,##0") & _ " Characters in constants" & vbCrLf & vbCrLf lTotal = lTxtBox + lConstants sMsg = sMsg & Format(lTotal, "#,##0") & _ " Total characters (as constants)" & vbCrLf & vbCrLf sMsg = sMsg & Format(lFormulaValues, "#,##0") & _ " Characters in formulas (as values)" & vbCrLf sMsg = sMsg & Format(lFormulas, "#,##0") & _ " Characters in formulas (as formulas)" & vbCrLf & vbCrLf lTotal2 = lTotal + lFormulas lTotal = lTotal + lFormulaValues sMsg = sMsg & Format(lTotal, "#,##0") & _ " Total characters (with formulas as values)" & vbCrLf sMsg = sMsg & Format(lTotal2, "#,##0") & _ " Total characters (with formulas as formulas)" MsgBox Prompt:=sMsg, Title:="Character count" ExitHandler: Application.ScreenUpdating = True Exit Sub ErrHandler: If bPossibleError And Err.Number = 1004 Then bPossibleError = False bSkipMe = True Resume Next Else MsgBox Err.Number & ": " & Err.Description Resume ExitHandler End If End Sub
The macro may seem quite long, but it is very well structured in exactly what it does. First, it looks through all the text boxes in a worksheet. If they are not grouped (you cannot count characters in grouped text boxes), then the characters in them are tallied up. Then the macro tallies up the characters in cells containing constants. Finally, it counts all the characters used in cells containing formulas. The balance of the macro is used to present the information in a message box.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8349) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Counting All Characters.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
When you design your worksheets, you probably want users to interact with those worksheets in specific ways. What ...
Discover MoreWorkbooks get corrupted from time to time; that's a fact of life in an Excel world. If those corrupted workbooks contain ...
Discover MoreIf you have a large, complex workbook, you may want to make sure that it is always calculated manually instead of ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments