Form controls in MS EXCEL. When to Use Embedded Objects

Laboratory work

STUDY OF THE INTERFACE OF THE SPEECH PROCESSOR MS EXCEL 2010. SIMPLE CALCULATIONS

Goals of the work:

1) get acquainted with the capabilities of the spreadsheet processor;

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

3) to get acquainted with the basic methods of working in MS Excel 2010.

The task:

1) study item 1 "Training material";

2) complete the tasks given in paragraph 2;

3) answer test questions(item 3).

EDUCATIONAL MATERIAL

Basic concepts of a spreadsheet

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

The functions of the spreadsheet processor are very diverse:

§ creating and editing tables;

§ design and printing of tables;

§ Creation of multi-table documents, united by formulas;

§ construction of diagrams;

§ work with ET as with a database; data sampling by request;

§ creation of summary and summary tables;

§ solving "what-if" problems by selecting parameters;

§ solution of optimization problems;

§ creation of macro programs using the built-in programming language Visual Basic for Applications (VBA).

The Excel spreadsheet is part of an integrated software package Microsoft Office.

After starting Excel 2010, a window with the following structure appears on the screen:

1. Program title.

2. Quick Access Toolbar with a panel settings button, located by default at the top of the Excel application window (Fig. 1). Designed for quick access to the most frequently used functions. By default, the panel contains only three buttons: Save, Undo, Redo (Redo). The Quick Access Toolbar can be customized by adding new commands to it.

Rice. 1. Quick Access Toolbar

3. Ribbon. In Excel 2010, menus and toolbars are designed as a ribbon. The commands are organized into logical groups organized on tabs. Tabs task oriented. Groups on each tab, the task is broken down into its components. Command buttons in each group are used to execute commands or display a menu of commands. The icon (button) of the group is a small square in the lower right corner of the group of elements in the tab (Fig. 2). Clicking on an icon opens a dialog box or task pane corresponding to that group for extension functionality. For example, the group icon Font tabs home opens a dialog box Cell Format. A group icon Clipboard displays the task pane Clipboard.Not every group has an icon.

By default, the window displays seven permanent tabs: home, Insert, Page layout, Formulas, Data, Peer review, View.tab home opens by default after starting the program.

Rice. 2. Group buttons

In addition to the permanent whole line contextual tabs, for example, for working with pictures, diagrams, etc., which appear automatically when switching to the appropriate mode or when an object is selected or the cursor is placed on it. In some cases, several tabs appear at once, for example, when working with charts, three tabs appear: Constructor, Layout And Format. There is no way to force the display of contextual tabs.

You can navigate the ribbon using the keyboard: if you press the key , prompts will appear on the ribbon to navigate to the appropriate tab (Fig. 3).

Rice. 3. Ribbon navigation keys

By clicking on the tab, you can see hints on the keys to navigate the current tab (Fig. 4).

Rice. 4. Tab navigation keys home

You cannot replace the ribbon with toolbars or menus from previous versions of Microsoft Excel. You can't delete the tape either. However, the ribbon can be hidden (collapsed) to enlarge the work area. Ways:

1. Press the button Collapse ribbon located on the right side of the tab title bar. Press the button to return Expand Ribbon.

2. To quickly hide/show the ribbon, just double-click on the title of any tab.

3. The tape can be rolled up in another way:

a) right-click anywhere on the ribbon;

b) in the context menu, select the command Collapse ribbon.

4. To minimize or restore the ribbon, you can also press the key combination .

The ribbon can be customized: rename and reorder permanent tabs, create new tabs
and delete them, create, delete, change the arrangement of groups of elements on tabs, add and delete individual elements, etc.:

1) right-click anywhere on the ribbon;

2) in the context menu, select the command Ribbon customization.

Also, the ribbon is configured in the window Excel Options in category Ribbon customization menu File.

4. Tab (menu) "File". tab File always located in the feed first from the left. The menu contains commands for working with files ( Save, Save as, Open, close, Latest, Create), to work with the current document ( Intelligence, Print, Access), as well as to customize Excel ( reference, Parameters). Button Output quits
with application.

5. Mini toolbars. Mini-toolbars (Fig. 5) contain the main most frequently used elements for decorating document text, drawings, diagrams and other objects. Unlike other Office 2010 applications (Word, PowerPoint, etc.), Excel 2010 does not automatically display the minibar when you select a worksheet. To display it, right-click on the selected area. The composition of the elements of mini-panels is constant and unchanged.

Rice. 5. Mini toolbar and context menu

6. Formula bar, consisting of three parts:

a) active cell addresses ;

