5 What is MS Excel objects. Working with data types in Microsoft Excel

A programmer working in Excel must freely navigate the world of its objects. The power of office programming is determined by the fact that the programmer is initially at the disposal big number Already ready-made objects. To touch the capabilities provided, objects need to know.

A conversation about Excel objects is advisable to start with the examination of the Excel document frame. I wrote a lot about the important concept of the framework of the document in previous books. Numerous Object Libraries of Office 2000, the set of which for the programmer and represent Office 2000, set the framework of all documents that can be built in this environment. When a new document is created, for example, Excel's workbook, then by default, from the entire set of libraries, several, which are the objects of the document are selected. These objects are available to a programmer, without any additional effort. The central role in the framework of Excel documents is played, of course, Excel library objects. But the knowledge and all other objects included in the framework are necessary. For example, when programmatically creating an interface, you need knowledge of the general objects of the Office library. I also note that if desired, the programmer can always expand the framework of the document by adding those or other libraries to it. The default frame at the moment when a new working book opens, consists of objects that are part of the following libraries:

• Excel - Library specifying the basis of Excel documents. Here the class specifies the root object Excel.application, and all classes of objects embedded in the root object.
• Office. - Library of objects common to all Office 2000 applications. There are classes that define toolbar - CommandBar and classes of other common objects. Here are the classes asking the assistant (Assistant object and all classes associated with it). In particular, a new object appeared, which was not in the previous version - the answer wizard (ANSWER WIZARD).
• Stdole - Library of classes, allowing to work with OLE - objects and implement automation.
• VBA. - Library of classes associated with the language VBA. It serves all standard functions and constants built into the language, Collection and Errobject classes.
• Vbaproject - The default project associated with the document. Classes that can be created by a programmer in this project, methods, properties, all this is available for viewing, as well as classes embedded in standard libraries.

If you compare the framework excel books , for example, with a frame document Word. They differ in that the basis of one is based on the Excel library, the other is the Word library. These libraries contain specific applications for applications. As for the interface objects, objects defining the VBA editor, automation, then general objects are used here. Office, Stdole, VBA Libraries are common to all Office 2000 Library applications. I told about the objects of these libraries in detail.

I note that although the Excel document frame has not changed in Office 2000 compared with the previous version, at the same time object model There were quite significant changes, new objects appeared, new properties and methods of previously existing objects.

Excel object model

First of all, a few words about how it is arranged object model Excel and other Office 2000 applications. In this model, objects are interconnected by an embedding attitude. At the zero level of the hierarchy, there is some central object in which other objects are built into the first level of the hierarchy. In each of the objects of the first and subsequent levels, the following levels can be built. So this process continues. Thus, objects in this model "thick", since they are built into a large number of other objects. This is especially true of the objects standing at the top levels of the hierarchy.

Formally, embedding is implemented using the properties of objects. Properties can be as terminal, non-object, and the so-called participating properties that return objects when calling them.

Let us proceed to the consideration of the Excel 9.0 object library and start with the central object of this library - Excel.application.

Excel Application object

Excel.application object Specifies Excel application. And therefore properties, methods and events of this object should characterize the application as a whole. It is clear that this object must have the Workbooks property that returns all the workbooks open in the application, the Windows Property, which returns open windows, properties, such as Commandbars, returning interface objects, and other similar properties. The methods and events characteristic of the entire application as a whole seems to be not so much. So, it would seem, the structure of this object should be quite simple. However, it is realistic, - the Excel.application object has a very large number of properties, methods and events, which does not allow me to describe them completely, and there is no particular sense in this. An Excel.Application object, in my opinion, is clearly overloaded, many of its properties and methods without any damage could be eliminated because they operate with objects facing lower hierarchy levels and have no direct attitude to the entire application as a whole. I will give only one example. The first alphabetic property ActiveCell returns an object specifying an active cell. It is clear that we are talking about the active cell of the active page of the active working book. It is not clear only why it was necessary to add this property to the appointment. It is enough enough to have the Worksheet object asking a book page. Moreover, if at the time of calling the ActiveCell property there is no active page with cells, then an error will occur, which does not happen if the active cell calls the Worksheet object. Examples of such overload object Application can be brought a lot. I am in my description of objects top level I will not always mention such properties, believing that it is better to tell about them where they are essentially necessary.

General objects and excel.application

Let's start consideration from the properties of an Excel.APPlication object that have already familiar to us common objects:

Table 3.1. General objects available in Excel.application

	Appointment object	Library
Assistant.	Assistant to organize its own dialog system.	Office.
Answer Wizard	Master of answers, standing behind the assistant. Can be used when creating your own reference system.	Office.
COM Addins.	Collection of component common for Office 2000 applications.	Office.
Commandbars.	Collection of tool panels, without working with which you can not do when creating your own Excel document interface.	Office.
FileSearch.	The object used when searching for files.	Office.
Language Settings	Object specifying language preferences common to Office 2000 applications.	Office.
Debug.	The object used when debugging program projects.	VBA.
VBE.	Root object when working with software projects.	VBA.

