Chapter 11 Programming SQL language As discussed in Chapter 9, the main specialty of the SQL language is constructing user queries against a database. Therefore, programming in SQL language is mainly the formation of such

Chapter 1 Microsoft Word Text Editor

Chapter 3 Microsoft Word, or How to Print on a Computer Why do you need a word processor? Beginning of work Microsoft Settings Word Panels tools Creating a document Formatting a document Highlighting Editing a document WordArt tables and borders.

Programming in VBA in Word

Outputting text to a Word document

5.1. Basic Word Objects

In addition to all the VBA programming capabilities discussed in Part I, you can add the ability to output all calculation results, transformations, and messages to a Word document, with the possibility of further printing. To do this, you need to look at the basic objects of the Word application.

The key to the Word object model is the Application object because it contains all the other Word objects. Its elements at different levels of the hierarchy are about 180 objects. The root Application object itself has over a hundred elements: properties, methods, and events.

The properties of any object are divided into two groups: participant properties (objects) and terminal properties (regular VBA variables).

A unified system for organizing menu bars and tool buttons is provided by the CommandBars object, help by Assistant, and search by FileSearch.

The central objects of Word are the Documents and Templates collections, or rather their constituent elements, the document itself and templates.

Let's look at the background objects.

The AutoCorrect object supports automatic correction typed texts. Its capabilities are equivalent to the AutoCorrect command in the Tools menu.

The Browser object allows you to move the insertion point that points to objects in the document.

The Dialogs collection of objects represents the collection of dialog boxes built into Word. You cannot add new or delete elements from this collection programmatically. But the corresponding windows can be opened and shown on the display screen and thereby organize a dialogue by the user on the topic specified by the corresponding window.

Three objects related to grammar and spell checking: Languages, Dictionaries, SpellingSuggestions, – allow you to set the desired language, select a dictionary, including custom dictionaries, and also work with a list of words suggested for correction when a spelling error is detected. The Spelling and Language commands in the Tools menu provide similar functionality when working with a document manually.

Using the Options object, you can programmatically set various application and document options, just as you would if you selected the Options command on the Tools menu.

When an application is opened, a Documents collection is created containing all open documents. At the start, the collection contains at least one new or previously existing document. A new document is added using the Add method, and an existing document is added using the Open method of the Documents object. To get to the desired document, it is enough to indicate its index - the name of the file storing the document, or its serial number in the collection. The Item method can be used for the same purpose, but it is usually omitted. The Save method allows you to save the document, and the Close method, by saving the document in a file, closes it and removes it from the collection.

The global Dialogs property returns a collection of dialog boxes. The wdDialogFileOpen constant specifies a specific dialog box - an object of the Dialog class.

Classes that define the structuring of document text

Text is the basis of most documents. It can be structured using different units when solving certain transformation problems. The smallest unit of text is usually a character. In addition, there are the following units: words, sentences, paragraphs, as well as larger units: pages, paragraphs, chapters.

The Characters, Words, Statements, Paragraphs, Sections classes allow you to work with sequences (collections) of characters, words, sentences, paragraphs and sections. The largest unit after the paragraph is the section. An element of the Characters, Words, and Statements collections is an object of the Range class. The Range object allows you to work with either a single element or an arbitrary sequence of elements. Documents, subdocuments, paragraphs, sections - they all have a Range method or property that returns the range associated with the object. Therefore, work with text is done in one way or another through the methods and properties of the Range object.

Document Object Events

The Document object can respond to three events that occur as a result of user actions.

Table 16

Let's look at the main classes that define the document structure.

1. Subdocuments (Subdocument) – a collection and the subdocument itself. There is some reasonable limit on the size of a single document. If a document has more than 10–20 pages, it becomes inconvenient to work with. In this case, it contains a main document and subdocuments. The main document in this case has a collection of subdocuments, each of them is, in fact, a document that can be worked with independently.

The AddFromRange method of the SubDocuments class creates a subdocument by selecting the area specified by the Range parameter from the main document.

2. Tables (Table), TablesOfAuthoritiesCategories (T.O.A.C), TablesOfAuthorities (TableOfAuthorities), TablesOfContents (TablesOfContent), TablesOfFigures (TablesOfFigure). The Table class defines “regular” tables with an arbitrary number of rows and columns and arbitrary filling of fields. The remaining classes define tables of a special type.

3. Shapes(Shape), InlineShapes(InlineShape)– these two collections with their elements allow you to add drawings to the document, but not only them. ActiveX and OLE objects are also elements of these collections. The elements of these two collections differ in how they are tied to the document: the first can be freely moved, the second is rigidly tied to a given area of ​​the document.