b) buttons Cancel, Input, which appear in input or edit mode, and buttons Function insertion f x ;

c) the contents of the active cell.

To view and edit the contents of the selected cell, you can increase the height of the formula bar. To do this, click on the button Expand formula bar. If, in this case, not all the contents of the cell are displayed, you can use the scroll bar. To reset the formula bar to its original state, click the button Collapse formula bar. You can remove/display the formula bar as follows: File® Parameters® Additionally® category Screen® Show formula bar.

7. Coordinate string- contains the names of the columns.

8. Coordinate column- contains line numbers.

9. Working field.

10. Horizontal and vertical scrollbars.

11. Sheet tabs with worksheet navigation buttons.

12. Status bar, which indicates the modes of operation Ready, Input, Edit; book view modes Normal, Page Layout, Paged; scale bar.

Basic Excel Objects

1. Column. The Excel table contains 16384 columns (2 14). Columns are identified by Latin letters. Since there are only 26 of them, after Z the designation of the columns goes with double letters AA, AB, AC, ..., GA, GB, GC, ..., HX, HY, HZ, and after the ZZ column - with triple letters AAA, AAB, AAC, ..., AAZ, ABA , … The numbering ends at the XFD column. To quickly jump to the first or last column (row) of the worksheet, you need to press the key<ctrl> and the corresponding cursor key.

2. Line- they are in the table 1048576 (2 20).

3. Cell- the intersection of the row and column. Each cell has a unique address, which indicates the name of the column and the number of the row at the intersection of which it is located. Examples of using A1 style cell addresses are shown in Table 1.

Table 1

Cell style A1

Excel supports alternative cell reference system called R1C1 (from English words row- line and Column- column). In this system, both columns and rows of a table are numbered, and the row number precedes the column number. For example, cell A1 is called R1C1 (row 1, column 1). Cell B1 is R1C2 (row 1, column 2). You can switch to an alternative style and back by going to the menu File® Parameters® Formulas® category Working with formulas® R1C1 link style.

The cell where the cursor is located is called current , and certain actions are being performed with it at a given moment in time.

4. Cell block is a rectangle in which the addresses of the cells of the upper left and lower right corners are indicated, separated by a colon, for example, A1:C5. If a block of cells is specified in the action to be performed, then all its cells are involved.

5. Worksheet - this is a table created for solving a problem, a diagram, a macro, a drawing. The default sheet name is Sheet1, Sheet2, .... Worksheets can be used the following actions:

§ renaming;

§ removal;

§ insert;

§ movement;

§ copying.

These actions are performed using the context menu when the mouse pointer is on the sheet tab or in the group cells tabs home(Fig. 6).

Rice. 6. Group cells tabs home

Menu File® Parameters® Additionally® category Show options for next book® Show sheet tabs allows you to remove/display sheet tabs.

6. Workbook is a file that is stored on disk and contains one or more sheets. By default, the workbook is named Book 1, Book 2 , …. You can save the file using the menu item File® Save. Command Save as is selected if you want to save a file already written to disk under a new name. Excel 2010 files have the extension .xlsx .

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

8. Picture- created with a group Illustrations tabs Insert in the Excel environment itself or can be inserted from another graphics editor.

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

Data types

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

1. Text– any sequence of characters used primarily for table headings, rows, columns, and comments.

2. Number. Three types of numeric data (constants) can be displayed in an Excel cell:

a) whole numbers is a sequence of digits from 0 to 9 with or without a sign: +25; -one hundred.

b) fixed-point real numbers- these are decimal fractions in which the integer part is separated from the fractional comma: 28.25; -3.765.

c) real floating point numbers are numbers written in the following form: 1.5E + 03 or 2E-08. This record is also called exponential notation (scientific format).

By default, a correctly entered number is aligned to the right edge of the cell. An incorrectly entered number is treated as text and left-aligned. If the number does not fit the width of the cell, then the entire cell is filled with the symbol # (Fig. 7).

Rice. 7. Type of numbers on the worksheet

3. Formula - is an expression consisting of numbers, cell addresses, functions, and operation signs, and beginning with a sign = . For example:

A1*20%+12*A1*D12.

The order in which arithmetic operations are performed in a formula is the same as in mathematics.

4. Function- these are programmed formulas that allow you to perform frequently occurring sequences of calculations (standard functions for various purposes).

5.date of is a date, presented in various formats, from which arithmetic and logical operations can be performed. For example, 10/21/2011; October 21, 2011 and others

Copying cells

copying can be done in the following ways:

1) tab home® group Clipboard® team Copy;

2) using the command Copy context menu;

3) using a combination of keys ;