All objects shown in this table play an important role in software work with Excel documents, as, however, with other Office 2000 documents.

Properties - Participants in the object

Consider now properties - participants in the Excel.application object, which returns objects specific to Excel, as I warned, I will consider only the basic properties that are really necessary when working with an Excel.application object.

Table 3.2. Main properties - participants

Property that returns an object	Appointment object
Workbooks.	Collection of documents open in Excel - workbooks. The main object, thanks to which you can access any Excel document and then work with the objects of this working book.
Windows	Collection of open windows in all desktops. The fact is that the same workbook is often useful to open in several windows, which allows you to see different sections of the working book. The Windows collection allows you to access each such window. Most often, the Windows property is used when working with the Workbook object, for the Application object, this is one of the examples of the overload that I mentioned above.
WorksheetFunction.	The object is a container in which numerous excel functionsStarting from ordinary mathematical functions and ending with features used to solve statistical problems, forecast, work with dates and other.
Addins.	Collection of components that expand the ability to solve special tasks in Excel.
Autocorrect.	Familiar by word Application An object that allows you to set the automatic adjustment of the dial-up texts in Excel cells.
DefaultWebOptions.	An object that allows you to set parameters for Excel documents stored as Web pages. Similar to a similar Word.Application object, but has its own specifics.
Dialogs.	The Dialogs object as well as the three previous objects - Addins, AutocorRect, DefaultWebOptions. Refers to a group of similar objects found in each of Office 2000 applications that have many common, but also having differences related to the specifics of the application. As in Word, the Dialogs object sets the standard collection dialog boxeswhich can be opened in Excel, allowing you to organize a user dialogue.
Names.	One of the overloaded properties that returns the collection of all names used for individual cells and areas of all open Excel documents. Most often, this property is used when working with a separate workbook or a separate page.
Odbcerrors.	Collection of ODBCERROR class objects. Elements of this collection are created automatically source of ODBC data, if errors occur when executing a data request. If there were no errors, then the collection will be empty.
OLEDBERRORS.	Collection of OLDBERROR class objects. Similar to the previous collection, its elements appear if there are errors in the process of working with the database when the OLE DB interface is used.
RecentFiles.	The object relating to the group of similar objects of the Office 2000 family. It specifies the collection of files stored by the latest use Excel documents.

The main content of this chapter will be related to the consideration of the Workbooks collection, or rather with the object workbook and in it in it. But before you move further, I will give a brief overview of those invested in Excel.application objects available at this level, in fact, related to the lower levels of the hierarchy of the Excel object model:

• A group of active objects - ActiveWorkBook, ActiveWindow, ActiveCeet, ActiveChart, ActiveCell, ActivePrinter- Returning an active workbook, window, active working page, diagram or cell, if any at the time of calling the corresponding property. In the absence of the requested active object, an error will occur. All these objects will be discussed in detail, but a little later, when we go down the hierarchy of objects. A mansion is a property that returns an active printer. This property really makes sense to tie with the application. Note that objects standing at the lower levels of hierarchy, such as Workbook, do not possess this property, so you can get to the printer only through the Application object.
• A group of collections and Range objects that are part of the corresponding active object - Sheets, Charts, Rows, Columns, Cells, Range - Returning collections of work pages, pages diagrams active Workbook, Range object containing all rows, columns, cells or a specified area of \u200b\u200bthe active working page. Also, as in the case of calling the objects of the previous group, when calling these properties should be careful because an error occurs if there is no corresponding active object.
• The selection property returns a selected object in an active window. The type of object returned depends on the current selection. Returns nothing if there is no selected object in the active window.
• The ThisworkBook property returns the current workbook containing the macro executed, one of the operators of which and caused this property. This property represents the only way to get to the working book containing the Addin component, from the inside of macros that make up this component.

Terminal properties

Terminal properties are much, and it is clear why. An Excel application, like other Office 2000 applications, can be customized by the user at their discretion. You can manually manually configure this setting. Setting manually mostly conducted from the service menu | Parameters using the capabilities provided by various tabs in the Opening Options window. For software setup Terminal properties are used - this is their main purpose. Naturally, I will not dwell on all properties - they are simple. In the above view, a selective description of some groups of terminal properties is presented:

• A group of properties specifying the default application properties - DefaultFilepath, DefaultSaveFormat, DefaultSheetDirection- The default path, default format, the direction of viewing text (left to right or right to left) as specified for some languages. The previously mentioned property of DefaultWebOptions also adjoins the same properties.
• A group of Boolean properties that allow you to enable or disable the display on the screen of certain application items - Displayalerts, DisplayCommentInDicator, DisplayFormulabar, DisplayStatusbar And other display properties. The first of these properties allows you to manage the issuance of some messages to the screen during macros, the second is to display a special indicator when the comments show. More often you have to use the display of the formula and status panels. Especially often you have to use these properties when the Excel document is used for special purposes, for example, when displaying various forms when appearance The document does not resemble the usual spreadsheet. I note that the DisplayGridlines used in these cases, which allows you to disconnect the grid, belongs to the Windows object, not the Application object.
• A group of boolean properties that allow you to enable or disable certain properties - EnableAnimations, EnableAutocomplete, EnablecancelKey, EnableEvents, Enablesound. The first of these properties allows you to control the animation when adding or removing rows and columns of the working sheet, the second - autofill the cells of the table. The EnablecancelKey property is not Boolean, it takes the values \u200b\u200bspecified by the corresponding listing, and allows you to control the program interrupt process when you press the Ctrl + Break key combination. The default XLINTERRUPT value allows you to interrupt the execution of the macro and go to debug mode, where there is a step-by-step execution. However, using this property, you can set different modes as, for example, the transfer of control error handler at the time of the interrupt. This property should be used carefully, since when looped, a situation may occur when it cannot be interrupted by a program without applying coarse methods. The EnableEvents property allows you to manage the enable application of the application of the Application object, and the Enablesound property controls the inclusion of sound during Office 2000 applications.
• A group of properties that control the size of the main window of the Excel application - Height, Width, Left, Top, setting the height, the width of the window and the coordinates of the upper left corner of the window.
• Many other properties that allow you to manage the cursor, scrolling, user characteristics and many other parameters anyway, characterizing the Excel application.

Application object methods

Methods have an Excel.application object less than properties, but also about fifty. We give a brief overview, again, combining them if possible in groups:

Public sub repeatandundo () "Creating items to repeat and cancel in the Edit menu Call Application.Onrepeat (" Hello "," Test ") Call Application.onundo (" 7 to a1 "," write7 ") End Sub Public Sub Test () MSGBOX ("Hi!") End Sub Public Sub Write7 () Range ("A1") \u003d 7 END SUB

The Repeatandundo procedure creates the appropriate Point menu items, and the TEST and WRITE7 procedures will be called when the user is selected by these menu items. I note that I do not see a really special benefit from the use of these methods, since these menu items will be updated with any user actions.

• Repeat and Undo methods are close in spirit to those considered just that methods. They allow you to repeat or cancel the last action of the user when it works manually.
• Another important method that allows you to launch a macro for execution is the RUN method (Macro, Arg1, Arg2, ...). The RUN method allows you to execute a macro (procedure or function) of a working book project or a function from DLL or XLL. The macro that runs on execution may be in the same workbook as the macro that caused the RUN method, but can also belong to another workbook. In this case, naturally, projects must be linked by reference and in the project, which causes a different project macro, a link to the called project must be set. When you call a macro, an arbitrary number of arguments can be transmitted, all of them are transmitted by value, so that notice, it is impossible to transfer the object itself, but only its value specified by the Value property. The Run method in turn returns the value resulting from macro execution. I will give a simple example, demonstrating all the features of the RUN method call:

BookOne document project I gave the name BookoneProject. This project announced a global variable

Option Explicit Public Globalz AS Variant

In the module named ModuleOne of this project, I placed a description of the Plusxy procedure and the PLUS1 function. They perform simple and understandable without commenting action.

Public Function Plus1 (Byval X As Integer) AS Integer Plus1 \u003d X + 1 End Function Public Sub Plusxy (Byval X AS Integer, Y As Integer) Globalz \u003d X + Y End Sub

In the same module, the Testrun procedure demonstrating the RUN method calls.

Public Sub Testrun () "Starting to perform the function and procedures" in the same project DIM Z AS Integer Z \u003d Application.Run ("Plus1", 7) debug.print "z \u003d", zz \u003d application.run (" Plusxy ", 5, 7) debug.print" Globalz \u003d ", Globalz," z \u003d ", z end sub

Here are the results of its implementation:

z \u003d 8 globalz \u003d 12 z \u003d 0

In the project of another Excel Workbook with the BookWo name, I set a link to the BookoneProject project and in one of the modules placed the TestRun1 procedure, which calls the BookoneProject project macros:

Public Sub Testrun1 () "Startup to execute functions and procedures" in another project BookoneProject, "to which the link is installed. Dim z as integer z \u003d application.run (" BookoneProject.Module1.plus1 ", 7) MSGBox (" z \u003d "& z) Call Application.RUN1 (" BookoneProject.Module1.plusxy ", 5, 7) MSGBox (" globalz \u003d "& bookoneproject.globalz) END SUB

And in this embodiment, the Run method successfully copes with a call to the macros of another project. Of course, B. this example Instead of using the RUN method, it would be possible to directly call the same PLUS1 function. But, I hope, you understand that the true value of the RUN method is that the name of the macro performed can be transmitted to it as a parameter, so, depending on the situation, it can run different macros. But let's finish with an example and return to the consideration of other methods of Excel.application object.

