Excel's capabilities are not limited to a 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 just like the rest of Excel's built-in functions.

A macro is a computer code written for Excel in a programming language Visual Basic for Applications (VBA). The basic concepts of the VBA programming language are covered on our website in the VBA Tutorial. However, before you start writing VBA code, we recommend that you read the lessons that discuss the security of Excel macros 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 a macro in an Excel workbook, make sure the security settings are set correctly.

Visual Basic Editor

Macro recording

The Excel macro recording tool is a great way to efficiently perform simple repetitive tasks. It can also be used as an aid in writing more complex macros.

Excel VBA Tutorial

For those who are just starting to learn the Excel VBA programming language, we offer a small introductory course on Visual Basic for Applications.

A macro in Excel is a small program (script) written in VBA language(Visual Basic for Applications) varieties of the Basic language (Basic).

Macros, as a rule, prescribe a sequence of actions with data in an Excel spreadsheet.

This is very convenient if you have to perform the same sequence of data operations many times. It is enough to record a macro once and run it every time you want to perform the necessary actions. When you run a macro, the program itself will perform all the necessary calculations and process the information in the table.

How to create a simple macro.

There are two ways to create macros in Excel:

Using the "Record macro" option;
Write a VBA macro in the macro editor and apply it to a document.

The easiest way is "Recording a macro", and we will consider it.

First you need to enable the Developer tab if it is not enabled.

Need to click right click click on the toolbar, select "customize the ribbon", in the ribbon settings, check the box next to the "Developer" panel and click "OK".

After you have enabled the developer panel, you can start recording a macro.

To record a macro, you should:

- when saved in "This Book", the macro will work only in the current document;

- when saved in the "Personal Book", the macro will work in all documents on your computer.

You can add a macro description to help you remember what the macro does.
Press "OK".
If you have not specified a keyboard shortcut, recording will start immediately after pressing the OK button.
When recording is in progress, you must perform the required sequence of actions.
When you're done, click the Stop Recording button.

Recorded macros are displayed in the macro book.

To view them, click the "macros" button. A list of macros will appear in the window that appears. Select the desired macro and click Run.

Macros in the book can be edited. To do this, select a macro and click the "Edit" button. When you click on the "Edit" button, the macro editor will open with a script written in the VBA language.

Let's try to edit the macro.

For example, continue it for a few more cells.

After the adjustments, close the editor and try to run the macro again.

Today I again present to your attention an article by Mikhail Konstantinovich Chernyakov, who will talk about working with macros.

As you work through Excel 2010 documents, you may find yourself doing the same tasks over and over again. Some of them (such as saving and closing files) are quite fast, others involve a series of steps that require some time and effort. Instead of doing the same steps manually every time, you can create MACRO-SYS, which are programs that automate the execution of a given sequence of actions.

Before you start creating a macro, you need to carefully consider the procedure that it should automate. Because the modern computers are characterized by high speed, extra steps practically do not affect the result, however, skipping the necessary steps will require rewriting the macro. Once you have planned out all the steps that the macro should take, click the arrow on the button Macros In chapter Macros tab View Macro Recording. A dialog box will appear

Creating macros

1. Enter a macro name, for example, Multiplication table in the designated field.

2. Add the letter "y" to the keyboard shortcut.

3. In the description, add the text " Multiplication table up to 100»

4. Click on the button OK.

5. Now you can perform the actions that you want to record as a macro, for example, create a multiplication table for 10.

6. In cell A2, enter 1, select this cell and drag it down 9 cells. Optionally, specify a checkbox in the autocomplete options Fill.

7. In cell B1, enter 1, select this cell and drag it 9 cells to the right. Optionally, specify a checkbox in the autocomplete options Fill.

8. Enter the formula in cell B2: =$A2*B$1. Select it and drag first 9 cells down, and then the selected column 9 columns to the right. When finished, click on the button arrow Macros tab View Stop Recording

Similarly, you can create other macros, for example, to clear the worksheet:

Delete In chapter cells tab View, and then click on the command Macro Recording.

2. Enter a name for the macro, for example, cleaning in the field provided for this and add the letter " O».

3. Select a range of cells A1:K11.

Macros In chapter Macros tab home, and then click on the command Delete cells - Delete with shift up.

5. Click the button arrow Macros tab View, and then click on the command Stop Recording.

Running, modifying, and deleting a macro

Click on the button arrow Macros In chapter Macros tab View, and then click on the command Macros. A dialog box will appear Macro.

1. To run Macro and click on the button Run.

2. To change existing macro, you can simply delete it and record it again.

3. If a minor change is required, you can open the macro in VBA editor and make changes to the macro code. To do this, just select it in the dialog box Macro and click on the button Change.

4. To delete macro, just select it in the dialog box Macro and click on the button Delete.

Editing macros

