The article is intended for people who want to learn how to write programs using Visual Basic (VBA) built into Excel, but have absolutely no idea what it is.
To begin with, a few words about why this is needed. The VBA tool in MS Excel provides us with a universal tool for quickly and accurately solving any individual user tasks in MS Excel. You can, of course, use the ones built into MS Excel functions of which there are a great many, but they do not always solve the problem.
So, let's create for example the simplest program. We will use MS Excel 2007. Open MS Excel, click “save as” and save your program file by clicking “Excel workbook with macro support”.
Next, you need to enable the "Developer" tab. To do this, click "Excel Options" 
Check the box "Show Developer tab on the ribbon"
After that, on the ribbon, at the top of the Excel sheet, the “Developer” tab will appear, which contains tools for creating VBA macros.
Let's imagine a small task - let's say we have 2 numbers, we need to add them and, using the resulting sum, get the value from our table.
Let's put the following values in the cells of Sheet1:
on Sheet2 fill the cells, creating a table of 2 columns
Next, go to Sheet1, click on the “Developer” tab, “Insert”, select the button on it 
and draw a button on Sheet1, after which the “Assign macro to object” window will immediately appear, in which we select “Create” 
After this, the Visual Basic editor will open and the name of the procedure that will be executed when the button is pressed will be automatically written. Under the name of the procedure, enter the following code:
The code will do the following:
- MsgBox ("This is my first Macro!") - message
- The variable q is assigned the value of a cell on Sheet1, with coordinates 2 row, 2 column
- The variable w is assigned the value of a cell on Sheet1, with coordinates 3 row, 2 column
- In a cell on Sheet1, with coordinates 4 row, 2 column, the sum q+w is written
Next, we get the value of column B from Sheet2, which is located on the same line where the value of our sum coincides with the value of column A.
Let's enter the following code:
and when you click the button we get the following result:
The result shows that the macro selected a number from the table on Sheet2 in accordance with our sum.
I won't go into detail about this tricky code, since the purpose of this article is to start writing macros. There are a lot of resources for VBA on the Internet, with examples and explanations, although the amount of information in the help is quite enough to automate the calculations.
Thus, using VBA it is possible to automate calculations of any complexity and sequence. Reference tables can be copied from various literature onto separate Excel sheets and write a sequential calculation with buttons.
Topic 2.3. Presentation software and office programming basics
Topic 2.4. Database management systems and expert systems
2.4.11. Training base data with the main button form "Training_students" - Download
VBA Programming and Macros
2.3. Presentation software and office programming basics
2.3.7. Office Programming Basics
BASIC visual programming language for applications: Visual Basic for Applications (VBA)
VBA is a subset visual language Visual Basic (VB) programming, which includes almost all of the tools for creating VB applications.
VBA differs from the VB programming language in that VBA is designed to work directly with Office objects and cannot be used to create a project independent of Office applications. Thus, in VBA language programming is VB, and the programming environment is implemented in the form of a VB editor, which can be activated from any MS Office application.
For example, in order to open the VBA editor from PowerPoint, you need to run the command Tools / Macro / VBA Editor. You can return from the editor to the application by selecting the Microsoft PowerPoint command in the View menu or by pressing the Alt + F11 key combination.
Using the built-in VBA editor set of controls and form editor, the user can create user interface for a project being developed with a screen form. Controls are objects, and each object has a set of possible events (for example, a click or double click mouse, key press, dragging an object, etc.).
Each event manifests itself in certain program actions (responses, reactions). A user form allows you to create application dialog windows. The VBA programming language is used to write program code, such as creating user functions in Excel.
The fact that the VBA programming system is designed to work with Office objects allows it to be effectively used to automate development activities various types documents.
Let's look at the algorithm for creating user functions in VBA:
1. Open the VBA code editor window by executing the command Tools / Macro / Visual Basic Editor or pressing Alt+F11.
2. Select the editor menu item Insert / Module.
3. Next, perform Insert / Procedure. In the Add Procedure dialog box that opens, enter the name of the function (for example, SUM5) and set the switches: Type – to the Function position; Scope (Scope) - to the position Public (General) and click OK.
Rice. 1.
4. In the editor window for VBA programming, a function template will appear: title - Public Function SUM5() and ending - End Function, between which you need to place the function body code.
Rice. 2.
5. Next, enter a list of function parameters, for example, in parentheses we indicate (x, y, z, i, j), the data type (for accurate calculations) and the type of value returned by the function (in in this example we will not enter). In addition, we introduce the body of the function, for example, SUM5 = x + y + z + i + j. As a result, we get the following program text:
Public Function SUM5(x, y, z, i, j)
SUM5 = x + y + z + i + j
End Function
6. Returning to the window Excel applications, in which, for example, we need to sum five values.
7. Execute “Insert/Function” and in the function wizard window that opens, select the “User-defined” category, and in the “Select a function” window, select SUM5 and click OK.
Rice. 3.
Rice. 4.
Macros
Creating a Macro
Let's create a macro to automatically sum two numbers in cells A1, B1 and place the result in cell C1, as well as fill cell C1 with turquoise color.
Algorithm for creating a macro for the task:
1. Select Tools/Macro, Start Recording.
2. In the Macro name field, enter a name for the macro. The first character of the macro name must be a letter. Spaces are not allowed in the macro name; You can use underscores as word separators.
3. To run a macro using a keyboard shortcut, enter a letter in the Keyboard shortcut field. Acceptable use CTRL combinations+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for capital letters), where letter is any letter key on the keyboard. Do not select a standard keyboard shortcut because the selected keyboard shortcut overrides the standard shortcuts Microsoft keys Excel at that time with this book.
4. In the "Save" field, select the workbook in which you want to save the macro. Save the macro to "This Workbook". To create brief description macro, enter the required text in the Description field. The screenshot shows an example of filling out the “Record Macro” dialog box
Rice. 5.
5. Click OK.
6. Execute the macro commands you want to record.
Rice. 6.
7. Click the Stop Recording button on the floating OS toolbar (Stop Recording) or Tools / Macro / Stop Macro.
When a macro has finished recording, it appears in the list of macros under its name.
The VBA editor automatically recorded a sequence of macro commands or a program based on the actions performed (Fig. 7.).
Rice. 7.
To call a macro, you must execute the command Tools / Macro / Macros. After that, in the dialog box with a list of macros, you can find it by name and click the Run button.
Assigning a toolbar button to run a macro
The macro can be launched using the button on the built-in toolbar; to do this, you need to do:
- Select Settings from the Tools menu.
- In the Settings dialog box, select the Commands tab and select the Macros option in the Categories list, and select “Custom Button” in the Commands list.
- From the Commands list, use your mouse to drag the custom button onto the toolbar.
- Click this button right click mouse and select the Assign macro to command context menu.
- Enter a name for the macro in the Macro name field.
Assigning an area of a graphic object to run a macro:
- Create a graphic object.
- Apply the context menu to the selected graphic object.
- Select the Assign macro command from the context menu.
- In the Assign Macro to Object dialog box that appears, enter the name of the macro in the Macro name field, then click OK.
Editing a macro is carried out using the VBA editor; to do this, you must do the following:
- Select the command Tools / Macros / Macros.
- Select the name of the macro you want to change from the Name list.
- Click the Edit button, a Visual Basic window will open, in which you can edit the commands of the selected macro written in Visual Basic.
Removing a macro:
- From the Tools menu, select Macros, and then select Macros.
- In the list of macros in the current workbook, you must select the macro that you want to delete and click the Delete button.
Renaming a Macro
To rename a macro, you must enter macro editing mode and change the title in the program text. The new name will automatically replace the old one in the macro lists, and by keys quick call the macro will be called with the new name.
With tables or other Office objects, situations often arise when you need to do the same actions. This not only takes up extra time, but is also very annoying. Luckily, there are macros to solve this problem.
Designed for creating macros software environment VBA, but you don't have to be a programmer or learn Visual Basic for Application to learn how to write them. For this purpose, there are specially developed tools that create VBA code at your command, without requiring additional knowledge from you. However, mastering this language is not that difficult.
Macros are created in applications using recording tools. In this case, a certain key combination is assigned the desired sequence of actions. Open an Office application. Select the fragment you will format.
Select the menu item “Tools” -> “Macro” -> “Start Recording” (in Office 2007 – “View” -> “Macros” -> “Record Macro”). In the “Record Macro” window that appears, set the name of the new macro; by default it is “Macro1”, but it is better to give it a name, especially if there will be several macros. Maximum size name fields – 255 characters, dot and space characters are not allowed.
Decide on the choice of button or key combination that your macro will use in the future. Try to choose the most convenient option, especially if it is intended for frequent use. Select the appropriate item in the “Assign macro” field: “button” or “keys”.
If you select "button", the "Quick Settings" window will open. When you select “keys”, you just need to enter the combination on the keyboard. Review "Current Combinations" to avoid repetition. Click "Assign".
The macro created in Word and PowerPoint will be valid for all documents in the future. To make an Excel macro available to all documents, save it in the personal.xls file, which runs automatically when you open the application. Execute the command “Window” -> “Display” and select the line with the name of the file personal.xls in the window that appears.
Enter a description of the macro in the Description field. Click OK and you'll be back in your document, but you can now see a recording icon on your mouse cursor. Formatt the text with the sequence of actions you want to automate. Be very careful and do not do unnecessary actions, because the macro will record all of them, and this will affect its execution time in the future.
Execute the command “Tools” -> “Macro” -> “Stop recording”. You created a VBA object without writing a single line of code yourself. However, if you still need to make changes manually, enter the object through the “Macros” section, the “Change” command, or using the Alt+F8 key combination.
Excel's capabilities are not limited to the set of built-in functions. By writing macros, you can create your own functions to perform non-standard tasks in Excel.
For example, a self-written macro can be attached to an icon and displayed on the Menu Ribbon. Or you can create custom function(UDF) and use it in the same way as other built-in Excel functions.
A macro is computer code written for Excel in the Visual Basic for Applications (VBA) programming language. Basic Concepts The VBA programming language is discussed on our website in the VBA Tutorial. However, before you start writing VBA code, we recommend that you take a look at the lessons that cover Excel macro security and the Visual Basic editor.
Setting permission to use macros in Excel
Excel has built-in protection against viruses that can enter your computer through macros. If you want to run in Excel workbook macro, make sure your security settings are configured correctly.
Visual Basic Editor
Recording Macros
Excel's macro recording tools are a great way to efficiently complete simple, repetitive tasks. It can also be used as an aid when writing more complex macros.
Excel VBA Tutorial
For those who are just starting to learn a programming language Excel VBA, we offer a short introductory course on Visual Basic for Applications.
To automate repetitive tasks in Microsoft Excel You can quickly record a macro. Let's say you have dates in different formats and you want to apply the same format to all of them. This can be done using a macro. You can record a macro that applies required format and then run it when needed.
When you record a macro, all actions described in Visual Basic for Applications (VBA) code are recorded. These actions may include entering text or numbers, clicking cells or commands on the ribbon or menu, formatting cells, rows or columns, and importing data from an external source, e.g. Microsoft Access. A Visual Basic Application (VBA) is a subset of the powerful Visual Basic programming language that is included with most Office applications. Although VBA provides the ability to automate processes between Office applications, you don't need to know VBA code or software programming if you need it.
It's important to know that when you record a macro, almost everything you do is recorded. So if you make a mistake, such as pressing the wrong button, the macro recorder will record that action. In this case, you can write the entire sequence again or change the VBA code. Therefore, before recording the process, you should work it out well. The more accurately you record the sequence, the more efficiently the macro will work.
Developer, which is hidden by default, so you need to enable it first. For more information, see Display the Developer Tab.
Record a macro
On the tab Developer click Macros to view the macros associated with the workbook. Alternatively, you can press the keys ALT+F8. This will open a dialog box Macro.
Attention:
|
Learn about macro security settings and their meaning. |
||
|
Macros can be run in various ways, for example using a keyboard shortcut, graphic object, panel quick access, buttons or even when opening a book. |
||
|
You can use the Visual Basic Editor to edit macros that are attached to a workbook. |
||
|
assign macro. In the field Assign macro |
||
|
Learn how to enable or disable macros in Office files. |
||
|
Press the keys ALT+F11. |
||
Working with Recorded Code in the Visual Basic Editor (VBE)
With the Visual Basic Editor (VBE), you can add your own variables, control structures, and other elements to recorded code that the macro recorder does not support. Since the macro recorder captures almost every step performed during recording, you may also need to remove unnecessary code. Reviewing recorded code is a great way to learn VBA programming or hone your skills.
An example of changing the recorded code can be found in the article Getting started with VBA in Excel.
Record a macro
Before recording macros, it is useful to know the following:
A macro written to work with an Excel range will only run on cells in that range. So if you add to the range new line, the macro will not apply to it.
If you need to record a long sequence of tasks, we recommend using several smaller macros instead.
A macro can also contain non-Excel tasks. The macro process can span other Office applications and other programs that support Visual Basic for Applications (VBA). For example, you can record a macro that first updates a table in Excel and then opens Outlook to email it.
Macros and VBA tools are located on the tab Developer, which is hidden by default, so you need to enable it first.
Go to settings > Excel... Toolbar & > Ribbons.
To record a macro, follow the instructions below.
Working with macros recorded in Excel
On the tab Developer click Macros to view the macros associated with the workbook. This will open a dialog box Macro.
Note: Macros cannot be canceled. Before you run a recorded macro for the first time, save or create a copy of the workbook to prevent unwanted changes. If you are not satisfied with the results of the macro, you can close the workbook without saving it.
Below are additional information about working with macros in Excel.
|
Learn how to enable or disable macros in Excel for Mac. |
||
|
If a workbook contains a VBA macro that you want to use elsewhere, you can copy that module to another workbook using the editor Microsoft Visual Basic. |
||
|
Assigning a macro to an object, shape, or graphic element |
In a worksheet, right-click the object, picture, shape, or element to which you want to assign an existing macro, and then choose assign macro. In the field Assign macro select the macro you want to assign. |
|
|
You can assign a macro to an icon and add it to your Quick Access Toolbar or Ribbon. |
||
|
You can assign macros to forms and ActiveX controls on a worksheet. |
||
|
Opening the Visual Basic Editor |
On the tab Developer click Visual Basic or select Service > Macro > Visual Basic Editor. |
|
|
Learn how to find help for Visual Basic elements. |
More information
You can always ask a question to an Excel Tech Community specialist, ask for help in the Answers community, and also suggest new feature or improvement on the website
