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

To insert controls into a worksheet, you must display the 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 you can do this like this: Open the tab File; Click the button Options; Click the button Customize your feed; Select a team Customize the Ribbon and in the Main Tabs section check the box Developer.

Now you can insert a control through the menu: .

Please note that in this menu you can insert ActiveX Controls, which 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, but Form Controls can be directly bound to a cell on the worksheet.

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'll look at a more complex example of using controls and .

Example

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

Using one of 3 controls Combo Box, List And Counter, user can select table column (year) . The desired control element is selected using the group Switches. The name of the selected element is highlighted in gray (see. A8:B8 in Fig. higher). The selected year is highlighted in dark gray in the table (see. H9:H12 in Fig. higher). The display of this highlight is adjustable Checkbox(flag background is red). Scroll bars 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 highlighting of the selected year column in the table. Link an element to a cell $G$2 . If the checkbox is unchecked, then this cell will contain FALSE (this value corresponds to 0), if checked, then TRUE (this value corresponds to 1).

To configure, select a range 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 lines. Once the range is selected it should contain G 9 ).

• call tool ( Home/ Styles/ Conditional Formatting/ Create a 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 take the value TRUE when 2 conditions are met simultaneously:
• expression value ( OffsetYear(varies from 1 to 5 (i.e. from 2009 to 2013) + 6) will match 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 is Conditional Formatting installed;
• select the radio button List;
• in the control List let's choose 2010;
• let's make sure that the column 2010 highlighted in grey;
• Scroll bar Let's change the quantity in the 2010 column.

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/Insert). However, to work with these elements you need to write a program in VBA.

Friesen Irina Grigorievna – candidate of pedagogical sciences, teacher 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 specialty 230103 “ Automated systems information processing and management".

Office programming

The manual provides sufficient material to carry out practical classes in discipline, as well as basic theoretical material necessary when studying a particular issue. Based on the proposed material, you can build 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.

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

IN textbook Over 60 problems are covered, accompanied by 130 pictures and detailed explanations.

Book:

Sections on this page:

Using Range and Selection Objects

In Excel, the most important object is the Application object. The Application object is the main 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 intended to be set general parameters Excel applications. In the Excel hierarchy, the Workbook object comes immediately after the Application object and represents the workbook file. The workbook is stored in either XLS (standard workbook) or XLA (fully compiled application) 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 (for the properties of the Range object, see Table 19, for the methods, see Table 20).

In the Excel hierarchy an object Range(range) comes immediately after the object worksheet An object Range is one of the key VBA objects. The selection object appears in two ways in VBA - either as a result of the Select method or by calling the selection property. The type of object obtained 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. Interesting feature Range and Selection objects is that they are not members of any family of objects.

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

Specifying Groups of Rows and Columns Using a Range Object

If a range specifies only column or row names, then the Range object specifies the range consisting of the specified columns or rows. For example, Range("a:c") specifies a range consisting of columns a, b, and c, and Range("2:2") specifies a second row. Another way to work with rows and columns is the Rows and Columns methods, which return collections of rows and columns. For example, column a is columns(1), and the second row 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 alternative way working with the cell. For example, cell A2 as an object is described by Range (“A2”) or Cells (l, 2). In turn, the cells object, nested in Range, also allows you to write the range in an alternative form, which is sometimes convenient for work, namely Range(“A2:NW”) and Range(Cells(1,2), Cells(3,3 )) define the same range.

Table 19

Range object properties

Range object methods

Range object methods using Excel commands

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

AutoFill method

The AutoFill method automatically fills cells in a range with elements of a sequence. The AutoFill method differs from the DataSeries method in that the range in which the progression will be located is explicitly specified. Manually, this method is equivalent to placing the mouse pointer on the fill handle of the selected range (in 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 The range from which filling of the type begins. Valid values: xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues, xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend. Default xlFillDefault

AutoFilter method

The AutoFilter method is a simple way to query and filter data in a worksheet. When AutoFilter is enabled, each field header in the selected data range becomes a drop-down list box. By selecting a request to display data in a drop-down list box, only those records that meet the specified conditions are displayed. The drop-down list box contains the following condition types: All, Top Ten, Custom, Specific Data Item, Blanks, and NonBlanks. The method is launched manually by selecting the Data, Filter, AutoFilter command.

When using the AutoFilter method, two syntaxes are allowed.

Syntax 1:

An object. AutoFilter

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

Syntax 2:

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

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

Arguments:

field An integer indicating the field in which data is filtered

Criteria1 Specify two possible filtering conditions and criteria2 fields. It is allowed to use a string constant, for example 101, and relation signs >,<,>=, <=, =, <>

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

The showAllData method and the FilterMode and AutoFilterMode properties are useful when working with filters.

ShowAllData Method Shows all filtered and unfiltered rows of the worksheet

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

AutoFilterMode property Valid values: True (if AutoFilter method drop-down lists are displayed on the worksheet), False (otherwise)

GoalSeek method

The GoalSeek method (parameter selection) selects the value of a parameter (an unknown quantity), which is a solution to an equation with one variable. It is assumed that the equation is reduced to the form: the right side is a constant, independent of the parameter, which is included only in the left side of the equation. The GoalSeek method is performed manually using the 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, to correctly find the root, care must be taken to correctly indicate this initial approximation.

Syntax:

An object. GoalSeek(Goal, ChangingCell)

Arguments:

An object The cell in which the formula is entered, which is the right side of the equation being solved. In this formula, the role of a 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, which does not contain a parameter

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

With Application

Maxiterations = 1000

MaxChange = 0.0001

These values ​​are manually set on the Calculation tab of the Options dialog box, called up 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 rows of lists and databases, as well as columns of worksheets, taking into account up to three criteria by which the sort is performed. 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 Range to be sorted

Order1

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

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

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 row); xlLeftToRight (from 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 occurs on the first column of this range, and the secondary sort occurs 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 doesn't offer a direct solution to these problems, but the techniques discussed below can help you solve these problems.

1 way

Round function

X= round(2.505, 2)

The value of x will be 2.5, not 2.51.

Therefore it is often not used.

Method 2

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 the desired value.

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

Data reduction

To convert the input 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 into the text field in the created form, enter a variable and write the expression:

A = Cdbl(textBoxN.text)

After which you can work with this variable.

To display values ​​directly into Excel workbook cells, it is convenient to use the Range object:

range("A5").value = a

The reverse function 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 one or another text window.

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

– reading a value from a cell and displaying it in a text window.

The Trim(string) function returns a copy of the string with leading and trailing spaces removed.

Creating VBA Programs

Using the GoalSeek Method

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

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

Execution technology

1. Launch Excel and save the document.

2. Go to the VBA editor.

3. Create a shape according to the figure shown. 92.

4. Place the required text (design) on the Excel sheet, providing appropriate cells for displaying information (Fig. 93).

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

5. Process the buttons.

Calculate button

Private Sub CommandButton1_Click()

Dim a, b, c As Double

a = CDbl(TextBox1.Text)

b = CDbl(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, based on the text data entered into the appropriate text fields of the form, automates the entry of data for students of a certain specialty of an educational institution. The results of filling out the text fields are displayed on an Excel sheet, which allows you to print the data if necessary.

Programming in Visual Basic is based on object-oriented programming (OOP) and its application in Excel. In order to use Visual Basic programming elements in Excel, it is necessary to define the concept: object, object properties, methods, objects and their use in the program.

OOP is the most modern style in computer program development. This style is used because modern program design attempts to achieve certain specific goals. The program must be: testable, upgradable, reusable, portable.

All these requirements are met if the principle of program modularity is used. During development, modular programs are broken down into separate parts called modules. Each module performs specific, strictly defined transformation functions and has access only to the data that is necessary for a given transformation. Module code (module program), developed with a strictly defined interface for other program modules, is easy to debug, maintain and understand. Another aspect of modularity is its closed nature, which ensures that any changes to a module's code will only affect the functionality of that module and nothing else.

OOP makes maximum use of the principle of modularity. A software object in OOP is called a “container”. A container includes data and code that knows how to manipulate that data.

An object is a collection of data along with program code designed to process it.

In other words, a software object (container) is a block consisting of code and data.

It can be represented as a physical object, such as a button or menu. A software object has a specific conversion function and contains the specific code and necessary data to implement that function, but its internal workings are hidden from the user.

In more traditional programming, a computer program is developed as functional sets of lines in which blocks of data are passed from module to module and each module modifies or uses them as it sees fit. Modules do not contain data, but only code for modifying the transferred data. If a program accidentally passes incorrect data, the procedure will still process it and return garbage or even crash your system.

In OOP, data and code are combined in a single structure called an object. Instead of passing data from module to module to perform calculations, a message is sent to the object that contains this data. It is impossible to transfer bad data to an object, because all data is inside the object.

Common examples of Visual Basic and Excel objects include tables, cell areas, command buttons, text windows, work folders, charts, and modules. A software object has certain properties and methods.

Properties are the visible characteristics of an object. The properties of an object determine its appearance and behavior.

Methods - these are the operations of transforming this data.

Visible characteristics are data that can be accessed outside the object. Properties are data that an object manipulates or that allows you to control what the object looks like or how it behaves. For example, property Value text window is the text that is entered into the window.

When a method is executed, it can only change the values ​​of properties of this object, but not other objects. A method can only ask an object to change some property.

It is impossible to call an object, as well as a procedure. To change a property of an object or execute one of its methods, a message must be sent to the object. For example, to perform an operation on object data, specify

Object name. Method,

and to change some property

Object name. property=value.

Let the name of the object be Power, which has the Value property, then it is possible to change this property using the instruction:

Power. Value = 3 ‘The Value property – value – will be equal to 3

or storing the value of an object’s characteristic in a variable

X = Power. Value ‘The value of the value property is placed in the variable x.

There are dozens and even hundreds of different objects in VBA. However, some of them will be encountered at almost every step. Some of them are given in table. 4.1.

Table 4.1

VBA Excel Objects

Object class	Description of the object
Application	This object represents the Excel application itself, and generally includes all the built-in functions of MS Excel.
Workbook	Determines the state of the workbook, such as whether it is open for reading or which method is currently active. The ActiveWorkBook object belongs to the same class - it represents the currently active workbook.
Worksheets	The object is used when copying or deleting worksheets, hiding or showing them, and performing calculations for worksheet formulas. The ActiveWorkSheet object belongs to the same class - this is an object that represents the currently active worksheet.
Window	An object of this class is used when minimizing or maximizing a window, splitting it into parts, and fixing sills. ActiveWindow – Represents the active window
Range	An object of this class allows you to change the properties of a cell span, such as the font used, check or change the contents of cells, cut or copy a span, etc. This is the most commonly used object class. The following objects belong to the same class: ActiveCell - active cell. A single cell is a special case of Range.

As already mentioned, each object has a number of inherent characteristics or properties. Some of them are given in table. 4.2

Table 4.2

Properties of some VBA objects

An object	Property	Description
Application	ActiveWindow ActiveWorkBook ScreenUpdating StandardFont	Active window Active workbook Default font name for new worksheets
WorkBook	ActiveSheet FullName Name Saved	Active Worksheet The full name of the workbook, including the path. Workbook name. An indication that the workbook state is saved to disk (values ​​False if changes have been made to the workbook)
Worksheet	Name Previons ProtectContents Visible	Worksheet name Previous worksheet The mode for protecting the contents of worksheet cells. Worksheet visibility mode (hidden or shown).
Window	ActiveCell DisplayGridlines Selection Visible WindowState	Active cell Grid line display mode Current selected object. Window visibility mode. Window display mode (minimize window, full screen, normal size)
Range	Column Font Formula Name Row Value Worksheet	First column of the interval Font used in the interval Interval formula Interval name First line of the interval Cell value Worksheet

Here are a few examples that show how object properties are determined (set):

1) set the font size to 14 pt in the active cell.

ActiveCell.Font.Size=14

Where Size– object property Font;

2) installing a font called Courier New Cyr in the active cell

ActiveCell.Font.Name = "Courier New Cyr"

Where Name– object property Font;

3) setting the font style in the active cell to italics