Editing macros is carried out by means MicrosoftVisualBasicforApplications (VBA) . The editor can be launched with the command VisualBasic in Group Code tabs Developer or by pressing keys alt+ F11 .

Adding Macro Buttons to Tabs

New user interface Excel 2010 allows you to create and add custom tabs, groups, and commands, including macros.

1. To do this, right-click to call the context menu of any tab, for example, Developer, and choose a command Ribbon customization.

2. Select a tab, for example, Developer, and press the button Add a group.

3. Press the button Rename and enter in the field Display name Name new group, For example, Macros OK.

4. From the list Select teams click Macros.

5. Find macro Multiplication table and button Add Macros.

6. Press the button Rename and enter in the field Display name Multiplication table, select a symbol and press the button OK.

7. Find macro Clear and button Add include him in the created group Macros.

8. Press the button Rename and enter in the field Display name the name of the new team, for example, Clear, select a symbol and press the button OK.

9. After pressing the button OK of the Excel Options dialog box on the tab Developer band appear Macros with teams Multiplication table And Clear.

Rice. 4. Adding Macro Buttons to the Developer Tab

Adding a macro button to a panel quick access

The new user interface in Excel 2010 allows you to quickly find built-in commands, but it will take a few seconds to run a macro using a dialog box. Macro. Excel 2010 has several ways to make macros more accessible.

You can make the dialog box easier to access Macro by adding a button Macros to the Quick Access Toolbar.

1. To do this, right-click on the button arrow Macros In chapter Macros tab View.

2. In context menu click on command Add to Quick Access Toolbar.

3. A button will appear on the Quick Access Toolbar. Macros, which, when clicked, opens a dialog box Macro.

Rice. 5. Adding a Macro Button to the Quick Access Toolbar

If you prefer to run a macro without opening a dialog box Macro, you can add a button that runs the macro to the Quick Access Toolbar. This is especially useful if the macro is automating a task that is frequently performed. To add a macro button to the Quick Access Toolbar:

1. Click the button Setting the Quick Access Chant at the end of the Quick Access Toolbar.

2. Click on Other commands to display the page Setting dialog box Exce parametersl.

3. Click the field arrow Select commands from.

5. Click on the macro for which you want to create a button.

6. Click the button Add.

7. Click the button OK.

Another way to add a macro button Clear on the Quick Access Toolbar is the ability to install it from the tab.

Click on a team Clear groups Macros tabs Developer right-click and select Add to Quick Access Toolbar from the context menu.

Creating objects for executing macros

Assigning macros to shapes allows you to create more complex "buttons" than those displayed in the Quick Access Toolbar. If desired, you can even design your own button layouts for various objects. To assign a macro to a shape, right-click on it, and then click on the command Assign macro in the context menu. In the dialog box that appears, click on the desired macro, and then click on the button OK.

When assigning macros to shapes, do not change the macro name that appears in the dialog box. Assign a macro to an object, because it contains a reference to an object. Changing the macro name breaks this link and prevents the macro from executing.

To run a macro Multiplication table you can create a shape in the form of a rectangle, and for Clear- in the form of an ellipse:

1. Click on the button arrow figures In chapter Illustrations tab Insert, and then click on the command rounded rectangle.

2. Enter a shape rounded rectangle to a range of cells M2:R4. Enter the text " Multiplication table". Set the font size of the entered text to 18.

3. Click on the rectangle with the right mouse button, and then click on the command Assign macro Multiplication table and then click the button OK.

4. Click on the button arrow figures In chapter Illustrations tab Insert, and then click on the command Oval.

5. Enter a shape Oval to a range of cells M7:P10. Enter the text " cleaning". Set the font size of the entered text to 18. Align the caption to the center. Change the fill color of the shape to red.

6. Right click on the oval and then click on the command Assign macro in the context menu. In the dialog that appears, click on the macro Clear and then click the button OK.

Now, to run the macro, just click on the shape. Click on the shape Oval and the table will disappear from the sheet. Click on the shape rounded rectangle and the multiplication table will reappear on the sheet.

1. It is advisable to create macros for routine, repetitive actions. To create macros, no knowledge of programming languages is required.

2. Macros can be run, modified, edited and deleted as needed. Editing macros requires knowledge of the algorithmic language Basic, preferably Visual Basic for Applications. The latter allows you to manage dialog boxes and solve non-standard tasks.

3. Macro launch buttons can be placed on tabs, panels quick start or figures of any type.

In Excel and Word, macros are a great help when you need to urgently look at a specific piece. object model, but there is no Internet at hand, for example. True, most often you will have to carefully clean the resulting code from unnecessary "traces" of moving through the cells and other optional actions. But it's worth it. Recording and viewing a macro will take about 2 minutes. But searching for some types of information (especially related to graphs) on MSDN in the context of an object model can take hours and is not guaranteed to be effective.

