2.深入解析数据类型与变量——《Excel VBA 程序开发自学宝典》

2.1 数据类型

实例

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 数据类型转换

实例：

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