excelperfect

引言：這是在ozgrid.com論壇中看到的一個(gè)VBA程序，特輯錄于此，供有興趣的朋友學(xué)習(xí)參考。

下面的程序統(tǒng)計(jì)工作簿中所有工作表的字符總數(shù)，包括其中的文本框中的字符數(shù)。

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

        '統(tǒng)計(jì)文本框中的字符

        For Each shp In wks.Shapes

            If TypeName(shp) <>'GroupObject' Then

                lTxtBox = lTxtBox +shp.TextFrame.Characters.Count

            End If

        Next shp

        '統(tǒng)計(jì)包含常量的單元格中的字符

        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

        '統(tǒng)計(jì)包含公式的單元格的字符

        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')& _

            ' 個(gè)字符' &vbCrLf

    sMsg = sMsg & '常量中有 ' &Format(lConstants, '#,##0') & _

            ' 個(gè)字符' &vbCrLf & vbCrLf

    lTotal = lTxtBox + lConstants

    sMsg = sMsg & Format(lTotal,'#,##0') & _

            ' 個(gè)字符 (作為常量)' &vbCrLf & vbCrLf

    sMsg = sMsg & '在公式中(作為值)有 ' &Format(lFormulaValues, '#,##0') & _

            ' 個(gè)字符' &vbCrLf

    sMsg = sMsg & '在公式中(作為公式)有 ' &Format(lFormulas, '#,##0') & _

            ' 個(gè)字符' &vbCrLf & vbCrLf

    lTotal2 = lTotal + lFormulas

    lTotal = lTotal + lFormulaValues

    sMsg = sMsg & '(公式作為值)有 ' &Format(lTotal, '#,##0') & _

            ' 個(gè)字符' &vbCrLf

    sMsg = sMsg & '(公式作為公式)有 ' &Format(lTotal2, '#,##0') & _

            ' 個(gè)字符'

    MsgBox Prompt:=sMsg, Title:='字符統(tǒng)計(jì)'

ExitHandler:

    Application.ScreenUpdating = True

    Exit Sub

ErrHandler:

    If bPossibleError And Err.Number = 1004Then

        bPossibleError = False

        bSkipMe = True

        Resume Next

    Else

        MsgBox Err.Number & ': '& Err.Description

        Resume ExitHandler

    End If

End Sub

對(duì)于下面的示例工作簿，運(yùn)行CountCharacters過(guò)程后的結(jié)果如下圖1所示。

圖1

歡迎到知識(shí)星球：完美Excel社群，進(jìn)行技術(shù)交流和提問(wèn)，獲取更多電子資料。