4) using the "drag-and-drop" technique while holding down the key . In this case, a sign appears next to the mouse pointer. + (sign of copying);

5) using a marker in the lower right corner of the copied cell. In the absence of this marker, you must use the command File® Parameters® Additionally® category Edit Options® Allow cell fill and drag handles.

The main MS Excel objects that are described in this section include the following: workbook (Workbook) and a family of workbooks (Workbooks), worksheet (Worksheet) and a family of worksheets (Worksheets), cell range or cell (Range).

After the object, usually through a dot “.”, a property or method is indicated. Sometimes, to get to a certain object, you need to go through the hierarchy of parent objects.

Workbooks("KHMra1.xls").Worksheets("^MCT1").Activate

The WorkBooks("KHMra1") family provides access to the workbook. The name of the book is given in parentheses.

The WorkSheetsE".HMCTl") family provides access to a worksheet. The name of the sheet is given in parentheses.

The Range("^Mana30H") family provides access to a range of cells or a cell. The brackets indicate a range of cells or a cell name.

The Cells(row no., column no.) provides

cell access. Cell coordinates are given in parentheses.

WorkBooks("Book1")

WorkSheets("Sheet1")

Range("Al:B10")

More on the topic Basic objects of MS Excel:

1. 2.1. Information as the main object of the information sphere and system of law
2. § 4. Main types of immovables: Land plots, buildings, structures, premises, objects of construction in progress and enterprises (clause 1239-1244)

Term Excel Objects(understood broadly as the Excel object model) includes the elements that make up any Excel workbook. These are, for example, worksheets ( Worksheets), strings ( Rows), columns ( columns), cell ranges ( Ranges) and the Excel workbook itself ( Workbook) including. Every Excel object has a set of properties that are integral to it.

For example, an object Worksheet(worksheet) has properties Name(name), Protection(protection), visible(visibility), scroll area(scroll area) and so on. Thus, if during the macro execution it is required to hide the worksheet, it is enough to change the property visible this sheet.

IN Excel VBA there is a special type of object - collection. As the name suggests, a collection refers to a group (or collection) of Excel objects. For example, collection Rows is an object containing all the rows of the worksheet.

All core Excel objects can be accessed (directly or indirectly) through the object workbooks, which is a collection of all currently open workbooks. Each workbook contains an object Sheets is a collection that includes all worksheets and sheets with charts in a workbook. Every object Worksheet consists of a collection Rows- it includes all the rows of the worksheet, and collections columns- all columns of the worksheet, and so on.

The following table lists some of the most commonly used Excel objects. Full list Excel VBA objects can be found on the Microsoft Office Developer site (in English).

The table above shows how Excel objects are accessed through parent objects. For example, a reference to a range of cells can be written like this:

Workbooks("Book1").Worksheets("Sheet1").Range("A1:B10")

Assigning an object to a variable

In Excel VBA, an object can be assigned to a variable using the keyword set:

Dim DataWb As Workbook Set DataWb = Workbooks("Book1.xlsx")

active object

There is an active object at any given time in Excel Workbook is the workbook currently open. Similarly, there is an active object Worksheet, active object Range etc.

Refer to active object Workbook or sheet in VBA code, you can either active workbook or ActiveSheet, and on the active object Range- How on Selection.

If a reference to a worksheet is written in the VBA code, without specifying which workbook it belongs to, then Excel refers to the active workbook by default. Similarly, if you refer to a range without specifying a specific workbook or sheet, Excel will default to the active worksheet in the active workbook.

So to refer to a range A1:B10 on the active worksheet of the active workbook, you can simply write:

Range("A1:B10")

Changing the active object

If during the execution of the program it is required to make another workbook active, another worksheet, range, and so on, then you need to use the methods Activate or Select like this:

Sub ActivateAndSelect() Workbooks("Book2").Activate Worksheets("Sheet2").Select Worksheets("Sheet2").Range("A1:B10").Select Worksheets("Sheet2").Range("A5") .Activate End Sub

Object methods, including the methods just used Activate or Select, will be discussed in more detail below.

Object Properties

Each VBA object has properties set for it. For example, an object Workbook has properties Name(name), RevisionNumber(number of saves), Sheets(sheets) and many others. To access the properties of an object, you write the name of the object, followed by a dot, followed by the name of the property. For example, the name of the active workbook can be accessed like this: ActiveWorkbook.Name. So to assign to a variable wbName the name of the active workbook, you can use the following code:

Dim wbName As String wbName = ActiveWorkbook.Name

Earlier we showed how the object Workbook can be used to access an object Worksheet using this command:

Workbooks("Book1").Worksheets("Sheet1")

