VBA Excel: sample programs. Macros in Excel. Examples of macros in Excel. Dialogue VBA message msgBox

Few people know that the first version of the popular product Microsoft Excel 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 people work only with a small fraction of the capabilities of this spreadsheet processor and do not even realize how the ability to program in Excel could make their life easier.

What is VBA

Programming in Excel is carried out using the Visual Basic for Application programming language, which was originally built into the most famous spreadsheet processor from Microsoft.

Experts attribute its relative ease of learning to its advantages. As practice shows, even users who do not have professional programming skills can master the basics of VBA. Features of VBA include script execution in an office application environment.

The disadvantage of the program is compatibility problems different versions. They occur because the VBA program code accesses functionality that is present in the new version of the product but not in the old version. Also, the disadvantages include the excessively high openness of the code for changes by an outsider. However, Microsoft Office as well as IBM Lotus Symphony allow the user to encrypt the initial code and set a password to view it.

Objects, collections, properties and methods

These are the concepts that need to be understood by those who are going to work in the VBA environment. First of all, you need to understand what an object is. In Excel, these functions are sheet, workbook, cell, and range. These objects have a special hierarchy, i.e. obey each other.

The main one is Application, which corresponds to the Excel program itself. Then come Workbooks, Worksheets, and Range. For example, to access cell A1 on a specific worksheet, you must specify a path that takes into account the hierarchy.

As for the concept of “collection”, this is a group of objects of the same class, which in the entry has the form ChartObjects. Her individual elements are also objects.

The next concept is properties. They are a necessary characteristic of any object. For example, for Range it is Value or Formula.

Methods are commands that indicate what needs to be done. When writing code in VBA, they must be separated from the object by a period. For example, as will be shown later, very often when programming in Excel, the Cells(1,1).Select command is used. It means that you need to select a cell with coordinates

Selection.ClearContents is often used together with it. Executing this means clearing the contents of the selected cell.

How to get started

Then you need to go to the VB application, for which you just need to use the key combination “Alt” and “F11”. Next:

• in the menu bar located at the top of the window, click on the icon next to the Excel icon;
• select the Mudule command;
• save by clicking on the icon with the image;
• they write, let's say, a draft of the code.

It looks like this:

Sub program()

"Our code

Please note that the line "Our code" will be highlighted in a different color (green). The reason is the apostrophe placed at the beginning of the line, which indicates that a comment follows.

Now you can write any code and create for yourself new tool in VBA Excel (see program examples below). Of course, it will be much easier for those who are familiar with the basics of Visual Basic. However, even those who do not have them can get used to it quickly enough if they wish.

Macros in Excel

This name hides programs written in Visual Basic for Application. Thus, programming in Excel means creating macros with the necessary code. Thanks to this feature, the Microsoft spreadsheet processor develops itself, adapting to the requirements of a specific user. Having figured out how to create modules for writing macros, you can begin to consider specific examples VBA programs Excel. It's best to start with the most basic codes.

Example 1

Task: write a program that will copy the value of the contents of one cell and then write it to another.

To do this:

• open the “View” tab;
• go to the “Macros” icon;
• click on “Record macro”;
• fill out the form that opens.

For simplicity, leave “Macro1” in the “Macro name” field, and insert, for example, hh into the “Keyboard shortcut” field (this means that you can launch the program with the quick command “Ctrl+h”). Press Enter.

Now that the macro recording has already started, the contents of a cell are copied to another. Return to the original icon. Click on “Record Macro”. This action means the end of the program.

• go back to the “Macros” line;
• select “Macro 1” from the list;
• click “Run” (the same action is launched by starting the key combination “Ctrl+hh”).

As a result, the action that was performed while recording the macro occurs.

It makes sense to see what the code looks like. To do this, go back to the “Macros” line and click “Change” or “Login”. As a result, they find themselves in the VBA environment. Actually, the macro code itself is located between the lines Sub Macro1() and End Sub.

If copying was done, for example, from cell A1 to cell C1, then one of the lines of code will look like Range(“C1”).Select. Translated, it looks like “Range(“C1”).Select”, in other words, it goes to VBA Excel, to cell C1.

The active part of the code is completed by the ActiveSheet.Paste command. It means recording the contents of the selected cell (in in this case A1) to the selected cell C1.

Example 2

VBA loops help you create various macros in Excel.

VBA loops help you create various macros. Suppose we have a function y=x + x 2 + 3x 3 - cos(x). You need to create a macro to get its graph. This can only be done using VBA loops.

The initial and final values ​​of the function argument are x1=0 and x2=10. In addition, you must enter a constant - the value for the argument change step and the initial value for the counter.

All VBA Excel macro examples are created using the same procedure as presented above. In this particular case, the code looks like:

Sub program()

