Operations with Microsoft Excel objects. Form controls in MS EXCEL

Form controls (Combobox, Checkbox, Counter, etc.) help you quickly change the data on the sheet in a certain range, turn options on and off, make selections, etc. In principle, you can do without them, but they do control the data on the sheet more visual and reduce the possibility of entering incorrect data.

To insert controls on a sheet, you must display a tab Developer.

In MS EXCEL 2007, this can be done through the menu Office button/ Excel Options/ General/ Show Developer tab on the ribbon .
In MS EXCEL 2010, this can be done like this: Open the tab File; Click the button Options; Click the button Customize Ribbon; Choose a team Ribbon Customization and Main Tabs check the box Developer.

Now you can insert a control through the menu: .

Note that this menu allows you to insert ActiveX Controls that are located below the form controls of interest to us. Both types have the same elements Button, List, Checkbox, etc. The difference between the two is that to use ActiveX Controls you must use VBA, while Form Controls can be directly bound to a cell on a sheet.

For those who have not previously worked with Form Controls, we recommend that you familiarize yourself with them in detail in the following articles:

In this article, we will look at a more complex example of sharing controls and .

Example

Let's analyze a specific example of using several Controls at once. The sample file shows how the user can edit the values in the table using controls (range F9:K12 ).

With one of 3 controls Combo Box, List and Counter, user can select table column (year) . The desired control is selected using the group switches. The name of the selected element is highlighted in gray (see Fig. A8:B8 in fig. above). The selected year is highlighted in the table in dark gray (see Fig. H9:H12 in fig. above). The display of this selection is adjustable checkbox(flag background is red). scrollbars can be edited price and Quantity in the selected year, but only within a certain range. Now - in more detail.

Switches

First, let's insert an element into the sheet. With this element, we will turn on and off the selection in the table of the column of the selected year. Link element to cell $G$2 . If the checkbox is unchecked, then this cell will be FALSE (this value corresponds to 0), if checked, then TRUE (this value corresponds to 1).

Select a range for setting. G9:K12 . Since the formula in Conditional Formatting will contain , we will make sure that after selecting the range, the active cell is G9 (i.e., the range must be selected starting from it. The hint is the field Name, located to the left of Formula bars. After selecting a range, it should contain G 9 ).

