End Sub
[ ] Public, () , Private, () .
Sub VBA , _, , ( ).
_ , : _ As _, As VBA , . [ ( ) ] .
: , ; .
Const.
:
Const _ As =
:
'
' Pi
Const Pi As Single = 3.1415926
Const () , VBA, ; _ , VBA; As () , VBA, ; _ (. 2, .1.6, . 4). VBA As _. _ , <b> <Enter>.
: , .
VBA , , .
:
Dim [Static] _ [ As ]
Dim () , VBA, . _ , VBA. As () , VBA, . _ (. 2, .1.6, . 4). Static () VBA, , , Static .
() VBA.
|
|
|
End Sub , . Sub End Sub.
: [ ( ) ] , . [ ( ) ] .
( ):
1) . , . . () , , . . , .
2) , (General Declarations), Dim Private. .
3) , . , , . , Dim Public.
:
[Public|Private] Const _ As =
Option Explicit, .
Option Explicit General . Option Explicit Dim, Private, Public, ReDim Static.
(_)
(event) (: , , Excel ..) _ (), _.
_:
Private Sub _ ()
()
End Sub
_ Sub. _ , , _ VBA.
1( ). , a,b h. . (.2)
1.
: a=6, b=5 h=3.
: S-? P-?
S = ah;
P = 2(a + b).
(!) -.
2. Excel
1 a, B1 b, C1 h.
|
|
|
3.
: a, b, h ( Single)
(): S, P ( Single).
4.
1 (ommandBatton1).
: , () .
( ( )). Alpabetic, Caption, 1.
. . . VBA -:
:
Private Sub CommandBatton1_Click()
Dim a, b, h As Single
Dim S, P As Single
a = Range(A1).Value
b = Range(B1).Value
h = Range(C1).Value
S = a * h
P = 2 * (a + b)
B4 B5
Range(B4).Value = = & S
Range(B5).Value = = & P
End Sub
5.
(ViewMicrosoft Excel Alt+F11). , () . 4 = 18, 5 Þ = 22.
6. 1, () , , _, Enter.
2. αº ( ) .
3. . 
. : 
, 
.
4. ( ) 
. : 
.
5. ( ) 
.
: 
.
VBA Arccos(X) = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
2 If... Then
: 1 . VBA.
. If... Then
VBA If... Then .
If... Then , - , . , .
:
If < > Then < _1 > Else < _2 >
= True _1, Then, . False, Else _2. Else , , = True, _1, . = False, .
:
If <_1> Then
<_1, =True>
[ ElseIf <_2> Then
<_2, _2=True>]
[ ElseIf <_3> Then
<_3, _3=False>]
[ Else
<>]
End If
<_1> , , . True, _1 Then. , ElseIf . _2= True, _2. , ElseIf , Else.
|
|
|
If . , - , True Else.
6 ( ) a,b c. .
1.
: a, b, c.
: .
(!) -.
2. Excel
1 a, B1 b, C1 c.
3.
: a, b, c.
(): max.
4.
1 (ommandBatton1).
( ( )). Alpabetic, Caption, .
. . . VBA -:
:
Private Sub CommandButton1_Click()
Dim a, b, c As Single
Dim max As Single
a = Range("A1").Value
b = Range("B1").Value
c = Range("C1").Value
If a > b And a > c Then
max = a
Else
If b > c Then
max = b
Else
max = c
End If
End If
B4
Range("B4").Value = " = " & max
End Sub
5. .
(ViewMicrosoft Excel Alt+F11). , () . 4 = 10.
6. . 1, () , , _, Enter.
7. x Î [a,b], y=[x]. .
8. y x. 
.
9. a, b, c , x, y, z, 
3
: VBA .
. . . , .
( ) For, For Next. For Next :
|
|
|
For = To [Step ]
[Exit For]
[]
Next [],
For VBA, ; , , Next , ;
To VBA, ; Step VBA, , ; , , ( ) , ; Exit For (), IfThen SelectCase; Next VBA, .
Step , .
10 ( ). . 
, 
1.
: a n .
: Sum, Pr.
(!) -.
2. Excel
1 a, B1 n, C1 h .
3.
: a, n, h ( Single).
: ( ) i ( Integer),(bi) b ( Single).
(): Sum, Pr ( Single).
4.
1 (ommandBatton1).
( ( )). Alpabetic, Caption, .
. . . VBA -:
:
Private Sub CommandButton1_Click()
Dim a, n, h, i As Integer
Dim Sum, Pr, b As Single
'
a = Range("A1").Value
n = Range("B1").Value
h = Range("C1").Value
'
Sum = 0
Pr = 1
For i = a To n Step h
b = Sin(2 * i + 0.4)
Sum = Sum + b
Pr = Pr * b
Next i
B4 B5
Range("B4").Value = " = " & Sum
Range("B5").Value = " = " & Pr
End Sub
5.
(ViewMicrosoft Excel Alt+F11). , () . 4 = 10.
6. 1, () , , _, Enter.
4
: VBA .
, , , , . , True () False (). VBA Do While... Loop Do Until... Loop. .
Do While | Until :
:
Do While | Until < >
[Exit Do]
Loop
:
Do
[Exit Do]
Loop While | Until < >,
Do VBA, ; While|Until VBA, While -> , < > True , < > False. Until -> , < >= False , < >= True; <> , True False; Loop VBA, .
|
|
|
, <>, , <>.
, . , - . , . , <Ctrl+Break>.
Sub, , .
, ( ), . .
, . Call, . . ( Call ).
5
: VBA .
11 ( ). e n , 2n/n!< e. 1- n -.
1.
: Eps . : 2n/n!< e.
: n , u .
,
u = 1 '
n = 0 '
Range("C1:E20").Clear
Do Until (u < Eps) Or (n >= Limit)
n = n + 1
q = 1
For i = 1 To n
q = q * i
Next i
u = 2 ^ n / q '
Cells(n, 4).Value = n
Cells(n, 5).Value = u
Loop
.
,
u1=2\1/1!,
u2=2\2/2!=2*2/(1*2)= u1*21/2,
u3=2\3/3!=22*2/(1*2*3)= 2*2/3 .,
.. 2/n, .
, , Limit = 100.
(!) -.
2. Excel
1 Eps =, B1 Eps.
3.
Limit = 100 ( Integer)
: Eps ( Single).
: ( ) u1 ( Single) .
(): u ( Single) , n ( Integ) .
4.
1 (ommandBatton1).
( ( )). Alpabetic, Caption, 2n/n < Eps.
. . . VBA -:
Private Sub CommandButton1_Click()
_
End Sub
() VBA: Insert()\Procedure()
.3 Add Procedure ( ) Sub() Name() _ OK. () ( ), ().
:
Public Sub _()
'
Const Limit As Integer = 100
'
Dim Eps As Single
Dim u As Single
Dim u1 As Single
Dim n As Integer
'
Eps = Range("b1").Value
'
u = 1 '
n = 0 '
Range("C1:E20").Clear
'
Do Until (u < Eps) Or (n >= Limit)
Cells(n, 4).Value = n
u1 = u
u = u1 * 2 / n '
n = n + 1
Cells(n - 1, 5).Value = u
Loop
'
Range("A6:A7").Clear
If n >= Limit Then
Range("A7").Value = n & " ."
End If
End Sub
5.
(ViewMicrosoft Excel Alt+F11). , () . D1:E6 .
6.
1, () , , Eps2, Enter.
12. . x1, x2, . ; : 
.
13. a p 
:
; 
.
e, : 
?
14. : 
, , e. . , , 0, 1, .. 20=1, 0!=1.
15. n x : 
.
6
: String.
| String ( ) | () | 1 | 1 65400 |
| String ( ) | () | 10 + 1 | 0 |
| Val() | |
| CStr() | |
| CInt() | Integer |
| Cvar() | Variant . |
| Len($) | $ |
| Asc( ) | ASCII |
| Chr(ascii) | , ASCII |
| Mid($, & [, &]) | $ & $ ($ - ) |
| Left($, &) | $ & |
| Right($, &) | $ & . |
.
Mid( , 6, 3)
.
. Chr(13) Chr(10). 13 10 ASCII , , , (, ).
16 ( ). , . MS Excel, ASCII .
1.
: . . . . .
(!) -.
2. Excel
1 . . . ....
3.
: St ( String).
: i ( Integer) , SS ( String) , x1( Integer) ASCII ".", L ( Integer) , SSS ( String) , , x1( Integer) ASCII SSS.
(): n ( Integer) .
4.
1 (ommandBatton1).
Private Sub CommandButton1_Click()
Dim St As String
Dim SSS As String
Dim SS As String
Dim L As Integer
Dim x As Integer
Dim x1 As Integer
Dim i As Integer
Dim n As Integer
'
St = Range("A1").Text
SS = "."
' ASCII "."
x1 = Asc(SS)
'
L = Len(St)
'
n = 0
For i = 1 To L
SSS = Right(St, i)
x = Asc(SSS)
If x = x1 Then
n = n + 1
End If
Next i
' A2
SSS = " " + CStr(n)
Range("A2").Value = SSSEnd Sub
5.
(ViewMicrosoft Excel Alt+F11). , () . 4 7.
6. .
1, () , , , Enter.
17. , 9 . MS Excel. 9 .
18. , . . MS Excel.
:
1.
2. -
3. : , ,
4. -
5. VBA
6. .
1
1.1.
.
, , . - (, , , ).
1.2.
1. .
2. .
3. ( "" ).
4. - .
5. - .
6. - .
7. ( , ..)
1.3.
I. ( ) , . / .
II. ( ) , . , .
III. . .
IV. ( ) , .
1.4.
I. .
II. 
( ), . . , , .
III. - ( flow-chart). - . . 4.
IV. .
1.5.
, , , , ( ) , . , . "", , , , :
Ø ;
Ø , , ;
Ø ;
Ø ;
Ø ( );
Ø ( );
Ø , , , ;
Ø ;
Ø .
. , , , , , , . , , (, , -). , , . , , , , (, ). . , !
, , . - , (). , , , . , , , .
. .
. "" . , . - , .
- . , , , .
. , . - , "", . , , . , , - . , , ( ) , , . , ! , , , , . , ( ), .
, . , , , , , , . . - . . - . , , , .
- , . , . , ftp- .
, , ( , ), . , .
2
1. Visual Basic for Applications (VBA)
1.1. VBA
, VBA . :
1. (A-Z, a-z);
2. (-, -)[*];
3. 0 9;
4. _.
5. ( ), (, , );
6. , : + * / \ ^ = > < [ ] ().,:; { } & @;
7. , : <=, >=, <>. .
, , () Rem. , :
Rem
:
1. .
2. ., !, @, &, $, #, .
3. , .
4. 255 , , VBA 31 .
5. VBA.
6. , .
. . :
=
; = ; , , , - .
, , .
. . , , .
1.
Result = 10\3 3 ( );
Result = 10 Mod 3 1 ( );
Result = 3^2 9;
Result = -3*2 -6.
1.4.
True () False (), Boolean . VBA .1.
, True () False (). .1.
1.
| = | a = 10, b = 5 | a = b | False (), .. 10 5 |
| <> | a = 10, b = 5 | a < > b | True (), .. a = 10, b = 5 Þ 10 <> 5, Þ b |
| < | a = 10, b = 5 | a < b | False (), .. 10 5 |
| > | a = 10, b = 5 | a > b | True (), .. 10 5 |
| => | a = 10, b = 10 | a => b | True (), .. 10 10 |
| <= | a = 10, b = 5 | a <= b | False (), .. 10 5 |
| Text1.Text = | True (), , False () | ||
| k > = 15 | True (), k 15, False () |
1.5.
. , . VBA .2, ( ) .3.
2.
| And | True, True, False ( ) |
| Or | False, False; True ( ) |
| Not | True, False. False, True ( ). |
| Xor | True, True. True False, False. |
3.
| a | b | a And b | Not(a And b) | a Or b | a Xor b |
| True | True | True | False | True | False |
| True | False | False | True | True | True |
| False | True | False | True | True | True |
| False | False | False | True | False | False |
1.6.
(.4) , , .
4. VBA
| Integer | 2 | -32768 32767 | Dim I as Integer | |
| Byte | 1 | 0 255 | Dim K as Byte | |
| Long | 4 | -2147483648 2147483647 | Dim J as Long | |
| Single | 4 | -3.402E38 3.402E38 | Dim F as Single | |
| Double | 8 | -1.79E308 1.79E308 | Dim Summa as Double | |
| Boolean | 2 | False True | Dim Yes as Boolean | |
| String | 10 | + 2 | Dim Stroka as String | |
| Date | 8 | 1 100. 31 9999. | Dim BirthDay as Date | |
| Variant | 16 | Dim Chislo as Variant |
1.7.
5. VBA
| Abs(x) | x |
| Cos(x) | x, |
| Sin(x) | x, |
| Sqr(x) | x |
| Rnd[(x)] | 0 1. , , |
| Int(x) | x |
| Tan(x) | x |
| Atn(x) | x |
| Exp(x) | e x, e |
| Log(x) | x |
, , , , , . , , . , . , , , , . , , , . , . .
, , . . : , , .
, , , .
. , .
I. , .
, , , , , , . . , . , .
II. .
- , . , , , . . , , , , , , .
III.
