1
VBA
. VBA.
VBA Excel.
VBA :
- Excel Visual Basic;
- Insert Module. , .., , , .
Option Explicit. , , , Dim ( . 1.3).
, VBA, Option Explicit . , Dim, Option Explicit .
Run Run Sub/UserForm.
VBA.
1.1. , a b.
Sub primer1_1()
VBA
a = InputBox( : )
b = InputBox( : )
x = a^b
MsgBox( & x)
End Sub
Sub ; primer1_1. VBA ( ).
( ) . .
InputBox . a = InputBox ( : ) , a; :. x = a^b : ( a b), , ( x). MsgBox( & x) , x.
. & MsgBox , , x. & InputBox ( ). & .
VBA. . , 1.1 , , :
Sub primer1_1()
VBA
a = InputBox( : ): b = InputBox( : )
|
|
x = a^b: MsgBox( & x)
End Sub
, . Sub. VBA -. , VBA . - Function. 1.9.
. .
.
, , , . , VBA, 1.1.
1.1. VBA
Byte () | 0 255 ( ) |
Boolean () | True False |
Integer () | -32768 32767 ( ) |
Long( ) | -2147483648 2147483647 ( ) |
Single( , ) | -3,402823×1038 3,402823×1038 |
Double( , ) | -1,79769313486231×10308 1,79769313486232×10308 |
Date () | 01.01.100 31.12.9999 |
String () | , |
. . VBA, .
.
. , , - (Sub Function). VBA Dim.
, , , a ( , ), b c ( , ), d ( ), x - , y (.. True False). :
Dim a As Single, b As Integer, c As Integer, d As Byte, x As String, y As Boolean
, , d , 255, - .
Dim, Variant. , . , z Dim, z= (.. ), - z=5 (.. ). Variant , . , , Dim .
Dim (. 1.4), , .
|
|
Dim , , .. InputBox. , , z Dim, (, 7), , ( 7). , , . 1.6.
, Dim . , , , b, c As Integer : Integer c, b , Variant.
.
, (Sub Function) (Dim). Const.
, , , 250, , 1128. :
Const cena = 250, ves = 1128
. cena = cena + 10 cena = 220 .
.
, . , .. , , :
- , , . Dim ;
- , . Dim Private , .. Sub Function;
- , (.. , ). Public .
1.2. :
Private x As Integer
Sub primer1_2a()
Dim y As Integer
x = 5
y = 10
Call primer1_2b
End Sub
Sub primer1_2b()
MsgBox ("x=" & x)
MsgBox ("y=" & y)
End Sub
x , ; y , , , .. primer1_2a.
primer1_2a. Run Run Sub/UserForm. x 5, y 10. primer1_2b. Call ( 1.8). x y. x , , ( primer1_2a), ( primer1_2b). x=5. y , , primer1_2a, primer1_2b. y=, y primer1_2b (, , ).
If.
If :
If 1 Then
, , 1
Elseif 2 Then
, , 1 , 2
|
|
Else
, , 1, 2
End If
Elseif Else , If :
If Then
, ,
Else
, ,
End If
:
If Then
, ,
End If
If , End If .
1.3. 2, 5, 3, 5.
Sub primer1_3()
x = InputBox( : )
If x < 5 Then x=x*2 Else x=x*3
MsgBox( & x)
End Sub
If , End If .
If.
1.4. .
Sub primer1_4()
Dim a As Single, b As Single, c As Single
a = InputBox (" a", " ")
b = InputBox (" b", " ")
c = InputBox (" c", " ")
d = b ^ 2 - 4 * a * c
If d > 0 Then
x1 = -b - Sqr(d) / (2 * a): x2 = -b + Sqr(d) / (2 * a)
MsgBox ("x1=" & x1)
MsgBox ("x2=" & x2)
Elseif d = 0 Then
x = -b / (2 * a)
MsgBox ("x=" & x)
Else
MsgBox (" ")
End If
End Sub
a = InputBox (" a", " ") , , (.. a). " a: " . " " , .
.
VBA, , . Dim. ,
Dim a(1 To 6) As Integer, b(1 To 3, 1 To 10) As Single, c (1 To 6, 1 To 3) As String
, a , ( , Integer). b ; ( Single). c ; .
Dim . , Dim a(1 To m) As Integer , m .
Dim (.. ), , . ReDim. .
, , , (, ). :
Dim a() As Integer
m = InputBox (" ")
ReDim a(1 To m)
, . , , ReDim, .
|
|
. , , . :
a(2) = 15
b(2,7) = 8.3
c(1,4) =
a 15. b, , 8,3. c, , .
, , .
. For.
(.. , ) VBA For, :
For _ = _ To _ Step
,
Next _
, . Step , , 1. .
1.5. ( m n), m n ( a). . asum.
Sub primer1_5a()
Dim a(1 To 5, 1 To 10) As Single, asum(1 To 10) As Single
m = InputBox(" : ")
n = InputBox(" : ")
For i = 1 To m
For j = 1 To n
a(i, j) = InputBox("a(" & i & "," & j & "): ")
Next j
Next i
For j = 1 To n
For i = 1 To m
asum(j) = asum(j) + a(i, j)
Next i
Next j
For j = 1 To n
MsgBox (" " & j & "- = " & asum(j))
Next j
End Sub
Dim ( ). Dim a(1 To 5, 1 To 10) As Single , a , . , , ( Single).
. , a :
For j = 1 To n
For i = 1 To m
asum(j) = asum(j) + a(i, j)
Next i
Next j
j ( ) 1. j=1 :
For i = 1 To m
asum(j) = asum(j) + a(i, j)
Next i
.. i ( ) 1 m, a(1,1), a(2,1), , a(m,1). asum(1) asum, a.
j ( ) 2. , .. i ( ) 1 m, a(1,2), a(2,2),, a(m,2). asum(2). j =3,, n, .. a.
asum :
For j = 1 To n
MsgBox ("" & j & "- = " & asum(j))
Next j
j 1 n 1, .. 1, 2, 3, , n. (.. n ) MsgBox ("" & j & "- = " & asum(j)), .. j - asum.
primer1_5a a , asum ( , .. m n, ). m n, 5 10, : , . , . :
Sub primer1_5b()
Dim a() As Single, asum() As Single
m = InputBox(" ")
n = InputBox(" ")
ReDim a (1 To m, 1 To n), asum(1 To n)
a asum , m n.
|
|
1.6. ( a), ( x). a , x. x, .
Sub primer1_6a()
Dim a(1 To 5, 1 To 3) As Single, x As Single
m = 5: n = 3
MsgBox( )
For i = 1 To m
For j = 1 To n
a(i, j) = InputBox("a(" & i & "," & j & "): ")
Next j
Next i
x = InputBox(" x: ")
For i = 1 To m
kol = 0
For j = 1 To n
If a(i,j) = x Then kol = kol + 1
Next j
If kol = n Then MsgBox (i & "- " & x)
Next i
End Sub
, x, kol. For i = 1 To m , For j = 1 To n . (i -) kol=0, .. kol . i - x, , kol (If a(i,j) = x Then kol = kol + 1). ,
For j = 1 To n (.. i - ) kol , x. If kol = n Then MsgBox (i & "- " & x) kol. (.. ), , , x. . For i = 1 To m, .. i , .
, x, , Dim Single, .. , . . , a(i,j) x (If a(i,j) = x Then ) , Single (.. ), x , (, 7 7).
. , x, vse.
Sub primer1_6b()
... . primer1_5a
For i = 1 To m
vse = True
For j = 1 To n
If a(i,j) <> x Then vse = False
Next j
If vse = True Then MsgBox (i & "- " & x)
Next i
End Sub
vse True. x, , vse False. , vse False, , x ( True, x). If vse = True Then vse. True, , , x. .
1.7. . .
Sub primer1_7()
Dim a(1 To 3, 1 To 5) As Single
m = 3: n = 5
MsgBox( )
For i = 1 To m
For j = 1 To n
a(i, j) = InputBox("a(" & i & "," & j & ")")
Next j
Next i
For j = 1 To n
maximum = a(1,j)
For i = 1 To m
If a(i,j) > maximum Then maximum = a(i,j)
Next i
MsgBox (" " & j & "- " & maximum)
Next j
End Sub
For j = 1 To n ,
For i = 1 To m . maximum . : maximum = a(1,j). , maximum, . maximum .
1.8. . . .
Sub primer1_8()
Dim a(1 To 3, 1 To 5) As Single
m = 3: n = 5
MsgBox( )
For i = 1 To m
For j = 1 To n
a(i, j) = InputBox("a(" & i & "," & j & ")")
Next j
Next i
For j = 1 To n
maximum = a(1,j)
nomer = 1
For i = 1 To m
If a(i,j) > maximum Then
maximum = a(i,j)
nomer = i
End If
Next i
x = a(1,j)
a(1,j) = a(nomer,j)
a(nomer,j) = x
Next j
For i = 1 To m
For j = 1 To n
MsgBox("a(" & i & "," & j & ") = " & a(i,j))
Next j
Next i
End Sub
. maximum , nomer (, , , ). j - :
x = a(1,j)
a(1,j) = a(nomer,j)
a(nomer,j) = x
x , .
1.9. . . , ( predel). .
Sub primer1_9()
Dim a() As Single, sred() As Single, predel As Single
m = InputBox(" ")
n = InputBox(" ")
redim a(1 To m, 1 To n), sred (1 To m)
For i = 1 To m
For j = 1 To n
a(i, j) = InputBox("a(" & i & "," & j & ")")
Next j
Next i
predel = InputBox( )
k = 0
For i = 1 To m
sum = 0
For j = 1 To n
sum = sum + a(i,j)
Next j
sred_stroki = sum/n
If sred_stroki > predel Then
k=k+1
sred(k) = sred_stroki
End If
Next i
For i = 1 To k
MsgBox (sred(i))
Next i
End Sub
sred , predel. , predel (.. sred_stroki > predel), k - sred. k (k=k+1). (k -) sred: sred(k) = sred_stroki.