Well, for users who are not familiar with VBA, macros are definitely an indispensable and useful thing.

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 with a macro. You can record a macro that applies the desired format and then run it as needed.

When you record a macro, all the actions that are described in Visual Basic for Applications (VBA) code are recorded. These actions can include entering text or numbers, clicking cells or commands on the ribbon or menu, formatting cells, rows, or columns, or importing data from an external source, such as Microsoft Access. Visual Basic Application (VBA) is a subset of a powerful language Visual programming Basic, which is included with most Office applications. Although VBA provides the ability to automate processes between Office applications, you do not need to know VBA code or programmatic programming if you need to.

It is 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 log that action. In such a case, you can rewrite the entire sequence or modify the VBA code. Therefore, before recording the process, you should work it out well. The more precisely you write the sequence, the more effectively the macro will work.

Developer, which is hidden by default, so you need to enable it first. For more information, see Show the Developer tab.

Macro Recording

On the tab Developer click Macros to view the macros associated with the workbook. You can also 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 a variety of ways, such as a keyboard shortcut, a graphic object, the Quick Access toolbar, a button, or even when a workbook is opened.
	You can use the Visual Basic Editor to edit macros that are attached to a workbook.

	assign a macro. In field Assign macro


	Learn how to enable and disable macros in Office files.
	Press the keys ALT+F11.

Working with written 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 the recorded code that the macro recorder does not support. Since the macro recorder captures almost every step that occurs during recording, you may also need to remove unnecessary code. Viewing recorded code is a great way to learn VBA programming or hone your skills.

An example of modifying the recorded code can be found in the Get started with VBA in Excel article.

Macro Recording

Before recording macros, it is useful to know the following:

A macro written to operate on an Excel range will only run on cells in that range. Therefore, if you add a new line to the range, 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 cover other Office applications and other programs that support Visual Basic for Applications (VBA). For example, you can record a macro that first updates a spreadsheet in Excel and then opens Outlook to email it.

Macros and VBA tools are 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 undone. Before you run a recorded macro for the first time, save or make a copy of the workbook to prevent unwanted changes. If you are not satisfied with the results of running a macro, you can close the workbook without saving it.

The following is more information about working with macros in Excel.

		Learn how to enable and disable macros in Excel for Mac.
	If the book contains VBA macro to be used elsewhere, this module can be copied to another workbook with Microsoft editor Visual Basic.
Assigning a macro to an object, shape, or graphic element	On a sheet, right-click the object, picture, shape, or element to which you want to assign an existing macro, and then click assign a macro. In field Assign macro select the macro you want to assign.
	You can assign a macro to an icon and add it to the Quick Access Toolbar or Ribbon.
	You can assign macros to forms and ActiveX controls on a sheet.
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.

additional information

You can always ask a question to the Excel Tech Community , ask for help in the Answers community , and also suggest new feature or improvement on the website

Introduction

Without exaggeration, we can say that Microsoft Office of any version is the most useful and most used Microsoft product. And a modern leader, and a manager, and a teacher, and a student, and a schoolboy - everyone who has a computer at his disposal uses this system to one degree or another.

One of the most important and useful aspects of preparing Microsoft Office to perform certain tasks is to automate the processes of user interaction with Microsoft Office applications. These applications are not complete products configured to perform all possible tasks, but are systems that need some customization, which is provided by a variety of tools, both interactive and software. All Microsoft Office applications support the Visual Basic for Applications (VBA) programming language. VBA allows you to work with Microsoft Office, as with some constructor: at the disposal of the developer of VBA applications, not only a large number of objects and collections, but also the possibility of settings that allow you to programmatically configure any application to such an extent that the user of such an application may not understand which application is "communicating" with.

The most important advantage of VBA is the ability to combine any Microsoft Office applications to solve almost any information processing tasks. In this sense, Microsoft Office can be considered a programming system like C++, Delphi, etc., but with more powerful and diverse features, since there are immeasurably more system-managed objects and ready-made solutions for end users.

An attractive feature of VBA is that it is very convenient for the first acquaintance with programming in the Windows environment. This is facilitated by the widespread use of Microsoft Office applications, an endless variety of possible practical tasks, an intuitive integrated environment of the Visual Basic editor, the ability to learn programming by analyzing code recorded using a macro recorder, and the presence of a huge number of objects that can be controlled from VB code. Deeper knowledge of VBA programming will allow you to solve almost any task: from automating the creation of simple documents to processing databases using both desktop and network DBMS.

This manual offers a set of tasks, grouped into laboratory works on the issues under study. Each laboratory work involves completing tasks on prescribed actions, comprehending what has been done and performing exercises to consolidate the acquired skills.

On the basis of the proposed material, it is possible to organize variability in teaching VBA, depending on the preparation of students. Labs cover the basics of the Visual Basic language, as well as operations for creating macros, procedures and functions, spreadsheet applications with

