VBA Excel: sample programs. Macros in Excel

Few people know that the first version of the popular Microsoft Excel product appeared in 1985. Since then, it has gone through several modifications and is in demand among millions of users around the world. At the same time, many work only with a small fraction of the capabilities of this spreadsheet processor and do not even know how the programming skills in Excel could make life easier for them.

What is VBA?

Programming in Excel is carried out through the programming language Visual Basic for Application, which is initially built into Microsoft's most famous table processor.

Experts attribute the comparative ease of development to its advantages. As practice shows, the basics of VBA can be mastered even by users who do not have professional programming skills. VBA features include running a script in an office application environment.

, . , VBA , , . . Microsoft Office, IBM Lotus Symphony .

, ,

, VBA. , . Excel , , . , .. .

Application, Excel. Workbooks, Worksheets, Range. , A1 .

"", , ChartObjects. .

— . . , Range — Value Formula.

— , , . VBA . , , "" Cells(1,1).Select. , (1,1) .. A1.

Selection.ClearContents. .

, « Excel ».

VB, «Alt» «F11». :

• , , Excel;
• Mudule;
• , floppy disk;
• , , .

:

Sub program ()

'

End Sub

, «' » (). , , , .

VBA Excel ( . ). , , Visual Basic, . , , .

Excel

, Visual Basic for Application. , Excel — . Microsoft , . , , VBA Excel. .

1

: , .

:

• «»;
• «»;
• « »;
• .

« » «1», « » , , hh ( , - «Ctrl+h»). Enter.

, , - . . « ». .

:

• «»;
• « 1»;
• «» ( «Ctrl+hh»).

, .

, . «» «» «». VBA. , Sub 1() End Sub.

, , 1 C1, , Range(“C1”).Select. , «(“C1”).», VBA Excel, 1.

ActiveSheet.Paste. ( 1) 1.

2

VBA Excel.

VBA . , y=x + x² + 3x³ – cos(x). . , VBA.

x1=0 x2=10. , — .

VBA Excel , . , :

Sub programm()

x1 = 1

x2 = 10

shag = 0.1

i = 1

Do While x1 < x2 ( x1 < x2)

y=x1 + x1^2 + 3*x1^3 – Cos(x1)

Cells(i, 1).Value = x1 ( x1 (i,1))

Cells(i, 2).Value = y ( y (i,2))

i = i + 1 ( );

x1 = x1 + shag ( );

Loop

End Sub.

"" , x, — y.

, "".

3

VBA Excel 2010, , Do While For.

, . . For , .

, . . , 10 . .

For i = 1 to 10 Next

«» , « 1 10 ».

, , 1 11, :

For i = 1 to 10 step 1 Next.

step — . . , .

(i,1). i . , .

, :

Sub program()

For i = 1 To 10 Step 1 ( For i = 1 To 10)

Cells(i, 1).Value = i ^ 2 (.. (i,1) i)

Next ( )

End Sub.

, (. ), ( 10 ).

4

- . VBA Excel. , , , If …Then ( ) If …Then …END If.

. , "", (1,1) :

1, ;

0, ;

-1, .

"" , «» Alt F11. :

Sub program()

x= Cells(1, 1).Value ( x (1, 1))

If x>0 Then Cells(1, 1).Value = 1

If x=0 Then Cells(1, 1).Value = 0

If x<0 Then Cells(1, 1).Value = -1

End Sub.

"" .

VBA

, Microsoft . , VBA. , "" Word, 160 . . :

• . , , , .
• . Excel, .
• . Array, IsArray; LBound; UBound.
• VBA Excel . . , , Space , , Asc ANSI. "", , .
• . , CVar Expression, Variant.
• . "". , WeekdayName ( ) . Timer. , .
• . , Oct .
• . Format. Variant , , .
• .

"".

5

. :

. :

• "";
• VBA, , .

.

Excel. , , -, , , . (), , , . . , "Օ".

, . :

• NN– ;
• TP TF – ;
• SF SP – ;
• IP IF – .

, «» Itog . , ItogTP – , , « ».

VBA

, . % (F – P) / P * 100, — (F – P).

"".

ItogP=ItogP + P ItogF=ItogF+ F.

= (ItogF – ItogP) / ItogP * 100, , — (ItogF – ItogP).

, .

, , , "1.xls".

« » 1 . . , « » . «» "".

, Excel . vba excel ( . ) "". , , , , "" "".