step = 0.1

Do While x1< x2 (цикл будет выполняться пока верно выражение x1 < x2)

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

Cells(i, 1).Value = x1 (the value x1 is written to the cell with coordinates (i,1))

Cells(i, 2).Value = y (the y value is written to the cell with coordinates (i,2))

i = i + 1 (counter is in effect);

x1 = x1 + shag (the argument changes by the step value);

End Sub.

As a result of running this macro in Excel, we get two columns, the first of which contains the values ​​for x, and the second for y.

Then a graph is built from them in a standard way for Excel.

Example 3

To implement loops in VBA Excel 2010, as in other versions, along with the already given Do While construction, For is used.

Consider a program that will create a column. In each of its cells the squares of the corresponding row number will be written. Using the For construct will allow you to write it very briefly, without using a counter.

First you need to create a macro as described above. Next we write down the code itself. We assume that we are interested in the values ​​for 10 cells. The code looks like this.

For i = 1 to 10 Next

The command is translated into “human” language as “Repeat from 1 to 10 in steps of one.”

If the task is to obtain a column with squares, for example, of all odd numbers from the range from 1 to 11, then we write:

For i = 1 to 10 step 1 Next.

Here step is a step. In this case it is equal to two. By default, the absence of this word in the cycle means that the step is single.

The results obtained must be saved in cells numbered (i,1). Then, each time the loop is started, with i increasing by a step size, the row number will automatically increase. Thus, the code will be optimized.

Overall the code will look like:

Sub program()

For i = 1 To 10 Step 1 (you can simply write For i = 1 To 10)

