1 / 84

Phn 2: VISUAL BASIC FOR APPLICATION VBA

Ph?m Th? Kim Ngoan. 2. Chuong 1: T?NG QUAN V? VBA. I. Gi?i thi?u chungII. ModuleIII. C?u tr

elewa
Download Presentation

Phn 2: VISUAL BASIC FOR APPLICATION VBA

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    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

More Related