当您使用工作表时,尤其是其他人的工作表时,您可能正在寻找一种计算工作表中字符数的方法。

在这方面,以下宏非常方便。它计算整个工作簿中的字符数,包括插入各个工作表中的任何文本框中的任何字符。

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

宏可能看起来很长,但是它的结构完全正确。首先,它浏览工作表中的所有文本框。

如果未对它们进行分组(无法对分组文本框中的字符进行计数),则会对它们中的字符进行计数。然后宏会计算包含常量的单元格中的字符。最后,它计算包含公式的单元格中使用的所有字符。宏的余额用于在消息框中显示信息。

注意:

如果您想知道如何使用此页面(或_ExcelTips_网站上的任何其他页面)中描述的宏,我准备了一个特殊页面,其中包含有用的信息。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(8349)适用于Microsoft Excel 2007、2010、2013、2016、2019和Office 365中的Excel。您可以在此处为Excel的较早菜单界面找到此技巧的版本: