Written by Allen Wyatt (last updated September 26, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
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 2021. You can find a version of this tip for the older menu interface of Excel here: Counting All Characters.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
If you have a macro that takes a long time to process a workbook, you might want to continue working in Excel while the ...
Discover MoreNeed to rename a file in a macro? It's easy to do using the Name command, as discussed in this tip.
Discover MoreAt times, you may want your macro to pause execution. This tip presents four different ways you can add a delay into your ...
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 © 2025 Sharon Parq Associates, Inc.
Comments