using dialog boxes. It is supposed to master the Visual Basic programming language.

According to the structure, each work has the following components:

the purpose of the lesson;

l necessary materials for assignments;

l reference material;

ь commented practical tasks;

l exercises for independent work;

o control questions.

Thanks to this structure, the student has all the conditions for the qualitative development of the material of laboratory work. The exercises involve performing operations similar to the commented practical tasks, but independently. This leads to comprehension and consolidation of acquired skills.

The manual can be used by graduate students and students of any specialties studying the disciplines "Computer Science, Numerical Methods and Computer Graphics", "Fundamentals of Information Technology".

Part 1. Macros and vba programming language. visual basic editor environment

Regardless of the operating system you are using and software applications you often run the same command sequences for many routine tasks. Instead of repeating a sequence of commands each time you need to perform a task, you can create macro(macro), which will perform this sequence for you. Macros allow you to enter a single command that performs the same task that you would need to enter multiple commands manually to accomplish.

Macros have other advantages besides convenience. Because computers are more capable of performing repetitive tasks than humans, the record macro recorder repeatedly executed commands increases the accuracy and speed of work. Another advantage of using macros is that they usually do not require the presence of a human operator.

The macro recorder (or simply "recorder") records all user actions, including errors and incorrect launches. When the program plays back a macro, it executes each command recorded by the recorder in exactly the same order that you executed them at the time of recording.

Macros recorded by the macro recorder are not flexible, so they cannot respond to changing or changing conditions. The completed macro is saved as a VBA procedure. In VBA, you can write a macro that checks for various predefined conditions and selects the appropriate course of action based on those conditions. As for repetitive actions in the macro itself, macros recorded by the recorder have significant limitations. If you need a recorded macro to repeat an action multiple times, you must manually repeat that action as many times as needed when recording the macro. Such a macro always repeats this action the same number of times each time you run it, until you edit or overwrite it.

In addition to enhancing certain macros recorded by the macro recorder, you can use VBA to connect, organize, and manage multiple recorded macros that allow you to complete a complex common task that is made up of several smaller tasks.

With the help of macros, you can create custom menus, dialog boxes and toolbars that can change the interface of well-known Word, Excel, Access, and PowerPoint products beyond recognition. It is appropriate to note here also the possibility of creating a diverse system for checking data entered by the user in dialog boxes. Once you learn how to write programs in VBA, you will most likely never start creating a macro using a recorder.

Laboratory work number 1. Recording new macros in Excel. Performance

macros

Purpose of the lesson: Be able to set start conditions for a macro, launch the macro editor and assign a name to the macro, perform actions that need to be recorded for use later, stop the macro editor, run macros for execution.

Materials for the lesson: MS Excel 2003.

Exercise 1. Create an Excel macro that formats the text in the current cell with Arial, bold, size 12.

1. Set start conditions .

For this:

^Run excel2003 (Start/All Programs/Microsoft office / Microsoft office excel 2003), if it is not already running;

open any workbook;

select any worksheet;

select any cell in the worksheet.

2. Select a location and name for storing the macro.

For this:

Choose from the menu Tools/Macro/Start Recording…(Tools/Macro/Record New Macro…);

* in the opened dialog box macro recording (record Macro) (Fig. 1), in the text box macro name (Macro Name) enter FormatArialBold12 as a macro name;

Leave the text that Excel inserted into the field unchanged Description(Description), but add the following: Formats the text of a range:Arial, Bold, 12 ; this additional comment will help you (and others) determine the purpose of this macro;

o if you are sure that you will often use a macro that

are going to record, you can assign to run it hotkey; if - yes, enter the hotkey in the textbox Keyboard shortcut (Shortcut key) window Macro recording;

* click on the button OK to start macro recording; as soon as you click on the button OK in the dialog box macro recording, Excel will launch

macro recorder, will display the panel ^ Stop Recording (stop recorder) and start recording your actions. The macro recorder will save your every action in a new macro.

Remark 1. The available options when saving macros are Personal Macro Book (Personal Macro Workbook), A new book (New Workbook) And This book (This Workbook). When you choose as a macro storage location Personal macro book, Excel saves the macro in a special workbook file named Personal.xls in the folder where Excel is installed. Excel automatically opens this workbook every time you start working. Since macros from all open workbooks are always available to you, a macro saved in a Personal.xls workbook will also be available to you at all times. If the Personal.xls workbook doesn't exist, Excel will create it. Choice This book will cause Excel to store the new macro in the current active workbook. Choice A new book will cause Excel to create a new workbook in which this macro will be saved - the workbook that was active when you started the macro recorder remains the active workbook; any actions you record are performed in this workbook, not in a new workbook created to save the macro.

