-
除法
\整除
?8\16
0
/除
?8/16
0.5 -
逻辑运算符
运算符 | 例句 | 结果 |
---|---|---|
Not 非 | ?not true | false |
and 与 | ?True and false | false |
xor 异或 | 1 xor 0 | 1 |
or 或 | ?true or false | True |
eqv 相等 | ||
imp 隐含 |
Xor—相同为false,不同为true。
- 数据类型
数据类型 | 类型标识符 | 字节 |
---|---|---|
字符串型string | $ | (0-65400) |
字节型 Byte | 无 | 1 |
布尔型Boolean | 无 | 2 |
整数型Interger | % | 2 |
长整型 Long | & | 4 |
单精度型 single | ! | 4 |
双精度型 double | # | 8 |
日期型date | 无 | 8公元100\ 1\1-99\12\31 |
货币型 currency | @ | 8 |
小数点型Decimal | 无 | 14 |
变体型Variant | 无 | 以上任意类型,可变 |
对象型 Object | 无 | 4 |
关键字 | 类型 |
---|---|
dim | 局部变量 |
Private | 私有变量 |
public | 公有变量 |
Global | 全局变量 |
static | 静态变量 |
const | 常量 |
eg:Const Pi=3.1415926 as single
- 数组
Dim 数组名([lower to ]upper [, [lower to ]upper, ….]) as type ;Lower缺省值为0。
如下例:
Dim array1() as double :
Redim array1(5) :
array1(3)=250 :
Redim preserve array1(5,10) - 书写规范
(1)VBA不区分标识符的字母大小写,一律认为是小写字母;
(2)一行可以书写多条语句,各语句之间以冒号 : 分开;
(3)一条语句可以多行书写,以空格加下划线 _ 来标识下行为续行;
(4)标识符最好能简洁明了,不造成歧义。 - 判断语句
- If…then…elseif…then…else……endif
eg:
If Number < 10 Then
Digits = 1
ElseIf Number < 100 Then
Digits = 2
Else
Digits = 3
End If
eg:
Sub if_()
Dim num As Integer
num = Cells(2, 1)
If num < 10 Then
Cells(1, 2) = "less than 10"
ElseIf num = 10 Then
Cells(2, 2) = "equal 10"
Else
Cells(3, 2) = "more than 10"
End If
End Sub
- Select Case…case…case…end select
eg:
Select Case Pid
Case “A101”
Price=200
Case “A102”
Price=300
……
Case Else
Price=900
End Case
Sub case_()
Select Case Sheet1.Range("A1")
Case "100"
Cells(4, 1) = 100
Case "200"
Cells(5, 1) = 200
Case Else
Cells(6, 1) = 300
End Select
End Sub
- choose函数
choose(index,choce-1,choose-2,choose-3,……,choose-n)
在这里插入代码片
- switch函数
- 循坏语句
- for next 语句 以指定次数来重复执行一组语句
for counter=start to end [step step]
[statements]
[exit for]
[statements]
next [counter]
Sub for_next()
Dim x As Integer, j%
For x = 10 To 1 Step -1
For j = 0 To 2
mystring = mystring & j
Next j
mystring = mystring & ""
Cells(x, 4) = mystring
Next x
End Sub
- for each ……next 主要功能是对一个数组或集合对象进行,让所有元素重复执行一次语句
for each element in group
[statements]
exit for
[statements]
next element
Sub for_each()
' Dim wrk As Worksheets
For Each wks In Worksheets
MsgBox "工作表的名称是:" & wks.Name
Next wks
End Sub
- do ……loop 在条件为true时,重复执行区块命令
Do { While | Until } condition
[ statements ]
[ Continue Do ]
[ statements ]
[ Exit Do ]
[ statements ]
Loop
或者
Do { While | Until } condition
[ statements ]
[ Continue Do ]
[ statements ]
[ Exit Do ]
[ statements ]
Loop {while|until} condition
Sub do_()
Dim index As Integer
Dim Stri1
index = 1
Do While index <= 100
If index > 10 Then
Exit Do
End If
Stri1 = Stri1 & index
Cells(index, 2) = Stri1
index = index + 1
Loop
End Sub
- while 语句
While conditions
[statements]
Wend
Sub while_()
Dim d As Integer
d = 5
While d < 10:
strin = strin & d
Debug.Print strin
d = d + 2
Wend
End Sub