VBA Excel: Sample Programs. Macros in Excel. Sample Visual Basic for ApplicationVBA Tasks Tutor Help

The content of this page will continue to grow...

1. Program-calculator "Raising numbers to large powers".
   

   Implement exact calculation of exponentiation to great degrees single digit numbers (from 2 to 9) using long arithmetic.

2. From the forum by Jonny323. VBA word

   Hi all. I really need a macro, I think it's not difficult: Print the first page in 6 copies and the 2nd page in 2 copies. Thank you in advance.

3. ProgressBar on the form. VBA Excel

   A control that shows the progress of the program (how much work has been done from 100%)...

4. Statistical calculations in the selected text. VBA word

   Create a macro in Word that will determine the number of words, commas and periods in arbitrary text.
   Words are groups of characters that are separated by spaces (one or more) and do not contain spaces within themselves. Dots or commas cannot be inside a word. Dots do not count as dots...

5. Statistical calculations in the text of the document. Using UserForm to enter data. VBA word

   Create in Word program- In the form, the user enters a word or phrase, and the program scans the entire text and inserts the entered word (phrase) at the end of the document, next to which are listed all the page numbers on which this word occurs. Matching page numbers are not allowed. That is, if the specified word occurs three times on page 5, the number 5 appears only once in the output. The form should consist of the following components:
   - 1 TextBox for entering a word (phrase);
   - 1 button to exit the program;
   - 1 button to start the search and insert the result into the document;
   - Label type component with explanatory text above the input line;
   - Besides, on the form there can be components of type Frame;
   

6. Working with ListBox (Excel). Binding, sorting, multiple selection...

7. Program for solving Japanese Sudoku puzzle (Excel)

8. Query execution macros (i.e. generating a new table from an Excel database)

9. Hoare sort (algorithm from wikipedia.org)

   One of the most fast methods sorting.

10. Merge Sort (using Hoare)

    One of the fastest and most optimal sorting methods.

11. VBA Excel: Human Biorhythm Calculation Program

12. For blackarrow from programmersforum.ru

13. Determine which coordinate quarter
    a point with given coordinates A(a,b) falls. VBA Excel.

14. Representing an integer in words VBA EXCEL.

15. LOGICS.

    Solution of a quadratic equation.

16. VBA Excel.

17. VBA Excel. Triangle problem. Array, loop, logic.

    By given dimensions three sides of a triangle - determine its type (rectangular, obtuse, acute or impossible to build).

18. Alarm clock in Excel

19. VBA Excel.

20. VBA Excel.

21. VBA Excel.

    A macro that, in the selected range, represents All negative numbers in blue, positive - in red, zero - in white (or yellow).

22. VBA Excel. How to hide or show sheet items.

    Sometimes, it is required to hide some features from the user (as a rule, data placement is hidden):
    - visit other sheets of this book (it is better not to know about them);
    - scroll (horizontally and vertically) the sheet
    - read and change formulas in sheet cells
    - hide column and row lines
    - hide headers (names and numbers) of columns and rows

23. VBA Excel. Add and remove menu item.

    It's easy to add (remove) your own item to the Excel menu. It's all described in the help.
    But it is much more interesting to entrust this work to a macro.
    Then it becomes possible to form a special, individual menu when opening a specific document, and after closing it, bring the Excel menu to original state. (This example code works only in MS Excel 2003, and in 2007 changes in the names of control panels are needed)

24. Cellular automata VBA EXCEL.

25. Golden Section Method VBA EXCEL.

26. Replacing the maximum and minimum values ​​of the cells of the selected range. VBA Excel

    develop a program or custom form for finding and rearranging elements with the maximum and minimum value of the selected range of the worksheet

27. A string function that returns the squares of prime numbers from zero to the given number. VBA Excel

    Compose in excel function, which takes a natural number N and returns the squares of all prime numbers from 0 to a given natural N.

28. User form (UserForm) for entering data on commercial bank loans. VBA Excel

    Organize the input of information into a table containing data on the issuance of long-term loans by a commercial bank with the following data: code of the loan recipient, name of the recipient of the loan, date of issue of the loan, term (3-4 terms) for which the loan was issued (in days), percentage, amount loan, the date of payment of interest on the loan.

29. User form (UserForm) for entering data on Registration of tourists. VBA Excel

    at this very place...

We briefly got acquainted with recording a macro using the Excel recorder. Now we will delve into this jungle and write a couple of simple macros as an example. In one of the examples, we will traditionally demonstrate how to work with the “Message” type dialog box, in the other we will expand our dialog box.

Example 1: A simple msgBox dialog message in VBA

We will not deviate from the traditions of the beginning of all programming examples. Let's write a macro that, when run, will give us a message box with the inscription "Hello World". At the same time, consider working with custom dialogs.

Now we will write macros only in manual mode, no recorders!

So, in order to create a macro, you need to open the window of your favorite Visual Basic (VB) editor. To do this, perform the following steps: Service-Macro-EditorVisualBasic(Alt+F11).

The MS Visual Basic Editor window will open.

If you do not have left windows, then you need to enable them. To do this, press F4 - Opens the properties window Propertieswindows, and the keyboard shortcut Ctrl + R - opens a window projectexplorer. Without these windows it is difficult to work in the future. All! Done.

What do we see in the window project? This window reflects just the same structure of our book. Book objects - Sheet1, 2, 3, This book. We will study these objects in more detail in subsequent articles, but for now I return to the example.

What do you need to do to start writing code? You need to create a module. Note: In general, in the future I recommend separating the handler code into different modules. This will make the code easier to understand and create order in the structure.

Create a module: Insert-Module