Remark 2. Not always when you start the macro recorder, you can see a panel on the screen Stop recording. Since this is a regular panel, it may or may not be displayed. This, like other panels, is managed by the team Toolbars (Toolbars) menu View (view). In any case (if there is a panel on the screen Stop Recording or none), you can stop the macro recorder by selecting Service/Macro/Stop recording.

3. Record the actions and stop the macro recorder.

For this:

select a team Format/Cells… (Format/ Cells…) to display a dialog box Cell Format (Format Cells);

click on the label Font (Font) to display font options (Fig. 2);

select Arial on the list Font (Font); follow this step even if the font Arial already selected;

select Bold (Bold) on the list Inscription (Font style);

click on the button OK, to close the dialog Format cells and change the selected cell in the worksheet;

click on the button Stop Recording (stop Macro) on the panel Stop Recording (stop recorder) or select a command Service/Macro/Stop Recording (Tools/ Macro/ stop Recording).

Remark 3. Default Panel l? fStop Recording in Excel contains two command buttons. Left button is a button Stop Recording (stop); click this button to stop the macro recorder. The right button is the button Relative link (relative Reference). By default, Excel writes absolute links to cells in your macros. Button Relative link is a toggle button. When the entry with relative links is disabled, the button Relative link looks flat; when you place the mouse cursor over a button, the button's appearance changes and it looks depressed. When a relative link entry is enabled, the button Relative link on the panel Stop Recording pressed (is in the "recessed" position). Clicking on the button relative link, you can turn on and off recording with relative links during recording as you wish.

Task 2. Run a Macro FormatArialBoIdl2.

For this:

^select a cell in the worksheet (preferably a cell containing some text so you can see the changes);

^select command Service/Macro/Macros… to display a dialog box Macro;

Choose a macro PERSONAL. XLS! FormatAriaIBoIdl2 on the list Macro name and click on the button Run to run the FormatArialBoldl2 macro. The text in any cell that was selected before you ran this macro will now be formatted as Arial 12th bold font.

Exercise

Create an Excel macro that calculates the sum of cells A1, A2 and outputs the result to cell A3 in Times New Roman, italic, size 12.

Create a macro in Excel that displays the first name on a yellow background in blue letters in the current cell, and to the right of it the last name in Arial, bold, size 12.

Create a macro in Excel that displays the word in red letters in cell A3 “ Private: ”, to the right of it displays the quotient of cells A1 and A2 in font

Times New Roman, italic, size 12.

Visual Basic for Applications macros are saved as part of the files in which Excel (as well as Word and Access) typically contain their data—the macros are saved in workbook files in Excel. Macros are stored in a special part of the data file called Modules (modules). VBA module contains source(source code) macro - textual representation of instructions. Each Excel workbook file can contain no modules or contain one or more modules. Modules saved in the same Excel workbook have a common name project (project).

When recording a macro in Excel, you can only specify the workbook in which Excel saves the recorded macro - the current workbook, a new workbook, or a Personal.xls workbook. Excel selects the module in which the recorded macro is saved and creates that module if necessary. When Excel creates a module that stores a recorded macro, the module is named ModuleN, Where N is the number of modules created for a particular workbook during the current session. For example, the first time you save a recorded macro in Personal.xls (personal macro workbook), Excel creates a module named module1 . If you continue to record macros in the same session and save them to Personal.xls, Excel continues to save the recorded macros in the same module module1 until you choose another workbook. If later in the same session you again want to save the recorded macros in Personal.xls, Excel adds another module named module2 into this book.

If any workbook already contains a module with the same name as Excel has selected for the new module, Excel increments the number in the module name until the name of the new module is different from the names of existing modules.

To view modules saved in a particular workbook (and source code macro they contain), you need to use the Visual Basic Editor component. This component provides tools that are used to create new modules, view the contents of existing modules, create and edit macro source code, create custom dialog boxes, and perform other tasks related to writing and maintaining VBA programs. The Visual Basic Editor (VB Editor) contains the same features in Excel, Word, and Access.

Task 3. Start the VB Editor.

For this:

u select Service/Macro/EditorVisual Basic (Tools/ Macro/ Visual Basic Editor) or press the keyboard shortcut alt+ F11 ;

select from the menu view/ Code or press the key F7 .

Excel will launch the VB Editor (Figure 3).

Project Explorer

Properties Window

Editor windowsVB

The VB Editor window has three child windows, each of which displays important information about the VBA project. project(Project) - it is a group of modules and other objects stored in a particular workbook or workbook template. Each of the VB Editor windows is displayed by default in docked positions (Figure 3).

If necessary, you can move any of the child windows of the VB Editor to any position on the screen by dragging header line (title bar) this window in the same way as you would move any window on the Windows desktop. Dragging one of the child windows from its docked position causes it to become a floating window. floating (floating) windows always remain visible on top of other windows. You can also resize any of the child windows of the VB Editor by expanding or shrinking the window frame to make it larger or smaller, just like resizing any window on the Windows desktop.