This is possible because the collection Worksheets is a property of the object Workbook.

Some object properties are read-only, meaning that the user cannot change their values. At the same time, there are properties that can be assigned different values. For example, to change the name of the active sheet to “ My worksheet“, it is enough to assign this name to the property Name active sheet, like this:

ActiveSheet.Name = "My Worksheet"

Object Methods

VBA objects have methods to perform certain actions. Object Methods are procedures attached to objects of a certain type. For example, an object Workbook has methods Activate, close, Save and many more others.

In order to call a method on an object, you need to write down the name of the object, a dot, and the name of the method. For example, to save the active workbook, you can use the following line of code:

ActiveWorkbook.Save

Like other procedures, methods can have arguments that are passed to the method when it is called. For example, the method close object Workbook has three optional arguments that specify whether the workbook should be saved before closing, etc.

To pass arguments to a method, you must record the values ​​of these arguments separated by commas after calling the method. For example, if you want to save the active workbook as a file .csv with the name “Book2”, then you need to call the method SaveAs object Workbook and pass the argument filename meaning book2, and the argument file format- meaning xlCSV:

ActiveWorkbook.SaveAs "Book2", xlCSV

To make the code more readable, you can use named arguments when calling a method. In this case, the argument name is written first, then the assignment operator “ := ” followed by a value. Thus, the above method call example SaveAs object Workbook can be written differently:

ActiveWorkbook.SaveAs Filename:="Book2", :=xlCSV

In the window Object Browser The Visual Basic Editor shows a list of all available objects, their properties and methods. To open this list, start the Visual Basic Editor and click F2.

Let's look at a few examples

Example 1

This VBA code snippet can serve as an illustration of the use of a loop For Each. In this case, we'll use it to demonstrate object references. Worksheets(which is taken from the active workbook by default) and links to each object Worksheet separately. Note that the property is used to display the name of each worksheet. Name object Worksheet.

"Scroll through all the worksheets in the active workbook one by one" and display a message box with the name of each worksheet Dim wSheet As Worksheet For Each wSheet in Worksheets MsgBox "Found worksheet: " & wSheet.Name Next wSheet

Example 2

This VBA code example shows how worksheets and cell ranges can be accessed from other workbooks. In addition, you will make sure that if a reference to a specific object is not specified, then active Excel objects are used by default. This example demonstrates the use of the keyword set to assign an object to a variable.

In the code below, for an object Range method is called PasteSpecial. This method passes an argument paste meaning xlPasteValues.

"Copying a range of cells from sheet 'Sheet1' of another workbook (named Data.xlsx)" and pasting only the values ​​into the 'Results' sheet of the current workbook (named CurrWb.xlsm) Dim dataWb As Workbook Set dataWb = Workbooks.Open( "C:\Data") "Note that DataWb is the active workbook. "Hence, the following action is performed on the Sheets object in DataWb. Sheets("Sheet1").Range("A1:B10").Copy "Pastes the values ​​copied from a range of cells into the "Results" worksheet "of the current workbook. Note that the CurrWb.xlsm workbook is not "active" so must be referenced. Workbooks("CurrWb").Sheets("Results").Range("A1").PasteSpecial Paste:=xlPasteValues

Example 3

The following VBA code snippet shows an example of an object (collection) columns and demonstrates how it is accessed from an object Worksheet. In addition, you will see that when referring to a cell or range of cells in the active worksheet, you don't have to refer to that worksheet. We meet again keyword set, with which the object Range assigned to a variable Col.

This VBA code also shows an example of property access value object Range and changing its value.

"With the help of a loop, we look at the values ​​​​in column A on sheet" Sheet2 "," perform arithmetic operations with each of them and write the result "in column A of the active worksheet (Sheet1) Dim i As Integer Dim Col As Range Dim dVal As Double "Assign variable Col column A of worksheet "Sheet2" Set Col = Sheets("Sheet2").Columns("A") i = 1 "Look through all the cells of the Col column in sequence until an empty cell is encountered Do Until IsEmpty(Col. Cells(i)) "Perform arithmetic operations on the value of the current cell dVal = Col.Cells(i).Value * 3 - 1 "The following command writes the result to column A "of the active sheet. There is no need to include the sheet name in the reference, "because this is the active sheet of the workbook. Cells(i, 1).Value = dVal i = i + 1 Loop

Creating a "Products.xls" worksheet