call the tool ( Home/ Styles/ Conditional Formatting/ Create Rule);
select Use Formula to define formatted cells;
in field " Format values for which the following formula is true» enter =AND(COLUMN(G9)=YearOffset+6,$G$2) The formula will become TRUE when 2 conditions are met simultaneously:
expression value ( OffsetYear(changes from 1 to 5 (i.e. 2009 to 2013) + 6) matches the current column number (7, i.e. 2009);
Checkbox Conditional Formatting installed.
select the required format, for example, gray fill color;
click OK.

Testing

make sure the checkbox Conditional Formatting installed;
select switch List;
in the control List choose 2010;
make sure the column 2010 highlighted in grey;
scroll bar change the quantity in column 2010.

The result is shown in the figure.

Unfortunately, form controls Checkbox, Combo Box and List there is no way to format the displayed font. But this can be done for ActiveX controls ( Developer/ Controls/ Paste). True, to work with these elements, you need to write a program in VBA.

To facilitate data entry into a table in Excel, you can use special forms that will help speed up the process of filling a table range with information. Excel has a built-in tool that allows you to fill in this way. Also, the user can create his own version of the form, which will be maximally adapted to his needs, using a macro for this. Let's take a look at the different uses for these useful fill tools in Excel.

The filling form is an object with fields whose names correspond to the names of the columns of the columns of the table being filled. You need to enter data into these fields and they will immediately be added as a new line to the table range. The form can act both as a separate built-in Excel tool, and located directly on the sheet as its range, if it is created by the user himself.

Now let's look at how to use these two types of tools.

Method 1: Built-in object for Excel data entry

First of all, let's learn how to use the built-in form for Excel data entry.

It should be noted that by default the icon that launches it is hidden and needs to be activated. To do this, go to the tab "File", and then click on the item "Options".

In the Excel options window that opens, move to the section "Quick Access Toolbar". Most of the window is occupied by an extensive settings area. On the left side there are tools that can be added to the quick access toolbar, and on the right side there are those that are already present.
In field "Select commands from" set value "Commands not on the ribbon". Further, from the list of commands, arranged in alphabetical order, we find and select the position "The form…". Then click on the button "Add".

After that, the tool we need will be displayed on the right side of the window. Click on the button OK.

Now this tool is located in the Excel window on the Quick Access Toolbar, and we can use it. It will be present when any workbook is opened by this instance of Excel.

Now, in order for the tool to understand what exactly it needs to fill in, you should design the table header and write any value in it. Let the table array we have will consist of four columns that have names "Name of product", "Quantity", "Price" and "Sum". We enter these names in an arbitrary horizontal range of the sheet.

Also, in order for the program to understand exactly which ranges it will need to work with, you should enter any value in the first row of the table array.

After that, select any cell of the table blank and click on the icon on the quick access toolbar "The form…", which we previously activated.

So, the window of the specified tool opens. As you can see, this object has fields that correspond to the names of the columns of our table array. In this case, the first field is already filled with a value, since we entered it manually on the sheet.

Enter the values that we consider necessary in the remaining fields, and then click on the button "Add".

After that, as we can see, the entered values were automatically transferred to the first row of the table, and in the form there was a transition to the next block of fields, which correspond to the second row of the table array.

We fill the tool window with the values that we want to see in the second row of the table area, and again click on the button "Add".

As you can see, the values of the second row were also added, and we didn’t even have to rearrange the cursor in the table itself.

Thus, we fill the table array with all the values that we want to enter into it.

In addition, if desired, you can navigate through previously entered values using the buttons "Back" and "Further" or vertical scrollbar.

If necessary, you can correct any value in the table array by changing it in the form. In order for the changes to be displayed on the sheet, after making them in the corresponding block of the tool, click on the button "Add".

As you can see, the change immediately occurred in the table area.

If we need to delete some line, then through the navigation buttons or the scroll bar we go to the block of fields corresponding to it in the form. After that, click on the button "Delete" in the tool window.

A warning dialog box opens, informing you that the row will be removed. If you are confident in your actions, then click on the button OK.

As you can see, the line was extracted from the table range. After filling and editing is completed, you can exit the tool window by clicking on the button "Close".

After that, to give the table array a more visual visual appearance, formatting can be done.

Method 2: Create a UserForm

In addition, with the help of a macro and a number of other tools, it is possible to create your own user form to fill in the table area. It will be created directly on the sheet, and represent its range. With the help of this tool, the user himself will be able to implement those features that he considers necessary. In terms of functionality, it will practically not be inferior to the built-in analogue of Excel in anything, and in some ways, perhaps, it will surpass it. The only downside is that you will have to create a separate form for each table array, and not use the same template, as is possible with the standard version.

As in the previous method, first of all, you need to make a header for the future table on the sheet. It will consist of five cells with names: "No. p / p", "Name of product", "Quantity", "Price", "Sum".

Next, we need to make a so-called “smart” table from our table array, with the ability to automatically add rows when filling adjacent ranges or cells with data. To do this, select the header and, being in the tab "Home", click on the button "Format as table" in the toolbox "Styles". This opens a list of available style options. The choice of one of them will not affect the functionality in any way, so we simply choose the option that we consider more suitable.

Then a small table formatting window opens. It specifies the range that we previously selected, that is, the range of the header. As a rule, everything is filled in this field correctly. But we should check the box next to the parameter "Table with headers". After that, click on the button OK.

So, our range is formatted like a "smart" table, as evidenced even by the change in visual display. As you can see, among other things, filtering icons appeared next to each column header name. They should be disabled. To do this, select any cell of the "smart" table and go to the tab "Data". There on the ribbon in the toolbox "Sort and Filter" click on the icon "Filter".

There is another option to disable the filter. In this case, it will not even be necessary to switch to another tab, remaining in the tab "Home". After selecting a table area cell on the ribbon in the settings block "Editing" click on the icon "Sort and Filter". Select an item from the list that appears. "Filter".

As you can see, after this action, the filtering icons disappeared from the table header, as required.

Next, we need to create the data entry form itself. It will also be a kind of tabular array consisting of two columns. The names of the rows of this object will correspond to the names of the columns of the main table. Columns are an exception. "No. p / p" and "Sum". They will be absent. The numbering of the first of them will be done using a macro, and the calculation of the values in the second will be done by applying the formula for multiplying the quantity by the price.
Let's leave the second column of the data input object empty for now. Values will be entered directly into it later to fill the rows of the main table range.

After that, we create another small table. It will consist of one column and it will contain a list of products that we will display in the second column of the main table. For clarity, the cell with the title of this list ( "Product List") can be filled with color.

Then we select the first empty cell of the value input object. Go to tab "Data". Click on the icon "Data validation", which is located on the ribbon in the toolbox "Working with data".

The input validation window will open. Click on the field "Data type", which has the parameter set by default "Any Value".

Choose a position from the options that appear. "List".

As you can see, after that the window for checking input values has somewhat changed its configuration. Added an extra field "Source". We click on the icon to the right of it with the left mouse button.

Then the input validation window is minimized. We select with the cursor with the left mouse button pressed the list of data that are placed on the sheet in the additional table area "Product List". After that, again click on the icon to the right of the field in which the address of the selected range appeared.

There is a return to the window for checking the entered values. As you can see, the coordinates of the selected range are already displayed in the field "Source". We click on the button OK at the bottom of the window.

Now, to the right of the selected empty cell of the data entry object, an icon in the form of a triangle has appeared. When you click on it, a drop-down list opens, consisting of names that are pulled from the table array "Product List". It is now impossible to enter arbitrary data into the specified cell, but you can only select the desired position from the list presented. Select an item in the dropdown list.

As you can see, the selected position is immediately displayed in the field "Name of product".

Next, we will need to assign names to the three input form cells where we will enter data. Select the first cell, where the name is already set in our case "Potato". Next, go to the range name field. It is located on the left side of the Excel window at the same level as the formula bar. Enter an arbitrary name there. It can be any name in Latin, in which there are no spaces, but it is better to use names that are close to the tasks solved by this element. Therefore, the first cell, which contains the name of the product, we will call Name. Write this name in the field and press the button Enter on keyboard.

In the same way, we assign the name to the cell in which we will enter the quantity of goods Volume.

And the cell with the price - Price.

After that, in exactly the same way, we give the name to the entire range of the above three cells. First of all, we will select, and then we will give it a name in a special field. Let this be the name Range.

After the last action, we must save the document so that the names that we assigned can be perceived by the macro we created later. To save, go to the tab "File" and click on the item "Save as…".

In the save window that opens, in the field "File type" choose a value "Excel macro-enabled workbook (.xlsm)". Next, click on the button "Save".

Then you should in your version of Excel and enable the tab "Developer" if you haven't already done so. The fact is that both of these functions are disabled by default in the program, and their activation must be forced in the Excel options window.
After you have done this, go to the tab "Developer". Click on the big icon "Visual Basic", which is located on the ribbon in the toolbox "The code".

The last action causes the VBA macro editor to launch. In the area of Project, which is located in the upper left part of the window, select the name of the sheet where our tables are located. In this case, this "Sheet 1".

After that, go to the lower left area of the window called "Properties". Here are the settings for the selected sheet. In field "(Name)" Cyrillic name should be replaced ( "Sheet1") to the name written in Latin. You can give any name that is more convenient for you, the main thing is that it contains only Latin characters or numbers and there are no other characters or spaces. It is with this name that the macro will work. Let in our case this name be product, although you can choose any other that meets the conditions that were described above.
In field Name you can also change the name to a more convenient one. But this is optional. The use of spaces, Cyrillic and any other characters is allowed. Unlike the previous parameter, which specifies the name of the sheet for the program, this parameter assigns a name to the sheet that is visible to the user in the shortcut bar.

As you can see, after that the name will automatically change. Sheet 1 in the area of Project, to the one we just set in the settings.

Then go to the central area of the window. This is where we will need to write the macro code itself. If the white code editor field in the specified area is not displayed, as in our case, then click on the function key F7 and it will appear.

Now, for our specific example, we need to write the following code in the field:
Sub DataEntryForm()
Dim nextRow As Long
nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row
With production
If .Range("A2").Value = "" And .Range("B2").Value = "" Then
nextRow = nextRow - 1
End If
Production.Range("Name").Copy

.Range("A2").Formula = "=IF(ISBLANK(B2), """", COUNTA($B$2:B2))"
If nextRow > 2 Then
Range("A2").Select

End If
.Range("Range").ClearContents
end with
end sub

But this code is not universal, that is, it is only suitable for our case unchanged. If you want to adapt it to your needs, then it should be modified accordingly. So that you can do it yourself, let's look at what this code consists of, what should be replaced in it, and what should not be changed.

So the first line:

Sub DataEntryForm()

"DataEntryForm" is the name of the macro itself. You can leave it as it is, or you can replace it with any other that meets the general rules for creating macro names (no spaces, using only Latin letters, etc.). Changing the name won't affect anything.

Wherever the word occurs in the code product you must replace it with the name that you previously assigned to your sheet in the field "(Name)" areas "Properties" macro editor. Naturally, this should only be done if you named the sheet differently.

Now consider this line:

nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row

Number "2" in this line means the second column of the sheet. It is in this column that the column is located "Name of product". According to it, we will count the number of rows. Therefore, if in your case a similar column has a different order in the account, then you need to enter the appropriate number. Meaning "End(xlUp).Offset(1, 0).Row" in any case, we leave it unchanged.

If .Range("A2").Value = "" And .Range("B2").Value = "" Then

"A2" are the coordinates of the first cell in which the row numbering will be displayed. "B2"- these are the coordinates of the first cell on which data will be output ( "Name of product"). If they are different for you, then enter your data instead of these coordinates.

Go to line

Production.Range("Name").Copy

It contains the parameter Name mean the name we gave the field "Name of product" in the input form.

In lines

Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues
.Cells(nextRow, 3).Value = Producty.Range("Volum").Value
.Cells(nextRow, 4).Value = Production.Range("Price").Value
.Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Value

denominations Volume and Price mean the names that we have assigned to the fields "Quantity" and "Price" in the same input form.

In the same lines that we indicated above, the numbers "2", "3", "four", "5" mean the numbers of the columns on the Excel sheet corresponding to the columns "Name of product", "Quantity", "Price" and "Sum". Therefore, if in your case the table is shifted, then you need to specify the corresponding column numbers. If there are more columns, then by analogy, you need to add its lines to the code, if there are fewer, then remove the extra ones.

The line multiplies the quantity of the product by its price:

Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Value

The result, as we see from the record syntax, will be displayed in the fifth column of the Excel sheet.

This expression performs automatic line numbering:

If nextRow > 2 Then
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & nextRow)
Range("A2:A" & nextRow).Select
End If

All values "A2" mean the address of the first cell where the numbering will be done, and the coordinates " A"- the address of the entire column with numbering. Check where exactly the numbering will be displayed in your table and change these coordinates in the code, if necessary.

The line clears the range of the data entry form after the information from it has been transferred to the table:

Range("Diapason").ClearContents

It's not hard to guess that Range) means the name of the range that we previously assigned to the data entry fields. If you gave them a different name, then that is what should be inserted in this line.

The rest of the code is universal and will be made unchanged in all cases.

After you have written the macro code into the editor window, you should click on the save floppy disk icon on the left side of the window. Then you can close it by clicking on the standard window close button in the upper right corner.

After that, we return to the Excel sheet. Now we need to place a button that will activate the created macro. To do this, go to the tab "Developer". In the settings block "Controls" click the button on the ribbon "Insert". A list of tools opens. In the tool group "Form Controls" choose the first one - "Button".

Then, with the left mouse button held down, we circle the area where we want to place the macro launch button, which will transfer data from the form to the table.

After the area is circled, release the mouse button. Then the window for assigning a macro to an object is automatically launched. If your book uses several macros, then select the name of the one that we created above from the list. We call it "DataEntryForm". But in this case, there is only one macro, so just select it and click on the button OK at the bottom of the window.
You can then rename the button however you want by simply highlighting its current name.

In our case, for example, it would be logical to give her a name "Add". Rename and click on any free cell of the sheet.

So, our form is completely ready. Let's check how it works. Enter the required values in its fields and click on the button "Add".

As you can see, the values are moved to the table, the line is automatically assigned a number, the amount is calculated, the form fields are cleared.

Fill out the form again and click on the button "Add".

As you can see, the second row is also added to the table array. This means the tool is working.

Annotation: The lecture is devoted to the description of the MS Excel object model and detailed description methods, properties, and events of the Application.

13.1. Features of programming for MS Excel

Microsoft Office Excel are popular spreadsheets. Usually, programming for this program, they pursue the following goals:

Automation of calculations.
Automation of input and processing of information.
Working with databases - output, input, analysis, visualization of information.
Analysis of financial and other information.
Creation of systems for organizing automated data entry
Math modeling.

In general programming in Excel is similar to working with Microsoft Word. However, one of the main differences is that in Excel the workspace of the worksheet is divided into cells, each of which has its own name. Cell names can be of two types.

The first kind (A1 style) is the name, consisting of a literal column name and a row number. For example, A1 is the cell located at the intersection of column A (the first one) and the first row.
The other kind is cell indexes (naming style R1C1 ). To address a cell in this style, indicate the row number (R - Row - line) and column number (C - Column - column), at the intersection of which the cell is located. The rows are initially numbered, and the column numbers start from 1 - the first column corresponds to column A, the second - B, and so on. For example, (2, 3) is the address of the cell located at the intersection of the second row and the third column, that is, if we shift this to style A1, we get cell C2 (Fig. 13.1.)

Rice. 13.1.

The following objects are used to perform most operations in MS Excel.

Excel.Application (Application) - An object representing the Microsoft Excel application, similar to Word.Application .
Workbook(Workbook) - represents a workbook - analogous to a Microsoft Word document. However, in Word we work with data located in a document, and in Excel there is one more object on the way to the data - a worksheet.
Worksheet(Worksheet) - a workbook in MS Excel is divided into worksheets. It is on the sheet that cells are located that can store information and formulas.
Range (Range) - can be represented as a single cell or a group of cells. This object replaces many objects for working with document elements (character , word , etc.) that are used in Microsoft Word. As a result, work with the sheet becomes very clear and convenient - to work with any cell, you only need to know its name (in A1 format) or address (R1C1).
QueryTable (Query Table) - this object is used to import information from databases into Microsoft Excel. Database connection, information request, etc. are produced by means of the object, and the query results are uploaded to the MS Excel sheet in the form of a regular table.
PivotTable (PivotTable) is a special kind of Excel spreadsheet - it allows you to interactively summarize and analyze large amounts of information, in particular, taken from a database.
chart(Diagram) - represents a diagram. They are usually used for data visualization.

Let's start the review MS Excel object model from the Application object.

13.2. Application object

MsgBox Excel.Application.Name Listing 13.1. Display application name

When the program runs, a message box will display the name of the application—in this case, Microsoft Excel. Obviously, the Name property of the Application object returns the name of the application.

Now let's look at the most important methods and properties of Application . Some of them are similar to those in MS Word. For example, the Quit method, just like in Word, closes the application, the Visible property is responsible for the visibility of the program window, etc.

13.3. Methods

13.3.1. Calculate - forced recalculation

Called on the Application object, this method allows you to recalculate all book openings. It can also be called for individual books (object Workbook) sheets ( Worksheet), cells and their ranges (Range ). For example, the code in Listing 13.2. allows you to recalculate all open workbooks.

Application.Calculate Listing 13.2. Recalculate all open books

13.3.2. GoTo - move to a cell

13-02-Excel GoTo.xlsm - an example for paragraph 13.3.2.

Allows you to select any range of cells in any book, and if the book is not active, it will be activated. The method can also run Microsoft Excel macros.

The full method call looks like this:

Goto(Reference, Scroll)

The Reference parameter is a reference to a cell or range of cells that should be selected after the method call. The name of the macro can also be used here - then it will be run.

The Scroll parameter is responsible for "rewinding" the Excel sheet to the selected cells - so that the upper left corner of the selection would coincide with the upper left corner of the displayed sheet area. If Scroll is set to True, the sheet is rewound, if it is False, it is not.

For example, such a call (Listing 13.3.) allows you to select cell H500 on the active sheet.

Application.Goto _Reference:=ActiveSheet.Range("H500"), _Scroll:=True Listing 13.3. Select cell H500

As you can see, accessing the active sheet is very similar to accessing the active document in MS Word. Note that we are using a full method call - Application.GoTo - as you know, you can usually use the properties and methods of the Application object in your code without specifying this object. However, if you do not specify Application in this case, then instead of the GoTo method, the program will try to execute unconditional jump operator Go To .

13.3.3. SendKeys - simulate keystrokes on the keyboard

13-03-Excel SendKeys.xlsm - an example for paragraph 13.3.3.

A very interesting method - allows you to send keystrokes to the active application window. The full method call looks like this:

SendKeys(Keys, Wait)

The Keys parameter allows you to specify the keys that will be passed to the application. The method supports emulation of both alphanumeric and control keys, for which a special encoding is applied. Alphanumeric keys are specified when calling in their usual form. For example, to transfer the character "Ф" you need to specify it when calling a method, etc.

To send a keystroke to an application backspace- use code (BS) . To send a button press Enter use the ~ (

Many Excel users don't see the difference between "cell format" and "data type". In fact, these are far from identical concepts, although, of course, they are related. Let's find out what the essence of data types is, what categories they are divided into, and how you can work with them.

The data type is a characteristic of the information stored in the worksheet. Based on this characteristic, the program determines how to process a particular value.

Data types are divided into two large groups: constants and formulas. The difference between them is that formulas output a value to a cell, which can change depending on how the arguments in other cells change. Constants are constant values that do not change.

In turn, the constants are divided into five groups:

Text;
Numeric data;
Date and time;
Boolean data;
Erroneous values.

Let's find out what each of these data types represents in more detail.

Text values

The text type contains character data and is not considered by Excel as an object of mathematical calculations. This information is primarily for the user, not for the program. Text can be any character, including numbers, as long as they are appropriately formatted. In DAX, this kind of data refers to string values. The maximum text length is 268435456 characters in one cell.

To enter a symbolic expression, you need to select the cell of the text or general format in which it will be stored, and type the text from the keyboard. If the length of the text expression goes beyond the visual boundaries of the cell, then it is superimposed on top of the neighboring ones, although it continues to be physically stored in the original cell.

Numeric data

Numerical data is used for direct calculations. It is with them that Excel undertakes various mathematical operations (addition, subtraction, multiplication, division, exponentiation, root extraction, etc.). This data type is intended exclusively for writing numbers, but it can also contain auxiliary characters (%, $, etc.). It can be used in several formats:

Actually numerical;
Percentage;
Monetary;
Financial;
Fractional;
Exponential.

In addition, Excel has the ability to split numbers into digits, and determine the number of digits after the decimal point (in fractional numbers).

Numeric data is entered in the same way as text values, which we talked about above.

date and time

Another type of data is the time and date format. This is exactly the case when the data types and formats match. It is characterized by the fact that it can be used to indicate on the sheet and carry out calculations with dates and times. It is noteworthy that in calculations this data type takes a day as a unit. And this applies not only to dates, but also to time. For example, 12:30 is considered by the program as 0.52083 days, and only then it is displayed in a cell in the form familiar to the user.

There are several types of time formatting:

h:mm:ss;
h:mm;
h:mm:ss AM/PM;
h:mm AM/PM etc.

The situation is similar with dates:

DD.MM.YYYY;
DD.MMM
MMM.YY and others.

There are also combined date and time formats, such as DD:MM:YYYY h:mm.

You also need to take into account that the program displays as dates only values starting from 01/01/1900.

Boolean data

Quite interesting is the boolean data type. It operates with only two values: "TRUE" and "FALSE". If we exaggerate, then this means “the event has come” and “the event has not come”. Functions, processing the contents of cells that contain logical data, perform certain calculations.

Erroneous values

Error values are a separate data type. In most cases, they appear when an incorrect operation is performed. For example, such incorrect operations include division by zero or the introduction of a function without observing its syntax. Among the erroneous values are the following:

#VALUE! – use of the wrong kind of argument for the function;
#A BUSINESS! – division by 0;
#NUMBER! – incorrect numerical data;
#N/A – unavailable value entered;
#NAME? – erroneous name in the formula;
#EMPTY! – incorrect input of range addresses;
#LINK! - occurs when deleting cells that were previously referred to by the formula.

Formulas

Formulas are a separate large group of data types. Unlike constants, they, most often, are not visible in the cells themselves, but only display the result, which can change depending on the change in the arguments. In particular, formulas are used for various mathematical calculations. The formula itself can be seen in the formula bar by highlighting the cell in which it is contained.

A prerequisite for the program to perceive the expression as a formula is the presence of an equal sign in front of it (=) .

Functions are a separate type of formulas. These are peculiar subroutines that contain a set set of arguments and process them according to a certain algorithm. Functions can be entered manually into a cell by first putting a sign in it «=» , but you can use a special graphical shell for this purpose Function Wizard, which contains the entire list of operators available in the program, divided into categories.

By using Function Wizards you can jump to the argument window of a particular operator. In its fields, data is entered or links to cells in which this data is contained. After pressing the button OK the specified operation is performed.

As we see, in Excel program There are two main groups of data types: constants and formulas. They, in turn, are divided into many other types. Each data type has its own properties, according to which the program processes them. Mastering the ability to recognize and work correctly with various types of data is the primary task of any user who wants to learn how to use Excel effectively for its intended purpose.

Frizen Irina Grigorievna – Candidate of Pedagogical Sciences, Lecturer information technologies, author of numerous works in the field of information technology.

This textbook was written in accordance with the state program for studying the discipline for secondary specialized educational institutions in the specialty 230103 "Automated systems for information processing and management."

Office programming

The manual provides sufficient material for conducting practical classes in the discipline, as well as the basic theoretical material necessary for the study of a particular issue. On the basis of the proposed material, it is possible to build the conduct of various types of classes.

The textbook aims to help students study this discipline in full, meeting the requirements of the state standard for the discipline.

It is intended for teachers and students of secondary specialized educational institutions, and can also be used by students of higher educational institutions studying this discipline.

The tutorial covers more than 60 problems, accompanied by 130 drawings and detailed explanations.

Book:

Sections on this page:

Using the Range and Selection Objects

In Excel, the most important is the Application object. The Application object is the top object in the Excel object hierarchy and represents the Excel application itself. It has over 120 properties and 40 methods. These properties and methods are for setting general preferences for the Excel application. In the Excel hierarchy, the Workbook object comes immediately after the Application object and represents the workbook file. The workbook is stored either in XLS (standard workbook) or XLA (fully compiled application) format files. Workbook properties and methods allow you to work with files. However, the most “used” in practice is the Range object, which best reflects the possibilities of using VBA in Excel (see Table 19 for the properties of the Range object, and Table 20 for methods).

In an Excel object hierarchy Range(range) comes immediately after the object worksheet. An object Range is one of the key objects of VBA. The selection object (selection) occurs in VBA in two ways - either as a result of the Select method, or when the selection property is called. The type of the resulting object depends on the type of the selected object. Most often, the Selection object belongs to the Range class, and when working with it, you can use the properties and methods of the Range object. An interesting feature of the Range and Selection objects is that they are not members of any object family.

When working with the Range object, you need to remember how Excel refers to a worksheet cell.

Specifying Row and Column Groups Using the Range Object

If only column or row names are specified in the range, then the Range object specifies the range of the specified columns or rows. For example, Range("a:c") specifies a range consisting of columns a, b, and c, while Range("2:2") specifies the second row. Another way to work with rows and columns is the Rows (rows) and columns (columns) methods, which return collections of rows and columns. For example, column a is columns(1) and row 2 is Rows(2).

Relationship between the Range object and the Cells property

Since a cell is a special case of a range, consisting of only a single cell, the Range object also allows you to work with it. The Cells object is an alternative way to work with a cell. For example, cell A2 as an object is described by Range("A2") or Cells(l, 2). In turn, the cells object, nested in the Range, also allows you to write the range in an alternative form, which is sometimes convenient for work, namely Range("A2: C3") and Range(Cells(1,2), Cells(3,3 )) define the same range.

Table 19

Range Object Properties

Range Object Methods

Range object methods that use Excel commands

The commands and methods built into Excel allow you to work effectively with a range: fill it with elements in a pattern, sort, filter and consolidate data, build a summary table and create scenarios, solve a nonlinear equation with one variable.

AutoFill method

The AutoFill method automatically fills the cells in a range with sequence elements. The AutoFill method differs from the DataSeries method in that it explicitly specifies the range in which the progression will be located. Manually, this method is equivalent to placing the mouse pointer on the fill handle of the selected range (into which the values that generate the created sequence are entered) and dragging the fill handle along the range in which the created sequence will be located.

Syntax:

an object. AutoFill(range, type)

Arguments:

Range Range from which filling starts type Valid values: xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues,xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend. xlFillDefault

AutoFilter Method

The AutoFilter method is a simple way to query and filter data in a worksheet. If AutoFilter is activated, then each field header of the selected data range turns into a drop-down list box. Selecting a request to display data in a field with a drop-down list displays only those records that meet the specified conditions. The drop-down list box contains the following types of conditions: All (All), Top ten (Top 10), Condition (Custom), specific data element, Blanks (Blanks) and Non-blanks (NonBlanks). Manually, the method is launched by selecting the command Data, Filter, Autofilter (Data, Filter, AutoFilter).

Two syntaxes are valid when using the AutoFilter method.

Syntax 1:

An object. AutoFilter

In this case, the AutoFilter method selects or cancels the Data, Filter, AutoFilter command applied to the range specified in the object argument.

Syntax 2:

An object. AutoFilter(field, criteria1, operator, criteria2)

In this case, the AutoFilter method executes the Data, Filter, AutoFilter command according to the criteria specified in the argument.

Arguments:

field An integer specifying the field on which to filter the data.

Criteria1 Specify two possible filter conditions and criteria2 fields. You can use a string constant, such as 101, and the relationship signs >,<,>=, <=, =, <>

operator Valid values: X1And (logical union of the first and second criteria); X1or (logical addition of the first and second criteria)

When working with filters, the showAllData method and the FilterMode and AutoFilterMode properties are useful.

ShowAllData Method Shows all filtered and unfiltered worksheet rows

FilterMode property Valid values: True (if the worksheet has filtered data with hidden rows), False (otherwise)

AutoFilterMode property Valid values: True (if the worksheet displays AutoFilter method dropdowns), False (otherwise)

GoalSeek method

The GoalSeek method (selection of a parameter) selects the value of a parameter (an unknown value) that is a solution to an equation with one variable. It is assumed that the equation is reduced to the form: the right side is constant, independent of the parameter, which is included only in the left side of the equation. Manually, the GoalSeek method is performed using the Service, Parameter selection (Tools, Goal Seek) command. The GoalSeek method calculates the root using the method of successive approximations, the result of which, generally speaking, depends on the initial approximation. Therefore, for the correctness of finding the root, one must take care of the correct indication of this initial approximation.

Syntax:

An object. GoalSeek(Goal, ChangingCell)

Arguments:

An object A cell in which a formula is entered that is the right side of the equation being solved. In this formula, the role of the parameter (unknown value) is played by a reference to the cell specified in the ChangingCell argument

Goal The value of the left side of the equation being solved that does not contain a parameter

The precision with which the root is found and the maximum allowed number of iterations used to find the root is set by the Maxchange and Maxiterations properties of the Application object. For example, determining the root with an accuracy of 0.0001 for a maximum of 1000 iterations is set by the instruction:

With Application

maxiterations = 1000

MaxChange = 0.0001

These values are set manually on the Calculation tab of the Options dialog box called by the Tools, Options command.

Sort method

Sorting allows you to arrange data in lexicographic order in ascending or descending order. The sort method sorts list and database rows and worksheet columns based on up to three sorting criteria. Sorting data manually is done using the Data, Sort command.

Syntax:

An object. Sort(key1, order1, key2, order2, key3, order3, header, orderCustom, matchCase, orientaticn)

Arguments:

An object The range to be sorted

Order1

order2 Specifies the ordering order. Valid values: xlAscending (ascending order); xlDescending (descending order)

header Valid values are: xlYes (the first line of the range contains a header that is not sorted); xlNo (the first line of the range does not contain a header, this value is considered by default); xlGuess (Excel decides if there is a title)

orderCustom Custom sort order. Default is Normal

matchCase Valid values: True (case-sensitive) and False (case-insensitive)

orientation Valid values: xlTopToBottom (sorting is done from top to bottom, i.e. by rows); xlLeftToRight (left to right, i.e. by columns)

For example, the range A1:C20 of worksheet sheet1 is sorted by the following command in ascending order, so that the initial sort is on the first column of this range, and the secondary sort is on the second:

Worksheets("Sheet").Range("A1: C20").Sort _

key1:=Worksheets("Sheet1").Range("A1"), _

key2:=Worksheets("Sheet1").Range("B1")

Rounding numbers

You often need to round decimal numbers, especially when working with monetary values. VBA does not offer a direct solution to such problems, but the techniques discussed below will help you solve these problems.

1 way

Round function

X=round(2.505, 2)

The x value will be 2.5, not 2.51.

Therefore, it is not often used.

2 way

Format function

sngRounding=Format(SngUnrounded, “#, 0.00”)

3 way

FormatNumber function

SngRounding= FormatNumber(sbgUnrounded, 2)

To change the decimal places, change the number of zeros after the decimal point in the Format argument, or change the number that specifies the value of the second argument to whatever you want.

Note. The variable into which the rounded value is placed must be of type string, single, double, decimal, currency, or variant, not integer or long.

Data reduction

To cast the entered data to the desired type, VBA includes an extensive set of functions, one of which is CDBL. Syntax:

CDbl(expression)

Required argument expression is any string or numeric expression. To read the information entered in the text field in the generated form, enter a variable and write an expression:

A = Cdbl(textBoxN.text)

After that, you can work with this variable.

To display values directly in the cells of an Excel workbook, it is convenient to use the Range object:

range("A5").value = a

The function opposite to CDbl is the CStr function - it converts numbers into strings and is convenient for displaying the result either in a cell on a sheet or in a particular text box.

TextBoxN.text = CStr(.Range("A8").value)

- reading the value from the cell and displaying it in the text box.

The Trim(string) function returns a copy of the string with the spaces at the beginning and end of the string removed.

Creating VBA Programs

Using the GoalSeek Method

Example 41. Develop a program that, using the entered numerical values of some equation, solves this equation and finds the unknown variable x. The result of the calculation is displayed in a text box on the form and on an Excel sheet.

Rice. 92. The developed form of example 41 in working order

Execution Technology

1. Launch the Excel application, save the document.

2. Switch to the VBA editor.

3. Create a form as shown in fig. 92.

4. Place the required text (formatting) on the Excel sheet, providing the appropriate information output cells (Fig. 93).

Rice. 93. Outputting results to an excel sheet after running the example form 41

5. Process the buttons.

Calculate button

Private Sub CommandButton1_Click()

Dim a, b, c As Double

a = CDbl(TextBox1.Text)

b = cbl(TextBox2.Text)

c = CDbl(TextBox3.Text)

With ActiveSheet

Range("b3").Value = a

Range("b4").Value = b

Range("b5").Value = c

Range("b6").FormulaLocal = "=b3*b7^3+b4*sin(b7)"

Range("b6").GoalSeek Goal:=c, changingCell:=Range("b7")

TextBox4.Text = CStr(.Range("b7").Value)

TextBox4.Text = FormatNumber(TextBox4.Text, 2)

Close button

Private Sub CommandButton2_Click()

Form initialization procedure

Private Sub UserForm_initialize()

Worksheets(1).Visible = False

Using AutoFill Methods When Filling Tables

Example 42 . Create a program that, according to the text data entered into the appropriate text fields of the form, automates data entry for students of a certain specialty of an educational institution. The results of filling in the text fields are displayed on an excel sheet, which allows you to print the data if necessary.