怎样使用VBA打造增强型状态栏？

1、首先在开发工具中打开VBA编辑器

2、在单元格区域当中输入一些内容作为例子

3、在VBA编辑器中插入模块

4、在模块当中输入如下代码，然后运行

Dim WithEvents Applic As Excel.Application '声明变量为Excel程序对象

 

Private Sub Class_Initialize()

 

    Set Applic=Excel.Application

 

    End Sub

 

Private Sub Class_Terminate() '删除对象示例的所有引用

 

    Set Applic=Nothing

 

    End Sub

 

Private Sub Applic_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

 

    '选择单元格时发生

 

    Dim MyAvg As Variant, MyCnt As Long, MyMax As Variant, MyMin As Variant

 

    Dim MySum As Variant, MyCnta As Double

 

    If TypeName(Target)="Range" Then  '如果选择的是单元格

 

      If Target.Cells.Count > 1 Then  '如果选区单元格大于1个

 

          MyAvg=Application.Text(Application.Average(Target), "0")

 

                                                      '计算平均值

 

          MyCnt=Application.Count(Target) '计算单元格数字个数

 

          MyMax=Application.Text(Application.Max(Target), "0")

 

                                                '计算最大值

 

          MyMin=Application.Text(Application.Min(Target), "0")

 

                                                '计算最小值

 

          MySum=Application.Text(Application.Sum(Target), "0")

 

                                                '计算合计

 

          MyCnta=Application.CountA(Target)  '计算非空单元格个数

 

          Application.StatusBar="平均: " & CStr(MyAvg) & " ↑" & _

 

              "数字个数: " & MyCnt & "↑" & "数据个数: " & MyCnta & "↑" & _

 

              "最大值: " & CStr(MyMax) & " ↑ " & "最小值: " & CStr(MyMin) & " ↑ " & _

 

              "合计: " & CStr(MySum) & " ↑ 四维实业公司"

Else

 

          Application.StatusBar=False '否则恢复状态栏

 

      End If

 

    Else

 

      Application.StatusBar=False '否则恢复状态栏

 

    End If

 

End Sub

5、重新打开工作簿，选中单元格A1:A5之数据（包括文本和数字），则状态栏显示相关的6种计算结果，如图