Tutorial de Programação VBA – Access – Parte 1

Objetivos:

!
" # !

$ %
&'
( ) ' '
(
* '

Programação com o Microsoft Access - Por que utilizar?

! #
# + *

* , )
& - " . /0
# ! 1 !
,
) 2 $ 2 /3
$ # 4

3 ) 4 4 # !
$ #5% 4
# $
4 !
"
$ # )
" !
) 4 $ "
$ ) 6 78*9
# " #

Vantagens em utilizarmos o VBA:

3 ( % ! !
) ! )
: &
• ( 4
"
) 2 $ $ # #
, # # % ) $ /
) " %
) # !
" #

•
* ($ # # *
" & ) 2 "
+
" ;<9, 8 ;<9 /
 = ; = ; $ 2"

• +
4 # " ) ) #
4 " ) ) (>
(3 ? + ) 2 ) @
(> (3 ?
2 ) : (> (3 ? + ! ! 4
2 # )
@ , 2 8
2" / ) ) $
(> (3 ? A @
) @ B
< ) 4 , B
2 / ) ! !

• ! "
!
! "

• # $ " % % &
" ( + ! ) " ( *
+ # 2 " "
) $
Introdução a linguagem VBA - Visual Basic For Applications

- . 0
@ 6 = >+ ! 1
0 - !
! ?
)
#
* & - " .
C

' " ( )
() ( + " ) %
# 3 #
) : ?
) +
" ) # "
" " - .
C

* $ () +

" 4 D ! &
• % ( (
- . 0
) ! " # )
' ' < )
8 2"
# ! &' 2 ! ' ' '
$ ) E F #
< ) ) #
- . ' ! '
 $ # (
) ( + # "
! ,
;
> 2" /# ) "
" B
( >, @ ( >/ "
2 ! (> 2 "
) , 2 / )
#
• & ; #
4 # + )
2 , $
( / 4 *2 0
*2 B ! 2
4 < ! 2
)1 # # " *
$ )1 3 2" +
" $ *% " ) G
GH ) % "
# ) ! !
* $ )
) H # )
+ # GI13 1HIIJ

Conclusão:
3 ! # #
!)1 #
2" % )

; H ( "
Tutorial de Programação VBA – Access – Parte 2
Objetivos:
H 8 !
" # !

$ %
&'
( ) ' '
(
* '

; H (

Aprendendo VBA
3 G % # + 2 )
2 # + * # " #
+ 4 !

0 2 #5% # "
! "
#
4

&
* K
L3B @ M; >
# 3B @
) 3 % )
! $

8 " "
" % ! @ N,/
@ N,
/ ! @ ,
/ % ) "
) " " )

* () % , , -
• () ; # #
# # 5 # ! ; )
2 !
" # # 6
> !
O

• . " 4
* # ! # # "
! ) "
+ ! &

