Computers, Software
VBA Excel: examples of programs. Macros in Excel
Few people know that the first version of the popular Microsoft Excel product appeared in 1985. Since then, it has undergone several modifications and is in demand among millions of users around the world. At the same time, many work with only a small part of the capabilities of this table processor and do not even guess how they could make life easier in Excel programming skills.
What is VBA?
Programming in Excel is carried out through the programming language Visual Basic for Application, which is originally built into the most famous table processor from Microsoft.
To its merits, experts attribute the comparative ease of development. As practice shows, users of VBA can master even those who do not have professional programming skills. The peculiarity of VBA is the execution of the script in the environment of office applications.
The disadvantage of the program are the problems associated with the compatibility of different versions. They are due to the fact that the code of the VBA program refers to the functionality that is present in the new version of the product, but not in the old one. Also, the minuses include too much openness of the code for change by an unauthorized person. Nevertheless, Microsoft Office, as well as IBM Lotus Symphony, allow the user to use encryption of the initial code and set a password for viewing it.
Objects, collections, properties, and methods
It is with these concepts that you need to understand who is going to work in the VBA environment. First of all, it is necessary to understand what an object is. In Excel, this is a sheet, a book, a cell, and a range. These objects have a special hierarchy, i.e. Obey each other.
The main one is Application, which corresponds to Excel itself. Then follow Workbooks, Worksheets, and also Range. For example, to access cell A1 on a particular sheet, you must specify a path that includes the hierarchy.
As for the concept of "collection", then this is a group of objects of the same class that in the entry 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, this is Value or Formula.
Methods are commands that show what needs to be done. When writing code in VBA, you need to separate them from the object by a period. For example, as will be shown later, very often when programming in Excel, use the command Cells (1,1) .Select. It means that you need to select a cell with coordinates (1,1) ie A1.
With it, Selection.ClearContents is often used. Performing it 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 selecting the "Excel workbook with macro support" type.
Then you need to go to the VB application, just use the "Alt" and "F11" keys. Further:
- 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 floppy disk;
- Write, let's say, a sketch of the code.
It looks like this:
Sub program ()
'Our code
End Sub
Note that the line "Our code" will be highlighted in a different color (green). The reason is in 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 the 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 they want, can quickly get used to it.
Macros in Excel
For this name, programs written in the Visual Basic for Application language are hidden. Thus, programming in Excel is the creation of macros with the desired code. Thanks to this feature, Microsoft's spreadsheet processor develops itself, adapting itself to the requirements of a particular user. After understanding how to create modules for writing macros, you can start to look at specific examples of VBA Excel programs. It is 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 "View" tab;
- Go to the icon "Macros";
- Press the "Record macro";
- Fill out the opened form.
For simplicity, the "Macro name" field is left with "Macro1", and in the "Keyboard shortcut" field, for example, hh is inserted (this means that you can launch the program with the "Ctrl + h" command). Press Enter.
Now that the macro recording has already started, copy the contents of a cell 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";
- In the list choose "Macro 1";
- Click "Execute" (the same action is started by starting the keyboard shortcut "Ctrl + hh").
As a result, there is an action that was performed during the recording of the macro.
It makes sense to see what the code looks like. To do this, go back to the "Macros" line and click "Edit" or "Login". As a result, they are in the VBA environment. Actually, the macro code itself is located between the lines Sub Macro1 () and End Sub.
If the 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 switches to VBA Excel, in cell C1.
The active part of the code is terminated by the ActiveSheet.Paste command. It means recording the content of the selected cell (in this case A1) in the selected cell C1.
Example 2
VBA cycles help you create various macros in Excel.
VBA cycles 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. You can do this only 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-value for the step of changing the argument and the initial value for the counter.
All examples of VBA Excel macros are created using the same procedure as above. In this particular case, the code looks like:
Sub programm ()
X1 = 1
X2 = 10
Shag = 0.1
I = 1
Do While x1
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 value of y is written in a cell with coordinates (i, 2))
I = i + 1 (the counter operates);
X1 = x1 + shag (the argument is changed 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 in the second - for y.
Then they plot the graph in a way that is standard for Excel.
Example 3
To implement the cycles in VBA Excel 2010, as in other versions, along with the already given Do Do construct, For is used.
Consider a program that creates a column. In each of its cells the squares of the corresponding row number will be recorded. Using the For construct will allow you to write it very shortly, without using a counter.
First you need to create a macro, as described above. Next, we write down the code itself. We think that we are interested in values for 10 cells. The code looks like this.
For i = 1 to 10 Next
The command is translated into "human" language, like "Repeat from 1 to 10 in steps of one".
If the task is to get a column with squares, for example, 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 should be stored in cells with the number (i, 1). Then each time the cycle starts, with increasing i by the step size, the number of the row will also 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 write simply For i = 1 To 10)
Cells (i, 1) .Value = i ^ 2 (that is, the value of the square i) is written in cell (i, 1)
Next (in a sense it plays the role of a counter and means one more cycle start)
End Sub.
If everything is done correctly, including recording and running the macro (see the instruction above), each time it is called, a column of the specified size (in this case consisting of 10 cells) will be obtained.
Example 4
In everyday life, very often there is a need to take a decision depending on some kind of condition. You can not do without them in VBA Excel. Examples of programs where the further course of execution of the algorithm is chosen, rather than predefined initially, most often use the If ... Then construction (for complex cases) If ... Then ... END If.
Let's consider a concrete case. Suppose you need to create a macro for Excel, so that the cell with coordinates (1,1) is written:
1 if the argument is positive;
0 if the argument is zero;
-1 if the argument is negative.
The creation of such a macro for Excel is started in the standard way, by using the "hot" keys Alt and F11. Then the following code is written:
Sub program ()
X = Cells (1, 1) .Value (this command assigns 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 correct value for the argument in Excel.
VBA Functions
As you may have noticed, programming in Microsoft's most famous table processor is not so difficult. Especially if you learn how to use VBA functions. Totally in this programming language, created specifically for writing applications in Excel and Word, there are about 160 functions. They can be divided into several large groups. It:
- Mathematical functions. Applying them to the argument, you get the value of the cosine, the natural logarithm, the whole part, and so on.
- Financial functions. Due to their availability and using programming in Excel, you can get effective tools for accounting and financial accounting.
- Array processing functions. These include Array, IsArray; LBound; UBound.
- VBA Excel functions for a string. This is a fairly large group. It includes, for example, the Space functions to create a string with a number of spaces equal to an integer argument, or Asc to translate characters into ANSI code. All of them are widely used and allow you to work 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 the Expression argument, converting it to a Variant data type.
- Functions of work with dates. They significantly expand the standard features of Excel. So, the function WeekdayName 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 passed from midnight to the specific moment of the day.
- Functions for converting a numeric argument to different number systems. For example, Oct issues 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 that are specified in the format description.
- And others.
The study of the properties of these functions and their application will significantly expand the scope of application of Excel.
Example 5
Let's try to solve more complicated problems. For example:
Given a paper document of the report of the actual level of costs of the enterprise. Required:
- To develop its template part by means of a table processor "Excel";
- Create a VBA program that will query the source data to fill it, make the necessary calculations and fill in the corresponding cells of the template.
Let us consider one of the variants of the solution.
Creating a Template
All actions are performed on a standard sheet in Excel. Reserved free cells for entering data for the month, year, the name of the company-consumer, the amount of costs, their level, turnover. Since the number of companies (companies) with respect to which the report is drawn up is not fixed, the cells for making values based on the results and the name of the specialist are not previously reserved. The work sheet is given a new name. For example, "Օ tchet".
Variables
To write a program to automatically fill in the template, you must select the symbols. They will be used for variables:
- NN- number of the current row of the table;
- TP and TF - planned and actual turnover;
- SF and SP - the actual and planned amount of costs;
- IP and IF - the planned and actual level of costs.
Denote by the same letters, but with the "prefix" Itog the accumulation of the total for this column. For example, ItogTP refers to the column of a table entitled "planned turnover".
Problem Solving Using VBA Programming
Using the notation introduced, we obtain formulas for deviations. If you want to calculate in%, you have (F - P) / P * 100, and in the sum - (F - P).
The results of these calculations can best be immediately added to the corresponding cells of the Excel table.
For results in fact and the forecast is obtained by the formulas ItogP = ItogP + P and ItogF = ItogF + F.
For deviations use = (ItogF - ItogP) / ItogP * 100, if the calculation is in percentages, and in the case of the total value - (ItogF - ItogP).
The results are immediately written to the corresponding cells, so there is no need to assign them to variables.
Before starting the created program, it is required to save the workbook, for example, under the name "Report1.xls".
The "Create report table" button needs to be pressed only once after entering the header information. You should know other rules. In particular, the button "Add line" should be pressed each time after you enter values for each activity in 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 tasks for Excel using macros. The ability to apply vba excel (examples of programs, see above) may be needed to work in the environment of the most popular text editor "Vord". In particular, you can write, as shown at the very beginning of the article, or through the writing of code create menu buttons, thanks to which many operations on the text can be performed by pressing the keys or through the "View" tab and the "Macros" icon.
Similar articles
Trending Now