• The Goto (,) method does not perform the macro, allows you to proceed to its consideration. Another may be the main purpose of the method is to switch to the specified point of the Excel working book. To proceed to the macro consideration, the Reference parameter must be a string specifying the name of the macro. To go to the specified area area, the Reference parameter is set by the Range object. Boolean Parameter SCROLL, having true, provides a scrolling area so that the specified point is in the upper left corner of the viewing area. The main thing is to pay attention to, the Goto method allows you to transitions between documents. Here is an example of macros from the BookTwo document, carrying out the transitions to a specified area and the Bookone document macro.

  Public Sub Gotorange () "Transition to a given area of \u200b\u200banother application application.goto workbooks (" BookOn.xls "). Worksheets (" List1 "). Range (" A20 "), True End Sub Public Sub Gotomacro ()" Go to the specified Macro in another project Application.Goto "BookoneProject.Module1.Testrun" End Sub

• Method Macroptions (,,,,,,,,,,,,,,,,, - This is another method associated with macros. It allows you to specify for the macro indicated by the first parameter, various characteristics - Description, Hot keys, Help section associated with this macro, and other properties.
• Method RecordMacro (,) - Also designed to work with macros. It allows you to add some program code to the macro created by the MacroRecorder tool. At the moment of calling the MacroRecorder method, it should be enabled and recorded a macro into a module that is not active, in other words, it is impossible to record to that module whose macro caused the RecordMacro method.
• The WAIT (TIME) method AS Boolean is the last method described by me methods of Excel.application object, which are included in a large group of methods designed to work with macros. It allows you to organize the calculation delay at the specified time specified by the method parameter. In the example below, the method is used to open and show the user some shape, and then close it after the specified time. This technique can be used in games whose goal is to check care. Here is the text of the corresponding macro:

  Public sub waitsometime () "opens the form for a limited time MSGBox (" The form will be shown for 10 seconds! ") Flyform.Show Application.Wait (NOW + TimeValue (" 0:00:10 ")) Flyform.hide End Sub

Take a look like the form itself.

Fig. 3.1.FLYFORM form open to "moment"

I give a drawing of this form only in order to clarify what purpose is being pursued in this example. I assumed that when opening the form, the user should have time to enjoy the time to enter two numbers in the X and Y fields, press the calculation button and remember the result. However, my intentions were not realized, and for what reasons. If the form has a modal form status, then the macro execution is suspended until the user closes the form. So in this case, the user has time to work with the form is not limited. That I understood. If the form has the status of the uniform form (property showmodal \u003d false), then the form will really be open for 10 seconds. But in this case, the user will not be able to work with this form, enter the values \u200b\u200bin the input field and press the command button. The worst thing is that when trying to enter values \u200b\u200bin the form field, they will actually fall into the arbitrary place of the program text and port the project itself. So you should be careful in a similar situation.

• Method Help (,) Allows you to call the help guide, specifying if necessary and the corresponding section in this manual. You can call as a standard reference system - in this case you do not need to specify the arguments when calling a method, or what happens more often, your own reference system. The first method parameter specifies the name of the file that stores the Reference Guide. This file may have a refinement "CHM" if the manual is prepared using the HTML Help Workshop toolkit, or have an "HTM" refinement if the help system is created using the Microsoft Winhelp toolkit.
• Methods INTERSECT (ARG1 AS RANGE, ARG2 AS RANGE, ...) AS RANGE and Union (ARG1 AS RANGE, ARG2 AS RANGE, ...) AS RANGE Range object defining a rectangular area representing the intersection or combining areas of arguments, which should be at least two and not more than 30, are returned as a result.
• The INPUTBOX method is essentially equivalent to the function of the same name from the VBA library and allows you to organize a user dialog and adopt the value entered. The INPUTBOX function is one of the most widely used features, and there are many examples of its call. Do not do without it and in the examples of this book. What to call the InputBox method of the Application object or the INPUTBOX function of the VBA library is a matter of taste.
• The Volatile () method allows you to enable or disable forced calculation for the functions caused in the working sheet formulas. The method is called directly into the function that is supposed to be labeled. Boolean Parameter Volatile marks a function as forcedly calculated if it is true. This value is the default parameter value.

I looked at most of the Application object methods. I note that in the previous version of these methods it was much more, since many Excel functions are mathematical and others have been available at this level. Now, as it should be, all of them are in a special worksheetFunction container.

In most examples of the introductory part of the section, we considered the possibilities of VBA without connection with the information on the work sheet. Only a few situations showed syntax structures that allowed to extract and write data into the cells of the sheets Microsoft Excel.. In this part of the section, we will consider in detail objects that allow you to work with the information contained in Microsoft Excel's workbooks. The examples given here are the foundation for more complex developments considered in subsequent articles.

Excel Workbook File is presented in WorkBook object that has a large number of Properties and methods. Background information on them is present in both the VBA electronic help and in. We will not deepen into purely reference information and in the introductory part we will consider only the information with which we will meet in the examples below.

So, property Worksheets. Workbook object represents a family of all working sheets of the book. And to appeal to a specific sheet of the book using this property, you should simply specify the sheet number as the parameter, which looks like this: Worksheets (Sheet number). As a sheet number, it is simply its serial number in the Microsoft Excel book. Another option is to specify as a sheet name parameter: Worksheets ("Sheet Name"). One of the most frequently programmable events associated with the book as a whole is an event. Open.which happens when opening a working book. So, if we want certain actions when opening a book, you should position the necessary software fragment within the procedure Workbook_open.. Programming this event will be considered in terms of examples given further.

The next object in the order of the hierarchy after Workbook. is the object Worksheet.representing a work sheet. From the manifold of methods of this object is widely used Activatewhich exists for the Worksheets Sheets family, about which we have already said above. For example, if you need to activate the second sheet when working on the first sheet of the first sheet, the program string syntax in the procedure (it can be performed, for example, click on the button) should look like this: Worksheets (2) .ActiveVate. If you want to activate the sheet, called Information about firmsYou should write this design in the procedure: Worksheets ("Information about firms"). Activate.

Microsoft Excel users know that this application offers a service associated with the protection of workbooks and their sheets. So, the method Protect. (Worksheets families) ensures the protection of the working sheet from making changes. For software installation Password protection (Password is specified in the Password parameter of this method) sheet with the title Staff You can do as follows:

1 2	Worksheets ("Employees") .Protect Password: \u003d "zv2345", _ drawingObjects: \u003d True, Contents: \u003d True, Scenarios: \u003d True

Worksheets ("Employees"). PROTECT PASSWORD: \u003d "ZV2345", _ DrawingObjects: \u003d True, Contents: \u003d True, Scenarios: \u003d True

There is a corresponding method Unprotect.allowing you to remove the protection from the sheet. For just installed protection, its removal method: Worksheets ("Employees"). Unprotect password: \u003d "zv2345". Any practical work in Microsoft Excel, one way or another, relates to information in cells. To work with cells in VBA there is an object Range. (Translated - range of cells). Using this object requires a setting of the range of the cells that are interested in us. It can be one cell or group of cells. So, if we write worksheets (1) .range ("C5"). Value \u003d 7, then this means that in the C5 cell of the first sheet, we programmatically record the number 7. Here is the main property of the object Range. - Value. Literally, it means the value or the contents of the cell (group of cells). In the following design, the set of cells is simply entered into the set of cells, the same set of Abspectorates: Worksheets (1) .range ("A1: A3"). Value \u003d "(! Lang: Abs" .!}