"P Q ,
@ ,
//
3 ( ! 4 &
# ,
/@ ,
/* ,
/ ,
/@ ,
/ *%

/ *#0 01 ! 4 !
 % " ,
/ ! Q ,
/R *% ,
/
! * $,/
• * 2 * 0 #
,
# + / ,
#
/( * ! $ #

! ! 4
4 + $ # #
S $ # #
" $ "

" @ , / )# >
+ " # !
= ; B &

@ @ @
@ @ =
@ 6

; P( @,/
; P = ,' '
/
P = (
* "' 3T* = @ = U6
;8= ;@ 8 9 @6
@ ;&'V
< * " &

/ 3 ! "
$ @ = ;
! " 2"

Conclusão:
3 #
% + 3 J % )
) " )
Tutorial de Programação VBA – Access – Parte 3
Objetivos:
J 8
! "
# !

$ %
&'
( ) ' '
(
* '

; H (

O Ambiente de Programação
"
# $ *
# !
2 3 ! )
&

; + + # "
( $ #
+ $ , &
$ /( ! 4

@ #
2 " )

D )1 ;
% ) "
, " B /
; @
# #
@

$ $ " )
3. # ) ; H # #
" (

( ) $ 8 ) # )
$ D & )
< (
( 2
2 D
0 ! 2 &
@ @ @
@ @ =
@ 6

; P( @,/
; P = ,' '/
P = (
* "' 3T* = @ = U6
;8= ;@ 8 9 @6
@ ;&'V
/ 3 ! "
$ 2"
@ = ; ! "

1" ) 8 &

/ " HIII

G *
H $.
J3 # # > )
W @% # 3
X3 '3 > ) ' % # * # 1 #
$ % # Y
&

Z; ) )
[9 ! ' ( " > ', " $ ( ) #
1 # $ /
( ," $ ( ) # 1 #
$ /
\; ) ' '
] @% # ( $
GI & "
GG % #
(# ' '
GH @% # , /#
GJ 3 4 # " ( 2
&
GW @% # Y
GX ; ) &

0 '
2 '# "
,
"/ $ ,
' ' $ ( C/
" &
; "^( C,
/

;
#
) 8 " + $ )
) ( D 1
 $ % 2
A A +
$ A &
;

2 " ,
$ ; /

/ *#0 0344/ * 38 ;@ ( * _ =* ; @6 U6
8 = (`@6 U = ;8 * ;
=6
= 0* <0 3 =a38 ; ; = > 9 = ; = = > =a3(6 ;

REFERÊNCIAS:
( $ "
) " $ "
# @ 6
66 $
" #

+ !
" "
2
" !
# ! 2
! = > =a3(6 !
= > =a3(6

( - # %
+ O6 %
2 ; ! % + +
$

" # ! % ! * ! *
0 !

! % ! &. 5# -
!
+ 9 ! '
* @ "" 9 N' ""
B # %
&
 # @
; +

@% # Y % ) % @ )
" >! % @ = ; #
! ! !
$ 2" # $ # %
= > =a3(6 ;

GZ 2 " #
@
" $ &
@ @ @
G[ # 2
4 + % @
# @ ; @ &
G\ 2 @ #
+ @ 3 @
2"

' 2% 67
8
71 "
67
8 " 69
87

B @
 " D
+ # + #
$ # D # !

G] @ 2 &
@ @ =
@ 6

; P( @,/
; P = ,' '/
P = (
* "' 3T* = @ = U6
;8= ;@ 8 9 @6
@ ;&'V
# # %
D "

@ 4
# ! "

HI "^( C,
/ @% # ; ,
# /; ) > ) &@ 8
% # Y 2" &

HG ( # '
"' $ @
HH % ) ) 8
HJ 2
HW " K
HX ) > )
HZ @% # "
H[ ; ) " &

H\ @% # Y $
H] > $ ) 8
JI> $ *

Conclusão:
3 )
@ 3 !
@ # !
Tutorial de Programação VBA – Access – Parte 4
Objetivos:
W 8
! "
# !

$ %
&'
( ) ' '
(
* '

; H (

Anatomia dos Módulos do VBA

2 $ 4 # !
2 2 * &
• *2 ,
; /
• *2 ( ,
( /

• & ; *2
4 2 !
2 ) 2 " 2 $
'
> 4 '3 2 (>
(3 ? B # #
) 2 '$ ' 4 2 '
> 4 '
' ' 4 # B !
! ) )
• & *2
) 2 2 )
2 * 2
) D 2
& () 0 2 4
4 D ) #
"
3 4 )
# 2

< 2 )
( !# 2
)

" 2
: 0* / !1; <!;=* !* () 7
b
; 4 4 D )
b + # # 2

( @
"

b
6+ G1 ; G

; ; G,
/
( G ; G
 ( H ; G

( ; G
;

b
> G1 ; G

b
6+ H1; H

; ; H,
/
( G ; H
( H ; H

( ; H
;

b
> H1; H

b
6+ 1;

; ; ,
/
( G ;
( H ;

( ;
;

b
> 1;
$ # 2 $ )
" * ) !
2 #

$ 6 )
X

Conclusão:
3 # *2 2
2 3 2 4 #
! "
@ (> !)1 ) 2
Tutorial de Programação VBA – Access – Parte 5
Objetivos:
X 8 3
)
! "
# !

$ %
&'
( ) ' '
(
* '

; H (

Tipos de dados - Declaração e “tipo” de variável:

0 ) 2 !
>! % ! ( !
# 2 " )
$ )

3 ) 2 %
% % , & 7
) ! @ " "&
@ "
@
@
3 ) # , "
/ ) 0 )
# ) # ) # #
) )
$) ; ) )
# # 3 # "
1 OO

" ) !
) 8 # ) "
2 "
" # "
" $
" 4 2
(

@ &
@ ^ ^ ) ^ ^ )
8 # ) B @
) + " "&
@ "N! ;
@ @
@ G H 6
/ *#0 01 # EF ) 8 #
! ) " ) # !
" ) # !
) " JIDIHDHIIG 8 )
# ) # # # )
# ) ! " 4 #

8 ! ) # "
@ ( ) 2 ! !
# ) $ # " !
! 4 2 &
"
4 2 #
) ! " ; !
) ) >
2"

3 9 G " ! )
"

= >? ; $ % % 7
@ 6
@ 6
PX
PH
P c
* "' ) ( &'V
" 2 ' " '
2" # !
) ) #

# $ (
# ! ) " )
6 ! ) 9 3
8

0 4 (

4 4
! ) ; $ ! ,
EE/
N
) "
3 ) )
( ) # & >
,
8 > /
N " I HXX
6 " 1JH[Z\ JH[Z[
( N " ]HJJJ[HIJZ\XWW[X\I\ ]HH JJ[HIJZ\XWW[X\I[
9 " H GW[W\JZW\ H GW[W\JZW[
S B # IG ? GII JG
@ ,
8 /
@! ]]]], $ GIIII $ /
; 8" $ ) " H $4
# # ( " ( *c
B

Conclusão:
3 # ) )
@ 8 ) %
# " ! 4
2 ! # 2 )
Tutorial de Programação VBA – Access – Parte 6
Objetivos:
Z 8 3
@ %
) 2 " % !
# # + # % ! "
3 $. # * @
3 $. % ! # # ) " (
>

$ %
&'
( ) ' '
(
* '

Fazendo cálculos e comparações com o VBA – Uso de Operadores:

! ) 4 ) ! 3

.$ *

! ) ! 3 8
! &

8 9 1

* 4@ !
c ; )
; 1 ; )
* d * )
@ D @ )
6 @ =
e
B B
"fN1g S B "
" f
% N
* * = H B

1" ) 8 3.
$ " 9 1 % ! # # ) # #
+ @ (
"

" ,
• . # ) ; H
• ) 8
• ( "
• 2 &

@ 6
@ 6
@ " @
PHX
PJ

P c
P 1
P D
P d
P e
" P f
P
b
0 * " "

* "' B &'V V ' 'V V ($ , GJ/

* "'
; &'V V ($ ,
GJ/V' ; &'V V ($ ,
GJ/
* "'
@ &'V V ($ ,GJ/
V' * &'V V ($ ,
GJ/
* "'
@ &'V V ($ ,
GJ/
V'" &'V " V ($ ,
GJ/
* "'
= &'V

• 2 2 > $ @
• ; > )
• ) > ) % # '" '
• ; ) " &

• @% # Y
• ; ) " &

• @% # Y
• ; ) " &

• @% # Y
• ; ) " &

• @% # Y
• ; ) " &

• @% # Y
• % ) ) 8
• * $ ) 6 !)1 2" "
3 " ! ! 4
+ : ) EF E F @ ! * " "
* " ) !
* " " # $ ? 10
" " ) < # "
" ) "
! A
# " B ( "
$ 2 &

* "' B &'V V ' 'V V ($ ,

GJ/

3 " " " &B* C B

) # A @
) ! ($ ,GJ/
($ , B / B %
B B ;(6
6, ; ( 6 $
6 /3 2 ;(6
6 B 3 B
GJ h 38 =g ( h 38 =g "
# # $ ( ! ($ ,GJ/
h 38 =g # E F $

( ! 4 )
" 2 2

Comparando valores com os Operadores de comparação:

4 " 4
) " 4 ( (
. 1 " 2 $ 3 !
4 2 $
, # $ 2" /
#
# " 6 8$ "
 " 6 8$ &

6 "gN8$
b( " # " # N
( G
( H

b( " # " N
( G
( H

(
6

( 6 8$ >! +
; " #5%
) " 2 3 # !
E # &gF "5D " # N

! 6 8$ " " &

6 "gN8$
i( " # " # N
( G
( H

(
6 "PN8$
i( " # " N
( G
( H

i( " # " # N
( G
( H

(
6

! 6 # )

3 8

8 1

* 4@ !
6 P S :
@ hg S
* # g S #
* # h j #
* gP S
* hP S
1" ) 8 3.
$ " ( 9 1 # % !!
# # ) # # +
( "

" ,
• ) ) 8
• ( " (
• 2 &

@ "N!C 6

"PGH
NPGX
!PHI
CPGX

6 "hN8$
* "'
" # N'

* "'
" # N'
6

6 Nh"8$
* "'Q # "'

* "'
Q # "'
6

6 NhPC8$
* "'N :C'

* "'
Q # C'
6

6 "hgN8$
* "'" N '
6

• 2 2 > $ @
• ; > )
• ) > ) % # '" '
• ; ) " &

• @% # Y
• ; ) " &

• @% # Y
• ; ) " &

• @% # Y
• ; ) " &

• @% # Y
• % ) )
#
@ # ) #
" B " 8 #
# )

0 1(

#
)
(

S "
)
" E " ,
k
"
/
0 )
)
# "
"
0 ) I,
! /
) ! ) !
0 ) " " "
) ! ! ,
''/ ) !
)
;
!
( ! #
4 2 3 [ $ E F
)

Conclusão:
3 "
8
! # # " 2 3 + ! )
)
Tutorial de Programação VBA – Access – Parte 7
Objetivos:
[ 8 3
1 ( % ()
! " #
!

$ %
&'
( ) ' '
(
* '

; H (

O Escopo das variáveis, no VBA

) # 2 ) !
) &
• 1 & 0 ) *B 2
# # , U 2 /( )
! # # 2 *2
4 2 0 ) + *2 " # *2
" ; ) @ 4 *2 0
) + *2 ) ! D 4
2

• 1 ) !
; ! )
0 ) + "
# "
*0 P 0 2 # $ # #
*2 $ "
2 + " $ :# $
$ 4 2"

" ) $
)

1" ) 8 3.
$ 1"1 9 1 # % ! # # )
# # + $ "
" )

"
• . # ) ; H
• ) ) 8
• ( 1"1
• 2 ( &

@ 6

"PGI
"PHI
( G
b !
b> G
b # ) "G
b !#
b + G

"GP c
* "' "GP 'V "G

• ( ! $ G
2 ) )
" G "
2 " (
• ) @ 4 2 ) &

@ "N "G 6

• 1 @ 4 !
3 ,
U /

• G # $
"
• ) 2 2 &

b
b )
b ) +
b

; G,
/
@
PX
P[
* "'"P 'V "V 'NP 'V NV ' P 'V V ' P 'V
;

• > $ @
• ) )
• @% # "
• ; ) " > &

• @% # Y ; ) " &

• # ) "G, / ) "G
'' ' ' ( )
G G 2
"
• @% # Y
• * $ ) 8
• ) ) 8
( " + ) , !
$ / ) 3 2"
#

Conclusão:
3 ) % #
" *2 8 "
) )

Tutorial de Programação VBA – Access – Parte 9

Objetivos:
] 8 3
!
! "
# !

$ %
&'
( ) ' '
(
* '

; H (

4 B +
! # 1 "
# $ #
B ! $
9 +
 . 7
77 "

0 ! > 3" 2 B
! 0 ! # ) B ! #
2 3 !
) >
&
> P
( G
( H

(
3"
3 + )
) (
) ! G

( " &

@ "
"PGI
; PI

i> ! GI B # !

> PG "
; P; c
3"

* "E ; P EV ;

3 " ) G :
) ; ( ) ! # G(
> 3" ) ; ) ! GI
B

+ ! G "
B + # GI&

@ "
"PGI
; PI

b> ! GI B +

> PG "; H
; P; c
3"

* "E ; P EV ;

( ; > &
 ! 7
77=

! $ 2
% ,
% ! &-$ 0
-$ 0 ! &3 +
( # 4

! F2 7
77=

3 ! -$ +
&
@ -$ D
( G
( H

(
9
, % , &
" < 1 2 )
" 3 ) ! ) "
$ !

/ *#0 01& (2 ! >

# ) ) )
"

( " &

@ "
"PGI
( PG
; PI

b> ! GI B # !

@ -$ ( hP "
; P; c(
( P( cG
9

* "E ; P EV ;

# 6 ) (
( # ( GG +
1

! 7
77= F2
3 (
2 ) " !
) " #
&
@
( G
( H

(
9 -$ D
3 # 2 "
< 1 2 ) "
3 ) ! ) " B !

/ *#0 01 (2 ! >
# ) ) )
"

( " &

@ "
"PGI
( PG
; PI

i> ! GI B # !

@
; P; c(
( P( c G9
-$ ( hP "

* "E ; P EV ;

" ( " &

@ "
"PGI
( PGG
; PI

i ) " B !
i( h"

@
; P; c(
( P( cG
9 -$ ( hP "

* "E ; P EV ;

G % " % % 4 +

* ( h" "PGI ( PGH ,

9 #
( ! /3
) " B ! )
< " GG ) ; (
" ) ; )GG
 ! ; 7
77=

3 ! 0 +
&
@ 0
( G
( H

(
9
, , & " 7
< 1 2 )
" 3 ) ! )
" $ !

/ *#0 01 (2 !
# ) ) ) >
"

( " &

@ "
"PGI
( PG
; PI

i> ! GI B # !

@ 0 ( g"
; P; c(
( P( cG
9

* "E ; P EV ;

# 6 ) (
( # ( GG +
1

! 7
77= ;

3 (
2 ) " !
) " # >
&
@
( G
( H

(
9 0
3 # > 2 " <
1 2 ) " 3
) ! ) " B !

/ *#0 01 (2 !
# ) ) ) >
"

( " &

@ "
"PGI
( PG
; PI

i> ! GI B # !

@
; P; c(
( P( cG
9 0 ( g"

* "E ; P EV ;

" ( " &

@ "
"PGI
( PGG
; PI

i ) " B !
i( gP "

@
; P; c(
( P( cG
9 0 ( gP "

* "E ; P EV ;

G % " % % 4 +

* ( gP " "PGI ( PGH ,

9 #
( $ GG /3
) " B ! )
< " GG ) ; (
" ) ; )GG

Conclusão:
3 2 %
> 3" @ -$ @ 0 4
Tutorial de Programação VBA – Access – Parte 11
Objetivos:
GG 8 3
4 !
! "
# !

$ %
&'
( ) ' '
(
* '

; H (

Funções para conversão de tipos

3 ! 4 "
4 # "
B ! " %1
# ! ) 2"
" ) @ (> (> "
" + (> " + "
! %1 B #
) )

3 #5% 4 #
2 " !

( ) "
< # B " !
! > 8"
) ) "

$ 2 &
* " ,
GIgHX/V $ ,
GJ/V ,
J/

$ > &

" GIgHX " ( > ?

)
( A J < #
! , " " / #
>

. )D
 (N ) " N
" ) N # I:HXX (
B " ) "
8" ) )
"

$ 2 &
* "( N ,
GIcJW/V $ ,
GJ/V ( N ,
JX/

$ > &

" GIcJW ,
WW/ N $
2 " ) "
N ) " I:HXX
* "( N ,GIIcHII/
.

(( ) " ( N
8" ) )

$ 2 &
* "(( ,
HXIJJXZ[[cJHWJJJJ/V $ ,
GJ/V (( ,
GHXIJHZX\H/

$ > &

" HXIJJXZ[[cJHWJJJJ ( N

; " (( ) "
> &
.

(@ ) " @
# ) )
) "

$ 2 &
* "(@ ,
'JGDGIDG]\I'
/V $ ,
GJ/V (@ ,
'HJ1IG1HII['
/

$ > &

$ 2 " ) "
2" &
* "(@ ,'
JGDIHDG]\I'/
. !

(@ ) " @
" ) )
"

$ 2 &
* "(@ ,
'W]WIZXGHW[ 1G['
/

$ > &

. /

(6 ) " 6
" ) )
"

$ 2 &
* "(6 ,
JH JZ/V ($ ,
GJ/V (6 ,
HIJXcWIWX/

$ > &
. =

(9 ) " 9
" ) )
" "
9 ) ) "

$ 2 &
* "(9 ,
'JWZHJGX'/

$ > &

. 4

(; ) " ;
" ) )
" "
; ) ) "

$ 2 &
* "(; ,
'JGWG\HXZ]H['/

$ > &

. 4

(; ) " ;

$ 2 &
* "(; ,
'GHJWX'V '$ '
/

$ > &

. / ."
 4 6 >" B
4 # B " "&
>",
1GIH/1g = 1GI

6 ,
1GIH/1g = 1GG
6 B B
A 3 1GIH 1GG ?
)>" B
A 3 1GIH 1
GI

$ 2 &
* ">",
1[JX/V ($ ,
GJ/V * "6 ,
1[JX/
$ > ; &

( 4
3 2" 4
4 ; @ D7

Conclusão:
3 4 4
! #

Tutorial de Programação VBA – Access – Parte 12

Objetivos:
GH 8 3
4 $ 8" ,
; /
!
" # !

$ %
&'
( ) ' '
(
* '

; H (

Funções para tratamento de String

3 4 ; #

Função Asc:
2 ;(6
6 ;
( " &
 ,
E $ F
/
" ZX # 2 ;(66
B ( '' B 2 ][

Função Chr:
($ , B / B A ;(6
6
B A

( " &
($ ,
ZX/
" '' B

*0 ! ($ " 0 ! ($ ,
GJ/
38 = # ! # $ * "
Função Len:
$ ; # A
( " &
* "9 ,
E " 9 K
KF/
" WG > &

Função LCase:
B ; # A
( " &
* "9 ,
E ;8 S0* k * 9 @ 0; @ >03_l 9( ; K
KF/
" A B
> &

Função UCase:
* 6
T;(09 ; ; # A
( " &
* "9 ,
E " K
KF/
" A B
> &
Função Left:
B + ,
9 # /
; S #

&

= 64 , 8

( " &
* "9 ,
E?B ( > FX/
" ;
)HC

" &
* "9 ,
E?B ( > F[/
6) &HC

Função Right:
B ,
= $ @ /
; S #

&

# 2 64 , 8

( " &
* "= $,
E?B ( > FZ/
" ;
)E F

" &
* "= $,
E?B ( > FGI/
6) & )

Função Mid:
 B
; S #

&

64 , I , 8

&

• I #

• B

( " &
* ,
E?B ( > F[X/
" [X
)
* ,
E; 38 * =6 FJ[/
) 0 # #

0 $ # !
A ( " &
9( ,
* ,
E; 38 * =6 FJ[/
/
"

* 38 * = # A
9( # B

Função String:
B !

&

4 6, 8

B ! # (

( " &
* "; ,
JXF
dF/
> &

Conclusão:
3 4 $
8" 4 ! "
" # "