VBA - ( Visual Basic for Application) Microsoft. , MS Office. VBA Excel, Access, Word .
VBA - , - . , Excel, , , , // , , .
' ( '- ).
VBA , (Visual Basic Editor), Excel . , "ALT+F11".
VBA '- , ' Excel, '.
, .
VBA :
( ), . ֳ ;
, , Excel;
, ;
- . , .
, Excel VBA, . ³ VBA.
VBA , , ' Excel, . , .
VBA , Excel , , , .
2 ' Excel, ...... 6
' - , Excel, : , , , . ' , . Excel :
':
Application - Excel,
Workbook - ,
Worksheet - ,
Range - ,
Chart - ,
UserForm - .
' . ѳ ', ' . , Workbooks ( ) ' Workbook. '. ' , ' ' ( ). , Worksheets(1) - , Worksheets(1) - ' 1.
|
|
' VBA 100 ', . ' ' .
' '. ' . ' Application ' '. , 1 1 ' :
Application. Workbooks(). Worksheets(1). Range(A1)
'. ' . Excel , :
Worksheets(1). Range(A1).
' , 䳿 ', 䳺.
, '. :
'. _
. . , :
1) , , , ;
2) ' := ,
, , . VBA .
, , ( ), :
1) Range(A1:D28). Sort Range(A1), xlAscending,_ Range(C1), xlDescending
2) Range(A1:D28). Sort key1:= Range(A1),_ Order1:=xlAscending, key2:=Range(C1), Order2:=xlDescending
- ', , , , . ', . . :
'. =
- , ' (, - Click ), . 䳿 , . VBA : . .
2. 1 ' Workbook Workbooks . 8
' Worksheet Worksheets. ' ' Workbook - , ' Application . .
:
ActiveSheet - ;
,
MsgBox ' & ActiveSheet. Name
|
|
' ;
Sheets - ;
Worksheets - .
:
Activate - , ; ,
Workbooks(1). Activate
Add - ' Workbooks;
Open - ;
Close - ;
Save - ;
SaveAs - ;
,
ActiveBook. SaveAs Filename: = . xls
:
Name - ' ; , ' :
Worksheets(1). Name=
UsedRange - , ' Range, ;
ActiveCell - .
:
Activate - ;
,
Worksheets(2). Activate
Add - ;
,
ActiveWorkbook. Worksheets. Add
;
Delete - ;
,
WorkSheets( ). Delete
2.2 ' Worksheet Worksheets ... 9
2.3 ' Range, Cells Selection .... 9
' Range - , ' VBA. . ,
Range(A1) - A1;
Range(B2:D28) - .
' Cells(s1, s2) - , s1, - s2. s1 s2 . , Cells(3,2) - B3. ' , s1 s2 , .
, 3 :
i=3: j=2: Cells(i,j). Select
' ActiveCell - , . Select.
' Selection () - , Select.
, :
Range(B2:C28). Select
Selection. Interior. ColorIndex=15
' Rows - .
' Columns - .
:
Name - ' ; ,
Range(A2:F12). Name=泔
Value - ; ,
Range(A2). Value=
Range(C3). Value=123
,
a=Range(C2). Value
2;
Count - ' ; k , A1:D4
k = Range(A1:D4). Rows. Count
CurrentRegion - . , , .
ij . ,
z=Range(A1). CurrentRegion. Rows. Count
z .
EntireRow, EntireColumn - . :
ActiveCell. EntireRow. Clear
ActiveCell. EntireColumn. Select
Formula - ; , Excel. ,
Range(D3). Formula = =C3*0,25
|
|
Range(C10). Formula = =sum(C3:C9)
, ,
.
FormulaR1C1 - , R1C1, R, - , - . , R3C2 B3. . ³ , . ,
ActiveCell. Value=24 24
ActiveCell. Offset(1,0). Value=7 - 7
ActiveCell. Offset(2,0). Select 2
ActiveCell. FormulaR1C1= =R[-2]C[0]+R[-1]C[0]
, , . , , .
FormulaLocal - 1, Excel.
, 2 :
Range(B2). FormulaLocal = =(C1:C4)
Text - , , .
Offset(__, __) - , , ;
,
ActiveCell. Offset(1,0). Select
, , ;
Font - , . :
Name - ' ; (Arial, Times New Roman, );
Size ;
Bold - True False ;
Italic - , True;
Underline - .
, , :
Range(A1). Font. Name = Arial
Range(A1). Font. Size = 18
Range(A1). Font. Bold = True
Borders - , . :
LineStyle - , :
xlContinuous (), xlDouble (), xlDash ();
ColorIndex - , 1 49; :
1 - | 2 - | 3 - | 4 - |
5 - | 6 - | 7 - | 8 - |
9 - | 10 - | 15 - |
Weight - , : xlThin (), xlMedium (), xlThick ().
,
Range(B2:D14). Borders. LineStyle = xlDouble
Range(B2:D14). Borders. Weight = xlMedium
B2:D14 .
, , Borders(). , , : xlEdgeTop ( ), xlEdgeBottom ( ), xlEdgeLeft ( ), xlEdgeRight ( ).
,
Range(B2:D14). Borders(xlEdgeTop). LineStyle = xlContinuous
Range(B2:D14). Borders(xlEdgeBottom). LineStyle = xlDouble
Range(B2:D14). Borders(xlEdgeLeft). LineStyle = xlDash
Range(B2:D14). Borders(xlEdgeRight). LineStyle = xlDash
, , .
Copy - . destination, , . , . , 1:4 :
|
|
Worksheets(1). Range(A1:C4). Copy
Destination:= Worksheets(2). Range(E5)
Cut - . destination , .
Delete - . :
Rows(3). Delete
Columns, Rows - , . , i j :
i=Selection. Columns. Count
j= Selection. Rows. Count
Insert - . :
Worksheets(1). Rows(4). Insert
Select - . , :
ActiveCell. Offset(3,2). Select
PasteSpecial - . Paste , . :
XlPasteAll - ;
XlPasteFormulas - ;
XlPasteValues - ;
XlPasteFormats - .
1:5 0 200 ( ), :
Worksheets(1).Range(A1:E5). Formula = =int(rand()*200))
Worksheets(1). Range(A1:E5). Copy
Worksheets(2). Range(A1:E5). PasteSpecial _
paste:=xlPasteValues
AutoFill - . , . :
Destination - , . ;
Type - , : xlFillDefault, xlFillSeries, xlFillCopy, xlFillValues, xlFillDays.
2:12 1 21:
Range(B2). Value=1
Range(B3). Value=3
Range(B2:B3). AutoFill destination:=Range(B2:B12)
Find - , . Nothing, . Find , . :
What - , ;
After - , ; , ;
Lookin - ; : xlFormulas - , xlValues - ;
LookAt - : xlWhole , xlPart .
, :
Range(E6). Formula = =Min(B2:E5)
Range(B2:E5).Find(Range(E6).Value,, xlValues, xlWhole).Select
Sort - , , . :
'. Sort (key1,order1,key2,order2, key3,order3)
Key - , ;
Order - . :
XlAscending - ; XlDescending - ;
, 1:20 1 , , - :
Range(A1:C20). Sort key1:=Range(A1), key2:=Range(B1)
' Application, Excel. Workbooks (), Worksheets (), Range (, ).
, "A1" , :
Application.Workbooks("").Worksheets("1").Range("A1").
(Collections). ' "". - ' . '. , ' Worksheets ' Worksheet, ':
|
|
ChartObjects ( ' ChartObject)
Range
PageSetup
PivotTables ( ' PivotTable).
(Properties). ' . , ' Range Value Formula.
Worksheets(Sheet1).Range(A1).Value
Worksheets(Sheet1).Range(A1).Formula
, , .
, Formula , :
MsgBox Range(A1).Formula - "1";
Range(B12).Formula = =2+6*100 - =2+6*100 B12.
(Methods). , . VBA ( " "). ' , :
Range("A1").Select
Cells(1, 1).Select
(Select) "A1". .
Selection.ClearContents
(Selection) (ClearContents).
(Debugger). VBA . , VBA. ³ , , .
³ Visual Basic Editor (Alt+F11). :
1. Sub LearningDebug()
2. Dim A As Long, B As Long, C As Long, D As Long
3.
4. D = 0
5. A = 10
6. Debug.Print "A = " + Trim(Str(A))
7. B = 15
8. Debug.Print "B = " + Trim(Str(B))
9. C = A + B
10. Debug.Print "C = " + Trim(Str(C))
11. C = Round(C / 5)
12. Debug.Print " 5: C = " + Trim(Str(C))
13. C = Round(C / D) ' , ( D = 0)
14. End Sub
Ctrl+G, ' Immediate. , VBA, .
F5, . F8 - .
F8 .. .
F9 . F5, . F5 F8, .
, , A, B, C, D .
Debug.Print "" Immediate, , . Str (A) . Trim () . Round () ( Long, ). , .
F8 . , . . ' , . , .
F8. Immediate ' :
= 10
A Immediate (, 8). Immediate :
= 8
Enter. A , 8. , , Immediate. , Immediate:
?A
Enter. ? Immediate , Debug. Print . . ³ - .
(C = Round (C / D)) F9. ' . ҳ , . F5, . Immediate ', . . ³ , .
F8 . , ', Debug. End, , .
, D. . Immediate :
D = 2
Enter. , 2, . F5 .
. , .
Sheets. (Sheets) (Workbooks). , 䳿 . , ?
:
1. Sub Test() ' moonexcel.com.ua
2. MsgBox (Str(Application.Workbooks.Item("Test.xls").Sheets.Count))
3. End Sub
(MsgBox), (Sheets.Count) (Workbooks) "Test.xls".
ϳ 쳺 , . , , .
. , Add. 4 Add(Before, After, Count, Type). '. . , , Count Type. , , xlWorkSheet xlChart . , .
1. Sub Test() 'moonexcel.com.ua
2. Sheets.Add After:=Worksheets("3"). Count:=4
3. End Sub
4 (Count:=4) "3". :
1. Sub Test() 'moonexcel.com.ua
2. Worksheets.Add
3. ActiveSheet.Move After:=Sheets (ActiveWorkbook.Sheets. Count)
4. End Sub
. , . , , . Visible. TRUE FALSE .
1. Sub Test() 'moonexcel.com.ua
2. ActiveWorkbook.Sheets("3").Visible = False
3. End Sub
(Range). . Excel ' Range, , . , 䳿 .
. :
1. Sub Test2() 'moonexcel.com.ua
2. Dim cur_range As Range '' Range
3. Set cur_range = Selection '' Range
4. ' , Immediate
5. Debug.Print cur_range.Address
6. Debug.Print cur_range.Columns.Count
7. Debug.Print cur_range.Rows.Count
8. End Sub
cur_range . , Debug.Print Immediate.
, $C$1:$E$5, - 3, - 5.
.UsedRange. . .UsedRange.
1. Sub Test() 'moonexcel.com.ua
2. Dim cur_range As Range
3. Set cur_range = ActiveSheet.UsedRange
4. Debug.Print cur_range.Address
5. End Sub
, , Excel, , .
(Properties). VBA , , .
. , , :
1. Sub Properties() 'moonexcel.com.ua
2. Range ("A1")
3. End Sub
A1. . , , Range ("A1").
Value Tab. :
1. Sub Properties() 'moonexcel.com.ua
2. Range ("A1").Value
3. End Sub
Value .
35 A1:
1. Sub properties() 'moonexcel.com.ua
2. Range("A1").Value = 35
3. ' A1 35
4. End Sub
( , ""):
1. Sub properties() 'moonexcel.com.ua
2. Range("A1").Value = " "
3. End Sub
, , . , - , , , :
1. - Sheets("Sheet2").
1. Sub properties() 'moonexcel.com.ua
2. Sheets("Sheet2").Range("A1").Value = " "
3. ': 2, A1
4. End Sub
2. , - Sheets(2).
1. Sub properties() 'moonexcel.com.ua
2. Sheets(2).Range("A1").Value = " "
3. End Sub
, , :
1. Sub properties() 'moonexcel.com.ua
2. Workbooks("Book2.xlsx").Sheets("Sheet2").Range("A1").Value = " "
3. End Sub
Value , , . , :
1. Sub properties() 'moonexcel.com.ua
2. Range("A1").Value = 35
3. Range("A1") = 35
4. End Sub
2. .
35 A1, :
1. Sub properties() 'moonexcel.com.ua
2. Range("A1").Clear
3. ': A1
4. End Sub
3. .
Font, ' , :
:
35 8:
1. Sub properties() 'moonexcel.com.ua
2. Range("A1") = 35
3. Range("A1").Font.Size = 8
4. End Sub
:
1. Sub properties() 'moonexcel.com.ua
2. Range("A1").Font.Bold = True
3. End Sub
:
1. Sub properties() 'moonexcel.com.ua
2. Range("A1").Font.Bold = False
3. End Sub
: :
1. Sub properties() 'moonexcel.com.ua
2. Range("A1").Font.Italic = True
3. End Sub
4.
: :
1. Sub properties() 'moonexcel.com.ua
2. Range("A1").Font.Underline = True
3. End Sub
: :
1. Sub properties() 'moonexcel.com.ua
2. Range("A1").Font.Name = "Arial"
3. End Sub
: :
1. Sub properties() 'moonexcel.com.ua
2. Range("A1").Interior.ColorIndex = 6
3. End Sub
2.4 ... 15
2.4
2.1. , 䳿:
1) 1 ʳ , 1 , 1 ֳ , D1 ;
2) 2 1, 3 2 4 16 3 15;
3) 2 16 0 100 ;
4) 2:16 , 2:16 2:16;
5) D2 , C2 2 D3:D16;
6) 16:D16 .
7) D18 , D.
8) C19 , C2:C16.
9) 2:16 .
10) .
Sub 21()
Range("A1"). Value = "ʳ "
Range("B1"). Value = " "
Range(C1),Value= ֳ
Range("D1"). Value = " "
Range("A1:C1"). Select
Selection. Columns. AutoFit
Range("A2"). Value = 1
Range("A3"). Value = 2
Range("A2:A3"). AutoFill Destination:=Range("A2:A16"),
Range("B2:B16"). Formula = "=INT(RAND()*100)"
Range(B2:B16). Copy
Range("C2"). PasteSpecial xlPasteValues
Range("D2"). Formula = "=A2*C2"
Range("D2"). Copy Destination:=Range("D3:D16")
Range("A16:D16"). Borders(xlEdgeBottom). LineStyle = xlDouble
Range("D18"). Formula = "=SUM(D2:D16)"
Range("C19"). Formula = "=MIN(C2:C16)"
Range("C2:C16"). Find(Range("C19"). Value,, xlValues, xlWhole). Select
ActiveCell. Interior. ColorIndex = 4
Range("A2:D16"). Sort key1:=Range("C1")
End Sub
3 VBA (Visual Basic for Application).