Cells(i, 1).Value = i ^ 2 (i.e. the value of square i is written to cell (i,1)

Next (in a sense plays the role of a counter and means another start of the loop)

End Sub.

If everything is done correctly, including recording and running the macro (see instructions above), then when you call it, you will get a column every time given size(in this case consisting of 10 cells).

Example 4

In everyday life, often the need arises to make one or another decision depending on some condition. You can’t do without them in VBA Excel. Examples of programs where the further course of execution of the algorithm is chosen rather than initially predetermined most often use the If ...Then construction (for complex cases) If ...Then ...END If.

Let's consider specific case. Suppose you need to create a macro for Excel so that the following is written in the cell with coordinates (1,1):

1 if the argument is positive;

0 if the argument is null;

-1 if the argument is negative.

The creation of such a macro for Excel begins in a standard way, through the use of “hot” Alt keys and F11. Next the following code is written:

Sub program()

x= Cells(1, 1).Value (this command assigns x the value of the contents of the cell at coordinates (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.

All that remains is to run the macro and get the required value for the argument in Excel.

VBA Functions

As you may have already noticed, programming in Microsoft's most famous spreadsheet processor is not that difficult. Especially if you learn how to use VBA functions. In total, this programming language, created specifically for writing applications in Excel and Word, has about 160 functions. They can be divided into several large groups. This:

• Mathematical functions. Applying them to the argument, they obtain the value of the cosine, natural logarithm, integer part, etc.
• Financial functions. Thanks to their availability and using Excel programming, you can obtain effective tools for accounting and financial calculations.
• Array processing functions. These include Array, IsArray; LBound; UBound.
• Excel VBA functions for string. This is a fairly large group. This includes, for example, the functions Space to create a string with the number of spaces equal to the integer argument, or Asc to convert characters to ANSI code. All of them are widely used and allow you to work with rows in Excel, creating applications that greatly facilitate working with these tables.
• Data type conversion functions. For example, CVar returns the value of the Expression argument by converting it to the Variant data type.
• Functions for working with dates. They significantly expand the standard ones. Thus, the WeekdayName function returns the name (full or partial) of the day of the week by its number. Even more useful is Timer. It gives the number of seconds that have passed from midnight to a specific point in the day.
• Functions for converting a numeric argument to different number systems. For example, Oct outputs numbers in octal.
• Formatting functions. The most important of them is Format. It returns a Variant value with an expression formatted according to the instructions specified in the format declaration.
• etc.

Studying the properties of these functions and their application will significantly expand the scope of Excel.

Example 5

Let's try to move on to solving more complex problems. For example:

A paper document reporting the actual level of costs of the enterprise is given. Required:

• develop its template part using the Excel spreadsheet;
• create a VBA program that will request initial data to fill it out, carry out the necessary calculations and fill in the corresponding template cells with them.

Let's consider one of the solution options.

Create a template

All actions are carried out on a standard sheet in Excel. Free cells are reserved for entering data on the name of the consumer company, the amount of costs, their level, and turnover. Since the number of companies (companies) for which the report is being compiled is not fixed, the cells for entering values ​​based on the results and the name of the specialist are not reserved in advance. The worksheet is given a new name. For example, "reports".

Variables

To write a program to automatically fill out a template, you need to select notations. These will be used for variables:

• NN - number of the current table row;
• TP and TF - planned and actual trade turnover;
• SF and SP - actual and planned amount of costs;
• IP and IF - planned and actual level of costs.

Let us denote the accumulation of the total for this column using the same letters, but with the “prefix” Itog. For example, ItogTP - refers to the table column entitled “planned turnover”.

Solving a problem using VBA programming

Using the introduced notations, we obtain formulas for deviations. If you need to calculate in %, we have (F - P) / P * 100, and in total - (F - P).

The results of these calculations can best be entered directly into the appropriate cells in the Excel table.

For actual and forecast totals, they are obtained using the formulas ItogP=ItogP + P and ItogF=ItogF+ F.

For deviations use = (ItogF - ItogP) / ItogP * 100 if the calculation is carried out as a percentage, and in the case of a total value - (ItogF - ItogP).

The results are again immediately written to the appropriate cells, so there is no need to assign them to variables.

Before running the created program, you need to save the workbook, for example, under the name "Report1.xls".

The “Create reporting table” button only needs to be pressed once after entering the header information. There are other rules you should know. In particular, the “Add Row” button must be clicked each time after entering values ​​for each type of activity into the table. After entering all the data, you need to click the “Finish” button and then switch to the Excel window.

Now you know how to solve Excel problems using macros. The ability to use vba excel (see examples of programs above) may also be needed to work in the most popular text editor at the moment, Word. In particular, you can create menu buttons by writing, as shown at the very beginning of the article, or by writing code, thanks to which many operations on text can be carried out by pressing the duty keys or through the “View” tab and the “Macros” icon.

MZTools - A very useful utility for those who program in VBA. Truly saves time when writing codes. It is important that the utility absolutely free. Here are some of its features:

• Automatically inserting error handlers into modules
• Inserting comments into the procedure header, with automatic insertion of time, module and procedure names, and the ability to indicate the author of the procedure
• Automatic numbering of lines of code
• Removing numbering from lines of code
• Create frequently used code templates
• Own clipboard for 9 operations with the possibility of codes and subsequent insertion of any of the 9 copied codes
• and many more useful things

The program is distributed free of charge.

(37.3 KiB, 3,708 downloads)

VBE Tools - How many of you have written code for a form, and then remembered that some element was not named as you wanted or simply wanted to be renamed due to a change in the code? And, of course, we encountered the problem that when renaming an element, we had to go into the code and replace all references to this element there with a new name. So, with the help of this program, you can rename the name of any form element (TextBox, Label, etc.) and ActiveX controls on an Excel sheet, and the program itself will replace all links in the code from the old name to the new one. Very convenient.

The program is distributed free of charge.

First, let's take a simple VBA Sub procedure as an example. It is stored in a VBA module and calculates the sum of the first hundred positive integers. When the calculation is complete, the procedure displays a message with the result.

Sub VBA_Demo()
" Example of a simple VBA procedure
Dim Total As Long, i As Long
Total = 0
For i = 1 To 100
Total = Total + i
Next i
MsgBox Total
End Sub

Download the note in or

This procedure uses some popular language elements:

• comment (line starting with apostrophe);
• variable declaration statement (line starting with the Dim keyword);
• two variables (Total and i);
• two assignment operators (Total = 0 and Total = Total + i);
• cyclic structure (For–Next);
• VBA function (MsgBox).

Comments

You can use a new line for the comment, or you can insert a comment after the statement on the same line.

In some cases, you need to test a procedure without inserting an instruction or even a whole set of instructions into it. Instead of deleting the corresponding statement, simply turn it into a comment by adding an apostrophe at the beginning of the line. VBA will then ignore the statement (or statements) when executing the procedure. To convert a comment into an instruction, simply remove the apostrophe.

Variables, data types and constants

Variable is a named storage location in computer memory. Variables can contain different types of data. You can use letters, numbers, and some punctuation marks in names, but the first letter in a variable name should always be entered. VBA is not case sensitive in variable names. You cannot use spaces or periods in names, but you can separate words with underscores.

VBA uses a lot of reserved words that are not allowed as variable or procedure names.

The VBA interpreter makes life easier for programmers by automatically processing any type of data. However, this has negative consequences - slower execution of operations and less efficient use of memory. As a result, letting VBA define data types on its own can cause problems running large or complex applications.

It is recommended to select a data type that uses the minimum number of bytes to store values. Excel uses the Double data type to perform mathematical calculations in worksheets. It is recommended to use it in the process of number processing in VBA to ensure the same accuracy of calculations.

There are three types of variable scopes:

Working with Constants

Sometimes you need to use a named value or a string that never changes - a constant. If a procedure makes multiple references to a specific value, such as an interest rate, you should declare that value as a constant and use the constant's name rather than its value in your expressions. This technique not only makes the program more convenient to read, but also makes it easier to subsequently change the code - it is enough to change only one instruction, and not several.

The Const operator is used to declare constants. For example,

Const NumQuarters as Integer = 4

Like variables, constants have a scope. If you try to change the value of a constant in VBA code, you will receive an error (as you would expect). A constant is a constant value, not a variable. In Excel and VBA, there are a number of predefined constants that can be used without declaration. You don't even need to know the meaning of these constants to use them. When writing macros, you typically use constants rather than values. The following procedure uses a built-in constant to change the page orientation of the active worksheet to landscape:

Sub SetToLandscape()
ActiveSheet.PageSetup.Orientation = xlLandscape
End Sub

The actual value of the xlLandscape variable is 2. The Object Browser window contains a list of all Excel and VBA constants. To open the Object Browser in VBE, press the key .

In VBA, date and time are defined as values ​​enclosed between # signs

Const FirstDay As Date = #1/1/2007#
Const Noon = #12:00:00#

Dates are always specified in month/day/year format, even if the system is configured to display data in a different format.

Assignment Operators

An assignment operator is a VBA statement that performs a mathematical calculation and assigns the result to a variable or object. Excel Help defines an expression as a combination of keywords, operators, variables, and constants. This combination returns a string, number, or object as a result. An expression can perform a calculation, process characters, or test data.

A lot of the work done in VBA involves developing (and debugging) expressions. If you know how to create formulas in Excel, then you will have no problem creating expressions in VBA. In an Excel worksheet formula, the result is displayed in a cell. On the other hand, a VBA expression can assign a value to a variable or be used as the value of a property. In VBA, the assignment operator is the equal sign (=).

Arrays

An array is a group of elements of the same type that have a common name; a specific array element is referenced using the array name and index. For example, you could define an array of 12 strings such that each variable corresponds to a month name. If you name the array MonthNames, you can refer to the first element of the array as MonthNames (0), the second as MonthNames (1), and so on until MonthNames (11).

You can declare an array containing exactly 100 integers like this:

Dim MyArray(1 To 100) As Integer

By default, VBA arrays use zero as the first element. If you want one to be used as the first index of all arrays, then you need to make the following declaration before the first procedure of the module: Option Base 1

A dynamic array does not have a predefined number of elements. It is declared with blank values ​​in parentheses: Dim MyArray() As Integer. However, before a dynamic array can be used in a program, a ReDim statement must be used to tell VBA how many elements are in the array. This is often done by using a variable whose value is unknown until the procedure is executed. For example, if the variable x is assigned a number, the size of the array is determined using the following statement: ReDim MyArray (1 to x).

Object Variables

Object variable is a variable that represents an entire object, such as a range or worksheet: Dim InputArea As Range. To assign an object to a variable, use the Set keyword: Set InputArea = Range("C16:E16").

Built-in functions

VBA has a number of built-in functions that make calculations and operations easier. For example, the VBA UCase function that converts a string to uppercase is equivalent to Excel functions CAPITAL To use an Excel function in a VBA statement, enter the following expression before the function name:

Application.WorksheetFunction

It is important to understand that you cannot use Excel functions for which VBA provides equivalent functions. For example, VBA does not allow you to access the Excel RQRT (SQRT) function because VBA has its own version of this function: Sqr. So the following statement throws an error:

MsgBox Application.WorksheetFunction.Sqrt(123)

The MsgBox function is one of the most useful in VBA. Among other things, this is an excellent debugging tool because you can insert a MsgBox function at any time to pause the program and display the result of a calculation or assignment. The MsgBox function not only returns a value, but also displays a dialog box in which the user can perform certain actions. The value returned by the MsgBox function is the user's response to the displayed prompt. The MsgBox function can be used even when a user response is not required, but a message is required. MsgBox function syntax:

MsgBox(message[, buttons] [, title] [, help_file, context])

• Message (required argument) - the message that is displayed in the dialog box.
• Buttons (optional argument) - a value that determines which buttons and icons (if necessary) are displayed in the message window. Use built-in constants (for example, vbYesNo).
• Title (optional) - the text that appears in the title bar of the message box. By default, Microsoft Excel text is displayed.
• Help_file (optional argument) - the name of the help file corresponding to the message box.
• Context (optional argument) - the context identifier of the help topic. Represents a specific Help topic to display. If you use the context argument, you should also use the help_file argument.

You can assign the resulting value to a variable or use a function without an assignment operator. In the example below, the result is assigned to the Ans variable.

Ans = MsgBox(" Continue? " , vbYesNo + vbQuestion, " Report ")
If Ans = vbNo Then Exit Sub

Note that the value of the button argument is the sum of two built-in constants (vbYesNo + vbQuestion). The constant vbYesNo displays two buttons in the message box: one labeled Yes and one labeled No. Adding vbQuestion to the argument will also display a question mark. As soon as the first statement is executed, the Ans variable will receive one of two values, represented by the constants vbYes and vbNo. In this example, the procedure ends after clicking the No button.

Managing objects and collections

VBA offers two constructs to help you manage objects and collections more easily. The With - End With construct allows you to perform multiple operations on a single object. To understand how it works, consider the following procedure, which changes six properties of the selected object (assuming the Range object is selected).

Sub ChangeFontl()
Selection.Font.Name = "Cambria"
Selection.Font.Bold = True Selection.Font.Italic = True
Selection.Font.Size = 12
Selection.Font.Underline = xlUnderlineStyleSingle
Selection.Font.ThemeColor = xlThemeColorAccentl
End Sub

This procedure can be rewritten using the With - End With construct. The procedure shown below works exactly the same as the previous one.

Sub ChangeFont2()
With Selection.Font
.Name = "Cambria"
.Bold = True
.Italic = True
.Size = 12
.Underline = xlUnderlineStyleSingle
.ThemeColor = xlThemeColorAccentl
End With
End Sub

Suppose you decide to perform an action on all objects in a collection, or you need to evaluate all objects in a collection and perform an action when certain conditions are met. This is an ideal situation to use the For Each - Next construct. Construction syntax:

For Each element In collection
[instructions]
[instructions]
Next [ element]

For example:

Sub CountSheets()
Dim Item as Worksheet
For Each Item In ActiveWorkbook.Worksheets
MsgBox Item.Name
Next Item
End Sub

The following example closes all windows except the active one:

Sub Closelnactive()
Dim Book as Workbook
For Each Book In Workbooks
If Book.Name<>ActiveWorkbook.Name Then Book.Close
Next Book
End Sub

Code execution control

Some VBA procedures start executing from the first lines of code. However, sometimes it is necessary to control the sequence of operations by skipping certain statements, re-executing some commands, and checking conditions to determine the next action the procedure will perform.

The GoTo statement redirects program execution to a new instruction that is marked in a special way (a text string ending with a colon or a number ending with a space specified before the instruction). The following procedure uses the VBA InputBox function to obtain the username. If the username is different from Howard, then the procedure moves to the WrongName label, where it ends its work. Otherwise, the procedure performs additional operations. The Exit Sub statement ends the execution of a procedure.

Sub GoToDemo()
UserName = InputBox(" Enter your name: ")
If UserName<>"Howard" Then GoTo WrongName
MsgBox("Hi Howard...")
" — [Additional code is entered here] —
Exit Sub
WrongName:
MsgBox "Sorry, this procedure can only be run by Howard."
End Sub

In reality, the GoTo statement is only needed to catch errors (the code above is a bad example that should not be used).

The If-Then construct is probably the most commonly used construct to group VBA statements:

If condition Then instructions_true

For example

Sub GreetMe()
If Time< 0.5 Then
MsgBox "Good morning"
Elself Time >= 0.5 And Time< 0.75 Then
MsgBox "Good afternoon"
Else
MsgBox "Good evening"
End If
End Sub

VBA uses a date and time system similar to that used in Excel. Time of day is expressed as a fraction, for example noon is represented as 0.5. The value 0.75 represents the time 18:00 - three quarters of the day and the moment when day turns into evening. Nested If-Then structures are quite cumbersome. Therefore, it is recommended to use them only for making simple binary decisions. If you need to choose between three or more options, then it is advisable to turn to the Select Case design.

The following procedure uses the VBA WeekDay function to determine whether the current day is Saturday or Sunday (the Weekday function returns 1 or 7). A corresponding message is then displayed.

Sub GreetUserlO
Select Case Weekday(Now)
Case 1, 7
MsgBox "It's the weekend"
Case Else
MsgBox "It's not the weekend"
End Select
End Sub

The VBA interpreter exits the Select Case construct as soon as the True condition is found. Therefore, for maximum efficiency, the most likely case should be tested first.

Cycle is the process of repeating a set of instructions. Perhaps you know in advance how many times the loop should repeat, or this value is determined by variables in the program. The simplest example good cycle - For-Next:

For counter = start That end
[instructions]
[instructions]
Next [ counter]

The following procedure sums the square roots of the first 100 integers:

Sub SumSquareRoots()
Dim Sum As Double
Dim Count As Integer
Sum = 0
For Count = 1 To 100
Sum = Sum + Sqr(Count)
Next Count
MsgBox Sum
End Sub

The value of the Step variable in a For-Next loop can be negative. The following procedure deletes rows 2, 4, 6, 8 and 10 in the active worksheet:

Sub DeleteRows()
Dim RowNum As Long
For RowNum = 10 To 2 Step -2
Rows(RowNum).Delete
Next RowNum
End Sub

For-Next loops can also contain one or more Exit For statements. When the program encounters this statement, it immediately exits the loop:

Sub ExitForDemo()
Dim MaxVal As Double
Dim Row As Long
MaxVal = Application.WorksheetFunction.Max(Range(" A:A "))
For Row = 1 To 1048576
If Cells(Row, 1).Value = MaxVal Then
Exit For
End If
Next Row
MsgBox "Maximum value in row" & Row
Cells(Row, 1).Activate
End Sub

The maximum value in a column is calculated using the Excel MAX function. This value is then assigned to the MaxVal variable. The For-Next loop checks each cell in the column. If a certain cell is equal to MaxVal, the Exit For statement ends the procedure. However, before exiting the loop, the procedure informs the user about the location of the desired cell and activates it.

The Do While loop runs as long as the specified condition is satisfied. A Do While loop can have one of the following two syntaxes:

Do
[instructions]
[instructions]
Loop

Do
[instructions]
[instructions]
Loop

The EnterDates1 procedure enters the dates of the current month into a worksheet column, starting with the active cell:

Sub EnterDatesl()
" Do While loop, condition is checked at the beginning
Dim TheDate As Date
TheDate = DateSerial(Year(Date), Month(Date), 1)
Do While Month(TheDate) = Month(Date)
ActiveCell = TheDate
TheDate = TheDate + 1
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

This procedure uses the TheDate variable, which stores the dates recorded in the worksheet. The first day of the current month is used to initialize the variable. While the loop was running, the value of the TheDate variable was entered into active cell, then this value was increased by one, after which the next cell was activated. The loop runs until the month value assigned to the TheDate variable matches the month value of the current date.

Do While loops can also include one or more Exit Do statements. When the Exit Do statement is reached, the loop ends and control is transferred to the statement following the Loop statement.

The Do Until loop structure has much in common with the Do While loop structure. The only difference is how the loop condition is checked. In the Do While option, the loop runs as long as the condition is true. In a Do Until loop, the loop runs until the condition is true. The Do Until structure can also be represented in two types of syntax.

Based on materials from the book. – M: Dialectics, 2013. – P. 211–251.

Despite the powerful functionality provided user interface Excel, there are a number of tasks that can only be performed programmatically. The book "Using Macros in Excel" is an introduction to Excel VBA programming, containing examples of solving various practical problems that arise when working in Excel.The book's material is intended for Excel users, as well as programmers who are not familiar with object model Excel. Along with the sections on developing macros for Excel 2002, it provides an introductory course on writing macros and programs in the Excel environment.

Book: Intensive Excel Programming Course Over the Weekend

Publisher: Dialectics
Pages: 421
Format: DJVU
Size: 12.6 MB
Quality: Normal
Language: Russian
Genre: programming
Year of publication: 2004
ISBN: 5-8459-0687-3

The capabilities of Microsoft Excel are not limited to working with data tables. Behind the spreadsheet processing tools lies a powerful programming language - VBA (Visual Basic for Applications). However, almost any user has the opportunity to learn how to write programs in VBA to solve the most diverse problems in Excel - from mechanical execution of calculations to creating a system for entering data with its own screen forms and with the possibility of revising the correctness of the entered values.

Book: Programming in VBA 2002

Quality: Normal
Language: Russian
Genre: programming

The book contains a course on programming in Visual Basic for Applications (VBA), which is the base language in Microsoft Office applications (Word, Excel, Access, PowerPoint, FrontPage, Visio, etc.). The book is intended for beginners to program in the Windows environment using Word, Excel, and PowerPoint objects.Part of the book is devoted to the development of Office applications that use databases stored in both separate files, and on remote servers.The book contains enough material to learn the basics of the Visual Basic language and create simple macros that help automate routine, repetitive work with documents, spreadsheets, charts, presentations, etc., as well as to develop quite complex database processing applications using dialog boxes that provide user with the most modern interface tools.Most of the examples in the book are devoted to current issues of commercial activity, so the book will be very useful to managers at various levels, for whom both Microsoft Office and the built-in VBA programming language are apparently intended.
The appendices at the end of the book can serve as a handy reference for working with both VBA and regular VB.

Many books have been written on Excel. But what makes this book special is that it puts spreadsheet application development into a broader context. VBA is just one component of the custom application development environment, albeit a significant one. This book will help you understand the intricacies of application development using VBA. It describes numerous means VBA language, its capabilities and environment of use. First, you will be offered an overview of the program's capabilities, then you will move on to defining the concepts of VBA programming, and then get acquainted with the language itself. If you are a novice VBA programmer, then in this publication you will find everything necessary information, which will be required for further work. If you already have enviable experience working with VBA, then this book will enrich and increase your knowledge, adding new techniques and real-life examples.

PDF version edited and kindly provided by the participant.

Microsoft Excel is much more than just a spreadsheet. With the introduction of the Visual Basic Editor in Excel 97, followed by the significantly improved stability of Excel 2000, Excel became a respected development platform in its own right. Excel applications are now found alongside those based on C++, Java, and the .NET development platform, as part of the core suite of mission-critical corporate applications.
Unfortunately, Excel is still too often thought of as a hobbyist platform, that people only develop Excel applications in their spare time to automate minor tasks. A brief look at many Excel VBA books seems to confirm this opinion. These books focus on the basics of automating Excel tasks using VBA. This book is the first of its kind in providing a detailed explanation of how to use Excel as the platform for developing professional quality applications.
While most other major development platforms seem to have a de facto standard text that explains the commonly agreed best practices for architecting, designing, and developing applications using that platform, until now Excel has not. This book attempts to fill that gap. The authors are professional Excel developers who create Excel-based applications for clients ranging from individuals to the largest multinational corporations. This book explains the approaches we use when designing, developing, distributing, and supporting the applications we write for our clients.

Few people know that the first version of the popular Microsoft product Excel came out 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 with only a small fraction of the capabilities of this spreadsheet processor and do not even realize how the ability to program in Excel could make their life easier.

What is VBA

Programming in Excel is carried out using the Visual Basic for Application programming language, which was originally built into the most famous spreadsheet processor from Microsoft.

Experts attribute its comparative ease of learning to its advantages. As practice shows, even users who do not have professional programming skills can master the basics of VBA. The special features of VBA include script execution in an office application environment.

• go back to the “Macros” line;
• select “Macro 1” from the list;
• click “Run” (the same action is launched by starting the key combination “Ctrl+hh”).

As a result, the action that was carried out during the macro recording process occurs.

It makes sense to see what the code looks like. To do this, go back to the “Macros” line and click “Change” or “Login”. As a result, they end up in the VBA environment. Actually, the macro code itself is located between the lines Sub Macro1() and End Sub.

If copying was performed, for example, from cell A1 to cell C1, then one of the lines of code will look like Range(“C1”).Select. In translation, it looks like “Range(“C1”).Select”, in other words, it moves to cell C1 in VBA Excel.

The active part of the code is completed by the ActiveSheet.Paste command. It means writing the contents of the selected cell (in this case A1) into the selected cell C1.

Example 2

VBA loops help you create various macros in Excel.

VBA loops help you create various macros. Let's assume that there is a function y=x + x2 + 3x3 - cos(x). You need to create a macro to get its graph. This can only be done using VBA loops.

The initial and final values ​​of the function argument are x1=0 and x2=10. In addition, you must enter a constant - the value for the argument change step and the initial value for the counter.

All VBA Excel macro examples are created using the same procedure presented above. In this particular case, the code looks like:

Do While x1< x2 (цикл будeт выполняться пока вeрно выражeниe x1 < x2)

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

Cells(i, 1).Value = x1 (the value of x1 is written to the cell with coordinates (i,1))

Cells(i, 2).Value = y (value y is written to the cell with coordinates (i,2))

i = i + 1 (counter is in effect);

x1 = x1 + shag (the argument changes by the step value);

As a result of running this macro in Excel, we get two columns, the first of which contains the values ​​for x, and the second for y.

Then a graph is built from them in a standard way for Excel.

Example 3

To implement loops in VBA Excel 2010, as in other versions, along with the already given Do While construction, For is used.

Let's look at a program that will create a column. In each of its cells the squares of the corresponding line number will be written. Using the For construct will allow you to write it very briefly, without using a counter.

First you need to create a macro as described above. Next we write down the code itself. We believe that we are interested in the values ​​for 10 cells. The code looks like this.

For i = 1 to 10 Next

The command is translated into “human” language as “Repeat from 1 to 10 in steps of one.”

If the task is to obtain a column with squares, for example, of all odd numbers from the range from 1 to 11, then we write:

For i = 1 to 10 step 1 Next.

Here step is a step. In this case it is equal to two. By default, the absence of this word in the loop means that the step is single.

The results obtained must be saved in cells numbered (i,1). Then, each time the loop starts with i increasing by the step size, the row number will automatically increase. Thus, the code will be optimized.

In general, the code will look like:

For i = 1 To 10 Step 1 (you can simply write For i = 1 To 10)

Cells(i, 1).Value = i ^ 2 (i.e. the value of square i is written to cell (i,1))

Next (in a sense, it plays the role of a counter and means another start of the cycle)

If everything is done correctly, including recording and running the macro (see instructions above), then when you call it each time you will get a column of the specified size (in this case, consisting of 10 cells).

Example 4

In everyday life, often the need arises to make one or another decision depending on some condition. You can’t do without them in VBA Excel. Examples of programs where the further course of execution of the algorithm is chosen and not predetermined initially, most often use the If ...Then construction (for complex cases) If ...Then ...END If.

Let's consider a specific case. Suppose you need to create a macro for Excel so that the following is written in the cell with coordinates (1,1):

1, if the argument is positive;

0 if the argument is zero;

1 if the argument is negative.

Creating such a macro for Excel begins in the standard way, through the use of the Alt and F11 hotkeys. Next the following code is written:

x= Cells(1, 1).Value (this command assigns x the value of the contents of the cell with coordinates (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

All that remains is to run the macro and get the required value for the argument in Excel.

VBA Functions

As you may have already noticed, programming in the most famous Microsoft table processor is not that difficult. Especially if you learn how to use VBA functions. In total, this programming language, created specifically for writing applications in Excel and Word, has about 160 functions. They can be divided into several large groups. This:

• Mathematical functions. By applying them to the argument, they obtain the value of the cosine, natural logarithm, integer part, etc.
• Financial functions. Thanks to their availability and using programming in Excel, you can obtain effective tools for accounting and financial calculations.
• Array processing functions. These include Array, IsArray; LBound; UBound.
• Excel VBA functions for string. This is a fairly large group. It includes, for example, the functions Space to create a string with the number of spaces equal to the integer argument, or Asc to convert characters to ANSI code. All of them are widely used and allow you to work with rows in Excel, creating applications that greatly facilitate working with these tables.
• Data type conversion functions. For example, CVar returns the value of the Expression argument by converting it to the Variant data type.
• Functions for working with dates. They significantly expand the standard capabilities of Excel. Thus, the WeekdayName function returns the name (full or partial) of the day of the week by its number. Even more useful is Timer. It gives the number of seconds that have passed from midnight to a specific point in the day.
• Functions for converting a numeric argument to different number systems. For example, Oct displays numbers in octal representation.
• Formatting functions. The most important of them is Format. It returns a Variant value with an expression formatted according to the instructions specified in the format declaration.
• etc.

Studying the properties of these functions and their application will significantly expand the scope of Excel.

Example 5

Let's try to move on to solving more complex problems. For example:

A paper document reporting the actual level of costs of the enterprise is given. Required:

• develop its template part using the Excel spreadsheet;
• create a VBA program that will request initial data for filling it out, carry out the necessary calculations and fill in the corresponding template cells with them.

Let's consider one of the solution options.

Creating a template

All actions are carried out on a standard sheet in Excel. Free cells are reserved for entering data by month, year, name of the consumer company, amount of costs, their level, turnover. Since the number of companies (societies) for which the report is being compiled is not fixed, the cells for entering values ​​based on the results and the name of the specialist are not reserved in advance. The worksheet is given a new name. For example, "report".

Variables

To write a program to automatically fill out a template, you must select notation. They will be used for variables:

• NN - number of the current table row;
• TP and TF - planned and actual trade turnover;
• SF and SP - actual and planned amount of costs;
• IP and IF - planned and actual level of costs.

Let us use the same letters, but with the “prefix” Itog, to denote the accumulation of the total for this column. For example, ItogTP refers to the table column entitled “planned turnover”.

Solving a problem using VBA programming

Using the introduced notations, we obtain formulas for deviations. If you need to calculate in %, we have (F - P) / P * 100, and in total - (F - P).

The best way to immediately enter the results of these calculations is into the corresponding cells of the Excel table.

For actual and forecast totals, they are obtained using the formulas ItogP=ItogP + P and ItogF=ItogF+ F.

For deviations use = (ItogF - ItogP) / ItogP * 100, if the calculation is carried out as a percentage, and in the case of a total value - (ItogF - ItogP).

The results are again immediately written to the corresponding cells, so there is no need to assign them to variables.

Before running the created program, you need to save the workbook, for example, under the name "Report1.xls".

The “Create reporting table” button only needs to be pressed once after entering the header information. There are other rules you should know. In particular, the “Add row” button must be pressed each time after entering values ​​for each type of activity into the table. After entering all the data, you need to click the “Finish” button and then switch to the Excel window.

Now you know how to solve Excel problems using macros. The ability to use VBA Excel (see examples of programs above) may also be needed to work in the most popular text editor at the moment, Word. In particular, you can, by writing, as shown at the very beginning of the article, or by writing code, create menu buttons, thanks to which many operations on text can be carried out by pressing the duty keys or through the “View” tab and the “Macros” icon.