4. Lists(List), ListParagraphs(ListParagraph), listTemplates (ListTemplate)– lists are convenient to enter into a document when dealing with enumeration. Lists can be designed in accordance with the template. There are two groups of templates: numbered lists and bulletin lists. The ListTemplates collection contains list design templates, and the ListTemplate class describes a specific template. A template is applied to a list of paragraphs and gives it the structure specified by the template. The Lists collection contains those document lists (paragraph lists) that are formatted as numbered lists or bulletin lists. The ListParagraphs collection represents a list of paragraphs from all lists in a document. The ListParagraphs property, which returns an object of the corresponding class, is possessed not only by the document, but also by List and Range objects. So, given a list - a List object, you can select a list of paragraphs. More often you have to do reverse operation– apply one of the possible templates to the list of paragraphs, giving it a “real” list structure. Then use the ListFormat object.

5. Comments(Comment), Bookmarks(Bookmark), FootNotes (FootNote), EndNotes(EndNote), Fields(Field)– these collections and their elements reflect independent but similar concepts. These are parts of the document that are indirectly related to it. During normal viewing of the document, they may not be visible.

The comments collection and the comment class define comments. Comments, as you know, are introduced to explain certain terms or concepts in a document. Formally, they are assigned to a certain area - the range object.

A large document, the individual parts of which must be accessed frequently, should be bookmarked. The bookmarks collection specifies all bookmarks for a given document.

Another way of commenting is footnotes. They can be of two types: subpage (at the bottom of the page) and end (at the end of the document). The first are collected in the footnotes collection, the second - endnotes.

6. Fields (Field) – this collection allows you to work with document fields. One of the features of the fields is that their values ​​are updated automatically depending on changes external conditions or context.

7. Story Ranges (Range) – this collection represents a collection of document parts called fragments (Story). The number of different document fragments is fixed. You cannot add elements to this collection in the normal way using the Add method. Fragments appear in the collection when the corresponding part of the document is created.

Fragments have a type specified by constants from the wdStoryType enumeration. The main fragment is the text of the document, the type of which is specified by the wdMainTextStory constant. Comments, links, and footers make up fragments of other types, i.e. the fragment itself is a Range object. So thanks to fragments, you can, for example, work with a collection of comments as a single area.

8. Variables (Variable) – you can associate a collection of Variant type variables with a document. This is an important collection for programmers, since the lifetime of the variables included in it coincides with the lifetime of the document. This makes it possible to save information about the operation of a particular procedure between sessions. For example, you can have counters that count the number of calls to a macro, and depending on this, determine its further operation in different ways.

The Document object has a Range method, which returns a Range object, and a Select method, which creates a Selection object. The Range method is a function that returns a Range object as a result; The Select method is a parameterless procedure that creates a Selection object as a side effect. The Range object has a Select method that turns an area of ​​the Range object into a selection. The Select method thereby defines a new Selection object. Symmetrically, the Selection object has a Range property that returns the Range object corresponding to the selected area.

Most of the previously described document parts are also parts (properties) of the Range and Selection objects.

The Range object is like a nesting doll: each area contains a smaller area. Here is an example of a correct (though not the most efficient) setting of a Range object:

ActiveDocument.Range.Sections(1).Range.Paragraphs(l).Range.Sentences(1). Words(1).Characters(1)

Range and Selection objects allow you to perform basic operations on text: “select”, “add”, “replace”, “delete”. Our objects have a large set of methods that allow us to implement these operations. All methods discussed here belong to both objects unless otherwise noted.

Selection

Selecting some portion of text essentially means defining a Range or Selection object. Objects define a certain area in the document text, and their Start and End properties allow you to set the beginning and end of this area. By changing the property values, you can set the desired selection area.

Move is the primary method for moving the insertion point. The rest of the methods in this group are modifications to varying degrees. The Move(Unit, Count) method shrinks the area to a point, pulling it to the beginning or end, and then moves the insertion point. The Unit parameter defines the movement units, and Count – the number of these units (default 1). The sign of the Count variable specifies the direction of contraction and movement. Positive values ​​of this parameter specify contraction to the end and movement forward, negative values ​​– contraction to the beginning and movement back. A pure contraction without moving the insertion point is specified as moving one unit. The method returns the number of units by which the movement actually occurred, or 0 if it was not carried out. The Unit parameter accepts the values ​​wdCharacter (default), wdWord, wdSentence, wdParagraph, wdSection, wdStory, wdCell, wdColumn, wdRow, and wdTable.

Moving methods do not affect the text itself - they only change the area specified by the Range and Selection objects. Therefore, these methods are only applicable to type variables Range, but not to fixed areas. For example, record

ActiveDocument.Paragraphs(l).Range.Move