Another way to work with cells is implemented using the object Cells.And the syntax of its use is as follows: Cells (Row number, column number). In fact, from the point of view of their use, the objects under consideration are similar. For example, to obtain in the variable z, the value of the D5 cell can be two different ways: Z \u003d Range ("D5"). Value or z \u003d Cells (5.4) .Value. As an example of a software construction on this topic You can give the following assignment:

1 2	Worksheets (2) .range ("C5") .Value \u003d _ Worksheets (3) .Cells (5, 1) .Value.

Worksheets (2) .range ("C5"). Value \u003d _ Worksheets (3) .Cells (5, 1) .Value.

Laboratory work

Studying the MS Excel Tabular Processor Interface. Simple calculations

Objectives:

1) familiarize yourself with the capabilities of the table processor;

2) familiarize yourself with the MS Excel 2010 user interface;

3) Get acquainted with the main techniques of work in MS Excel 2010.

The task:

1) Examine paragraph 1 "Training Material";

2) Perform the tasks shown in paragraph 2;

3) Answer the test questions (p. 3).

EDUCATIONAL MATERIAL

Basic concepts of a table processor

Tabular processor - this is computer program for storing and processing information presented in tabular form. The structure of the program is a two-dimensional array consisting of rows and columns, so these software Called another spreadsheets (ET).
With this, you can not only create tables, but also automate data processing.

Tableware functions are very diverse:

§ Create and edit tables;

§ Registration and printing tables;

§ Creation of multi-job documents united by formulas;

§ Construction of charts;

§ work with this as a database; Selection of data on requests;

§ Creation of final and summary tables;

§ Solution of tasks of type "What-if" by selecting parameters;

§ Solution of optimization tasks;

§ Creating macro program using the built-in programming language Visual Basic for Applications (VBA).

The Excel tabular processor is an integral part of the integrated Microsoft Office software package.

After excel launch 2010 The following structure appears on the screen:

1. Program header.

2. Panel quick access with the panel setup button, the default is located at the top of the Excel application window (Fig. 1). Designed for quick access to the most frequently used features. By default, the panel contains only three buttons: Save, cancel, return (repeat). The quick access panel can be configured by adding new commands to it.

Fig. 1. Quick Access Panel

3. Tape.In Excel 2010, the menu and toolbars are made in the form of a tape. Commands are ordered to logic groups collected on tabs. Tabs Focusing tasks. Groups Each tab breaks the task to its components. Team Buttons Each group serve to execute commands or display the command menu. The group icon (button) is a small square in the lower right corner of the group of items in the tab (Fig. 2). Click on the icon opens the corresponding dialog box or area of \u200b\u200btasks to this group for the extension. functionality. For example, group icon Font tabs the mainopens dialog box Cell format. A group icon Clipboard Displays the area of \u200b\u200btasks Clipboard. Each group has an icon.