ActiveCell.Font.Italic=True.

In addition, each object has its own methods (Method - describes the action that can be performed on the object). Some object methods are presented in table. 4.3.

Table 4.3

Table of some VBA object methods

An object	Method	Description
Application	Quit Undo	Completing MSExsel Cancels the last execution of an action
Workbook	Activate Close Save SaveAs	Activates the workbook. Closes the workbook. Saves the workbook. Saves the workbook under a different name.
Worksheet	Activate Calculate Delete Protect Unprotect	Activates the worksheet. Recalculates the worksheet value. Deletes a worksheet. Protects the worksheet. Cancels worksheet protection.
Window	Activate Close	Activates a window. Closes the window.
Range	Clear ClearContents ClearFormats Offset Select	Completely clears the spacing with formatting. Clears the contents of a cell. Clears cell formatting. Returns an interval at the specified offset from the original interval. Highlights an interval

Let's consider solving several problems.

Problem 4.1

Get information about the available properties of a workbook: the number of sheets in the workbook, the name of the workbook, and the name of the third sheet of this workbook. The result is displayed in the worksheet cells.

Solution

Sub Info()

"Counts the number of sheets and the result is placed in B1

Worksheets("Sheet1").Range("B1").Value =Worksheets.Count

"Defines the name of the active workbook and is placed in B2