First, let's look at how you can perform operations on a worksheet. Microsoft Excel from the Access program. To do this, we need a Products.xls file containing an Excel workbook with a single "Products" worksheet, which is a list of products from the Northwind demo database. You can use the file provided on the CD that accompanies the book, or you can create it yourself by exporting the "Products" table to Excel using the menu command Service, Office Links, Analysis in MS Excel(Tools, Office Links, Analyze It with Microsoft Excel) (See section "Quick export of data to other Microsoft Office applications" Chapter 3).

To prepare the Products.xls file for further experiments, follow these steps:

1. Open the Products.xls file by running Microsoft Excel.
2. Select cells A4 through D12 in the worksheet. Select a menu command Insert, Name, Assign(Insert, Name, Define). In the dialog box Naming(Define Name) enter in the text box Name(Names in workbook) name of the range: workRange (Fig. 15.25) and click on the button OK. This creates a named range that will be used in later examples.

Rice. 15.25. Create a named range in the "Products.xls" table

1. Select from the menu Service(Tools) command add-ons(Adds-Ins) and uncheck all checkboxes in the list List of add-ons(Add-Ins Available) Dialog Box add-ons(Add Ins). Removing add-ins reduces the time it takes to start Excel. To close the dialog box, click the button OK.
2. Save the changes by running the command File, Save(File, Save) or File, Save As(File, Save As).
3. Close Microsoft Excel.

Microsoft Excel VBA Object Hierarchy

In order to programmatically work with Excel objects, you need to have an understanding of object model Microsoft Excel. We will not describe this model in detail here, since it is rather complicated, we will present only its main objects.

• The Application object is an instance of Microsoft Excel itself. By applying various methods to this object, you can use almost any Excel menu command. The Application object has the properties of an ActiveWorkbook and

  ActiveSheet, which point to the current Workbook (workbook) and Worksheet (worksheet) objects. You can specify Excel.Application as the argument value<класс>functions CreateObject () and GetObjectO, as well as in the operator Dim objPlMH As New<класс>.

• The main object of Excel - Workbook (workbook). This object provides access to the properties and structure of an Excel workbook. Workbook objects are files that consist of several objects created by Microsoft Excel: Worksheet (worksheet) and Chart (chart). The Worksheet and Chart objects are contained in the Worksheets and Charts families.
• Worksheet objects are subordinate to the Workbook object and provide access to the properties and structure of worksheets in an Excel workbook. The main object of interaction between Microsoft Access 2002 and Microsoft Excel 2002 is the Worksheet object. It is possible to transfer information from the rows and columns of the Microsoft Access Recordset object to the cells of the Microsoft Excel Worksheet object and vice versa. If as an argument value<класс>function GetObject() or in operator Dim obj Name As New<класс>specify Excel.Sheet, then by default the first object of the Worksheets family will open - the first worksheet (ActiveSheet) of the Workbook object.
• Sheet objects are similar to Worksheet objects, but they are part of the Sheets family, which includes both Worksheet objects and Chart objects that represent chart sheets in a workbook.
• Range object - a range of worker cells Excel sheet. This can be a single cell, a row, a column, a rectangular area of ​​a worksheet, or an unlinked (arbitrary set of cells), or a 3D range of cells that includes cells across multiple worksheets. You can get or set the values ​​of a range of cells by defining a Range object. Such objects as a cell, row, column simply do not exist. It is the Range object with its properties and methods that provides work both with a single cell and with their range. You can use the name of the range that contains those cells to refer to the desired group of cells. If there are no named ranges on the worksheet, you can use the worksheet object's Cells method to set the coordinates of a cell or group of cells. The following are two ways to change the value of cell A1:

Microsoft Excel also provides many other objects for use as server application objects, but the types described above are the most commonly used in Access VBA automation technology.

Opening and working with an existing Excel worksheet

Before working with automated Microsoft objects Excel, set a link to the Microsoft Excel Object Library. For this:

1. Start Access and, if necessary, open the Northwind database. Call the VBA editor by opening any module.
2. Select the menu command in the VBA editor window Service, Links(Tools, .References) to open the dialog References(Links).
3. Check the box next to the element Microsoft Excel 10.0 Object Library(Fig. 15.26), then press the button OK, to close the window references.

Rice. 15.26.

Automation commands can be easily explored using the debug window Immediate. Therefore, display this window if it is not displayed. To do this, just click the appropriate button on the toolbar or a key combination +.

To programmatically open the "Products" worksheet worksheet:

1. Close Excel if it is running.
2. Create new module by executing the menu command Insert, Module(Insert, Module).
3. Add the following variable declarations to the declaration section:

1. Enter the following statement in the debug window (Figure 15.27):