has no effect because the first paragraph area is immutable. The Move method shrinks the area to a point, which is moved, so after its execution the area disappears, only the insertion point remains. The MoveStart and MoveEnd methods move the starting or ending point of an area, typically expanding the area.

Deleting text

The Delete method allows you to delete text. When called without parameters, it deletes the Range or Selection object that called it. When applied in the form Delete(Unit,Count) , the portion of text in the specified area is deleted. The Unit parameter specifies the units, but when deleting, only two values ​​are possible: wdWord and wdCharacter. The Count parameter specifies the number of units to be removed. If the region is contracted to a point, characters before or after the insertion point are removed, depending on the sign of the Count parameter.

Inserting text

The group of Insert methods of Range and Selection objects allows you to insert into a document. To insert text, use the InsertBefore(Text) and InsertAfter(Text) methods. The text parameter of type string specifies text to be inserted before or after the area specified by the range or selection objects. After you paste text, the area automatically expands to include the added text.

The Text property allows you to replace text in the selection, so there is no need to call the Insert(Text) method. The InsertBefore and InsertAfter methods are safe because the text is added without changing the contents of the area. A completely different matter is insertion methods, which are far from secure. When you insert into a region, such as using the InsertSymbol or InsertParagraph method, the contents of the region are replaced.

Working with a buffer

The Soru method, which has no parameters, copies an object (the contents of an area) to a buffer. The cut method, acting similarly, should also delete the object. But the object itself is not deleted - it is only contracted to a point, so that further operations are possible on it.

Sometimes it is not the text that is copied to the buffer, but its format. This is done by the CopyFormat method, which copies the format based on the first character of the selection object. If this character is a paragraph mark, the paragraph format is copied. Only the selection object has the CopyFormat method.

The Paste method allows you to place (“paste”) the contents of a buffer into the area specified by the Range and Selection objects. This operation is dangerous because it replaces, not adds, text. Therefore, the Paste method is usually applied to Range and Selection objects that have previously been collapsed to the insertion point. The PasteFormat method applies the formatting stored in the buffer to the Selection object.

For example, let's create a macro that inserts the list “lesson, exam, passed!”, copies it and places it again on the sheet.

Sub Macro1()

With Selection.ParagraphFormat

Selection.TypeText Text:="Working with text:"

Selection.TypeParagraph

ListGalleries(wdBulletGallery).ListTemplates(1)._ListLevels(1)

ListGalleries(wdBulletGallery).ListTemplates(1).Name = ""

Selection.Range.ListFormat.ApplyListTemplate

ListTemplate:=ListGalleries(wdBulletGallery).ListTemplates(1), ContinuePreviousList:=False, ApplyTo:= _wdListApplyToWholeList, DefaultListBehavior:=wdWord10ListBehavior

Selection.TypeText Text:="lesson"

Selection.TypeParagraph

Selection.TypeText Text:="exam"

Selection.TypeParagraph

Selection.TypeText Text:="passed"

Selection.TypeParagraph

Selection.Range.ListFormat.RemoveNumbers

NumberType:=wdNumberParagraph

Selection.TypeParagraph

Selection.Font.Bold = wdToggle

Selection.Font.Italic = wdToggle

Selection.TypeText Text:="Job complete!"

Selection.WholeStory

Selection.PasteAndFormat(wdPasteDefault)

5.2. Formatting a Document

Most important feature Working on VBA in Word is inserting text into a document when working with applications. For this purpose, Range and Selection objects are used, which are the main ones for almost any operation that can be performed with using Word VBA. Some of these actions can be applied to documents as a whole, but in general you need a range or selection before you make changes. We will, however, look at what you can do with a document when you create it.

An open Word document already contains Range objects that correspond to many of its elements. Each paragraph, table, table cell, comment, etc. defines ranges. For example, in order to insert some text into an existing document, you need to write the code:

ActiveDocument.Paragraphs(1).Range.Text = "Oxo-xo!!"

Moreover, this line will be located at the end of the existing paragraph. On the other hand, using the Selection object, you can also insert some text into the document by using the Add method and assigning the Text property to the Selection object:

Selection.Text = "Learning how to work with text in a Word document is an important part of learning to program in VBA, " + TextBox1.Text + ", and meets the needs of all programmers!"

As a result of this program code The following line will be displayed in the Word document:

Learning to work with text in a Word document is an important part of the ability to program in VBA, Svetlana, and meets the needs of all programmers!

Here the name Svetlana, for example, is read from a text field of some form that has only one input field for the name and a “Output text” button (Fig. 64).

Rice. 64. Data entry form

When working with text in a Word worksheet, you need to know the following codes:

color definition

Selection.Font.Color =

wdColorRed – red

wdColorDarkRed – burgundy

wdColorDarkTeal – turquoise