Worksheets("Sheet1").Range("B2").Value=ActiveWorkBook.FullName

"Determines the name of the third sheet of the workbook and places it in B3.

Worksheets("Sheet1").Range("B3").Value = Worksheets(3).Name

Task 4.1

Write a code using objects and methods that recalculates the number of sheets in the current workbook, adds another sheet and calls the last sheet “Hello”, displays the font size and font name on the new sheet.

Note

Add method: add.

Task 4.2

Write code to remove the last sheet from the book.

Information

One of the main objects of Microsoft Excel is the workbook and worksheet.

When you create, open, or save a file in Microsoft Excel, you actually create, open, and save a workbook. To work with a workbook, Visual Basic uses methods of the WorkBook object or the WorkBooks set.

Instructions With

The With statement allows you to execute a sequence of statements on a specified object without repeating the object's name. For example, if you have multiple properties that need to be changed on a single object, it is more convenient to place the property assignment statements inside a With control structure, referencing the object once, rather than referencing the object each time you assign its properties.

With< object>

<инструкции>

End With

Where With ,End With – keywords,

< object> – any Excel object,

<инструкции> – VBA statements using properties and methods < object>. Every instruction must begin with a period.

The following program fragment sets the range A1:C8 to bold red font with a character height of 20pt and does not use the instruction With.