When you then press the key Microsoft Excel starts in /automation mode. The CurDir function returns the fully qualified name of the current folder. If the Products.xls file was saved somewhere else, change the path to this file in the previous statement. Depending on the speed of your computer, it may take quite a while for Excel to start up. Loading Excel application completed when the status bar of the debug window reads Performance(Running) disappears and the inscription Ready (Ready) appears. This will create an instance of the Application Microsoft Excel class and set the xlwProd variable to a reference to the Workbook object. Note that the Getobject() function opens a hidden instance of the Excel application, the Excel icon does not appear on the taskbar, and you cannot interactively access the Excel workbook.

Comment

IN given operator Excel argument. Sheet is optional. If it is not specified, then the type of the created object will be determined automatically by the extension of the file specified in the first argument.

Rice. 15.27.

1. To make sure the workbook is open and we can access it, enter the following command in the debug window?xlwProd.Name

The Name property of the created workbook object contains the name of the Excel file: Tova-pbi.xls (Figure 15.28).

Rice. 15.28. Commands that allow you to read and set the values ​​of individual cells in the "Products" worksheet

1. The Products.xls file contains only one Worksheet object, so the Products worksheet is the active worksheet, an ActiveSheet object. To verify this, enter the command in the debug window:

The Name property of this object contains the name of the worksheet: Products.

1. Now try to refer to the first cell of the worksheet. Enter?xlwProd.ActiveSheet.Celled, 1). After a short pause, the expected result will appear - the line "Product Code". This is the heading of the first column of the table.
2. The Cells method allows you to access any cell in the worksheet. Enter?xlwProd. ActiveSheet. Cells (R, C), where R is the row number, and C is the column number of the specified cell, i.e. its coordinates (Fig. 15.28).
3. You can change the contents of a cell by entering an expression like: xlwProd.ActiveSheet.Cells(2,2).Value = "(!LANG:brown rice". Подобно тому как многие объекты Access при введении имени объекта возвращают его значение, метод Cells не требует явного указания свойства Value по умолчанию. Чтобы убедиться в том, что содержимое ячейки изменилось, введите?xlwProd.ActiveSheet.Cells (3,2), не дописывая выражения.Value.!}

You can also use the Formula property to set the value of a cell. The advantage of using the Formula property is that it can be used to enter formulas using native Microsoft Excel syntax, i.e. as cell references, such as "=A2+C6".

Using named ranges of cells

If you have created a named range of cells in an Excel worksheet, you can get the values ​​of the cells contained in that range by referring to the Range property of the Worksheet object. First, let's see what named ranges are present in the object we opened. Enter the command in the debug window (Fig. 15.29)

XlwProd.Names(1).Name

The Names collection represents all the names defined in the workbook. In this case, the first element of this family contains the name of the range: WorkRange.

You can see not only the name, but also what this range represents. Enter command

XlwProd.Names(1).Value The result will be: =Products!$A$4:$D$12,

i.e. rectangular area A4-D12 on the "Products" worksheet.

On fig. 15.29 shows expressions for controlling the Range object.

Rice. 15.29.

Let the xlsProd variable refer to the "Products" worksheet. To do this, enter the command:

Set xlsProd = xlwProd.ActiveSheet.

You can use the following statement to specify a specific cell within a named Range object:

XlsProd.Range("WorkRange").Cells(1,1)

This uses the Range property of the Worksheet object to access the named range, and then the Cells property of the Range object to specify a specific cell in the range. The first number indicates the row and the second indicates the column.

To refer to an object that is one level higher in the model object hierarchy, you can use the Parent property. On fig. Figure 15.29 shows how to access the Excel workbook containing the current worksheet and how to set the xlaProd object variable to refer to the Microsoft Excel Application object:

Set xlwWorkbook = xlsWorksheet.Parent.

Closing objects Workbook And Application

The Microsoft Worksheet object cannot be closed. The Close method can be used to close an Excel Workbook object, and the Quit method can be used to exit the application. Following statements close the Workbook object and then exit the automation server application, freeing up system resources:

XlwProd.Close xlaProd.Quit Set xlsProd = Nothing Set xlwProd = Nothing Set xlaProd = Nothing

If you have programmatically made changes to the worksheet, then when you close the Workbook object, you will be asked if you want to save the changes. If you don't want the user to be asked this question, enter the False argument for the Close method. To ensure that all resources are freed, all used object variables must be freed.

Comment

After setting a variable pointing to an Application object to Nothing, the corresponding application is not closed, although the memory occupied by the variable is freed. Therefore, it is necessary to close the application with the Quit method before releasing the corresponding object variable.

Creating an Excel Worksheet with Automation Code