By default, seven permanent tabs are displayed in the window: the main, Insert, Page layout, Formulas, Data, Review, View. Locking the main Opens by default after starting the program.

Fig. 2. Buttons Group

In addition to permanent, there are a number of contextual tabs, for example, to work with drawings, diagrams, etc., which appear automatically when switching to the appropriate mode or when the object is selected or the cursor is installed on it. In some cases, several tabs appear at once, for example, when working with diagrams, three tabs appear: Constructor, Layout and Format.There are no ways to comply with contextual tabs.

You can navigate the ribbon using the keyboard: if you press the key , Tapes will appear on the tape to go to the corresponding tab (Fig. 3).

Fig. 3. Ribbon travel keys

Going to the tab, you can see the prompts on the transition keys on the current tab (Fig. 4).

Fig. 4. Tab Transition Keys the main

Replace tape tool or menu panels previous versions microsoft applications Excel is impossible. Remove the ribbon is also impossible. However, to increase the workspace, the tape can be hidden (collapse). Methods:

1. Press the button Ribbon located on the right side of the tab names. To return to click on the button Expand tape.

2. To quickly hide / display, the tape is sufficiently double-clicking on the header of any tab.

3. The tape can be collapsed and otherwise:

a) Right-click anywhere in the tape;

b) In the context menu, select the command Ribbon.

4. To roll or restore the tape, you can also click the key combination .

Ribbon can be configured: rename and change the location sequence of constant tabs, create new tabs
And delete them, create, delete, change the location of groups of elements on tabs, add and delete individual items, etc.:

1) Right-click anywhere tape;

2) In the context menu, select the command Setting tape.

Also tape setup is performed in the window ParametersExcel In category Setting tape menu File.

4. Tab (Menu) "File". Tab File Always located in the tape of the first left. The menu contains commands to work with files ( Save, Save as, Open, Close, Last, Create), to work with the current document ( Intelligence, Print, access), as well as to configure Excel ( reference, Parameters). Button Outputfinishes
with the application.

5. Mini toolbar.Mini toolbars (Fig. 5) contain the main most frequently used elements for designing the text of the document, drawings, diagrams and other objects. Unlike other Office 2010 applications (Word, PowerPoint, etc.) in Excel 2010, the mini-panel is not automatically displayed when the sheet fragment is selected. To display it, right-click on the selected area. The composition of the elements of mini-panels is permanent and unchanged.

Fig. 5. Mini toolbar and context menu

6. Row of formulasconsisting of three parts:

a) Active Cell Addresses ;

b) buttons Cancel, Inputthat appear in input or edit mode and buttons Insert Functions F X ;

c) the contents of the active cell.

To view and edit the contents of the selected cell, you can increase the altitude of the formula string. To do this, click on the button. Expand the string formulas. If not all the contents of the cell are displayed in this case, you can use the scroll bar. In order to bring the formula string in the initial stateClick on the button Collapse formulas.Remove / display the formula string as follows: File® Parameters® Additionally® Category Screen® Show a string of formulas.

7. Coordinate string - Contains the names of columns.

8. Coordinate column - Contains row numbers.

9. Working field.

10. Horizontal and vertical scroll bar.

11. Sheet labelswith the transition buttons for working sheets.

12. Status barwhich indicates operating modes Ready, Input, Edit; Book view modes Normal, page markup, page; Large line.

Basic Excel Objects

1. Column. Excel Table Contains 16384 columns (2 14). Colinisidentified by Latin letters. Since they are only 26, after z, the designation of columns is the dual letters AA, AB, AC, ..., GA, GB, GC, ..., HX, HY, HZ, and after the ZZ column - aaa, aaav, Aas, ..., AAZ, ABA, ... The numbering on the XFD column ends. To quickly go to the first or last column (string) of the desktop, you need to press the key<Ctrl\u003eand the corresponding cursor control key.

2. Line - They are in Table 1048576 (2 20).

3. Cell - Row and column crossing place. Each cell has a unique address that specifies the name of the column and the number of the string, on the intersection of which it is located. Examples of using the addresses of the cells in Style A1 are shown in Table 1.

Table 1

The style of cells A1.

Excel supports alternative Category indication system called R1C1 (from English words Row - String I. Column. - column). In this system and columns, and the table rows are numbered, and the row number precedes the column number. For example, a cell A1 is called R1C1 (line 1, column 1). The B1 cell is R1C2 (line 1, column 2). Go to alternative style and back can be found in the menu File® Parameters® Formulas® Category Work with formulas® r1C1 link style.

Cell where the cursor is called current , And at the moment, certain actions are performed with it.

4. Block cells - This is a rectangle, which indicates the addresses of the cells of the left upper and lower right angles separated by the colon, for example, A1: C5. If the cell block is specified, the cell block is specified, all its cells are involved.

