VBA MS OFFICE
VBA . , , VBA MS Office. , VBA MS Office. : Excel, Word, Access . , . , Access, Excel, Word.
VBA Visual Basic (VB). . , VB , VBA , MS Office VBA. , VB , VBA , VBA.
VBA
, ,
, .
VBA :
- ;
- ;
- _;
- :
+
-
*
/
=
>
<
[ ]
()
{}
.
,
:
;
´
#
&
$
^
\
%
!
?
( )
:
<>
<=
>=
..
: =
.
, , .
, .
:
- ;
- .
, , . VBA. , , , , , , .
, , , , , .
:
|
|
|
Ø 255 ;
Ø , , ;
Ø _, , , %,!, $, &, @ ;
Ø , ;
Ø VBA .
:
: X15, F_I_O, NomerTel, A%, B@;
: 15x, ..., Tel, A%_1, B@@.
, , , , .
, PlanWupyska, Plan_wupyska, Planwupyska. R=F/P , :
Procent = Fakt / Plan
VBA : . , Name name Sin sin VBA . , , .
. . (, , , ), , .
:1) , , 2) , , 3) .
VBA , , , , Variant, . .1.
Variant, , . : . , .
| 1 | |||||
| , | |||||
| Byte | 0 255 | ||||
| Boolean | True False | ||||
| Integer | -32 768 32 767 | ||||
| Long | -2 147 473 648 2 147 483 647 | ||||
| Single | -3,40282338 - 1,401298-45 1,401298-45 3,40282338 | ||||
| Currency | -922337230685477,5808 922337203685477,5807 | ||||
| Date | 1 100 31 9999 | ||||
| Variant | 16 + N ( ) | ||||
|
|
|
, .
Dim.
:
Dim [As ] [, [As ]
:
. , .
. . As . Variant.
,
Dim A As Integer
Dim B As Single
Dim C As Boolean
, .
Dim:
Dim A As Integer, B As Single, C As Boolean
VBA :
, .
. , .
, .. . 0, Boolean False, ( ).
, . - , , .
, Dim, .
, 1) Dim 2) . Variant, . , , -, , .. , , . . -, - .
, Prim : K=K+10 K=K1+10. .
Variant , .
,
Dim A, , As Integer
Dim D As Variant
A, D Variant. D , A . , :
Dim A As Integer, As Integer, As Integer
, VBA . , , .
, Option Explicit:
Option Explicit
Sub Prim()
Dim K As Byte
K=K+10
...
End Sub
, .
|
|
|
VBA :
- . , . (3.14; 16; 4.2+02), (Ԕ; .. ) (True, False).
- , .. .
() . . MS Excel . , vbOKOnly, fmAltMask ..
Const. . .
Const:
Const [As ] =
:
. , .
. . As . .
. (, ), , .
,
Const Year As Integer=2003
Const Plan=129
: Year Plan. , .
Const NameGrup = - 21
NameGrup, 21.
Flag, True (), :
Const Flag = True
VBA
VBA . . , + , . VBA .
VBA :
- ;
- ;
- ();
- .
.
.
| 2 | ||||||
| + | A+B | y=4+2 | 6 | |||
| _ | A-B | y=4-2 | 2 | |||
| -A | y=-x | x=4 y=-4 x=-4 y=4 | ||||
| * | A*B | y=4*2 | 8 | |||
| / | A/b | y=10/4 | 2.5 | |||
| ^ | A^B | y=2^2 y=3^3^3 y=(-5)^3 | 4 19683 -125 | |||
| \ | A\B | y=11\4 y=9\3 y=100\3 | 2 3 33 | |||
| Mod | A Mod B | ( ) | y=10 Mod 5 y=12 Mod 3 y=12 Mod 4.3 y=12.6 Mod 5 | 0 1 0 3 |
. , .
|
|
|
VBA . + &.
+ , &, .
| 3 | ||
| + | y= 46+ 8 y= 46+ 8 y= +1+ | 468 - 54 - 1 |
| & | y= 46& 8 y= 46 & 8 y= & 1& | 468 - 468 - 1 |
| 4 | |||
| < | 2<5 2<2 | True False | |
| <= | 2<=2 2<=0 | True False | |
| > | 5>2 2>5 | True False | |
| >= | 2>=2 5>=2 | True False | |
| = | 2=2 2=5 | True False | |
| <> | 2<>5 2<>2 | True False |
(.4) . . True (), False (). :
Ø a <= N
Ø x <> (2*z b)/3
Ø i +1 > j -5
(. 5) True() False(). . :
Ø i <= N and W
Ø (z + 1) <> (x >3) and (y < 5)
Ø a > 0 or (b < 0 and c = 0) or g <> a + b
| 5 | |||||
| And | ( ) | A And B | True True False False | True False True False | True False False False |
| Or | ( ) | A Or B | True True False False | True False True False | True True True False |
| Not | Not A | True False | False True | ||
| Xor | A Xor B | True True False False | True False True False | False True True False |
VBA
VBA , . : , , , , , .
(.6).
| 6 | ||||
| , | ||||
| Atn (x) | pi=4*Atn(1) | pi | ||
| Cos (x) | M=1/cos(ugol) | |||
| Sin (x) | M=1/sin(ugol) | |||
| Tan (x) | M=1/tan(ugol) | |||
| Exp(x) | x | M=Exp(A) | A | |
| Log (x) | M=Log(A) | Ln(A),A>0 | ||
| Sqr (x) | M=Sqr(A) |  ,A>=0
| ||
| Abs (x) | M=Abs(A) | |A| |
(, , ), ().
, . , (A + Sin(x) - 6) - A, Sin(x) 6 ; + - () .
.
:
Ø 123 ( )
Ø Name ( )
.
, , , .
, .
.
|
|
|
:
Ø Sin(x) + 3
Ø (2*x + b^5)/2
Ø (A + 1)\(x^2 + b) Mod (A + B)
Ø I Mod 2
Ø (Cos(x + 1) ^3) ^2
1.1. y=sin (
-1)
:
y = Sin(Sqr(x + 2) -1)
1.2. 
:
y= (2*x + a) / (b-1) + Abs(x + a) ^ (1/3)
1.3. (x+1), .. y=lg(x+1)
.. VBA , : Lg(x) =ln(x)/ln (10). :
y = log(x+1) / log (10)
, .
, , (), , .
.
:
Ø 1&
Ø Fam & Im & Otch
Ø Kurs + Gruppa
Ø - & Procent
Ø , & Z & & K
, . .
:
Ø A>=B
Ø (x+1)<y
Ø (A(i)>x) And (A(i)<y)
Ø Name1=Name
Ø Sin(x+1)>(x+2)/3
.
, , , , - .
:
Ø x=2
Ø x+2>=3/(y+1)
Ø i<=N-i+1
Ø j<=M
Ø x>Z
.
And () Or ().
, z [x;y), , : z>=x z <y, - And ():
(z>=x) And (z<y) z>=x And z<y
z , True, False.
z [x; y),
(z<x) Or (z>=y) z<x Or z>=y
z , True, False.
:
Ø (i<=N) and (W=1)
Ø ((a>0) or (b<0)) and (c<>0)
Ø x>0 And (y>0 or z<0)
Ø (i>N) Or (i<M)
Ø c>3 or c>=10
| 7 | |
| () | |
| ^ ( ) | |
| - ( ) | |
| *, / (, ) | |
| \ ( ) | |
| Mod ( ) | |
| +, - (, ) | |
| &, + ( ) | |
| =, <>, <, >, <=, >= ( ) | |
| Not ( ) | |
| And ( ) | |
| Or () | |
| Xor () |
( ), . . .7.
, , . , . , , .
.
:
<> = <>
:
. , , .
. , .
, , , . , ,
x=3
y=2+x-x^2
y , - 4.
. .. , . : .
, . .
: Byte, Integer, Long, Single, Currency.
,
Dim x As Integer, y As Single, s As String
x, y s ,
x=3
y=5.1
s=
.
1. x
x=x+1
x=y
x 4, 5. 5.1 .
x=s
. .
2. y
y=y+1
y=x+1
y 6.1, 4. , .
, ,
y=s
.
3. s
s=y
s=x
, , .
VBA .
. : ( Excel), VBA ( VBA) , ( ).
. - , , . , , , .
VBA :
1. . , , .
2. . , . =.
3. . - . , , , ..
VBA . . - Sub. .
Sub :
Sub <>([< >])
< >
End Sub
:
Sub
. .
. ( ).
. .
. , , , .
End Sub
. .
, Demo() 156 :
Sub Demo()
C = 156
End Sub
.
VBA () - . :
1. , .. .
2. .
, .. , . :
- , . . , , . .
- , .
(´) . . , , .
:
Sub Lab1 ()
´
´ 2.2
´ . -11
´ ..
Dim S As Integer ´S
Dim i As Byte ´i
End Sub
3. .
. , : (_).
,
y = 2 * Sqr(x+3) - (log(x^2) +2) _
/ (sin(x-1))
:
- . , , ().
,
y= & _
- .
- .
- 1024 .
4. .
(:) () . , , . ,
x=x+a
y=x-b
x=x+a: y=x-b
5. .
, (, , , , , ..). VBA . , .
. , , . (, , ) ..
2.
Windows , .
VBA : .
(InputBox) , (MsgBox) .
, VBA, MS Excel, ( ) .
2.1. . MsgBox
(, , ).
.
MsgBox,
- , (. .1-5). OK . .
- .
MsgBox:
MsgBox [, ] [, ]
:
. , . 1024 .
. ,
Ø ;
Ø ;
Ø .
0. , , , (.8. 9). . :
vbOKCancel + vbExclamation
-
. , . , (.1).
, , .
| 8 | ||
| Buttons MsgBox, | ||
| vbOKOnly | OK | |
| vbOKCancel | OK, | |
| vbAbortReplyIgnore | , , | |
| vbYesNoCancel | , , | |
| vbYesNo | , | |
| vbReplyCancel | , |
| 9 | ||
| Buttons MsgBox, | ||
| vbCritical | 
| |
| vbQuestion | 
| |
| vbExclamation | 
| |
| vbInformation | 
|
. , , ().
2.1. .
Pr_1:
Sub Pr2_1()
'
MsgBox "!!!"
End Sub
, (.2.1.). MsgBox , OK, , Microsoft Excel.
(, , 2), MsgBox:
MsgBox !!!,,
. .2.2 ) !!! Name. (). .
Sub Pr2_1_()
'
'
Dim Name As String
Name = ""
MsgBox "!!!" & Name,, ""
End Sub
hr(). , . , Chr(9) Tab, Chr(13) Enter.
, , VBA-, VBA , Chr():
Ø vbCr , Chr(13);
Ø vbTab , hr(9). .
.2.2 ) Pr2_1_().
Sub Pr2_1_()
'
'
Dim Name As String
Name = ""
MsgBox "!!! " & Chr(13) & Name,, ""
End Sub
2.2. .
Pr2_2 , .2.3.
Sub Pr2_2()
'
'
MsgBox " -" & _
vbCr & " ", vbExclamation, _
""
End Sub
() vbExclamation (. .9).
2.3. (), 
( ).
(.2.4)
Sub Pr2_3()
'
'
' .
'
MsgBox " -" & _
Chr(13) & " ", vbOKCancel + _
vbQuestion + vbDefaultButton1, ""
End Sub
2.2. . InputBox()
(, ) .
InputBox(),
Ø , , : Cancel (. .2.5);
Ø ;
Ø String , , ;
Ø ( Empty) Cancel.
:
InputBox ( [, ] [, ]
:
. , . . 1024 .
-
. , . , .
. , . , . , .
, , .
,
x = InputBox(" ", " 3.5")
(.2.5) . x.
(,). , .2.6.
2.3. / / Excel
/ / MS Excel Range ( ) Cells () Worksheet ( ). Excel.
Cells, , .
,
Ø x=Cells(5,3).Value
x=Cells(5,3)
x = Range("C5")
x Value 5.
Ø x = Cells(1+i, 1+j)
x , 1+i 1+j .
Ø Cells(8,4)=x+2*y
D8 x+2y.
Ø p = InputBox(" ")
q = InputBox(" ")
Cells(p,q) = a + Range("A1")
, p q a + Range("A1"). .
2.4. 
. / / .
.2.7 ). Pr2_4() (.2.7 )).
Sub Pr2_4 ()
Dim a As Byte, b As Byte, x As Integer, y As Single
a = Cells (2, 3): b = Cells (3, 3): x = Cells (4, 3)
y = (x + 3) ^ 2 + (2 * a - 3 * b) / (x ^ 2 - 2.8)
Cells(6, 1) = " :"
Cells (7, 3) = y
End Sub