wdColorBlue – blue

wdColorGreen – green

wdColorBlack – black

wdColorOrange – orange

determination of fat content

Selection.Font.Bold =

wdToggle – boldness

definition of style

Selection.Font.Italic=

wdToggle – italics

alignment definition

Selection.ParagraphFormat.Alignment=

wdAlignParagraphRight – right alignment

wdAlignParagraphCenter – center alignment

wdAlignParagraphJustify – left alignment

inserting a specific sentence into the text

Selection.TypeText Text:="Example of working with text"

inserting a new empty line

Selection.TypeParagraph

setting letter size

Selection.Font.Size = 14

Note. To format your code correctly, use the ability to create a program writing macro.

One of the design elements of dialog boxes is the Image control. Its main properties:

Autosize – changes the size of the picture on the form (automatically or not);

Picture – displays a graphic file displayed on the form;

pictureSizeMode – sets the scaling of the picture (not the entire picture, the entire surface of the object, entirely inside the object);

PictureAligment – ​​sets the position of the picture inside the object (left, right, top, bottom);

pictureTilling – covers an object with a mosaic of a picture.

These properties are either set in the object properties window or specified in the listing.

Example 30. Create a program that will calculate the amount of heat released in a conductor when current flows through it. The formula for calculating the amount of heat is known as the Joule–Lenz law:

where Q is the amount of heat in Joules;

U – voltage in volts;

t – time in seconds;

S – cross-sectional area of ​​the conductor in square millimeters;

l – conductor length in meters;

p – resistivity of the conductor material in Ohm? mm 2 /m.

All initial data is entered into text fields forms. Based on the results of the calculation, the result is displayed in the form of an explanatory note in the document, and the numerical result is displayed in a special form window.

Execution technology

Create a form according to the figure shown. 65.

Rice. 65. The developed form of example 30 in working condition and output of the resulting information to a word document

When creating a form, set necessary properties elements. Set the Locked property of TextBox6 to True to prevent the user from accidentally typing text into it.

Description of procedures

Private Sub CommandButton1_Click()

If Documents.Count = 0 Then Documents.Add

Selection.Text = "When a current with a voltage of " + TextBox1.Text + "volts passes through a conductor with a length of " + TextBox4.Text + " meters, a cross-section of " + TextBox3.Text + "sq. mm and resistivity" + TextBox5.Text + " Ohm*mm2/m for " + TextBox2.Text + " seconds will be highlighted " + TextBox6.Text + " joules of heat"

Selection.Collapse direction:=wdCollapseEnd

Private Sub CommandButton2_Click()

Private Sub TextBox1_Change()

Private Sub TextBox2_Change()

Private Sub TextBox3_Change()

Private Sub TextBox4_Change()

Private Sub TextBox5_Change()

Private Sub scet()

If IsNumeric(TextBox1.Text) = True And

IsNumeric(TextBox2.Text) = True And

IsNumeric(TextBox3.Text) = True And

IsNumeric(TextBox4.Text) = True And

IsNumeric(TextBox5.Text) = True And Not Val(TextBox4.Text)

0 And Not Val(TextBox5.Text) = 0 Then

rez = ((Val(TextBox1.Text) ^ 2) * Val(TextBox2.Text) *

Val(TextBox3.Text)) / (Val(TextBox4.Text) *

Val(TextBox5.Text))

TextBox6.Text = Str$(rez)

CommandButton1.Enabled = True

TextBox6.Text = ""

CommandButton1.Enabled = False

Note. When working this application All input field values ​​must be numeric. VBA has special function to check whether or not the entered string is a number record - IsNumeric. To check if the values ​​in the last two input fields are non-zero, use the Val function, which converts a string expression to a numeric one if this string expression contains (or is all composed of) digits at the beginning.

The Str function does the opposite of what Val does—it converts the numeric value of an expression to a string value, allowing the value to be treated as a string.

Thus, the Val and Str functions convert the data types of the variables they process, from string to numeric and from numeric to string, respectively (see Chapter 3).

You cannot assign a button or menu item to a form to directly call an application from Word - this can only be done for modules. Therefore you need to apply next way. Create a macro (Menu + Insert + Module) in an open project:

Rename this macro as you wish, such as Teplo. And follow these steps.

1. Right-click anywhere on the toolbar.

2. From this menu, select the Settings command (or Tools + Settings).

3. In the Settings dialog box, click the Commands tab shortcut.

4. Scroll down the Categories list and find Macros. Select this item. In the “Save in” list, select the name of your document in which the macro was created (Fig. 66).

Rice. 66. Finding a macro to create a button

5. In the right window, find the required macro (or VBA program).

6. Drag the name of this macro or program to the toolbar.