projectexplorer(Project window) contains a tree-diagram of the open this moment files (workbooks) and objects contained in these files (host application objects, modules, links, forms, and so on). The Project Explorer can be used to navigate to different modules and other objects in a VB project using the buttons (on the toolbar of this window) = viewCode(Program), ^ viewObject(Object) and Sh. ToggleFolders(Folders).

PropertiesWindow(Properties window) contains all the properties of the current selection object. Tab Alphabetic(in alphabetical order) of this window provides a list

properties of the selected object, composed of property names in alphabetical order. Tab Categorized(by category) displays the object's properties sorted by category.

CodeWindow is a window where you can view, edit, or create VBA source code. In mode FFullmoduleview all macro source code in a module is displayed immediately in a scrolling text box, and the macro is separated from other macros by a gray line. The VB editor also allows you to view the contents of the module in the mode 1= procedureview(representation of the procedure). To select a view mode, click the buttons in the lower left corner of the Code Window (Figure 3).

When the Code Window is in Procedure View, only one macro's source code is visible. Use a drop down list procedure(procedure) to view another macro. In Full Module View, you can also use the Procedure drop-down list to quickly jump to a single macro.

Use a List ObjectList(object) to select the object whose procedures you want to view or edit. In the case of standard modules, such as modules that store macros you write, the only choice in the Object List is General(general area).

Editor MenuVB

In the VB Editor menu File(File) provides the commands needed to save changes to a VBA project and display the source code of your VBA macro. In table. 1 shows the commands of the File menu, their hot keys and the purpose of each command.

Table 1- Menu commands File

Team	hotkey	Action
<проект>)		Saves the current VBA project (presentation, drawing, etc. depending on the application in which the VB Editor is open) to disk, including all modules and forms.
I mport File… (file import)		Adds an existing module, form, or class to the current project (presentation). You can only import modules, forms, or classes previously saved with the Export File command from another project (presentation).
E xport File… (file export)		Saves the current module, form, or class in the format text file for importing into another project or for archiving purposes.
R emove … (delete<…>)		Permanently removes the current selection module or form from the VBA project (presentation). This command is not available if no item is selected in the Project Explorer.

Menu Edit(Edit) contains commands related to managing macro source code in the Code Window and objects in forms. In table. Table 2 shows the available commands of the Edit menu, their hotkeys, and describes the action performed by each command.

table 2– Menu commands Edit

Team	hotkey	Action
U ndo (cancel)		Cancels the most recent command. Not all commands can be undone. The menu is only available if there is something to cancel.
R edo (return)		Returns the most recent command that you canceled.
Cu t(cut out)		Cuts the selected text or object and places it on the Windows Clipboard. The selected text or object is removed from the module or form.
WITH yell (copy)		Copies the selected text or object and places it on the Windows Clipboard. The selected text or object remains unchanged.
P aste (paste)		Pastes text or an object from the Windows Clipboard into the current module or form.
C l ear (clear)		Removes the selected text or object from a module or form.
Select A ll (select all)		Selects all text in a module or all objects in a form.
F ind… (find)		Similar to the Find command in Word or Excel, allows you to find the specified text in a module.
		Repeats the last Find operation.
