VBA-TG第1节|基本数据类型详解

最近更新:'2019-04-30'

1.数据类型、变体类型与常用数值类型以及特殊符号的含义
2.日期类型的特点、本质和计算
3.逻辑类型与逻辑运算
4.逻辑变量控制循环、Exit/Goto/On Error
5.数据类型识别和转换原则，四舍五入规则
6.字符编码、ASC/CHR函数，字符串比较大小

主要内容介绍:
1.理解数据类型与变体
2.避免类型错误
3.避免莫名其妙的溢出错误
4.操作日期和时间
5.编写类似“交替涂色”的功能
6.中断循环、自动处理错误
7.识别数据的类型，处理类型转换
8.以数字形式处理字符，比如循环、排序

1.数据类型、变体类型与常用数值类型以及特殊符号的含义

1.1数据类型概念

不同类型的变量不仅占用内存大小不同，运算规则和处理方式也不相同.

整数类型和字符串类型

• Integer整数类型（只能存放-32767到+32767之间的整数）
• String字符串类型

1.2变体类型

如果没有使用As指定一个变量的类型，那么该变量被默认属于一种特殊类型.

变体类型存在的优缺点如下:
1.优点:

• 代码简单灵活，不用特别指定数据类型

2.缺点:

• 效率较低(执行速度慢/内存开销大)，特殊情况下会因为数据类型引发无法察觉的错误。

使用变体类型,需要注意以下内容:
1.没有as 类型,都是变体类型.

2.数据是变体类型,就会对数据类型进行自动转换.

即使表格中的单元格是文本内容但又是数字类型的,VBA都会转化成数字.

1.3 "+"是否可以代替"&"

"+"和"&"其实是不一样的,非必要的情况下,尽量使用"&".

从以上截图可以看出,

• 如果两边都是字符串时,"+"就相当于"&"
• 如果一边都是字符串(数字),一边是数字
  • "+"就会将字符串转为数字进行运算.最终结果是数字
  • "&"则会转数字转为字符串进行运算,最终结果是字符串
• 如果一边都是字符串(字母),一边是数字
  • "+"就会将字符串转为字母进行运算,因字母与数字运算就会出错
  • "&"则会转数字转为字符串进行运算,最终结果是字符串

1.4常量类型声明

以下是常用的数据类型的声明

1.5避免莫名其妙的溢出错误

比如integer数据类型,当a=15就没有报错溢出

Sub demo()

    Dim a As Integer
    
    a = 15
    
    MsgBox a
    
End Sub

比如integer数据类型,当a=40000就报错溢出

Sub demo()

    Dim a As Integer
    
    a = 40000
    
    MsgBox a
    
End Sub

这里有个小经验,当循环变量的时候一般设置变量为长整型.在excel最新版已经超过104万行数据.而使用整型的话3万多行就会报错.

以下案例,把a设置为integer,最终的结果为报错溢出.

再把案例a设置为long,最终的结果还是报错溢出.

再把案例a设置为变体类型,最终的结果还是报错溢出.

出错的原因是什么呢?

首先要理解计算机赋值语句的顺序,计算机是从左往右进行赋值,看到a=30000*2,就知道需要做一些运算的准备.根据以下截图的步骤进行赋值语句.

问题就出现在第1步.需要申请一块临时空间.根据以下截图的内容进行猜测.

回头看一下我们的案例,如果让结果不会报错呢?如下内容:

1.6用特殊字符代表数据类型

1.7下划线:_下划线能将很长的语句拆分成若干行的语句

1.8冒号: :将多个语句拼凑在同一行书写

1.9乘方运算:^

1.10反斜线:\

需要注意\和/的区别

2.日期类型的特点、本质和计算

2.1日期类型的基本特点

名称：Date
用途：存放表示时间的数据
精度：精确到秒
范围：公元100年1月1日0点0分0秒至公元9999年12月31日23点59分59秒

Sub demo()
    Dim d As Date
    d = #4/1/2019 9:34:00 AM#
    MsgBox d
End Sub