7. Create a corresponding button label and, if necessary, a drawing (using right button mice).

8. Close the Setup dialog box to finish.

Once all of the above steps are completed, a button to launch the application will be created.

Example 31. Create a program that allows you to fill out standard student applications educational institution, if all the necessary data is filled in the corresponding text fields of the form, and the reason for missing classes is selected from the drop-down list (Fig. 67, 68).

Rice. 67. The developed form of example 31 in working order

Example 32. Create a program that allows you to calculate the amount of money spent on gasoline when traveling to the country.

Please take into account the following: travel conditions can be: one way, round trip, you can take someone else’s car (these conditions are taken into account in the “travel conditions” drop-down list on the created form).

To the class teacher

AS-42 groups

Sidorov O.I.

from a student

Petrenko Oleg Ivanovich

Rice. 68.

The result obtained is calculated using the formula:

Result = distance * (gasoline consumption per 100 km) / 100*gasoline price per liter.

Please note that if you choose to travel “in someone else’s car”, then the price of gasoline is 0 rubles.

Rice. 69. The developed form of example 32 in working order

Also take into account that if the calculation result exceeds 400 rubles, then display the message “Should we go to the market?”

When creating the form, adhere to Fig. 69, when outputting text to a Word document - fig. 70.

When traveling to a dacha located 55 km away, with a gasoline price per liter of 18.50 rubles, gasoline consumption will be 101.75 rubles.

Rice. 70.

Example 33. Develop a program that produces Investor income report Nowadays. Please note that in the form interface interest rate selected from the drop-down list (provide two types of interest).

Rice. 71. The developed form of example 33 in working order

When calculating, adhere to the following formulas:

if the calculation scheme is simple, then

Income = deposited amount + deposited amount * interest rate/100*term/360;

if the calculation scheme is complex, then

Income = deposited amount + deposited amount * interest rate/100*term/360 +0.01*(deposited amount * interest rate/100*term/360).

When developing the form interface, rely on the given figure. 71, and when outputting calculations to a Word document - Fig. 72.

At the moment, citizen Olga Konstantinovna Evseeva has simple interest on her account with an initial investment amount of 1000 rubles. for 150 days at a 2% rate of 1008.33 rubles.

Rice. 72. Approximate format for outputting calculations to a word document

The result of the calculations with appropriate explanations is displayed in a Word document, and the numerical result of the calculations is given in the form field.

Example 34. Develop a program that allows you to fill out an application for reinstatement of a student card if it is damaged (stolen, lost, washed, etc.). Specify the reason for the damage in the drop-down list of the created form.

When working, adhere to the following figs. 73 and 74.

Italics indicate expressions inserted by the program after the corresponding text fields of the form being developed are filled in.

Rice. 73. The developed form of example 34 in working order

Rice. 74. Approximate format for outputting a statement into a word document

On a Word document page, you can organize work with a fairly complex program, without even creating separate Windows forms for this, which are launched, for example, when you open a document or when you click on a button on the document page, but simply by placing data entry fields on it and showing programmatically on the same page the results after changing the data.

However, many mathematical calculations are easier to perform in Excel system When storing information in databases, Access has very great capabilities for processing this information. When working with databases in large information systems, it may be possible to generate reports with their output in Word or Excel, and the development of such programs can only be carried out by a specialist who is well acquainted with both the architecture of the information system database and the language VBA.

You can learn some principles of working with Word objects if you record a certain sequence of your actions using the Word Record Macro tool, which is present on the Developer tab of the ribbon. This tab is present if Word Options On the Basic page there is a check mark next to the Show Developer tab on the ribbon item.

Let's do the following:

1) click on the Record macro button; neither the button nor the keys can be assigned to the macro; we set where the text of the macro will be saved: The macro is available for the current document;

2) move to the end of the document from the current position (it was not at the end) by pressing the Ctrl+End keys;

3) click Enter key and write the word “Hello!”;

4) repeat the operation of step 3 again;

5) highlight the last word by pressing Shift+Home and pressing the Delete key;

6) go to the beginning of the first line “Hello!”, press Shift+End and then the Delete key;

7) on the Developer tab, first click the Stop recording button, then the Visual Basic;

8) in the Visual Basic editor window that opens on the left in the Project panel, double-click on the section Progect() - Modules - NewMacros and see on the right a window with the text of the program in VBA, which is shown in Figure 5.43.

When saving a document that contains software modules, in the Microsoft Office 2007 system, you must select the file type Word Document with macro support, otherwise the program text will not be saved.

In the macro text in Figure 5.43, Selection is one of the main objects that the main Application object contains - Word application.

