??VBA

1
??VBA
?? VBA
VBA????Visual Basic for Applications(?????Visual
Basic),????????Microsoft Office???Visual
Basic??,??????????????,?????????????????
2
??VBA????
?????????
3
??VBA????
4
????Visual Basic??
?????????
5
????Visual Basic??
6
????Access 2000?VB?????
?? Visual Basic 5.0 ?? Access ???
??Access 2000???Access 97???????VB5.0???
7
?? Visual Basic 5.0 ?? Access ???
??VB ?????????
8
?? Visual Basic 5.0 ?? Access ???
??
???
VB5????
9
?? Visual Basic 5.0 ?? Access ???
??????
10
?? Visual Basic 5.0 ?? Access ???
??Data?????????
????????Data1???
???????
???????????
11
?? Visual Basic 5.0 ?? Access ???
12
?? Visual Basic 6.0 ?? Access ???
??VB ?????????
13
?? Visual Basic 6.0 ?? Access ???
??
???
VB6????
14
?? Visual Basic 6.0 ?? Access ???
?????
15
?? Visual Basic 6.0 ?? Access ???
16
?? Visual Basic 6.0 ?? Access ???
17

• ????Access?VBA???(????????????)
• ?????
• ?????????????????,?Access????,????????????

??(1)?????????????
18
??????????,???????????????????????, ??????????????
?
??(2)?????????
19
????,?????????????????????????idno,???????? ??????
????????,?????????????????????????? ??????idno???
???,??????????????????
??(3)???????
20
?????????????????????????,?????????? ????????,????
??????????
??(4)??????????
21

• ?????????
• ????????????????

??(5)??????
22
???????????,????????????????/??????? ????OLAP???
???????
??(6)??????
23
???????,???????????????????????????
??(7)???????
24
????????,??????????????????, ???????????????????
??(8)?????????????
25
????????????????????????????????,??????????, ????
????,????????????
??(9)??????????
26
???? ??????????????????????,?????????????
,?????????????????????????????Access????,?????????
VBA????????????? ?????????,???????????????89??????
??????????????????????89???????19????2049?5099
?100249?250499??500???????? ?????,????????????
?,?????????????????,?????????????????????????????,
????????????,???????????
27
??(10)??????????????
28
??????89????,?????????,???????,?2049?????
??(11)?????2049??????????
29
????????????,???????????,??????????? ?2049???????
??,???
??(12)????????
30
??????????????????????,????????????????? ?????????
??VBA????,????????????? ???????VBA???

Private Sub Combo267_AfterUpdate()
????????,????? Dim str str????? ????? If
Combo267 "??" Then str "method1"
??????????method,???? Else str
"method0" ???? End If If Check280 ltgt 0 Then
?????????????? Select Case Combo270
89?????????a5_2 Case "19???" str str "
and a5_2 lt 20" ????????? Case "2049?" str
str " and a5_2 gt 19 and a5_2 lt50" Case
"5099?" str str " and a5_2 gt 49 and a5_2 lt
100" Case "100249?" str str " and a5_2 gt
99 and a5_2 lt 250" Case "250?499?" str str
" and a5_2 gt 249 and a5_2 lt 500" Case
"500???" str str " and a5_2 gt 499" End
Select End If Me.Filter str ????????????? Me.F
ilterOn True ???? End Sub
31
??89?????????VBA??? Private Sub
Combo270_AfterUpdate() Dim str Select Case
Combo270 ??89???????? Case "19???" str
"a5_2 lt 20" Case "2049?" str "a5_2 gt 19
and a5_2 lt50" Case "5099?" str "a5_2 gt 49
and a5_2 lt 100" Case "100249?" str "a5_2 gt
99 and a5_2 lt 250" Case "250?499?" str
"a5_2 gt 249 and a5_2 lt 500" Case "500???" str
"a5_2 gt 499" End Select If Check280 ltgt 0 Then
?????????????? If Combo267 "??" Then
??????????? str str "and method1"
Else str str "and method0" End
If End If Me.Filter str ????????????? Me.Filte
rOn True ???? End Sub
32
????????VBA??? Private Sub Command269_Click()
Me.FilterOn False ????? End
Sub ??????????VBA??? ???????,??????(?????)?,?????
?????????,????????? Private Sub
Option229_GotFocus() If Left(idno, 1) ltgt "M"
And Left(idno, 1) ltgt "S" Then MsgBox ("??!
?????lt??gt??????? M?S ????," Chr(13)
"??????????Key???????!") End If End Sub Private
Sub Option231_GotFocus() If Left(idno, 1) ltgt
"B" Then MsgBox ("??! ?????lt??gt??????? B
????," Chr(13) "??????????Key???????!") End
If End Sub
33
????????????????,????????????????, ?????????
??(13)???????????
34
????????????,???????,????????????????? ?????,???R
ecord not found,?????????
??(14)?????????
??(15)?????????? ??????,????????,??????????
??(16)??????????
35
??(17)???????
36
??/?????????????????????????,??????? ??????Scan??
??,??????????
??(18)???????
37
??????????,??????????????????????????? ?,?????????
?????????????????? ???,????? ?????????????
??(19)?????????????
??(20)?????????
38
??,?????????????????????????????????
??(21)???????
??(22)???????????????
39
??????,??????????key?,????????????
??(23)??????????key?
40
?????VBA??? Private Sub Command17_Click() On
Error GoTo Err_Command17_Click Dim SQLStr
num InputBox("??????????????", "????") ??
??????????? If num ltgt "" Then Set rs
New ADODB.Recordset ?? ??ADO?? SQLStr
"select from ???? where idno '" num "'"
?? ??SQL????,?????????? rs.Open SQLStr,
CurrentProject.Connection, adOpenKeyset,
adLockOptimistic ?? ??ADO?? ???SQL??
If Not rs.EOF Then ?? ??????????
choice MsgBox("???????" Chr(10)
rs.Fields("comp") Chr(10) "????"
rs.Fields("idno") Chr(10) "Key?"
rs.Fields("keyno") Chr(10) "???", 4)
?? ?????????,??????????? If choice
vbYes Then DoCmd.GoToRecord , ,
acNewRec idno rs.Fields("idno")
keyno rs.Fields("keyno") ??
??????key????? End If Else
MsgBox ("???,?????????????????") ??
??????????? End If End If
Exit_Command17_Click Exit
Sub Err_Command17_Click MsgBox
Err.Description Resume Exit_Command17_Click
End Sub
41
??????????Excel??,?????(C\Tis\Olap.xls)?????????
???, ??????????????????????????????????89?????? ??
???89????????????
??(24)???????89????????
42
??(25)?????????89????????
43
????????????????????Excel???
??(26)????????
44
??(27)????????
45
??(28)?????????Excel?????
46
VBA????
ltExcel?VBA?????ANOVA??? gt
????????,???????????,?????????Excel????????????
?VBA???????,???????Visual Basic??????????,????????
??ANOVA??,?????
47
VBA????
ltExcel?VBA?????ANOVA??? gt
?????????????,????anova???
48
VBA????
ltExcel?VBA?????ANOVA??? gt
(??????)
49
VBA????
ltExcel?VBA?????ANOVA??? gt