840 likes | 1.04k Views
Ph?m Th? Kim Ngoan. 2. Chuong 1: T?NG QUAN V? VBA. I. Gi?i thi?u chungII. ModuleIII. C?u tr
E N D
1. Ph?m Th? Kim Ngoan 1 Ph?n 2: VISUAL BASIC FOR APPLICATION (VBA) Chuong 1: T?NG QUAN V? VB
Chuong 2: CC KHAI BO TRONG VB
Chuong 3: CC C?U TRC I?U KHI?N V KI?U D? LI?U C C?U TRC
Chuong 4: TH? T?C C?A NGU?I DNG
Chuong 5: ?I TU?NG V BI?N ?I TU?NG
2. Ph?m Th? Kim Ngoan 2 Chuong 1: T?NG QUAN V? VBA I. Gi?i thi?u chung
II. Module
III. C?u trc c?a Module
VI. T?o v th?c hi?n cc th? t?c
V. M?t s? hi?u ?ng khi vi?t v s?a ch?a m l?nh
3. Ph?m Th? Kim Ngoan 3 I. Gi?i thi?u chung Visual Basic l ngn ng? l?p trnh dng d? rng bu?c cc d?i tu?ng trong ?ng d?ng v?i nhau thnh m?t h? th?ng h?p nh?t. Cc chuong trnh c?a VBA (Visual Basic for Application) du?c t? ch?c v ho?t d?ng trong Access nh?m m?c dch xy d?ng cc ?ng d?ng ph?c t?p.
C th? dng VB d? th?c hi?n cc cng vi?c sau:
- Lm cho ?ng d?ng d? b?o tr hon
T?o ra cc hm/th? t?c c?a ngu?i s? d?ng d? x? l cc thao tc ph?c t?p m chua du?c MS Access cung c?p s?n.
X? l l?i theo ngu?i s? d?ng.
4. Ph?m Th? Kim Ngoan 4 I. Gi?i thi?u chung T?o hay thao tc v?i cc d?i tu?ng
Th?c hi?n cc thao tc c?p h? th?ng : th?c hi?n m?t ?ng d?ng khc, lin k?t gi?a cc ?ng d?ng.
?c di?m:
- Chuong trnh c?a VBA ho?t d?ng ch? y?u theo hu?ng s? ki?n : b?m chu?t t?i cc nt l?nh, di chuy?n vo hay ra d?i tu?ng di?u khi?n c?a form, report
Cc th? t?c v hm c?a VBA n?m r?i rc trong cc module c?a form, report ho?c module chung c?a CSDL v chng c th? g?i l?n nhau.
5. Ph?m Th? Kim Ngoan 5 II. Module 1. Khi ni?m Module: L t?p cc tu? ch?n, cc khai bo, cc th? t?c cng du?c luu tr? trong m?t don v? chuong trnh.
2. Phn lo?i Module:
- Module chu?n: C th? thi hnh b?t c? noi no trong ?ng d?ng.
- Module l?p: Cc th? t?c d?nh nghia trong Module l?p s? tr? thnh cc phuong th?c c?a d?i tu?ng ny.
* M?i Form/Report d?u c th? k?t h?p v?i m?t Module l?p (Form Module/Report Module), Module l?p ny du?c luu cng v?i Form/Report.
6. Ph?m Th? Kim Ngoan 6 III. C?u trc c?a Module 1. Module chu?n:
- Cc tu? ch?n
- Cc khai bo ton c?c (Public)
- Cc khai bo c?p module (Dim)
- Cc th? t?c s? d?ng ton c?c
- Cc th? t?c s? d?ng c?p module
2. Module lo?i:
- Cc tu? ch?n
- Cc khai bo c?p module
- Cc th? t?c x? l s? ki?n
- Cc th? t?c s? d?ng c?p module
7. Ph?m Th? Kim Ngoan 7 Module chu?n
8. Ph?m Th? Kim Ngoan 8 Module lo?i
9. Ph?m Th? Kim Ngoan 9 III. C?u trc c?a Module 3. C?u trc m?t th? t?c:
a. C?u trc Sub procedure :
Sub Tn_th? _t?c ([Cc tham s?])
Cc l?nh
End Sub
b. C?u trc Function procedure :
Function Tn_hm ([Cc tham s?]) [AS ki?u d? li?u]
Cc l?nh
Tn_hm = bi?u th?c gi tr?
End Sub
c. C?u trc th? t?c x? l s? ki?n :
Private Sub Tnd?itu?ng__tns?ki?n ([Cc tham s?])
Cc l?nh
End Sub
10. Ph?m Th? Kim Ngoan 10 VI. T?o v th?c hi?n cc th? t?c 1. Th? t?c x? l s? ki?n: Thu?ng l cc th? t?c g?n v?i cc di?u khi?n c?a Form (nt l?nh, ) d?t trong Module lo?i
- T?o m?i:
M? Form c di?u khi?n c?n g?c th? t?c ? d?ng thi?t k?.
Ch?n Properties c?a di?u khi?n c?n g?n th? t?c, ch?n ngan s? ki?n (Event)
Ch?n s? ki?n c?n g?n (On Click, ), ch?n nt , ch?n Code Builder, MS Access dua ra khun d?ng c?a th? t?c v?i tn tuong ?ng, t?i v? tr con tr?: g cc l?nh c?n th?c hi?n trong th? t?c.
- Th?c thi: M? Form, tc d?ng s? ki?n v?a g?n ln nt l?nh.
11. Ph?m Th? Kim Ngoan 11 VI. T?o v th?c hi?n cc th? t?c 2. Th? t?c Sub procedure v Function procedure: ?t trong Module lo?i ho?c chu?n.
- T?o m?i: M? mn hnh vi?t m l?nh, vi?t th? t?c theo c?u trc tuong ?ng.
- Th?c thi:
Bin d?ch cho d?n khi khng cn l?i c php
Menu Debug ? Complie Loaded Modules
Ch?y xem k?t qu? trong khung tr?c ti?p:
M? m hnh tr?c ti?p: Menu View ? DebugWindow / Immediate Window
12. Ph?m Th? Kim Ngoan 12 VI. T?o v th?c hi?n cc th? t?c Ch?y th? t?c:
Th? t?c khng c tham s?:
Tn_Module.Tn_th?_t?c
Call Tn_Module.Tn_th?_t?c
Call Tn_Module.Tn_th?_t?c ()
V d?: Tinhtoan.Tong
Call Tinhtoan.Tong
Call Tinhtoan.Tong()
13. Ph?m Th? Kim Ngoan 13 VI. T?o v th?c hi?n cc th? t?c Th? t?c c tham s?:
Call Tn_Module.Tn_th?_t?c (Cc tham s? th?c s?)
V d?: Call Tinhtoan.Tongab(15, 56)
Hm (Function procedure):
? Tn_Module.Tn_hm ()
? Tn_Module.Tn_hm
? Tn_Module.Tn_hm (Cc tham s? th?c s?)
V d?: ?Tinhtoan.Tong1()
?Tinhtoan.Tong2(25, 35)
14. Ph?m Th? Kim Ngoan 14 VI. T?o v th?c hi?n cc th? t?c G?i th? t?c t? cc th? t?c khc:
Th? t?c:
Call Tn_Module.Tn_th?_t?c (Cc tham s? th?c s?)
Hm: d?t trong cc bi?u th?c
Tn_Module.Tn_hm (Cc tham s? th?c s?)
15. Ph?m Th? Kim Ngoan 15 V. M?t s? hi?u ?ng khi vi?t v s?a ch?a m l?nh - T? d?ng xy d?ng cc pht bi?u:
Tools -> Options -> Module -> Auto list Members
- Thng tin nhanh:
Tools -> Options -> Module -> Auto QuickInfo
- T? d?ng ki?m tra c php:
Tools -> Options -> Module -> Auto Syntax Check
- Di chuy?n gi?a cc th? t?c trong module:
Ctrl + Page Down / Ctrl + Page Up
16. Ph?m Th? Kim Ngoan 16 Chuong 2: CC KHAI BO TRONG VB I. Cc tu? ch?n
II. Cc ki?u d? li?u co b?n
II. Khai bo
IV. Cc hm/th? t?c nh?p xu?t d? li?u
V. M?t s? hm / th? t?c c?a VB
17. Ph?m Th? Kim Ngoan 17 I. Cc tu? ch?n Option Explicit: ngu?i dng ph?i khai bo tu?ng minh cc bi?n
Option Compare Binary: phn bi?t ch? thu?ng v hoa khi vi?t chuong trnh
Option Compare Text: phn bi?t ch? thu?ng v hoa khi so snh
Option Base n: qui d?nh ch? s? d?u tin trong ki?u d? li?u m?ng
18. Ph?m Th? Kim Ngoan 18 II. Cc ki?u d? li?u co b?n
19. Ph?m Th? Kim Ngoan 19 II. Cc ki?u d? li?u co b?n
20. Ph?m Th? Kim Ngoan 20 III. Khai bo 1. Khai bo h?ng:
Public/Private CONST Tn_h?ng [AS Ki?u d? li?u] = Bi?u th?c gi tr?
V d?: Const Pi=3.14
Public Const S = y l v d?
Private Const so As Integer = 5
2. Khai bo bi?n:
Public/Private/Dim/Static Tn_bi?n [AS Ki?u d? li?u]
V d?: Public Hoten As String, DTB As Double
Private Diachi As String
21. Ph?m Th? Kim Ngoan 21 III. Khai bo Ph?m vi s? d?ng:
M?c th? t?c : Cc khai bo bn trong th? t?c.
M?c module : Cc khai bo Private Const trong ph?n khai bo c?a module.
M?c project : Khai bo Public
Tn:
G?m ch? ci, ch? s? v d?u g?ch chn, b?t d?u l ch? ci.
Khng trng v?i t? kho c?a Access.
? di khng vu?t qu 40 k t?.
22. Ph?m Th? Kim Ngoan 22 IV. Cc hm/th? t?c nh?p xu?t d? li?u 1. Nh?p d? li?u: hm InputBox, InputBox$
C php: INPUTBOX[$](Dng nh?c [[,tiu d?] [, gi tr? m?c d?nh][,x, y] ])
Hm InputBox tr? v? gi tr? Variant
Ch?c nang : Hi?n th? dng nh?c trong m?t h?p tho?i, d?i nh?p li?u v tr? v? gi tr? trong textbox
V d?: Nh?p 1 s? nguyn t? bn phm v luu trong bi?n a
Dim a as Integer
a = Inputbox(Nh?p a=, Nh?p s? li?u)
23. Ph?m Th? Kim Ngoan 23 IV. Cc hm/th? t?c nh?p xu?t d? li?u 2. Xu?t d? li?u:
a. Th? t?c Msgbox
C php: MsgBox Dng thng bo, ki?u h?p tho?i, tiu d?
b. Hm Msgbox
C php: MsgBox (Dng thng bo, ki?u h?p tho?i, tiu d?)
Ki?u h?p tho?i: Gi tr? cho bi?t cc nt l?nh no s? hi?n th? v cc bi?u tu?ng s? hi?n th? trong h?p tho?i.
Ch?c nang : Hi?n th? m?t thng di?p trong h?p h?i tho?i v d?i ngu?i s? d?ng ch?n m?t nt l?nh.
Khc nhau gi?a hm v th? t?c MsgBox : Hm MsgBox tr? v? gi tr? cho bi?t d ch?n nt l?nh no, cn th? t?c th khng.
24. Ph?m Th? Kim Ngoan 24 IV. Cc hm/th? t?c nh?p xu?t d? li?u * Tham s? ki?u h?p tho?i g?m 3 thnh ph?n: Nt l?nh no s? hi?n th?, ki?u bi?u tu?ng hi?n th?, nt ng?m d?nh.
- Nt l?nh hi?n th?:
0 / vbOnlyOK OK
1 / vbOKCancel OK, Cancel
2 / vbAbortRetryIgnore Abort, Retry, Ignore
3 / vbYesNoCancel Yes, No, Cancel
4 / vbYesNo Yes, No
5 / vbRetryCancel Retry, Cancel
25. Ph?m Th? Kim Ngoan 25 IV. Cc hm/th? t?c nh?p xu?t d? li?u - Xc d?nh bi?u tu?ng:
0 khng
16 / vbCritical Critical (Stop)
32 / vbQuestion Warning query
48 / vbExclamation Warning Message
64 / vbInformation Information
26. Ph?m Th? Kim Ngoan 26 IV. Cc hm/th? t?c nh?p xu?t d? li?u - Xc d?nh nt ng?m d?nh:
0 / vbDefaultButton1 nt th? 1
256 / vbDefaultButton2 nt th? 2
512 / vbDefaultButton3 nt th? 3
* Gi tr? c?a hm khi nt l?nh du?c ch?n:
1 OK 4 Retry
2 Cancel 5 Ignore
3 Abort 6 Yes 7 No
c. In ra mn hnh tr?c ti?p: Debug.print bi?u th?c
27. Ph?m Th? Kim Ngoan 27 IV. Cc hm/th? t?c nh?p xu?t d? li?u V d?: In ra du?i d?ng h?p tho?i t?ng c?a a+ b (dng th? t?c)
Msgbox T?ng c?a a+b= & a+b, vbOkCancel + vbInformation, K?t qu?
In ra du?i d?ng h?p tho?i t?ng c?a a+ b c ki?m tra nt l?nh b?m (dng hm Msgbox)
IF Msgbox (T?ng c?a a+b= & a+b, vbOkCancel+vbInformation, K?t qu?)= vbOk THEN
28. Ph?m Th? Kim Ngoan 28 V. M?t s? hm / th? t?c c?a VBA Cc hm v? chu?i:
LTRIM(St) c?t cc k t? tr?ng bn tri chu?i
RTRIM(St) c?t cc k t? tr?ng bn ph?i chu?i
TRIM(St) c?t cc k t? tr?ng bn ph?i v bn tri chu?i
LEN(St) d? di chu?i
LEFT(St,n) l?y n k t? bn tri chu?i
RIGHT(St,n) l?y n k t? bn ph?i chu?i
MID(St,i,n) l?y n k t? b?t d?u t? v? tr i trong chu?i
STR(n) d?i s? thnh chu?i
29. Ph?m Th? Kim Ngoan 29 V. M?t s? hm / th? t?c c?a VB LCASE(St) d?i cc k t? trong chu?i thnh ch? thu?ng
UCASE(St) d?i cc k t? trong chu?i thnh ch? in
Cc hm tnh ton:
ABS(n) tr? tuy?t d?i
FIX(r) l?y ph?n nguyn c?a s? th?c
INT(r) l?y ph?n nguyn g?n nh?t v nh? hon r
SQR(n) can b?c 2 c?a n
EXP(n) hm e mu n
VAL(St) d?i chu?i thnh s?
Cc hm v? ngy, gi?:
NOW() l?y ngy gi? c?a h? th?ng
DATE() ngy h? th?ng
TIME() gi? h? th?ng
30. Ph?m Th? Kim Ngoan 30 V. M?t s? hm / th? t?c c?a VB DATEPART(dt) l?y m?t ph?n c?a ngy/thng/nam
DATEPART(d, dt) l?y ph?n ngy
DAY(dt)
DATEPART(m, dt) l?y ph?n thng
MONTH(dt)
DATEPART(yyyy, dt) l?y ph?n nam
YEAR(dt)
Cc hm ki?m tra:
ISNUMERIC(n) ki?m tra n c ph?i l m?t s? khng?
ISDATE(d) ki?m tra d c ph?i l ki?u ngy khng?
Ch :
dng ch thch
Vi?t l?nh trn nhi?u dng dng d?u space v g?ch du?i ( _)
31. Ph?m Th? Kim Ngoan 31 Chuong 3: CC C?U TRC I?U KHI?N V KI?U D? LI?U C C?U TRC Cc c?u trc di?u khi?n:
1. C?u trc l?a ch?n:
2. C?u trc l?p:
3. L?nh Exit:
II. Ki?u d? li?u c c?u trc:
1. M?ng:
2. B?n ghi:
32. Ph?m Th? Kim Ngoan 32 I. Cc c?u trc di?u khi?n 1. C?u trc l?a ch?n:
a. IF .. THEN
C php1:
IF (di?u ki?n) THEN kh?i l?nh vi?t trn cng 1 dng
Cp2: IF (di?u ki?n) THEN
kh?i l?nh
ENDIF
33. Ph?m Th? Kim Ngoan 33 I. Cc c?u trc di?u khi?n V d?:
Cp1: IF a >= b THEN Debug.Print a
Cp2: IF a >= b THEN
Debug.Print a
ENDIF
34. Ph?m Th? Kim Ngoan 34 I. Cc c?u trc di?u khi?n b. IF .. THEN .. ELSE
C php: IF (di?u ki?n) THEN
kh?i l?nh 1
ELSE
kh?i l?nh 2
ENDIF
c. IF .. THEN .. ELSEIF
C php: IF (di?u ki?n1) THEN
kh?i l?nh 1
ELSEIF (di?u ki?n2) THEN
kh?i l?nh 2
ELSE
kh?i l?nh
ENDIF
35. Ph?m Th? Kim Ngoan 35 I. Cc c?u trc di?u khi?n V d?: IF a >= b THEN
Debug.Print a
ELSE
Debug.Print b
ENDIF
V d?: IF (a >= b) and (a >=c) THEN
Debug.Print a
ELSEIF (b >= a) and (b >=c) THEN
Debug.Print b
ELSE
Debug.Print c
ENDIF
36. Ph?m Th? Kim Ngoan 36 I. Cc c?u trc di?u khi?n d. SELECT CASE END SELECT:
C php: SELECT CASE bi?u th?c gi tr?
CASE gtr?1: kh?i l?nh 1
CASE gtr?2: kh?i l?nh 2
CASE ELSE
kh?i l?nh
END SELECT
37. Ph?m Th? Kim Ngoan 37 I. Cc c?u trc di?u khi?n V d?:
SELECT CASE n
CASE 1: Debug.Print m?t
CASE 2: Debug.Print hai
CASE 3: Debug.Print ba
CASE 4: Debug.Print b?n
CASE 5: Debug.Print nam
CASE ELSE
Debug.Print l?n hon nam
END SELECT
38. Ph?m Th? Kim Ngoan 38 I. Cc c?u trc di?u khi?n 2. C?u trc l?p:
a. FOR .. NEXT
C php:
FOR bi?n_d?m=gi tr? d?u TO gi tr? cu?i [STEP bu?c l?p]
kh?i l?nh
NEXT bi?n_d?m
V d?: Tnh t?ng 10 s? nguyn duong d?u tin
Tong = 0
FOR i =1 TO 10 [Step 1]
Tong = Tong + i
NEXT i
39. Ph?m Th? Kim Ngoan 39 I. Cc c?u trc di?u khi?n b. DO WHILE .. LOOP:
C php 1: DO WHILE (di?u ki?n)
kh?i l?nh
LOOP
C php 2: DO
kh?i l?nh
LOOP WHILE (di?u ki?n)
C php 3: WHILE (di?u ki?n)
kh?i l?nh
WEND
40. Ph?m Th? Kim Ngoan 40 I. Cc c?u trc di?u khi?n V d?: Tnh t?ng 10 s? nguyn duong d?u tin
Tong = 0: i =1
DO WHILE (i < =10)
Tong = Tong + i : i =i + 1
LOOP
Tong = 0: i =0
DO
Tong = Tong + i : i =i + 1
LOOP WHILE (i < = 10)
41. Ph?m Th? Kim Ngoan 41 I. Cc c?u trc di?u khi?n c. DO UNTIL LOOP:
C php 1: DO UNTIL (di?u ki?n)
kh?i l?nh
LOOP
C php 2: DO
kh?i l?nh
LOOP UNTIL (di?u ki?n)
3. L?nh Exit:
Exit Do Exit For
Exit Function Exit Sub
42. Ph?m Th? Kim Ngoan 42 I. Cc c?u trc di?u khi?n V d?: Tnh t?ng 10 s? nguyn duong d?u tin
Cp1:
Tong = 0: i =1
DO UNTIL (i > 10)
Tong = Tong + i : i =i + 1
LOOP
Cp2: Tong = 0: i = 0
DO
Tong = Tong + i : i =i + 1
LOOP UNTIL ( i > 10)
43. Ph?m Th? Kim Ngoan 43 II. Ki?u d? li?u c c?u trc 1. M?ng:
Khai bo:
DIM Tn_m?ng (ch? s?1, ch? s?2, ) AS ki?u d? li?u
Truy xu?t:
Tn_m?ng(ch? s?)
V d?:
Option Base 1
Dim A (10) As Integer, B(3, 4) As Integer
Truy xu?t d?n ph?n t? th? 4 c?a m?ng A: A(4)
Truy xu?t d?n ph?n t? hng 2, c?t 3 c?a m?ng B: B(2, 3)
44. Ph?m Th? Kim Ngoan 44 II. Ki?u d? li?u c c?u trc M?ng d?ng:
Khai bo: DIM Tn_m?ng() AS ki?u d? li?u
Thay d?i kch thu?c:
REDIM Tn_m?ng(ch? s?1, ch? s?2, )
V d?:
Option Base 1
Dim A () As Integer
REDIM A(5)
Xa m?ng: Erase Tn_m?ng
45. Ph?m Th? Kim Ngoan 45 II. Ki?u d? li?u c c?u trc 2. B?n ghi:
?nh nghia ki?u:
TYPE Tn_ki?u_b?n_ghi
Thnh ph?n1 AS ki?u dli?u1
Thnh ph?n2 AS ki?u dli?u2
END TYPE
Khai bo:
Public/Private/Dim Tn_bi?n AS Tn_ki?u_b?n_ghi
Truy xu?t thnh ph?n:
Tn_bi?n.Tn_thnh ph?n
46. Ph?m Th? Kim Ngoan 46 II. Ki?u d? li?u c c?u trc V d?:
TYPE Hocsinh
Hoten as String
Ngaysinh as Date
Toan as Single
ENDTYPE
Khai bo m?t bi?n HS ki?u hocsinh
Dim HS as Hosinh
Truy xu?t thnh ph?n Hoten c?a bi?n HS
HS.Hoten
47. Ph?m Th? Kim Ngoan 47 Chuong 4: TH? T?C C?A NGU?I DNG I. Tham bi?n v tham tr?
II. ? qui
III. Thi hnh Macro trong VB
IV. Kch ho?t cc ?ng d?ng khc
V. G? r?i m VB
48. Ph?m Th? Kim Ngoan 48 I. Tham bi?n v tham tr? - Truy?n theo tham tr?(ByVal): cc tham s? khng thay d?i gi tr?
- Truy?n theo tham bi?n: gi tr? cc tham s? thay d?i n?u th? t?c g?i c cu l?nh l thay d?i gi tr? c?a chng
V d? truy?n theo tham tr? v tham bi?n
49. Ph?m Th? Kim Ngoan 49 I. Tham bi?n v tham tr? Sub Thamso(ByVal a As Integer, b As Integer)
a = a + 1
b = a
End Sub
----------------------
Sub goi()
Dim a As Integer : Dim b As Integer
a = 5: b = 7
Call Thamso(a, b)
Debug.Print "a=", a
Debug.Print "b=", b
End Sub
50. Ph?m Th? Kim Ngoan 50 II. ? qui Trong th? t?c c l?i g?i d?n chnh th? t?c d
V d? th? t?c d? qui
Function Dequi(a As Byte, n As Byte)
If n = 0 Then
Dequi = 1
Else
Dequi = Dequi(a, n - 1) * a
End If
End Function
51. Ph?m Th? Kim Ngoan 51 III. Thi hnh Macro trong VB C php: Docmd.Tn_phuong_th?c [cc tham s?]
Cc phuong th?c c?a Docmd c tn v ch?c nang gi?ng nhu cc thao tc (Action) trong Macro
Sub Vedau_Click()
Docmd.GotoRecord , , ac First
EndSub
52. Ph?m Th? Kim Ngoan 52 Thi?t k? Form dang nh?p h? th?ng, yu c?u ngu?i s? d?ng ph?i nh?p vo m?t kh?u khi mu?n s? d?ng CSDL