When writing program text, you can use the pop-up list of properties and methods that appears for standard objects in the Visual Basic editor after writing the name of the object and clicking the dot after it (see Figure 5.44); a complete description of the properties and methods of objects is available in the help system.

You can also get a pop-up list of properties and methods by pressing the Ctrl+Space key combination.

In addition, when writing program text for standard procedures and functions, tooltips appear on their syntax (see Figure 5.45).

MsgBoA(Protr [ShsteLz VbMsgBoxStule = vbOKOPlu], [,default] [,xpos] [,ypos])

Arguments:

message– a string expression displayed as a message in a dialog box. May contain multiple lines. To separate lines, you can use a carriage return (chr(13)), a line feed (chr(10)), or a combination of these keys (chr(13) & chr(10));

title

Default– a string expression that is displayed in the input field as the default if the user does not enter another string. If this argument is omitted, the input field appears blank;

Xpos– a numeric expression that specifies the horizontal distance between the left border of the dialog box and the left edge of the screen;

Ypos– a numeric expression that specifies the vertical distance between the top border of the dialog box and the top edge of the screen.

To pass this information (the entered value in the input field) to the program, assign the value returned by the InputBox function to a string variable (Fig. 75), for example:

strA=InputBox("Which seats do you prefer?","Russian Railways","By the window")

Rice. 75. Result of the InputBox function

The MsgBox procedure displays a dialog box containing a message, waits for the user to press a button, and then returns an Integer indicating which button was pressed.

Syntax:

MsgBox(message [, buttons] [, title] [, help_file, section])

Arguments:

message– a string expression displayed as a message in a dialog box;

buttons – a numeric expression that represents the sum of values ​​that indicate the number and type of buttons displayed, the type of icon used, the primary button, and the message box modality. The default value is 0. See table for all values ​​of this argument. 17, 18;

title– a string expression displayed in the title bar of the dialog box. If this argument is omitted, the application name is placed on the string;

help file– a string expression that specifies the name of a help file containing help information about this dialog box. If this argument is specified, the context argument must also be present;

chapter - a numeric expression that specifies the number of the corresponding section of the help system.

Table 17

Note. The first buttons are active by default.

Table 18

If there are only two buttons in a message box, an If...then statement is great for figuring out which button was clicked. For example:

If MsgBox("Start?", vbYesNo)= vbYes then

Operators for the action of this button

Operators for the action of another button

Example 35 . Create a program so that when you launch the form, enter a name in the text field and click on the “Output Text” button, a dialog box appears asking permission to output text (Fig. 76).

Rice. 76. Example 35 Form in Design View

Listing the work of the form

Private Sub CommandButton1_Click()

If MsgBox("Output text?", vbYesNo) = vbYes Then

If Documents.Count = 0 Then Documents.Add Selection.Text = “Learning how to work with text in a Word document is an important part of learning to program in VBA, “+ TextBox1.Text + “, and meets the needs of all programmers!”

Selection.Font.Color = wdColorBlue

Selection.Font.Bold = wdToggle

Selection.Font.Italic = wdToggle

As a result of launching the application (Fig. 77), the following sentence will appear in the Word document:

Learning to work with text in a Word document is an important part of the ability to program in VBA, Sveta, and meets the needs of all programmers!

Rice. 77. Example 35 form in working mode

Example 36. Create a form that simulates the simplest game into heads and tails (Fig. 78). The player sets a flag instead of tossing a coin, and the computer after pressing the button Coin toss triggers a random number corresponding to heads or tails. If there is a match, the computer wins; if there is a mismatch, the player wins. All actions are accompanied by pop-up dialog boxes.

Execution technology

1. Create a form to implement this game.

2. Description of procedures.

Coin Toss button

Private Sub CommandButton1_Click()

coin = Int(2 * Rnd)

If OptionButton1.Value = True Then

If coin = 0 Then MsgBox “out of luck. Better start learning VBA"

If coin = 1 Then MsgBox “lucky. Congratulations, you win"

If OptionButton2.Value = True Then

Rice. 78. The developed form of example 36 in working condition and a dialog box that reacts to the result of the game

If coin = 1 Then MsgBox “out of luck. Better start learning VBA"

If coin = 0 Then MsgBox “lucky. Congratulations, you win"

Quit game button

Private Sub CommandButton2_Click()

Dim ima As String

intA = MsgBox(“Press the button!”, vbYesNoCancel +

vbExclamation + vbDefaultButton3, “VBA for dummies!”)

If MsgBox("start?", vbYesNoCancel) = vbYes Then

ima = InputBox("enter your name", "Example input box")

If ima<>""Then

MsgBox "Hello," & ima, vbInformation, "Message Box Example"

MsgBox "ignorant, you forgot to enter your name" & ima, vbExclamation, "another example of a message box" End If