备注:
1.括上#,说明中间是一个日期表达式,而不是其他表达式,比如1÷19÷2016等
2.4/1/2019这里代表的是月日年

最终显示的结果如下:

2.2时间函数的常见三种类型

2.2.1 获取系统时间

• Date函数（年/月/日）
• Time函数（时/分/秒）
• Now函数（年/月/日/时/分/秒）

2.2.1.1 Date函数

获取当前系统日期（年/月/日）,即电脑右下角显示的电脑时间,具体如下

Sub demo()
    Dim d As Date
    d = Date
    MsgBox d
End Sub

代码显示的结果如下:

2.2.1.2 Time函数

获取当前系统时间（时/分/秒）

Sub demo()
    Dim d As Date
    d = Time()
    MsgBox d
End Sub

代码显示的结果如下:

2.2.1.3 Now函数

获取完整系统时间（年/月/日/时/分/秒）

Sub demo()
    Dim d As Date
    d = Now()
    MsgBox d
End Sub

代码显示的结果如下:

2.2.2 时间数据解析

excel自带对时间的解析有如下:

• Year（d）：返回时间d中的年份数字
• Month（d）：返回时间d中的月份数字
• Day（d）：返回时间d是当月第几日
• Weekday（d）：返回时间d是当周第几天
• Hour（d）：返回时间d是当日第几小时
• Minute（d）：返回时间d是该小时第几分
• Second（d）：返回时间d是该分种第几秒

Sub demo()
    Dim d As Date, a As Long
    d = #4/1/2019 10:35:00 AM#
    a = Year(d)
    MsgBox a
End Sub

代码显示的结果如下:

小案例,统计帝王的死亡月份,如下:

具体代码如下:

Sub deadmonth()
    Dim d As Date, i As Long
    i = 3
    Do While Trim(Cells(i, 2)) <> ""
        d = Cells(i, 4)
        Cells(i, 5) = Month(d)
        i = i + 1
    Loop
End Sub

代码显示的结果如下:

2.2.3时间运算

2.2.3.1DataDiff函数

小案例,统计帝王的寿命,以秒为单位,如下:

具体代码如下:

Sub life()
    Dim d As Date, i As Long
    i = 3
    Do While Trim(Cells(i, 2)) <> ""
        d = Cells(i, 4)
        Cells(i, 6) = DateDiff("s", Cells(i, 3), Cells(i, 4)) & "秒"
        i = i + 1
    Loop
End Sub

代码显示的结果如下:

2.2.3.2DataAdd函数

小案例具体如下:

Sub a()
    '向未来前进500天,显示"2020/8/13"
    MsgBox DateAdd("d", 500, #4/1/2019#)
End Sub

代码显示的结果如下:

Sub a()
    '向过去退500天,显示"2017/11/17"
    MsgBox DateAdd("d", -500, #4/1/2019#)
End Sub

代码显示的结果如下:

2.3时间函数需要注意的地方

日期型数据本质上是一个 Double类型的数字.
0代表1899年12月30日0时0分0秒
整数部分每增减1就是增减1天，小数部分0.1代表0.1天，即2.4小时或2小时24分钟。但是小数部分减法运算导致的时间变化比较复杂，请勿轻易使用。

1 )日期可以相加,整数部分每增减1就是增减1天

Sub test()
    Dim d As Date
    d = #4/1/2019#
    d = d + 5
    MsgBox d
End Sub

代码显示的结果如下:

2 )0代表1899年12月30日0时0分0秒,增加1,代表就是在0的基础上的日期增加一天.

Sub test()
    Dim d As Date
    d = 1
    MsgBox d

End Sub

代码显示的结果如下:

3 )0代表1899年12月30日0时0分0秒,增加1.5,代表就是在0的基础上的日期增加一天12小时.

Sub test()
    Dim d As Date
    d = 1.5
    MsgBox d

End Sub

代码显示的结果如下:

4 )0代表1899年12月30日0时0分0秒,增加1.5+1/24,代表就是在0的基础上的日期增加一天13小时.

Sub test()
    Dim d As Date
    d = 1.5 + 1 / 24
    MsgBox d