5. Work leaf -this is the created table to solve the problem, diagram, macro, drawing. Standard sheet name - sheet1, sheet2, .... With work sheets you can perform the following actions:

§ renaming;

§ removal;

§ insert;

§ moving;

§ copying.

These actions are performed using context menu with the mouse pointer installed on the sheet label or in the group Cells tabs the main(Fig. 6).

Fig. 6. Group Cells tabs the main

Menu File® Parameters® Additionally® Category Show options for the next book® Show sheet labelsallows you to remove / display sheet labels.

6. Workbook - This is a file that is stored on the disk and contains one or more sheets. By default, the workbook has a name Book1, book 2 .... You can save the file using the menu item File® Save. Team Save asselectable if you want to save the file already recorded on the disk under the new name. Excel files 2010 is the default expansion .xlsx .

7. Diagram- This is a graphical display of the data of the table. It can be stored on a separate sheet, and may be accompanied by text or table.

8. Picture- Created with the help of a group Illustrations tabs Insert In the Excel environment itself or can be inserted from another graphic editor.

9. Modules Visual Basic- programs called macros and programming language created Visual Basic.

Data types

As a rule, the following types of data are used in this:

1. Text - Any character sequence used mainly for table titles, rows, columns and comments.

2. Number. IN excel cell You can display three types of numeric data (constants):

a) whole numbers - This is a sequence of numbers from 0 to 9 with or without it: +25; -100.

b) real numbers with fixed semicolons - These are decimal fractions in which the whole part is separated from a fractional semicol: 28.25; -3,765.

c) floating semicolons - These are the numbers recorded in the following form: 1.5E + 03 or 2E-08. This post is also called exponential form recording number (Scientific format).

By default, the correctly entered number is aligned with the right edge of the cell. Incorrectly entered number is considered to be text and leveled on the left edge. If the number is not placed in the width of the cell, then the entire cell is filled with a symbol # (Fig. 7).

Fig. 7. View of the numbers on the work sheet

3. Formula -this is an expression consisting of numbers, addresses of cells, functions and operations of operations and starting from the mark = . For example:

A1 * 20% + 12 * A1 * D12.

The procedure for performing arithmetic action in the formula is the same as adopted in mathematics.

4. Function - These are programmed formulas that allow you to produce frequently encountered sequences of calculations (standard functions of different purposes).

5.date- This is the date presented in various formats from which arithmetic and logical operations can be produced. For example, 10/21/2011; October 21, 2011, etc.

Copying cells

Copy can be carried out in the following ways:

1) Tab the main® Group Clipboard® Team Copy;

2) using the team Copy context menu;

3) Using the key combination ;

4) Using the "Drag-and-Leave" technology while pressing the key . In this case, a sign appears next to the mouse pointer + (Sign of Copy);

5) Using a marker in the lower right corner of the copied cell. In the absence of this marker, you must use the team File® Parameters® Additionally® Category Parameters Edit® Allow filling and dragging markers.

TO the number of MS Excel's main objects, which are described in this section include the following: Workbook (Workbook) I.

workbooks Family (Workbooks), Worksheet Worksheet and Worksheets Family, Cell Range or Cell (Range).

After the object, usually through the point ". "The property or method is specified. Sometimes, to get to a certain object, you need to go through the hierarchy of higher objects.

Example:

Workbooks ("Book1.xls"). Worksheets ("List1"). Activate

The Workbooks family ("Book1") provides access to the workbook. In brackets indicate the name of the book.

The Worksheets family ("List1") provides access to the working sheet. In brackets indicate the name of the sheet.

The range of Range ("Range") provides access to the range of cells or cell. In brackets indicates the range of cells or cell name.

Cells Family (line number, column number) provides access to the cell. In brackets indicate the coordinates of the cell.

Examples:

Workbooks ("Book1")

Worksheets ("List1") Range ("A1") Range ("A1: B10") Cells (2.3) Cells (k, i + 1)

5.4.4.1. Workbook object and Bookooks

IN hierarchy MS Excel ObjectWorkbook (working book) goes immediately after objectApplication and is a working book file. The working book is stored either in the format ofXLS files (Standard Workbook) orxla (fully compiled application). Properties and working book methods allow

work with files. This object is included in the family (set)

Workbooks (<Индекс>) Returns an object by index in the set.

Workbooks ("<Имя>") Returns the object by name in the .ActiveWorkBook set returns a link to the active book at the time of execution of the command.

5.4.4.1.1. Properties

ActiveSheet Returns the active sheet of the book. To obtain a sheet name, the Name property is used.

Examples:

MSGBOX ("Name of Active Sheet" & ActiveSheet.name)

MSGBox (activeworkbook.ActiveSheet.name) - displays the name of the active working sheet in the dialog box.ActiveDialog. - Returns the active dialog box.ActiveChart. - Returns an active diagram.

Sheets - Returns the family of all sheets of the book.

Worksheets - Returns the family of all working sheets of the book.

Example:

For Each S in ActiveWorkBook.Sheets MSGBOX (S.NAME)