If MsgBox(“Are you sure you thought?”, vbYesNoCancel) = vbNo Then

MsgBox ("ha ha")

Else: MsgBox “Well, finally!”

Example 37 . In the developed gaming application provide for the creation of a “Bank” window in which the result of the player’s points will be displayed when the game is stopped. Rules of the game: when the player wins, one is added to the amount in the bank; when he loses, it is added to the computer (subtracted from the amount in the bank).

Provide all necessary dialog boxes.

Example 38. Simulate full game into heads and tails. The player deposits a certain amount of money into the bank. You cannot add money to the bank during the game. The game consists of a sequence of steps that are a priori finite. At the next step, the player thinks of either heads or tails. The computer "flips" a coin. If “the coin falls on the same side” that the player specified, then the bank increases by one, otherwise it decreases by one. The game ends either at the player’s request, or when the bank value becomes zero or more than 10,000 rubles. (certain amount). The player takes the contents of the bank for himself. You can provide for the maximum and minimum amounts that were in the bank throughout the game.

Note. You can simulate throwing a die using the function Int(6* Rnd)+1. The rules change: the one who threw the most wins.

Execution technology

Let's consider one of the options for solving this problem. Let's complicate this task by the fact that each subsequent window is called by a corresponding action.

1. Let the first dialog box appear when you start the application (Fig. 79). When you click on the “Start Game” button, dialog boxes appear asking for the player’s name and confirmation to start the game (see Figure 80). After which the bet entry form appears (see Fig. 81).

Private Sub CommandButton1_Click()

imya = InputBox("enter your name", "Registration", "????")

If MsgBox("Start?", vbYesNo, "Have you changed your mind?") =

This form makes the initial bet selection and launches the main form of example 38.

Rice. 79. Example 38 Game Launch Dialog

Rice. 80. Start Game Dialog Boxes

Rice. 81. Bid Selection Dialog Box

Button Throw simulates a player tossing a coin, calculates the player’s wins and losses, displaying the corresponding values ​​in text windows with a message about the game score (Fig. 82).

Rice. 82. The main form of example 38, simulating the game of heads and tails

Throw button

Private Sub CommandButton1_Click()

TextBox1.Value = TextBox1.Value + 1

If b = Fix(Rnd * 2 + 1) Then

TextBox4.Value = TextBox4.Value + 1

TextBox5.Value = TextBox5.Value + 1

TextBox4.Value = TextBox4.Value – 1

TextBox6.Value = TextBox6.Value + 1

If TextBox4.Value< 1 Then

MsgBox (“You Lose!!!”)

If Val(TextBox2.Text)< Val(TextBox4.Text) Then

TextBox2.Value = Val(TextBox4.Text)

If Val(TextBox3.Text) > Val(TextBox4.Text) Then

TextBox3.Value = Val(TextBox4.Text)

OptionButton1.Value = False

OptionButton2.Value = False

CommandButton1.Enabled = False

Button Exit ends the game and issues final messages (see Fig. 83).

Private Sub CommandButton2_Click()

MsgBox("Batch" + TextBox1.Value + (Chr(13)) + "in the jar" + TextBox4.Value + (Chr(13)) + "your maximum" + TextBox2.Value + (Chr(13)) + " your minimum" + TextBox3.Value + (Chr(13)) + "score" + TextBox5.Value +":" + TextBox6.Value)

Private Sub UserForm Initialize()

Unload UserForm2

OptionButton1.Value = True

TextBox4.Value = a

Label6.Caption = name

TextBox2.Value = TextBox4.Value

TextBox3.Value = TextBox4.Value

Rice. 83. Final calculations of the game of example 38

Note. For the game to work correctly, it is necessary to create a module in which to declare global variables containing information about the value of the initial bet and the player’s name, as well as launching the first form of the game using a button on the toolbar. In addition, it is necessary to modify all modules for the corresponding forms (Fig. 84).

Rice. 84. Declaring global variables and creating a launch module for the main game form in example 38

Example 39. When paying for tuition, the student deposits money into the cash desk, where the accountant fills out a payment receipt by hand. Automate the manual work of an accountant by creating a form that fills out a payment card, which can be sent for printing if necessary.

Execution technology

Template and application interface

As an interface, we will first consider the simplest form of an application that meets the conditions of the task and includes the corresponding text input fields and operating buttons (Fig. 85).

Rice. 85. Example 39 form in working order

Let's start with developing the form standard document (printed form). This form can be made based on a template with text fields in which changing information will be entered. To do this, run following commands: File + Create. On the right side of the new document window, select the section Templates and hyperlink On my computer(Fig. 86). Then a dialog box will appear Templates(Fig. 87), in which on the tab Are common you need to select the icon new document and put the switch Sample in frame Create.

