Using VBA to solve practical problems
Let's look at some examples that illustrate how VBA can be used to solve practical problems.
Example 12.1. Create a user function in Microsoft Excel, which in the specified range counts the number of cells whose values correspond to the specified template.
We will set the template as a text string. In the template line, you can use the special template symbols shown in Table. 12.33.
Any single character
Any number of characters or no character
Any single digit (0-9)
Any single character in the list
Any single character that is not in the list
To create a function, go to the Visual Basic Editor, open the program module into which you want to place the function, or add a new program module. Put the following text in this module:
Public Function Count Template (Range As Range, _
As String Template)
Dim k As Integer, r
k = 0
For Each r In Range
If r Like Template Then k = k + 1
CountShape = k
In our function, we used the comparison operation for the Like lines in VBA. The template is set according to the syntax VΒA. The result is a simple but very useful function for statistical processing of text data.
To add a description of the function that appears in the Function Wizard window, open the Macro window (the Developer tab), enter the function name (AccountShape) In the Macro name field, click the Settings button. Enter a description for the function in the Description field.
You can call the function on the worksheet using the function wizard. It is in the User Defined Category category.
Figure 12.1 shows the use of the function on the worksheet. The string of the template in this case has the form Mo * 17 * & quot ;. Among the names of goods, we are looking for monitors with a diagonal length of 17 ". To set the inside the text constant, it was required to specify it twice, so that it would be reversed, that this is not the end of the line.
Fig. 12.1. Using the User Function
Example 12.2. Suppose that on the Excel worksheet, starting from cell A1, there is a table with the results of the exams. We will write a macro in Excel, which will create a new Word document, put in it the header Statement and a table copied from Excel.
To create such a macro, go to the Visual Basic Editor (in Excel), open an existing one, or create a new program module that will contain the macro. Place the following test procedure in this module:
Public Sub PrintToWord ()
Dim wrd As Word.Application
Set wrd = CreateObjectfWord.Application )
.Visible = True
.ParagraphFormat.Alignment = wdAlignParagraphCenter
Font.Bold = wdToggle
.Font.Size = 16
.TypeText Text: = Statement
Range ( A1 ). CurrentRegion.Copy
.Tables (1) .AutoFormat Format: = wdTableFormatGrid3
.Tables (1) .Select
.Font.Size = 12
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.ParagraphFormat.FirstLinelndent = _
.Columns.Width = lnchesToPoints (1.5)
.Rows (1) .Select
.Font.Bold = True
Set wrd = Nothing
The peculiarity of this macro is that we use the objects of another application (Word) in Excel. To make this possible, execute the command Tools/Refercnces , in the opened window find the line Microsoft Word: 14.0 Object Library (instead of 14.0 on your computer there may be another version), put a tick next to it (click the mouse) and click the OK button.
All calls to Word objects must begin with the main Word.Application object. In our case, it can not be omitted, because by default an Excel object will be used. Application with other properties and methods. When working with Excel objects, the Excel.Application object can be omitted, because the macro was created in Excel. The Word.Application object in the macro creates the CreateObject function. It starts a new instance of the Word application and returns a reference to the created object that is stored in the wrd variable. It is this variable that is used in the program to access Word objects.
The information is transferred from the workbook to the Word document, as usual, through the clipboard. In the macro for this purpose, the methods of Copy and Paste of the corresponding objects are used.
The TypeText and TypeParagraph methods of the Selection object insert text and an empty paragraph into the selected section of the document.
Quite a lot of macro instructions are associated with formatting. If you want to know which property you need to assign a value to make the necessary formatting, write down the macro that performs the necessary actions using the Record Macro command and analyze its text.
To run a macro, go to the data sheet and run the Developer/Macros command. Find the name of the PrintToWord macro in the list and click Run . If you select Options in this window, you can assign a shortcut to the macro that will be used to call the macro.
Example 12.3. In the workbook, there is a table with recipient information, consisting of two columns: Recipient and Address (Sheet1), and a table containing the names of all the streets (Sheet2). Data on these sheets are shown in Fig. 12.2.
Create a dialog box for entering information into the recipient table. The window is shown in Fig. 12.3.
Fig. 12.2. The data on sheets Sheet1 and Sheet2
Fig. 12.3. Dialog box for entering data into the table
To create such a window, go to the Visual Basic Editor. Add a form module to the project. In our project, the name of this form is UserForml. Place the controls in the form: four fields for entering a surname (TextBoxl), at home (TextBox2), housing (TextBox3) and apartments (TextBox4); a combo box for entering the street name (ComboBoxl); five Label elements, which are used to display explanatory inscriptions next to previous elements; Add buttons (Command Button 1) and Exit (CommandButton2).
In the form code window, place the following text:
Private Sub CommandButton1_Click ()
ActiveCell.Value = TextBoxl.Text If Right (ComboBox1 .Text, 4) = nep. Or _
Right (ComboBox1.Text, 6) = boulevard Then
S = ComboBoxl .Text
S = & amp; ComboBoxl .Text
S = S & amp; & quot ;, house & amp; TextBox2.Text
If TextBox3.Text & lt; & gt; Then S = S & amp; & amp; TextBox3.Text
S = S & amp ;, kb. & Amp; TextBox4.Text
ActiveCell.Next. Value = S
Cells (ActiveCell.Row + 1, ActiveCell.Column) .Activate
TextBoxl .Text =
Private Sub CommandButton2_Click ()
The CommandButtonl_Click procedure is executed when the Add button is clicked. It transfers data from the form to the worksheet. The contents of the TextBoxl field are written to the active cell of the worksheet, the generated address is to the next cell on the right (the Next property is used to access this cell). The cursor is placed in the cell in the next line, and all the zeroes are cleared.
The CommandButton2_Click procedure is executed when the Exit button is clicked. Using the Hide method, the form is removed from the screen.
To make the form convenient to call when working with a table, proceed as follows. In the program module (not in the form module!), Create the following macro:
Public Sub Adrlnput ()
UserForml .ComboBoxI .RowSource = _
Sheet2! & amp; Sheet2.Range ( A3 ). CurrentRegion.Address
In the Adrlnput macro, the ComboBoxI element is first filled with values, and then the form is displayed. To fill the ComboBox1 list, the RowSource property is used, which is assigned a link to the worksheet range of Sheet2 with street names (as a text string). Since the macro is not in the form module, to access the element, we must first specify the form name, and then, after the point, the name of the element. By the condition of the problem, it is known that the names of the streets are located starting from cell A3. Using the CurrentRegion property for this range, we get a new range containing all street names. This allows you to change the list of streets (add, delete), the program does not depend on its size. The Address property returns a text string corresponding to the reference to the computed range, for example, A3: A15 & quot ;. By adding a sheet name using the concatenation operation to the resulting row, we get the desired property value. The Show method displays the form on the screen.
Use the Developer/Macros command to call the macro on the worksheet. You can assign a combination of keys to the macro ( Developer/Macros/Parameters ), then calling it when entering data will be even more convenient.
Details of the practical use of VBA are highlighted in the book "Computer Science for Economists: Practicum" Ed. VP Polyakova, VP Kosareva (Yurayt, 2012).