End Sub

代码显示的结果如下:

5 )0代表1899年12月30日0时0分0秒,d如果是负数,则是在0的基础上的日期往前数.

Sub test()
    Dim d As Date
    d = -1.75234
    MsgBox d

End Sub

代码显示的结果如下:

3.逻辑类型与逻辑运算

3.1 逻辑函数的基本特点

逻辑函数常涉及到是两个逻辑变量,是True/False,相关注意事项如下:

• True ,False:
  VBA保留字，代表真/假
• "True"," False":
  两个字符串，与逻辑无关

Sub booltest()
    Dim a As Boolean
    a = True
    MsgBox a
End Sub

代码显示的结果如下:

Sub booltest()
    Dim a As Boolean
    a = 3 > 5
    MsgBox a
End Sub

代码显示的结果如下:

3.2逻辑运算

3.2.1 AND运算

小案例,具体如下:

Sub booldemo()
    Dim age As Integer, married As String
    age = 25: married = "单身"
    If age < 30 And married = "单身" Then
        MsgBox "我这有包狗粮,拿走不谢哈"
    Else
        MsgBox "你是婚了呢,还是剩了呢?"
    End If
End Sub

代码显示的结果如下:

3.2.2 OR运算

3.2.3逻辑运算需要注意的事项

• 对于IF、Do While等语句：
  先得到条件表达式的最终结果（True/Fase），再决定下一步怎样执行。

• 发生死循环时可以尝试按下“ctrl”+“Pause"组合键！如果不好用，可使用任务管理器强制停止 Excel.

3.2.4逻辑案例

3.2.4.1案例1

第1行不涂色,如果城市与前一行相同,则不涂色;若与上一行城市不同,则涂色.具体可看截图,如下:

方案1

Sub paintCities()

    Dim paint As Boolean, i As Long
    
    '用paint代表涂色规则，即当前行是否应该涂色
    '开始时paint为false，即对第一行不涂色
    paint = False
    
    '从第二条数据（行号为4）开始扫描
    i = 4
    Do While Trim(Cells(i, 2)) <> ""
    
        '如果这一行数据的城市与上一行不同，则改变涂色规则
        If Trim(LCase(Cells(i, 2))) <> Trim(LCase(Cells(i - 1, 2))) Then
            
            If paint = True Then
                paint = False
            Else
                paint = True
            End If
        
        End If
        
        '确定本行涂色规则后，如果是True，则涂色
        If paint = True Then
            Range(Cells(i, 2), Cells(i, 4)).Interior.Color = 15177845
        End If
    
        i = i + 1
    Loop

End Sub

方案2

Sub paintCities2()

    Dim paint As Boolean, i As Long
    
    '用paint代表涂色规则，即当前行是否应该涂色
    '开始时paint为false，即对第一行不涂色
    paint = False
    
    '从第二条数据（行号为4）开始扫描
    i = 4
    Do While Trim(Cells(i, 2)) <> ""
    
        '如果这一行数据的城市与上一行不同，则改变涂色规则
        If Trim(LCase(Cells(i, 2))) <> Trim(LCase(Cells(i - 1, 2))) Then
            paint = Not paint
        End If
        
        '确定本行涂色规则后，如果是True，则涂色
        If paint Then
            Range(Cells(i, 2), Cells(i, 4)).Interior.Color = 15177845
        End If
    
        i = i + 1
    Loop

End Sub

        'paint = Not ((Trim(LCase(Cells(i, 2))) = Trim(LCase(Cells(i - 1, 2)))) Xor paint)

相关知识点

4.逻辑变量控制循环、Exit/Goto/On Error

使用逻辑变量,中途跳出循环

查找第一个被观音收归的妖怪

方案一

Sub guanyintudi()
    Dim found As Boolean, i As Long
    found = False
    i = 3
    Do While found = False And Trim(Cells(i, 2)) <> ""
        If InStr(Cells(i, 6), "观音") > 0 Then
            Range(Cells(i, 2), Cells(i, 6)).Interior.Color = 65535
            found = True
        End If
        i = i + 1
    Loop