The same actions that occur when a button is pressed Analysis in MS Excel, can be done using VBA automation code. The advantage of this method is the ability to format the created object specifically for the needs of a particular application. Consider the CreateCustomSheet() function, which creates a new Worksheet object and fills it with data from the "Products" table of the database. Microsoft data access:

Function CreateCustomSheet() As Integer "Create an MS Excel worksheet from the "Products" table "Declaration of local variables" (Object variables are defined at the module level) Dim saeBorey As Database "Current database Dim rstProd As Recordset "Recordset object Dim intRow As Integer " Row Count Dim intCol As Integer "Column Count "Opening a table in the current database Set dbNorthwind = CurrentDb() Set rstProd = dbBopeu.OpenRecordset("Products", dbdpenTable) DoCmd.Hourglass True "Creating a new Excel Workbook object Set xlwProd = CreateObject( "Excel.Sheet") ""Creating an Application object to apply the Quit Set method xlaProd = xlwProd.Parent intRow = 1 intCol = 1 rstProd.MoveFirst "Go to the first record Do Until rstProd.EOF "One-record loop For intCol = 1 Then rstProd.Count "Loop with one field step If (Not IsNull(rstProd(intCol -1))) Then xlwProd.ActiveSheet.Cells(intRow, intCol).Value = CStr(rstProd(intCol -I)) End If Next intCol rstProd.MoveNext intRow = intRow + 1L oop For intCol = 1 To xlwProd.ActiveSheet.Columns.Count "Format each worksheet column xlwProd.ActiveSheet.Columns(intCol).Font.Size = 8 xlsCust.ActiveSheet.Columns(intCol).AutoFit If intCol = 8 Then "Align left-aligned numeric and "mixed postal codes xlwProd.ActiveSheet.Columns(intCol).HorizontalAlignment = _xlLeft End If Next intCol DoCmd.Hourglass False xlwProd.SaveAs(CurDir & "\Products_2.xls") xlaProd.Quit End Function

The data type returned by the rstProd(intCol-l) expression must be specifically changed from variant to string using the cstr() function, otherwise Microsoft Excel will display #N/A# in the corresponding column instead of the desired value. If the Recordset object contains fields whose types are not Text, then use the appropriate CType() function to determine the data type in the column.

The xlLeft constant assigned as the value to the eighth column's HorizontalAlignment property is a built-in Excel constant that is defined when a reference to the Microsoft Excel 10.0 Object Library is established. Selecting an element Constants(Constants) in the list Modules/ Classes the object viewer window, when the Excel library is connected, displays xlConst constants. On fig. 15.30 shows the numerical value of the xlLeft constant, which is one of the constants for setting the value of the HorizontalAlignment property.

Rice. 15.30. Values ​​of built-in xlConst constants in a window Object Browser Access

Operator input? CreateCustomSheet() in the debug window runs the function we discussed above. On fig. 15.31 shows the workbook "ToBapy_2.xls" with a worksheet created using the CreateCustomSheet () function and opened in Microsoft Excel.

Rice. 15.31. Part of an Excel worksheet created from the "Products" table

Practice shows that Microsoft Office applications (Excel, Word, Power Point, etc.) are among the most commonly used Windows applications. Each of them is a COM server, and therefore, any object included in it can be used by your program as its own.

There are two ways to access the methods and properties of a COM object: by referencing its type library (early binding) and by name (late binding). For Object Pascal, early binding is preferable, since in this case the compiler can check the correctness of accessing the properties and methods of external objects, and the code it creates is usually executed faster. In the same time base language Accessing Microsoft Office Servers - Visual Basic for Application (VBA) does not support pointers and therefore cannot use interfaces. Especially for this kind of languages ​​(in addition to VBA, pointers do not work either JavaScript languages, SmallTalk, and some others), COM technology introduced dispinterfaces that allow you to access methods and properties by name, and not by address. When you install Office, you can install the VBA help, which describes the interfaces in detail Microsoft servers Office, indicating the purpose of methods and properties, as well as the parameters for accessing them. In fact, these are the only documents available to the programmer that he should rely on when programming access to the powerful features of Microsoft Office servers. Note that the standard installation of Microsoft Office does not install VBA help. If in the Program Files | Microsoft Office | Office you will not find vbaxl8.hlp (Excel help), vbawrd8.hlp (Word help), etc., you must add them using the Start | Setting | Control panel | The installing and deleting of programms.

Version 5 of Delphi includes components of the Servers page that allow you to access the COM objects of these servers using type libraries, but these components are practically not documented. Moreover, the libraries themselves are already implemented in the dclaxserver50 package, so I was never able to get their texts using this version of Delphi. In all cases, studying the extensive texts of libraries (for example, the Excel_TLB.pas file contains more than 20 thousand lines) does little even for an experienced programmer.