An empty module window opened before us, reminiscent of a notepad. We already saw such a window when we recorded the first macro in . According to the rules of "good manners" let's give a name to our module, let's call it " macrobook". To do this, go to the properties window and enter a name in the field (name)

Go to the code editing window and write the following:

Ready! Opening the working window excel books, press Alt+F8 and see our macro "Hello"

All that remains is " Run". As a result of the macro, we will receive a message of the following form and content:

Example 2: Extended dialog message msgBox in VBA

Consider another type of dialog messages that contain additional buttons"Yes", "No", "Cancel"

Let's move on to the code of our "Hello" macro and add to the command msgbox following:

MsgBox"Hello, World!", vbYesNoCancel, "My macro"

Note that when you put a comma after "Hello, World!", you should have a list of all available dialog box types.

Experiment with each to understand.

Let's run our macro

Now we have a completely different form of communication.

That's all for now. Stay tuned for articles and tutorials. If you have any questions, I will be happy to answer, and if necessary, I will write an article with an explanation.

Video: An example of how msgbox works in vba

MZTools - 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:

• Automatic insertion of error handlers into modules
• Inserting comments in the procedure header, with automatic insertion time, module name and procedure, the ability to specify the procedure's author
• Automatic code line numbering
• Removing numbering from lines of code
• Generating Frequently Used Code Templates
• Own clipboard for 9 operations with the possibility of codes and the subsequent insertion of any of the 9 copied codes
• and many more useful

The program is distributed free of charge.

(37.3 KiB, 3,708 downloads)

VBE Tools - Many of you have had to write code for a form, and then remember that some element was not named as you wanted, or you just wanted to rename it due to a change in the code? And, of course, they faced the problem that when renaming an element, you have to go into the code and replace all references to this element with a new name there. So, with the help of this program, you can rename the name of any form element (TextBox, Label, etc.) and ActiveX controls on Excel sheet, and the program itself will replace all links in the code from the old name to the new one. Very comfortably.

The program is distributed free of charge.

Few people know that the first version of the popular Microsoft product Excel appeared in 1985. Since then, it has gone through several modifications and is in demand by millions of users around the world. At the same time, many people work with only a small fraction of the capabilities of this spreadsheet processor and do not even know 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 is originally built into the most famous spreadsheet processor from Microsoft.

Specialists refer to its advantages as a comparative ease of development. As practice shows, even users who do not have professional programming skills can master the basics of VBA. The features of VBA include the execution of a script in an office application environment.

• again go to the line "Macros";
• 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 takes place, which was carried out in the process of recording a macro.

It makes sense to see what the code looks like. To do this, go back to the "Macros" line and click "Change" or "Enter". As a result, they end up in a 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, this looks like “Range (“C1”). Select”, in other words, it switches to VBA Excel, to cell C1.

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) to the selected cell C1.

Example 2

VBA loops help you create various macros in Excel.

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

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

All VBA Excel macro examples are created using the same procedure as 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 (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 active);

x1 = x1 + step (the argument is changed 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 on them in a way that is standard for Excel.

Example 3

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

Consider a program that will create a column. In each of its cells, the squares of the number of the corresponding line 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, write down the code itself. We consider 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 increments 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 cycle means that the step is single.

The results obtained must be saved in cells with the number (i,1). Then at each start of the cycle with an increase in i by the step size, the row number will automatically grow. Thus, code optimization will occur.

In general, the code will look like:

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

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

Next (in a sense, 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 each time it is called, a column of the specified 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't do without them in VBA Excel. Examples of programs, where the further course of the algorithm execution 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 cell with coordinates (1,1) is written:

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:

x= Cells(1, 1).Value (this command assigns the x 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

It remains to run the macro and get the desired value for the argument in Excel.

VBA Functions

As you may have 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 get the value of the cosine, natural logarithm, integer part, etc.
• Financial functions. Thanks to their presence and using programming in Excel, you can get effective tools for accounting and financial calculations.
• Array processing functions. These include Array, IsArray; lBound; UBound.
• VBA Excel functions for row. This is a fairly large group. It includes, for example, functions Space to create a string with the number of spaces equal to an integer argument, or Asc to convert characters to ANSI code. All of them have a wide application 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 an Expression argument by converting it to a Variant data type.
• Functions for working with dates. They significantly expand the standard capabilities 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 elapsed from midnight to a particular time in the day.
• Functions for converting a numeric argument to different number systems. For example, Oct returns a number in octal.
• Formatting functions. The most important of them is Format. It returns a value of type Variant with an expression formatted according to the instructions given in the format declaration.
• etc.

The study of 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 of the report of the actual level of costs of the enterprise is given. Required:

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

Let's consider one of the solutions.

Create a template

All actions are carried out on a standard sheet in Excel. Free cells are reserved for entering data on the month, year, name of the consumer company, the amount of costs, their level, and turnover. Since the number of companies (companies) in relation to which the report is compiled is not fixed, the cells for entering values ​​based on the results and the full 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 in a template, you need to choose notation. 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 - actual and planned amount of costs;
• IP and IF - planned and actual level of costs.

Let's designate by the same letters, but with "prefix" Itog accumulation of total on the given column. For example, ItogTP - refers to the column of the table, entitled as "planned turnover".

Solving a problem using VBA programming

Using the introduced notation, we obtain formulas for deviations. If it is required 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 appropriate cells of the Excel spreadsheet.

For actual and forecast totals, it is 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 needs to be pressed only 1 time after entering the header information. There are other rules to be aware of. In particular, the "Add Row" button must be pressed each time after entering values ​​for each type of 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 problems for Excel using macros. The ability to use vba excel (see examples of programs above) may also be needed to work in the environment of the currently most popular text editor "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 performed by pressing the on-duty keys or through the "View" tab and the "Macros" icon.