Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Counting All Characters.
by Allen Wyatt
(last updated May 10, 2014)
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8349) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Counting All Characters.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
If you have a range of cells in which you want to count all the commas, there are several ways you can derive the figure ...Discover More
One of the things you can do with macros is to work with disk files. As you do so, you may have a need to create a new ...Discover More
When creating macros, it is often necessary to change from one type of data to another. Here's how you can change from a ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.