Charts - Returns a family of all book charts that are not implemented in working sheets.

Count - Returns the number of objects of the Bookbook family (the number of books open by the application).

FullName - Returns the full name of the working book.

Example:

MSGBOX (ActiveWorkBook.fulName)

Name - Returns the name of the active working book. 245.

Example:

MSGBOX (ActiveWorkBook.Name)

PATH - Returns the path to the working book file.

Example:

MSGBOX (ActiveWorkBook.path)

5.4.4.1.2. Methods

The ACTIVATE method activates the workingbook so that its first work sheet becomes current (accessible to work).

Example:

Workbooks ("Book1"). Activate

Workbook.ActiveVate.

The Close method provides the closure of the working book. CLOSE SAVECHANGES FILENAME - closes the book. ParameterSavechanges allows you to manage the preservation of changes in the working book. If its value is equal, then the changes are saved if -false is not saved. ParameterFileName is a string containing the name of the working book file.

Example:

Workbooks ("Book1"). Close Workbooks ("Book1"). Close Savechanges: \u003d True Filename: \u003d "Book2"

New Window is designed to open the specified book in a new window.

Example:

Workbooks ("Book1"). NewWindow

Save serves to save changes in the workbook.

Example:

Workbooks ("Book1"). Save

Saveas FileName - Used to save a book under a different name (in another file).

SaveAscopy - Designed to save the working book in another file, leaving it open with the same name.

Examples:

Workbooks ("Book1"). Saveas Filename: \u003d "kdjf.xls"

ActiveBook.SaveAscopy FileName: \u003d "My book"

Open FileName - Allows you to open a workingbook with the name specified in the FileName parameter.

Example:

Workbooks.open FileName: \u003d "Book1.xls"

5.4.4.1.3. Events

The main events of the Workbook object are given in Table. 5.13.

	Table 5.13
	When an event occurs
	When closing a working book
	Before printing a working book
	Before saving a working book
	When adding a new sheet
	When opening a working book
	When activating the working sheet

5.4.4.2. Worksheet object Worksheets Family

IN hierarchy MS Excel ObjectWorksheet goes immediately after

the Workbook object represents the working sheet of the book and enters the Worksheets family (set).

Examples:

Worksheets ("" List1 "). Activate Worksheets (1) .ActiveVate

Example:

ActiveSheet.Range ("A1") \u003d 1

5.4.4.2.1. Properties

Name property allows you to work with the name of the working sheet.

Example:

Worksheets (1) .name \u003d "Results"

ActiveCell returns the active cell of the active working sheet.

cell with specified coordinates.

Columns (<столбец>) - Returns a link to the column. As a parameter, the name or number of the column can be specified.

Example:

Worksheets (1) .columns ("A") \u003d 1

Worksheets (1) .columns (1) \u003d 1

Rows (<строка>) -B promises a link to the string. The row number can be used as a parameter.

Example:

Worksheets (1) .rows (1) \u003d 1

Range (<Диапазон ячеек>) - Returns a reference to the specified range of cells.

Example:

Worksheets ("" List1 "). Usedrange.Value \u003d 1 count - Returns the number of sheets in the book.

Visible - Defines the display of the working sheet in the book. Its valid values: True - the work sheet is displayed; false - the service list is invisible (hidden), but it can be displayed on the screen using the command of the Community, the sheet,

Display (Format, Sheet, Show); xlveryhidden. - The work sheet is hidden and it can be displayed on the screen only programmatically.

Examples:

SUB Empty () Worksheets ("List3"). Visible \u003d False

Sub openly ()

Worksheets ("List3"). Visible \u003d True

SUB empty1 () Worksheets ("List4"). Visible \u003d XLVeryhidden

Excel allows you to place quite a lot different types Objects on a sheet: Charts, figures, pictures and Smartart, for example. To work with the object, you must choose it. The easiest way to choose a separate object is to click on it with the mouse button.

And what if you want to choose several objects? For example, you may need to move multiple objects, delete them or apply formatting. Excel provides various methods Select multiple objects.

Ctrl + Click

Hold down the CTRL key while clicking on objects - one of the ways to select multiple objects.

Panel selection and visibility

Panel Selection and visibilityshown in Fig. 151.1, provides fast way Selection (and hide) objects. To open this panel, select Home Editing Find and highlight the selection area. The panel contains the name of each object on the active sheet. Click on the name of the object, and it will be selected. Press CTRL to select multiple objects. You can also use this panel in order to hide objects (click on the small icon with the image of the eye) and change their order (use the two corresponding buttons at the bottom).

Dialog Box Dialog Box

To select all objects on the sheet, execute the command Home Editing Find and highlight the selection of groups of cells. Next, set the switch to the appropriate position and click OK.

Tool Selection Objects

Another way to select multiple objects is to use object selection tool (select Home Editing Find and select the selection of objects). When you execute this command, Excel goes into a special mode, and the mouse pointer takes the appearance of the arrow. Click the layout on the sheet and drag to select all objects in the rectangular area. To return to normal mode, press the key ESC.