R e place… (replace)		Similar to the Replace command in Word or Excel, it allows you to find the specified text in a module and replace it with other text.
(increase		Shifts all selected text to the right by the tab interval.

Team	hotkey	Action
(decrease	Shift+Tab Shifts all selected text to the left by the tab interval.
List Properties/ M ethods (list of properties/methods)		Opens the list in List Properties/Methods, displaying the properties and object methods The name you just entered. When the insertion point is in an empty space in List Properties/Methods, this command opens a list of globally available properties and methods.
Li s t Constants (list of constants)		Opens a list in the Code Window displaying valid constants for the property you just entered preceded by an "=".
Q uick info (information)		Opens a pop-up help window that displays the correct syntax for the procedure, function, or method you just entered in the Code Window.
Para m eterInfo (options)		Opens a pop-up window that displays the parameters (also called arguments) of the procedure, function, or statement you just entered in the Code Window.
complete W ord (complete a word)	Ctrl+Space The VB editor ends the word you type as soon as you enter enough characters for VBA to recognize the keyword.
B bookmarks		Opens a submenu with options for placing, deleting, or jumping to bookmarks that you have previously placed in your module. Unlike bookmarks in Word, VB Editor bookmarks do not have names.

Menu view(View) contains commands that allow you to select the elements of the VB Editor to view and how to view. In table. 3 shows the commands of the View menu, their hotkeys and the action performed by each command.

Table 3– Menu commands view

Team	hotkey	Action
D efinition (description)	Shift+F2 Displays the VBA source code for the procedure or function pointed to by the cursor; displays the Object Browser for objects in VBA Help.
Last position n (return to last position)	Ctrl+ Goes to the last position in the module after using the Shift+F2 command of the Definition menu or after editing the code.
O object Browser (view objects)		Opens the Object Browser, allowing you to determine which macros are currently available.
I mmmediate Window (debug window)		Displays the Immediate Window VBA debugger window.
Local s Window (local variables window)		Displays the Locals Window debugger window.
Wats h window (window control value)		Displays the debugger's Watch Window.
call stack k... (call stack)		Displays a list of the call sequence for the current VBA function or procedure.
P project explorer (project window)		Displays the Project Explorer.
Properties W indow (property window)		Displays the Properties Window.
Toolbo x(elements panel)		Displays the Toolbox. The Toolbox is used to add controls to custom dialog boxes.
T a b Order (jump order)		Displays the Tab Order dialog box, which is used when creating custom dialog boxes.
T oolbars (toolbars)		Displays a submenu that allows you to show or hide the various VB Editor toolbars or open a dialog box to customize one of the VB Editor toolbars.
		Returns you to the Microsoft Excel from which the VB Editor was launched, but leaves the VB Editor open.

Menu commands Insert(Insert) allow you to add various objects,

such as modules and forms into your project. In the Insert menu, none of the commands have "hot keys". In table. 4 shows the actions performed by each command of this menu.

Table 4– Menu commands Insert

Team Action

P rocedure… (procedure) UseForm

Inserts a new procedure (Sub, Function, or Property) into current module. A procedure is another name for a macro. Adds a new shape (used to create

custom dialog boxes) to the project.M odule (module) Adds a new module to the project. The VB editor gives this module name according to the rules described earlier.

(class module) Fi l e… (file)

Adds to the project class module (class module). Class modules are used to create custom objects in your project.

Allows you to insert a text file containing the original codeVBA, to the module.

Menu commands Format(Format) are used when creating custom dialog boxes and other forms. The commands on the Format menu allow you to align objects on a form with respect to each other, adjust the size of a control to fit its content, and perform many other useful tasks. The Format menu commands are presented here for completeness, although you won't use them until you start creating your own custom dialog boxes. In table. 5 shows the Format menu commands and their actions. Note that these commands do not have hotkeys.

Table 5– Menu commands Format

Team Action

A lign (align)

Opens a submenu of commands that allow you to align

the selected objects in the form in relation to each other.

Here you can align objects top/bottom,

right / left borders, in the center or middle of the created

object.

M ak Same Size

(align

size)

Size to Fi t

(fit

size)

Size to Gri d

(align

grid size)

Opens a submenu of commands that allow you to resize

selected objects up to the size of the specified object.

Simultaneously resizes the width and height of an object to match the size of its content.

Simultaneously changes the width and height of the object to the nearest grid labels. Form Design Guide The VB editor displays a grid in the form to make it easier to position and change the size of objects in the form.

Team Action

H orizontal Spacing (horizontal spacing)	Opens a submenu of commands that allow you to set the horizontal spacing for selected objects. Here you can set a uniform horizontal spacing, reduce or increase it, or remove any horizontal spacing between objects.
V ertical Spacing (vertical spacing)	Opens a submenu of commands that allow you to set the vertical spacing for selected objects. Here you can set a uniform vertical spacing, reduce or increase it, or remove any vertical spacing between objects.
C enter in Form (place in the center in the form)	Opens a submenu of commands that allow you to change the position of the selected objects so that they are centered horizontally or vertically on the form.
A r range Buttons (place	Opens a submenu of commands that allow you to automatically arrange command buttons on the form in a row with equal spacing along the bottom or right edge of the form.
G roup (to group)	Links several selected objects together into one group so that you can move, resize, cut, or copy the objects, treating them as if they were one.
U ngroup (split)	Ungroups objects that were previously linked together using the Group command.
O order (order)	Opens a submenu of commands that allow you to change the top-to-bottom order (called z-order) of overlapping objects on a form. Use the Order command to ensure, for example, that a text box always appears on top of a graphic on a form.

Menu commands Debug(Debug) are used when testing or debugging macros. Debugging This is the name of the process of finding and correcting errors in a program. In table. 6 shows the commands of the Debug menu, their "hot keys" and the actions performed.

Table 6– Menu commands Debug

Team	hotkey	Action
Compi l e (compile <проект>)		Compiles the project currently selected in the Project Explorer.
step I to (step with entry)		Executes your macro source code one statement at a time.
step O ver (walk step)		Like the Step Into command, the Step Over command allows you to execute all the instructions in a macro without pausing at each individual instruction.

Team	hotkey	Action
step o u t (step with exit)	Ctrl+ Executes all remaining statements in the Shift+F8 macro without pausing on each individual statement.
R un to Cursor (run to current position)		Executes the macro source code statements from the currently executing statement to the current cursor position.
A dd Watch... (add watch value)		Allows you to specify variables or expressions whose values can be observed during the execution of VBA source code.
E dit Watch… (change watch value)		Allows you to edit the specifications for watch variables and expressions that were previously created using the Add Watch command.
Q uick Watch… (control meaning)	Shift+F9 Displays the current value of the selected expression.
T oggle Breakpoint		Marks (or unmarks) the location in the VBA source code where you want the macro to stop running.
C learn all Breakpoints (remove all breakpoints)		Removes all breakpoints in the module.
set N ext Statement (set following instruction)		Allows you to change the normal execution of code by manually specifying the next line of source code to be executed.
Show Ne x t (show next instructions)		Causes the VB Editor to highlight the next line of code to be executed.

The Debug menu commands allow you to directly control the execution of the macro, stop and start the macro in given points and track the execution of a macro step by step.

Menu commands Run(Start) allow you to start the execution of a macro, interrupt or resume its execution, or return an interrupted macro to the state before execution (Table 7).

Table 7– Menu commands Run

Team	hotkey	Action
Run Sub/User Form (launch subroutine/User Form)		Causes VBA to run the macro that is currently being edited, that is, VBA runs the macro that has the insertion point on the text. If any form is active, VBA launches that form.
Break (interrupt)		Interrupts the execution of your VBA code and causes the VB Editor to enter Break mode. Break mode is used when debugging VBA code.
Resert (reset)		Sets all module-level variables and the Call Stack to their initial state.
Design Mode (constructor)		Turns Design mode on and off for the project. In this mode, no code in your project is executed and control events are not handled.

Menu commands Tools(Service) not only allow you to select a macro to execute, but also access external macro libraries and additional form controls (except those built into VBA). The Tools menu commands also provide access to the VB Editor's Options dialog box and properties. VBA project current selection in Project Explorer. In table. 8 shows the Tools menu commands and their actions. Tools menu commands do not have "hot keys".

Table 8– Menu commands Tools

Command Action
	Displays the References dialog box, allowing you to set references to object libraries, type libraries, or another VBA project. Once a link is established, the objects, methods, properties, procedures, and functions in that link appear in the Object Browser dialog box.
A dditional Controls… (additional items)	Displays the Additional Controls dialog box, which allows you to customize the Toolbox (toolbox) so that you can add controls to forms other than those built into VBA. Use the Additional Controls dialog box to add buttons to the Toolbox that allow you to add objects to the form, such as an Excel worksheet or a Word document.
M acros… (macros)	Displays the Macros dialog box, allowing you to create, edit, run, or delete macros.

Command Action
O options… (options)	Displays the Options dialog box, allowing you to select various options for the VB Editor, such as the number of spaces in a tab stop when VBA checks the syntax of your statements, and so on.
prop e rties… (project properties)	Displays the Project Properties dialog box, allowing you to set various properties for your VBA project, such as the project name, description, and context help file. This dialog box also allows you to protect the project so that no one can edit it without specifying a password.
D igital Signature… (digital signature)	Displays the Digital Signature dialog box, where you can set a digital signature certificate for the project.

On the menu Add- lns there is only one team Add- In manager, which causes the dialog box to be displayed Add- In manager. This window allows you to register, upload or download, and define the behavior of add-ons.

The VB Editor has two additional menus: Window(window) and Help(help). Both of these menus contain commands that are identical to the Window and Help menus found in other Microsoft Windows applications. Commands on the Window menu allow you to select the active window, split the current window, arrange child windows vertically and horizontally, arrange VB child windows in a cascade, or align the icons of minimized child windows. The Help menu commands are also identical to the Help menu commands in Word, Excel, and other Microsoft Windows applications. The Help menu of the VB Editor allows you to get context-sensitive help through the Microsoft Office help system and view the VBA help files for the host application from which you launched the VB Editor. If you have a modem or Internet access, you can use Help/ MSDN on the web to link to a variety of Web pages containing information about Microsoft and VBA products. The last command on the Help menu is the command About Microsoft Visual Basic. It displays a dialog box containing Microsoft Visual Basic copyright information. The About Microsoft Visual Basic dialog box also contains a command button System info… , which displays information about your computing system: which video system drivers, sound system and printer are installed, which programs are currently loaded into memory, which programs are registered in the Windows System Registry, and other technical information.

Editor toolbarsVB

By default, the VB Editor only displays the toolbar standard(Fig. 4). At the end of the panel, on the right, there is a button (with an arrow) More Buttons. If you want to remove or add some buttons from the panel to the Standard panel, click on this button, and then select the corresponding button in the menu that appears.

Launching the Office Assistant

Project window Reset

Cut Cancel Paste

abort

Inserting an object

1- 1

\ h

Additional element buttons

LnS, Colz

Copy

Excel Application

Repeat