Turn on the panel Forms(View + Toolbars + Forms). For further work you will need only one element of this panel - Text field

which allows you to create a changing field on the form (either through code or by the user). By selecting with the cursor a place in the Word document to locate the corresponding field and placing the element on it Field, you can get the required form. In order to expand the field, you need to place the cursor in it and press several times Tab. To underline a field, select it as text and apply an underline to it.

Rice. 87. Dialog Box Templates

Create a document template following Fig. 88.

Rice. 88. Template of a word document in which data for printing is entered

In some fields (fields are highlighted gray background) you can enter default values. The default value is set using the dialog box Properties, which can be displayed like this:

click right key mouse over the required text field;

select from the context menu that appears Properties ;

specify the default value (Fig. 89).

Default values ​​allow you to change only data that should change infrequently. For example, tuition fees are basically the same - 1,500 rubles, so the value of this field will not change very often.

Rice. 89. Setting parameters for the “opt_amount” text field

Let's look at other necessary elements of the "Text Field Options" working window. In field Type indicates the type of value that can be placed in the field. You can fill in the field Maximum length, although most important for further use VBA code is the field Bookmark. The value of this field will serve as a label where the cursor should be placed before placing the next piece of information into the document. Therefore, once all the required fields are defined, you need to provide meaningful labels for each of them. For the Word document form shown in Fig. 86, we set (sequentially) the following labels:

Last name – student’s last name;

Name – student’s name;

Patronymic – the student’s patronymic;

Group – name of the group;

Month_payment – ​​name of the month for which payment is made;

Payment_amount – deposited amount;

Full name_accountant – surname of the accepting accountant;

Payment_date – date of payment.

After completing the development of the document form, we will save it under the name Payment for studies in the catalog Templates. In Fig. 88 shows a template of the created document with changing fields.

Creating a Print module for a document template

The listing contains the code for the procedure for printing a payment receipt form, related and debugging procedures.

Listing(enter in Module 1 project Payment for studies)

"application form variables

Public fam1 As String

Public name1 As String

Public middle name1 As String

Public groups1 As String

Public month As String

Public amount As String

Public boo As String

Public date As String

" document template form variables

Public last name As String

Public name As String

Public Middle Name As String

Public group As String

Public month_opl As String

Public payment_amount As String

Public name_account As String

Public date_opl As String

Dim book(8) As String

Dim dataMas(8) As String

Dim i As Integer

Sub Print() "form printing procedure

"We enter information into the bookmarmas, datamas arrays

book(1) = "last name": dataMas(1) = last name

book(2) = "name": dataMas(2) = name

book(3) = "middle name": dataMas(3) = middle name

book(4) = "group": dataMas(4) = group

book(5) = “month_payment”: dataMas(5) = month_payment

book(6) = “payment_amount”: dataMas(6) = payment_amount

book(7) = “full name_account”: dataMas(7) = full name_account

book(8) = “opt_date”: dataMas(8) = date

"filling out the fields of the receipt:

ActiveDocument.FormFields(ActiveDocument.Bookmarks(book(i)).Name).Result = dataMas(i)

ActiveDocument.PrintPreview Performs a print preview.

"In order to actually print a document, use the PrintOut method of the Application object (see Help)

Due to the difference between the variables received and sent to print, we will declare two types of variables as Public: those received by the interface part of the application and those printed in the form of a template.

Please note that all data here is of type String. Such data of the same type allows you to write it into form fields in a loop containing only two statements (see listing description).

Creating modules for application form buttons

Button operation Issue a certificate consists of reading the entered information into the text fields of the application form, transferring their values ​​to the document template form variables with further output of the information to the document template.

Private Sub CommandButton1_Click()

last name = last name1

name = name1

patronymic = middle name1

group = group1

month_payment = month

payment_amount = amount

name_book = book

payment_date = date

Call Print

Procedures for assigning application form variables the values ​​entered into form text fields.

Private Sub textBox1_change()

fam1 = TextBox1.Value

Private Sub textBox2_change()

name1 = TextBox2.Value

Private Sub textBox3_change()

middle name1 = TextBox3.Value

Private Sub textBox4_change()

group1 = TextBox4.Value

Private Sub textBox5_change()

month = TextBox5.Value

Private Sub textBox6_change()

sum = TextBox6.Value

Private Sub textBox7_change()

boo = TextBox7.Value

Private Sub textBox8_change()

date = TextBox8.Value

Compile the program and run it for testing.

Example 40. Develop a postal transfer form to facilitate the work of employees associated with frequent processing of money transfers.

The finished document template and form are shown in Fig. 90 and 91.

Rice. 90. Example of a simplified postal order form with variable text fields (document template)