This section provides a brief description of the main objects of the two most popular servers, Excel and Word, as well as examples of how to use Excel in VBA style (by name) and using the components of the Servers page. Since the VBA language has been significantly extended specifically for the version of MS Office 97, this material cannot be used to work with earlier versions of the package.

Core Excel and Word Server Objects

VBA terminology uses the concepts of "object" and "collection". An object is a normal COM interface object that has properties, methods, and events. A collection is a group of objects of the same type. For example, the main Excel server object - Application defines the main properties and methods of the server, and the Worksheets collection represents a set of table pages in the current workbook, and so on. The object and collection hierarchies shown below are taken from the vbaXXX.hlp files. Unlike VCL objects, they are built not according to the principle of inheritance, but according to functional subordination.

Excel Objects

Excel Server is a powerful spreadsheet processor that implements the placement and processing of various kinds of data (both numeric and text), including the construction of graphs and charts based on them. When working with Excel, a so-called workbook (data file) is created with one or more sheets. All sheets of one workbook can be linked to each other, which allows you to organize joint calculations on the data placed on them.

Now a little explanation. The variables Sheet and Range are introduced only to shorten the text of the program: everywhere instead of Sheet, for example, you can write Excel.Workbooks.Sheets. Delphi 4 shipped with the XLCONST.PAS and XLCONST.DCU files, which define the xlXXX constants used in the vbaxl8.hlp documentation. Version 5 doesn't ship these files, so I use their numerical equivalents. The margin widths of an Excel printed document are specified in internal units corresponding to approximately 3.5 mm, so the values ​​specified in the Sheet.PageSetup.XXXMargin statements will set the left, bottom, and right margins to 1.1 cm and the top to 1.4 cm. Width column is defined in characters of text that fits in the column without clipping.

The Excel variable defines a field of the TForm1 class. When a class is created, a VarEmpty value is automatically placed in it. After finishing working with Excel, the user can close it. But in my program, Excel was not visualized, its work took place "behind the scenes", and the created table was written to the file specified by the user using the Excel.Workbooks.SaveAs(FileName) operator.

After that, Excel closed. Since in our case Excel shows its window, and the user may not close it, it is useful to write the following form's OnDestroy event handler:

Procedure TForm1.FormDestroy(Sender: TObject); begin if not VarIsEmpty(Excel) then Excel. Quit end;

When running the example, keep in mind that creating a price list using Excel is a rather lengthy process. On my computer (400 MHz, 64 MB) it took about a minute (for example, a similar price list is created using Quick Report in less than 2 seconds). At the end of the handler, the total running time is placed in the lb label.

early binding

The following example is functionally similar to the previous one. It also creates a price list using Excel, but this time it uses access directly through the server interfaces. A “surprise” awaits you: the execution time of the second example is 40 seconds longer! I could not find a reasonable explanation for this phenomenon, but both examples are on the accompanying disk, so you can see for yourself at any time.

Since the shape of the second example is exactly the same as the shape of the first one, I will not explain what needs to be done to create it. Add only the TExcelApplication component to the form and set its properties: Name=Excel, AutoConnect=True, AutoQuit=True. If you are using the form from the previous example as a template, do not insert the Excel field into the TForm1 class. The Button1Click handler should look like this (see Listing 2).

As you can see, it is very similar to the handler of the previous example. So I'll focus on the differences.

When accessing the SheetsInNewWorkbook property, as in many other cases of accessing interface properties and methods, a localization language identifier (lcid) must be specified. The value 0 encodes the default language. The same identifier is passed as the second parameter of the call to the Excel.Workbooks.Add method. The first parameter must be the file name (in WideString format) if the workbook has already been created, or the "empty" parameter EmptyParam if the workbook is being created for the first time.

All my attempts to work with Range objects have failed. To avoid judging me too much, I've placed the Excel_TLB.pas type library in the example directory - look through it at your leisure and try to find the right solution for changing the width of the columns and margins of the sheet, as well as for coloring the range, text alignment, etc.

There are some nuances when referring to cells. First, they are owned by the Application object, not the Sheet. Secondly, access to a specific element of the Cells collection (as well as any other collection) is possible only through its Item property.

Summing up, once again I want to draw your attention to the fact that, in terms of execution time, late binding at least does not lose to early binding - at least for the considered examples. Considering that the only documents on MS Office servers available to the vast majority of programmers are the help files vbaXXX.hlp, we can conclude that using variants (late binding) is simpler, more convenient, and most importantly, much clearer than working directly with interfaces (early binding) .

ComputerPress 6 "2001