End Sub

方案二

Sub guanyintudi()
    Dim found As Boolean, i As Long
    found = False
    i = 3
    Do While Not found And Trim(Cells(i, 2)) <> ""
        If InStr(Cells(i, 6), "观音") > 0 Then
            Range(Cells(i, 2), Cells(i, 6)).Interior.Color = 65535
            found = True
        End If
        i = i + 1
    Loop
End Sub

4.1Exit语句

1)Exit do 跳出循环
查找第一个被观音收归的妖怪,用exit do语句又是怎么样的呢?

Sub guanyintudi()
    Dim found As Boolean, i As Long
    found = False
    i = 3
    Do While Trim(Cells(i, 2)) <> ""
        If InStr(Cells(i, 6), "观音") > 0 Then
            Range(Cells(i, 2), Cells(i, 6)).Interior.Color = 65535
            Exit Do
        End If
        i = i + 1
    Loop
End Sub

2)Exit for 跳出循环
查找第一个被观音收归的妖怪,用exit for语句又是怎么样的呢?

Sub guanyintudi()
    Dim found As Boolean, i As Long
    found = False
    i = 3
    For i = 3 To 10000
        If InStr(Cells(i, 6), "观音") > 0 Then
            Range(Cells(i, 2), Cells(i, 6)).Interior.Color = 65535
            Exit For
        End If
    Next i
    MsgBox "已经找到!"
End Sub

3)Exit sub 跳出循环

Sub exittest()
    MsgBox "轻轻的我来了"
    Exit Sub
    MsgBox "轻轻的我走了"
End Sub

代码显示的结果如下:

4 ) 注意的地方
建议使用do while,不要使用while语句

exit只能跳出一层的循环

4.2goto语句

可以跳转到同一过程（函数）内的任意一行。

Sub exittest()
    Do While True
        Do While True
            Do While True
                MsgBox "深陷第三道无间轮回......"
                GoTo 三界外:
            Loop
        MsgBox "深陷第二道无间轮回......"
    Loop
    MsgBox "深陷第一道无尽间轮回......"
 Loop

三界外:
  MsgBox "解脱六道轮回"
End Sub

代码显示的结果如下:

注意:GoTo语句是因威力过于强大而被干年封禁的秘笈！

因为goto语句过于使用后会变成类似于面条般复杂.

4.2.1goto的使用场景-on error goto lable x

on error goto lable x用于一旦发生错误就直接调转到myerror标签继续运行.

Sub errortest()
    Dim i As Integer
    i = Cells(3, 8)
    On Error GoTo MyError:
    i = i ^ 2
    Cells(3, 9) = i
    Exit Sub
MyError:
    MsgBox "数字有点大,有问题call我"
End Sub

最终显示的结果如下:

4.2.2goto的使用场景-on error resume next

on error resume next用于如果某行出错,就忽略它并继续执行.

Sub errortest()
    Dim i As Integer
    i = Cells(3, 8)
    On Error Resume Next:
    i = i ^ 2
    Cells(3, 9) = i
End Sub

4.2.3goto知识点汇总

5.数据类型识别和转换原则，四舍五入规则

5.1 IsDate()函数

判断是否为date()类型

具体小案例,如下:

Sub dataaddjust()
    Dim i As Long
    For i = 3 To 18
        If IsDate(Cells(i, 2)) Then
            Cells(i, 4) = DateAdd("d", 38, Cells(i, 2))
        End If
    Next i
End Sub

5.2 IsNumeric()函数

用于处理文字和数字交叉在一起的表格

IsNumeric()函数容易犯错的两点如下:
1.易将IsNumeric拼写错误

2.易将IsNumber混淆
IsNumeric与IsNumber非常相似,二者还是有区别的

• 当单元格是个date类型的时候,IsNumber当成是数值,返回的是true
• 当单元格是个date类型的时候,IsNumeric不是数值,返回的是false

从而可以看出IsNumeric函数更精确一些.

5.3TypeName()函数

具体小案例如下:

5.3.1案例1

Sub TypeNameTest()
    Dim a As Date, s As String
    a = #4/1/2019#
    s = TypeName(a)
    MsgBox s
End Sub

代码显示的结果如下:

5.3.2案例2

Sub TypeNameTest()
    Dim a As Boolean, s As String
    a = True
    s = TypeName(a)
    MsgBox s
End Sub

代码显示的结果如下:

image.png

5.3.3案例3

Sub TypeNameTest()
    Dim i
    i = 3
    For i = 3 To 8
        Cells(i, 3) = TypeName(Cells(i, 2).Value)
    Next i
End Sub

5.3.4案例4

统计这5个人的工资

Sub salary()
    Dim i As Long, s As Long
    s = 0
    For i = 5 To 9
        If IsNumeric(Cells(i, 4).Value) Then
            s = s + Cells(i, 4)
        Else
            MsgBox "第" & i & "行D列不是数字,不纳入统计!"
        End If
    Next i
End Sub

代码显示的结果如下:

5.4数据类型的转换

比如字符串转换成日期

比如小数转换成整数

VBA提供了一系列的转换函数,具体如下:

• CBool（X）：根据x的值，生成一个逻辑类型的数据并返回
• CDate（x）：根据x的值，生成一个日期类型的数据井返回
• CStr（x）：根据X的值，生成一个字符串类型的数据井返回
• CInt（x）：根据x的值，生成一个整数类型的数据并返回
• CLng（x）：根据x的值，生成一个Long类型的数据并返回
• CDbl（x）：根据X的值，生成一个 Double类型的数据并返回
• CCur（x）：根据x的值，生成一个 Currency类型的数据并返回
• CSng（x）：根据x的值，生成一个 Single类型的数据并返回
• CByte（x）：根据X的值，生成一个Byte类型的数据并返回
• CDec（x）：根据x的值，生成一个 Decimal类型的数据并返回

具体案例如下:

转换数据类型的时候,即使不写这些转换函数,也不会出什么问题.因为VBA自动类型的转换效果非常好.

什么情况下无法执行类型转换？
含义相同,大小相容

• 含义相同:要转换的数据类型跟你想转换的目标类型是一致的

  
  

• 大小相容:转换的数据类型必须可以容纳之前的数字

  
  

小数部分怎样转化成整数？
VBA用的是"银行家"四舍五入方法

5.5Round()函数

• VBA所使用的还是银行家的四舍五入方法
• excel自带的函数,就是平常用的四舍五入

5.6Int()函数

image.png

6.字符编码、ASC/CHR函数，字符串比较大小

6.1Asc()函数

案例:将A转换成数字

Sub asctest()
    Dim a As Integer
    a = Asc("A")
    MsgBox a
End Sub

代码显示的结果如下:

6.1Chr()函数

6.2.1案例1

案例:将数字转换成对应的字符

Sub chrtest()
    Dim a As String
    a = Chr("65")
    MsgBox a
End Sub

6.2.2案例2

案例:将"领导您好,本功能暂时无法执行.但若能给我加工资,十分钟就可以搞掂!"这句话分成两行.

Sub msgboxDemo()
    Dim s As String
    s = "领导您好,本功能暂时无法执行." & Chr(13) & Chr(10) & "但若能给我加工资,十分钟就可以搞掂!"
    MsgBox s
End Sub

6.2.3案例3

在B列第2行开始顺序显示大写字母A-Z

Sub chrDemo()
    Dim i As Long
    For i = 65 To 90
        Cells(i - 63, 2) = Chr(i)
    Next i
End Sub

最终显示的代码结果如下:

6.2.4案例4

将表格中的等级更改成A-Z对应的等级.

Sub converDemo()
    Dim i As Long
    For i = 3 To 8
        Cells(i, 4) = Chr(Cells(i, 4) + Asc("A") - 1)
    Next i
End Sub

最终显示的代码结果如下:

6.3字符串比较规则

具体案例如下:

若在比较时不想区分大小写，可以先使用 UCase统一大小写后再行比较。