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.
The disadvantage of the program is the problems associated with the compatibility of different versions. They are due to the fact that the VBA program code refers to the functionality that is present in the new version of the product, but not in the old one. Also cons include the excessively high openness of the code for changes by an outsider. Nevertheless, Microsoft Office, as well as IBM Lotus Symphony, allow the user to use encryption of the initial code and set a password to view it.
Objects, Collections, Properties, and Methods
It is with these concepts that those who are going to work in a VBA environment need to understand. First of all, you need to understand what an object is. In Excel, a sheet, a book, a cell, and a range act as such. These objects have a special hierarchy, i.e. obey each other.
The main one is Application, which corresponds to the Excel program itself. This is followed by Workbooks, Worksheets, as well as Range. For example, to refer to cell A1 on a particular sheet, you must specify the path, taking into account the hierarchy.
As for the concept of “collection”, this is a group of objects of the same class, which in the record looks like ChartObjects. Its 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 show what needs to be done. When writing code in VBA, they must be separated from the object by a dot. For example, as will be shown later, very often when programming in Excel use the Cells (1,1) .Select command. It means that it is necessary to select a cell with coordinates (1,1) i.e. A1.
Together with it, Selection.ClearContents is often used. Its execution means clearing the contents of the selected cell.
How to start
First of all, you need to create a file and save it by assigning a name and choosing the type "Excel workbook with macro support".
Then you need to go to the VB application, for which it’s enough to use the key combination “Alt” and “F11”. Further:
- in the menu bar located at the top of the window, click on the icon next to the Excel icon;
- choose the Mudule team;
- save by clicking on the icon with the image floppy disk ;
- write, let's say, a sketch of the code.
It looks like this:
Sub program ()
'Our code
End sub
Please note that the line “Our code” will be highlighted in a different color (green). The reason is the apostrophe at the beginning of the line, which indicates that a comment follows.
Now you can write any code and create a new tool for yourself in VBA Excel (see examples of programs below). Of course, those who are familiar with the basics of Visual Basic will be much easier. However, even those who do not have them, if desired, will be able to get comfortable quickly enough.
Macros in Excel
Behind this name are programs written in Visual Basic for Application. Thus, programming in Excel is creating macros with the right code. Thanks to this feature, the Microsoft table processor self-develops, adapting to the requirements of a particular user. Once you’ve figured out how to create modules for writing macros, you can begin to look at specific examples of VBA Excel programs. 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 to another.
For this:
- open the tab "View";
- go to the "Macros" icon;
- click on "Record Macro";
- fill in the opened form.
For simplicity, “Macro1” is left in the “Macro Name” field, and, for example, hh is inserted in the “Keyboard Shortcut” field (this means that the program can be launched using the “Ctrl + h” blitz command). Press Enter.
Now that the macro recording has already been started, the contents of one cell are copied to another. Return to the original icon. Click on "Record Macro". This action means the end of the program.
Further:
- again go to the line "Macros";
- select "Macro 1" in the list;
- click "Run" (the same action is launched by the start of the keyboard shortcut "Ctrl + hh").
As a result, an action occurs that was performed during the recording of the macro.
It makes sense to see how the code looks. To do this, again go to the line "Macros" and click "Change" or "Login". As a result, they end up in a VBA environment. Actually, the macro code itself is between the lines of 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 transfers 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 this case, A1) in the selected cell C1.
Example 2
VBA loops help you create various macros in Excel.
VBA loops help you create different macros. Suppose that there is 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.
For the initial and final value of the argument of the function take x1 = 0 and x2 = 10. In addition, you must enter a constant - the value for the step of changing the argument and the initial value for the counter.
All Excel VBA macro examples are created using the same procedure as described above. In this particular case, the code looks like:
Sub programm ()
x1 = 1
x2 = 10
shag = 0.1
i = 1
Do While x1 <x2 (the loop will be executed as long as the expression x1 <x2 is true)
y = x1 + x1 ^ 2 + 3 * x1 ^ 3 - Cos (x1)
Cells (i, 1) .Value = x1 (value x1 is written to the cell with coordinates (i, 1))
Cells (i, 2) .Value = y (y value is written to the cell with coordinates (i, 2))
i = i + 1 (counter operates);
x1 = x1 + shag (the argument changes by the step size);
Loop
End sub.
As a result of running this macro in Excel, we get two columns, the first of which contains values for x, and the second - for y.
Then, a graph is built on them in the manner standard for Excel.
Example 3
To implement loops in VBA Excel 2010, as in other versions, For is used along with the already-constructed Do While construct.
Consider a program that creates a column. In each of its cells squares of the corresponding row number will be recorded. Using the For construct allows you to write it very briefly, without using a counter.
First you need to create a macro as described above. Next, write the code itself. We believe that we are interested in the values for 10 cells. The code is as follows.
For i = 1 to 10 Next
The command is translated into "human" language, as "Repeat from 1 to 10 in increments of one."
If the task is to get 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 stored in cells with the number (i, 1). Then, at each start of the cycle with increasing i by the value of the step, the number of the line will automatically increase. Thus, the code will be optimized.
In general, 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 in cell (i, 1))
Next (in a sense, plays the role of a counter and means another start of the cycle)
End sub.
If everything is done correctly, including recording and running a macro (see instructions above), then when it is called, each time a column of a given size will be obtained (in this case, consisting of 10 cells).
Example 4
In everyday life, very often there is a need to make one or another decision, depending on some condition. You can not do without them in VBA Excel. Examples of programs where the further course of the execution of the algorithm is selected and not predetermined initially, most often use the construction If ... Then (for complex cases) If ... Then ... END If.
Consider a specific case. Suppose you want to create a macro for Excel, so that it 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 starts in the standard way, through the use of the hot keys Alt 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 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
End sub.
It remains to run the macro and get the "Excel" the desired value for the argument.
VBA Functions
As you may have noticed, programming in the most famous Microsoft table processor is not so difficult. Especially if you learn how to use the 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. It:
- Mathematical functions. Applying them to the argument, they get the value of cosine, natural logarithm, integer part, etc.
- Financial functions. Thanks to their availability and using programming in Excel, you can get 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, Space functions to create a string with the number of spaces equal to an integer argument, or Asc to translate characters into ANSI code. All of them are widely used and allow working with strings in Excel, creating applications that greatly facilitate the work with these tables.
- Data type conversion functions. For example, CVar returns the value of an Expression argument, converting it to a Variant data type.
- Functions for working with dates. They greatly expand the standard features of Excel. So, the WeekdayName function returns the name (full or partial) of the day of the week by its number. Even more useful is the Timer. It gives the number of seconds that have passed since midnight to a specific point in the day.
- Functions for converting a numeric argument to different number systems. For example, Oct gives an octal representation of a number.
- Formatting functions. The most important of these is Format. It returns a value of type Variant with an expression formatted according to the instructions given in the format description.
- and so forth
Studying the properties of these functions and their application will significantly expand the scope of Excel.
Example 5
Let's try to solve more complex problems. For instance:
A paper report of the actual level of costs of the enterprise is given. It is required:
- to develop its template part by means of the Excel processor;
- compile a VBA program that will request the source data for its completion, perform the necessary calculations and fill in the corresponding template cells with them.
Let's consider one of the solution options.
Create 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, the sum 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 according to the results and the name of the specialist are not reserved in advance. The worksheet is given a new name. For example, "Օ tchet".
Variables
To write a program to automatically fill out a template, you must select the notation. They will be used for variables:
- NN– current row number of the table;
- TP and TF - planned and actual turnover;
- SF and SP - the actual and planned amount of costs;
- IP and IF are the planned and actual level of costs.
Let us denote by the same letters, but with the Itog prefix, the accumulation of the total for this column. For example, ItogTP refers to a column in a table entitled “planned turnover”.
Solving a problem using VBA programming
Using the introduced notation, we obtain formulas for deviations. If you want to carry out the calculation in%, we have (F - P) / P * 100, and in total - (F - P).
The results of these calculations can best be immediately entered into the corresponding cells of the Excel table.
For totals on the fact and forecast, they are obtained by the formulas ItogP = ItogP + P and ItogF = ItogF + F.
For deviations use = (ItogF - ItogP) / ItogP * 100 if the calculation is carried out in percent, and in the case of the 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 starting the created program, you need to save the workbook, for example, under the name "Report1.xls".
The “Create Reporting Table” key needs to be pressed only 1 time after entering the header information. You should know other rules. In particular, the “Add Row” button should be pressed each time after entering values into the table for each type of activity. 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 tasks for Excel using macros. The ability to use vba excel (examples of programs, see above) may be needed to work in the environment of the most popular text editor "Word" at the moment. 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 the text can be performed by pressing the standby keys or through the "View" tab and the "Macros" icon.