Range(“ A1:C8 ").Font.Bold=True

Range(“ A1:C8 ").Font.ColorIndex=3

Range(“ A1:C8 ”).Font.Size=20

The following example demonstrates the use of a With statement to assign values ​​to multiple properties of a single object.

With Range(“A1:C8”).Font

.Bold=True

.ColorIndex=3

.Size=20

End With

Shapes as an object

VBA allows you to organize a convenient and intuitive user interface with data using forms.

Forms are objects that have properties that determine their appearance, methods that determine their behavior, and events that determine how they interact with the user. By setting the form's properties and developing VBA code to make the form respond to events, you create an object that meets the requirements of a specific application.

Controls are objects contained within form objects. Each control type has its own set of properties, methods, and events that make it suitable for a specific purpose. Some controls used in applications are best suited for entering or displaying text. Other controls provide access to other applications and process data as if the remote application were part of the application itself.

Microsoft Excel (also sometimes called Microsoft Office Excel) is a spreadsheet program created by Microsoft Corporation for Microsoft Windows, Windows NT, and Mac OS. It provides economic-statistical capabilities, graphical tools, and, with the exception of Excel 2008 on Mac OS X, the macro programming language VBA (Visual Basic for Applications). Microsoft Excel is part of Microsoft Office and today Excel is one of the most popular programs in the world.

A valuable feature of Excel is the ability to write code using Visual Basic for Applications (VBA). This code is written using an editor separate from the tables. The spreadsheet is managed using an object-oriented code and data model. Using this code, the data from the input tables will be instantly processed and displayed in tables and charts (graphs). The table becomes the interface of the code, allowing you to easily work with it, change it, and guide calculations.

Using Excel, you can analyze large amounts of data. In Excel, you can use more than 400 mathematical, statistical, financial and other specialized functions, link different tables to each other, choose arbitrary data presentation formats, and create hierarchical structures. Truly limitless methods for graphically presenting data: in addition to several dozen built-in chart types, you can create your own, which can be customized to help visually display the subject of the chart. Those who are just learning to work with Excel will appreciate the help of "wizards" - auxiliary programs that help when creating charts. They, like good wizards, asking leading questions about the expected next steps and showing, depending on the planned answer, the result, will guide the user “by the hand” through all the stages of constructing the diagram in the shortest way.

Working with a table is not limited to simply entering data into it and drawing diagrams. It is difficult to imagine an area where analysis of this data would not be required. Excel includes a powerful analysis tool - the Pivot Table. With its help, you can analyze wide-format tables containing a large amount of unsystematized data, and with just a few clicks of the mouse button you can bring them into a convenient and readable form. Mastering this tool is simplified by the presence of an appropriate wizard program.

There are two main types of objects in Microsoft Excel: book and letter.

Book in Microsoft Excel is a file that is used to process and store data. Each book can consist of several sheets, so you can put a variety of information in one file and establish the necessary connections between them.

Letters serve to organize and analyze data that can be entered and edited simultaneously on multiple sheets, as well as perform calculations based on data from multiple sheets. Once created, charts can be placed on an email with the relevant data or on a separate email email.

Sheet names appear on tabs at the bottom of the workbook window. To switch from one letter to another, you need to specify the appropriate label. The name of the active sheet is in bold.

Microsoft Excel has a lot of different functions, for example:

1. Financial, among many special functions that calculate interest on a deposit or loan, depreciation charges, rate of return and a variety of inverse and related quantities.

2. Date and time functions– most of the functions in this category are responsible for converting date and time into different formats. Two special functions TODAY and TDATE insert the current date (the first) and the date and time (the second) into the closet, updating them every time the file is called or when any changes are made to the table.

5. Link and arrays. This category contains functions that allow you to access a data array (by column, row, rectangular interval) and obtain a variety of information from it: the numbers of columns and rows it contains, their number, the contents of the array element you need; you can find which compartment of this array contains the required number or text, etc.

6. Text– There are about two dozen teams in this group. With their help, you can count the number of characters in the collar, including spaces (DLST), find out the symbol code (CODE), find out which character is first (LEFT) and last (RIGHT) in a line of text, place a certain number of characters from another into the active box collars (PSTR), place in the active box all the text from another box in capital letters (UPPER CAPITAL) or capital letters (LOWER), check whether two text boxes match (COICH), find some text (SEARCH, FIND) and replace it with another (REPLACE ).

7. Checking Properties and Values– here are commands with which you can get information about the type of data in the collar (a number is there, text or some other information), about the format, about the current operating environment, about typical errors that have arisen in the formula, etc. P..

8. Working with the database– here you can find commands for statistical accounting (BDDISP - variance for a sample from the database, BDDISP - variance for the general population, DSTANDOTKL - standard deviation for the sample), operations with columns and rows of the base, the number of non-empty (BSCOUNT) or (BSCOUNT) cells, etc. .d.

9. Chart Wizard– built-in EXCEL program, which simplifies working with the main features of the program.

Purpose of MS Excel.

MS Excel is one of the most popular spreadsheet programs today. It is used by scientists, accountants, journalists, etc., with its help they maintain various tables, lists and catalogs, prepare financial and statistical reports, calculate the state of a trading enterprise, process the results of a scientific experiment, keep records, and prepare presentation materials. Excel's capabilities are very high. Text processing, database management - the program is so powerful that in many cases it is superior to specialized editor programs or database programs. Such a variety of functions may at first confuse you, rather than force you to use it in practice. But as you gain experience, you begin to appreciate the fact that the limits of Excel's capabilities are difficult to reach.

Over the long history of tabular calculations using personal computers, user requirements for such programs have changed significantly. At first, the main emphasis in a program such as VisiCalc was on counting functions. Today, along with engineering and accounting calculations, the organization and graphical representation of data is becoming increasingly important. In addition, the variety of functions offered by such a calculation and graphic program should not complicate the user's work. Windows programs provide the ideal prerequisites for this. Recently, many have just switched to using Windows as their user environment. As a result, many software companies began to offer a large number of programs for Windows.

Excel provides both ease of handling data and its safety. Excel allows you to quickly complete work that does not require a lot of paper and time, and also involves the involvement of professional accountants and financiers.

This program will be able to calculate the amounts in the rows and columns of tables, calculate the arithmetic mean, bank interest or dispersion; here, in general, you can use many standard functions: financial, mathematical, logical, statistical.

Excel has many more advantages. This is a very flexible system that “grows” with the user’s needs, changes its appearance and adapts to you. The basis of Excel is the field of cells and the menu at the top of the screen. In addition, up to 10 toolbars with buttons and other controls can be located on the screen. It is possible not only to use standard toolbars, but also to create your own.

Conclusion.

In order to know Excel you need to work in it. Using this program, you will certainly discover more and more new features and properties. Explore and experiment. If you don't like the results, try again. Almost everything you do in Excel can be undone, so you won't lose anything more than a few minutes of your time by experimenting.

©2015-2019 site
All rights belong to their authors. This site does not claim authorship, but provides free use.
Page creation date: 2016-08-08

In most of the examples in this introductory section, we looked at the capabilities of VBA without reference to the information in the worksheet. Only a few situations demonstrated syntactic constructs that allowed data to be retrieved and written to cells in Microsoft Excel sheets. In this part of the section, we will take a detailed look at the objects that allow you to work with information contained in Microsoft Excel workbooks. The examples given here are the foundation for more complex developments discussed in subsequent articles.

An Excel workbook file is represented in a Workbook object, which has a large number of properties and methods. Help for them is available in both the VBA online help and . We will not delve into purely reference information and in the introductory part we will consider only the information that we will later encounter in the examples given.

Yes, property Worksheets A Workbook object represents a family of all the worksheets in a workbook. And to access a specific worksheet using this property, you simply need to specify the sheet number as a parameter, which looks like this: Worksheets(sheet number). The sheet number is simply its serial number in the Microsoft Excel workbook. Another option is to specify the sheet name as a parameter: Worksheets. One of the most frequently programmed events associated with the book as a whole is the event Open that occurs when you open a workbook. So, if we want certain actions to be performed when opening a book, we should place the necessary program fragment inside the procedure Workbook_Open. Programming this event will be discussed in the examples below.

The next object in the hierarchy order after Workbook is an object Worksheet, representing the worksheet. Of the variety of methods of this object, it is widely used Activate, which also exists for the Worksheets family of sheets, which we already mentioned above. For example, if, when working on the first sheet of a Microsoft Excel workbook, you need to activate the second sheet, then the program line syntax in the procedure (it can be executed, for example, by clicking a button) should look like this: Worksheets(2).Activate. If you want to activate the sheet called Information about companies, then you should write the following construction in the procedure: Worksheets("Information about companies").Activate.

Users of Microsoft Excel know that this application offers a service related to the protection of workbooks and the sheets that make them up. Yes, the method Protect(Worksheets family) protects the worksheet from changes. To programmatically install password protection (the password is specified in the Password parameter of this method) of a sheet with the name Employees you can do this:

1 2	Worksheets("Employees").Protect Password:="zv2345" , _ DrawingObjects:=True , Contents:=True , Scenarios:=True

Worksheets("Employees").Protect Password:="zv2345", _ DrawingObjects:=True, Contents:=True, Scenarios:=True

There is also a corresponding method Unprotect, which allows you to remove protection from the sheet. For newly installed protection, the method for removing it is: Worksheets("Employees").Unprotect Password:="zv2345" . Any practical work in Microsoft Excel, one way or another, concerns information in cells. To work with cells in VBA there is an object Range(translated as a range of cells). Using this object requires setting the parameter to the range of cells that interest us. This can be a single cell or a group of cells. So, if we write Worksheets(1).Range (“C5”).Value = 7, this means that we programmatically write the number 7 into cell C5 of the first sheet. The main property of the object is used here Range - Value. Literally it means the value or contents of a cell (group of cells). In the following construction, the same set of letters ABSDE is programmatically entered into a set of cells: Worksheets(1).Range("A1:A3").Value = "ABSDE" .!}

Another way to work with cells is using the object Cells, and the syntax for using it is as follows: Cells (row number, column number). In fact, from the point of view of their use, the objects in question are similar. For example, you can get the value of cell D5 in the variable Z in two different ways: Z = Range("D5").Value or Z = Cells(5,4).Value. As an example of a program design on this topic, the following assignment can be given:

1 2	Worksheets(2).Range("C5" ).Value = _ Worksheets(3).Cells(5, 1).Value.

Worksheets(2).Range("C5").Value = _ Worksheets(3).Cells(5, 1).Value.