1. :
1.1. VBA Excel.
1.2. VBA Excel.
2. :
2.1. .
2.2. IBM PC/XT.
2.3. MS Excel.
2.4. Visual Basic for Application.
3. :
3.1. ( ).
3.2. 1. , . Excel.
3.2.1. Excel, . VBA. 1.
1 .
3.2.2. Excel (), (. 2).
2 Excel .
3.2.3. .
Private Sub CommandButton1_Click()
Dim a, b, c As Double
a = CDbl(TextBox1.Text)
b = CDbl(TextBox2.Text)
c = CDbl(TextBox3.Text)
With ActiveSheet
Range(b3).Value = a
Range(b4).Value = b
Range(b5).Value = c
Range(b6).FormulaLocal = =b3*b7^3+b4*sin(b7)
Range(b6).GoalSeek Goal:=c, changingCell:=Range(b7)
TextBox4.Text = CStr(.Range(b7).Value)
TextBox4.Text = FormatNumber(TextBox4.Text, 2)
End With
End Sub
Private Sub CommandButton2_Click()
UserForm1.Hide
End Sub
3.2.4. .
3.2.5. , , (. ). .
3.3. 2. , . Excel, .
3.3.1. Excel, . VBA. 3.
3 .
3.3.2. Excel (), (. 4).
4 Excel.
3.3.3. .
Const strNomer = 3
Dim strName1 As String '
Dim strName2 As String
Dim nomer As Long '
Private Sub CommandButton1_Click()
ActiveWorkbook.SaveAs ( . xls)
nomer = 1 End Sub
Private Sub CommandButton2_Click()
strName1 = Trim(Str(strNomer + nomer))
With ActiveSheet '
|
|
Range("A" + strName1).Value = nomer
Range("B" + strName1).Value = TextBox1.Text
Range("C" + strName1).Value = TextBox2.Text
Range("D" + strName1).Value = TextBox3.Text
'
strName2 = Trim(Str(strNomer + nomer + 1))
Set range1 =.Range("A" + strName1 +":D" + strName1)
Set range2 =.Range("A" + strName1 +":D" + strName2)
range1.AutoFill Destination:=range2
Range("A" + strName2 +":D" + strName2).Clear
End With
'
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox1.SetFocus
nomer = nomer + 1
End Sub
Private Sub CommandButton3_Click() '
UserForm1.Hide
With ActiveSheet
strName2 = Trim(Str(strNomer + nomer + 2))
Range("A" + strName2).Value =
Range("D" + strName2).Value = TextBox4.Text
End With
End Sub
3.3.4. .
3.4. 3. , . Excel, (. 5-6). .
5 Excel.
6 .
3.5. . .
3.6. .
4. :
4.1. .
4.2. .
4.3. .
4.4. .
4.5. .
4.6. .
5. :
5.1. Excel? ? .
5.2. Range?
5.3. Range Cells?
5.4. Range.
5.5. Range.
5.6. Range, Excel?
5.7. VBA Excel?
5.8. VBA?
5.9. ?
5.10. Word?
Range Selection
Excel Application. Application () Excel Excel. 120 40 . Excel. Excel Workbook ( ) Application . XLS ( ), XLA ( ). . Range, VBA Excel ( Range . 1, 2).
|
|
Excel Range () worksheet. Range VBA. selection () VBA Select, selection. . Selection Range, Range. Range Selection , . Range , Excel .
Range
, Range , . , Range (: ) , , , Range(2:2) . Rows () columns (), . , columns (1), Rows (2).
Range Cells
, , Range . Cells () . , 2 Range (A2) Cells (l, 2). , cells, Range, , , Range(2:ǻ) Range(Cells(1,2), Cells(3,3)) .
1 Range
Value | . x 1: x = Range('C1').Value a1:b2 1: Range('A1:B2').Value = 1 |
Name | . a1:b2 "": Range("A1:B2").Name = "" |
Count | . x , a1:b2: x = Range("A1:B2").Rows.Count |
CurrentRegion | . , . , , a1: y = Range("A1").CurrentRegion.Rows.Count |
ColumnWidth, RowHeight | |
WrapText | . True False. b2 " ", : With Range("B2").Value = " ".WrapText = True End With |
EntireColumn, EntireRow | . : ActiveCell.EntireRow.Clear ActiveCell.EntireColumn.Select |
Comment | comment (), . comment comments. AddComment, , . Comment Text, , . : Text(Text,Start,Overwrite) Text - , ; Start - . , ; overwrite - : True ( ) False ( ). comment visible, , |
VerticalAlignment | . : xlBottom ( ); xlCenter ( ); xlJustify ( ); xlTop ( ) |
Orientation | . -90 90 |
ShrinkToFit | : True ( , ) False ( ) |
Font | Font (). Font : Name - , , "Arial Cyr"; FontStyle - , Regular (), Bold (), Italic (), Bold Italic ( ); Size - ; Strikethrough - : True ( , ) False ( ); Superscript - : True ( ) False ( ); Subscript - : True ( ) False ( ); Underline - xlNone ( ) xlSingle ( ) |
Horizontal Alignment | . : xlGeneral ( , ); xlCenter ( ); xlRight ( ); xlLeft ( ); xlJustify ( ); xlCenterAcrossSelection ( ); xlFill ( ). |
|
|
2 Range
AutoFit | |
Clear, ClearComments, ClearContents, ClearFormats, ClearNotes | clear . A1:G37. Range("A1:G37").Clear ClearComments, ClearContents, ClearFormats ClearNotes , , |
Insert | . 1: Worksheets("1").Rows(4).Insert |
Select | |
Copy | . : Copy(destination) destination , . destination , . a1:d4 e5 2: Worksheets("1").Range("A1:D4").Copy destination:= Worksheets("2").Range("E5") |
Delete | |
AddComment | . : AddComment(Text) Text - , |
Address | . : Address(rowAbsolute, columnAbsolute, referenceStyle, external, relativeTo). : rowAbsolute - True False, True , ; columnAbsolute - True False, True , ; referenceStyle - xllA1 xlRld, xlAl , A1; external - True False, False , . : MsgBox Cells(1,1).Address - $A$1. MsgBox Cells(1,1).Address(rowAbsolute:=False) - $A1. MsgBox Cells(1,1).Address(referenceStyle:=xlR1C1) - R1C1 |
Cut | . : Cut(destination). destination , . destination , |
Columns, Rows | , . i j , : i = Selection.Columns.Count j= Selection.Rows.Count |
Range, Excel
|
|
Excel : , , , , .
AutoFill. AutoFill () . AutoFill DataSeries , , . ( , ) , .
: . AutoFill(, ).
: , : xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues,xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend. xlFillDefault
AutoFilter. AutoFilter () . AutoFilter , . , , . : (All), ( 10), (Custom), , (Blanks) (NonBlanks). , , (Data, Filter, AutoFilter). AutoFilter .
1: . AutoFilter
AutoFilter , , (Data, Filter, AutoFilter), , .
2: . AutoFilter (field, criteria1, operator, criteria2)
AutoFilter , , (Data, Filter, AutoFilter) , .
: field , , ; Criteria1 criteria2 . , 101, >, <,>=, <=, =, <>; operator : X1And ( ); X1or ( ); showAllData FilterMode AutoFilterMode.
ShowAllData. . C FilterMode : True ( ), False ( ). AutoFilterMode : True ( AutoFilter), False ( )
|
|
GoalSeek. GoalSeek ( ) ( ), . , : , , . GoalSeek , (Tools, Goal Seek). GoalSeek , , , , . .
: . GoalSeek(Goal, ChangingCell)
: , , . ( ) , ChangingCell; Goal , ; ChangingCell , ( ). , Goalseek, .
, , , Maxchange Maxiterations Application. , 0,0001 1000 :
With Application
Maxiterations = 1000
MaxChange = 0.0001
End With
(Calculation) (Options), , (Tools, Options).
Sort. . sort , , . , (Data, Sort).
: . Sort(key1, order1, key2, order2, key3, order3, header, orderCustom, matchCase, orientaticn)
: , ; Key1 ; Order1 . : xlAscending ( ); xlDescending ( ); key2 ; order2 . : xlAscending ( ); xlDescending ( ); header : xlYes ( , ); xlNo ( , ); xlGuess (Excel , ); orderCustom . Normal; matchCase : True ( ) False ( ); orientation : xlTopToBottom ( , . . ); xlLeftToRight ( , . . )
, 1:20 1 , , :
Worksheets().Range(A1: C20).Sort _
key1:=Worksheets(Sheet1).Range(A1), _
key2:=Worksheets (Sheet1).Range (B1)
. , . VBA , .
1
Round
:
X= round(2.505, 2)
2,5, 2,51.
.
2
Format
:
sng=Format(Sng, #, 0.00)
3
FormatNumber
Sng= FormatNumber(sbg, 2)
Format, , , .
. , , string, single, double, decimal, currency variant, integer long.
. VBA , CDBL. : CDbl()
. , , : = Cdbl(textBoxN.text)
. Excel Range: range(A5).value = a
, CDbl, CStr , . TextBoxN.text = CStr(.Range(A8).value) .
Trim () , , .