2.深入解析数据类型与变量——《Excel VBA 程序开发自学宝典》
2.1 数据类型
数据类型 | 所占字节 |
Byte | 1 |
Boolean | 2 |
Integer | 2 |
Long | 4 |
Single | 4 |
Double | 8 |
Currency | 8 |
Decimal | 14 |
Date | 8 |
Object | 4 |
String | 10 |
Variant | 16 |
实例
Sub test()
Dim sums as long, cell as range, I as integer, mystr as string
For each cell in range(“A1:A10”)
If vba.isnumeric(cell) then sums = sums + cell else mystr = mystr & cell
If cell = “” then I = I +1
Next cell
Debug.print “A1:A10中有空白单元格” & I &”个”
Debug.print “A1:A10中数据和为：” ; sums
Debug.print “A1:A10中文本为：” ; mystr
End sub
2.2 数据类型转换
函数 | 返回类型 |
Cbool | Boolean |
Cbyte | Byte |
Ccur | Currency |
Cdate | Date |
cbdl | double |
Cdec | Decimal |
Cint | Integer |
Clng | Long |
Csng | Single |
Cstr | String |
cvar | variant |
实例：
Sub 类型转换()
Dim funds as double
Funds = 80.42454
Msgbox “类型：” & typename(funds) & “值：” & funds ‘typename()用于识别数据类型
Msgbox “类型：” & typename(cbool(funds)) & “值：” & cbool(funds)
Msgbox “类型：” & typename(cbyte(funds)) & “值：” &cbyte( funds)
End sub
2.3 变量
实例：
Sub 显示用户名()
User_name = application.inputbox(“请输入您的姓名” , “姓名”,,,,,,2)
Msgbox user_name
End sub
Dim temp as byte ‘在sub之前dim，此变量为模块级变量，或者用public申明
Sub a()
Temp = 10
Msgbox temp
End sub
Sub b()
Temp = temp + 10
Msgbox temp
End sub
Sub a()
Dim temp = as byte ‘在sub内dim，两个sub内的变量不相关，为过程级别变量
Temp = 10
Msgbox temp
End sub
Sub b()
Dim temp as byte
Temp = 20
Temp = temp + 10
Msgbox temp
End sub
Let A = 10 ‘对非对象变量赋值
Set A = range(“A10”) ‘对单元格对象赋值
Set A = [A10]
Sub 设置A10的字体()
Activeworkbook.sheets(“sheet2”).range(“A10”).font.name = “黑体”
Activeworkbook.sheets(“sheet2”).range(“A10”).font.colorindex= 3
Activeworkbook.sheets(“sheet2”).range(“A10”).interior.colorindex = 5
End sub
Sub 设置A10的字体二()
Dim rng as range
Set rng = activeworkbook.sheets(“sheet2”).range(“A10”)
Rng.font.name = “黑体” ‘设置单元格对象后，程序简化很多而且提升效率
Rng.font.colorindex = 3
Rng.interior.colorindex = 5
End sub