How to design English word dictation program with Excel

1. "Aloud cell" button design

"Speak cells" is not a "common command," and this command is not found in the Ribbon by default. However, you can make this command appear on the Ribbon toolbar by using the custom Ribbon setting.

Select the file → options menu, go to the Options Settings dialog box, click Custom Ribbon, click to select the Start tab in the Main tab on the right, and click the new Group (N) button below to create a new custom group. The specific method is as follows.

In the Select command from (C) from the following location, select commands that are not in the Ribbon. Locate the Speak Cells command, select this command with your mouse click, confirm that the previous step is selected in the new group (custom) that was created on the Start tab, and click the Add button. This way, the speak cells command appears on the Start tab. In the same way, the "speak cells-stop reading cells" button is also displayed on this toolbar. The results are shown in Figure 1 (Figure 1).

When you have completed both of these steps, select the cells you want to read aloud with your mouse. Then click the "read aloud cells" button, the system will be from top to bottom in order to read the cell, whether in Chinese or English, can be read aloud, read aloud the effect is good; Click the "Speak cells-stop reading cells" button and the system stops reading. However, this feature lacks the option to set the spacing between cells, and reads all the cells as required. But dictation takes time to write, so we have to use VBA to solve the problem of the control of the pause interval between words.

2. VBA solves the Pause interval control

To design with Excel VBA, you must first display the development tools in the toolbar. Go to the Options Settings dialog box, click Custom Ribbon on the left, and in the right window, select the checkbox in front of developer tools, and the developer tool appears on the interface.

Click the Developer tab, and then click the Visual Basic button (or press alt+f11) to enter the VBA editing environment, clicking VBAProject (personl. XLSB) ", then select the" Insert → user form "menu, and after inserting a user form, modify its name tingxie,caption (that is, the form's caption) property to" Dictation program settings. "

On this user form, add two text box controls, two label controls, two command button controls, and the names of those controls with the system default name. The caption properties of the two tags are: "Number of words set" and "Dictation interval", the two label controls are placed before the two text box control, which is used to describe the purpose of the next two text boxes, and the text properties of two text boxes are: 20, 2, which are the two The initial value of the text box control that the program uses to let the user enter the number of Word quantity settings and dictation interval values, and the caption properties of the two command buttons are: OK, cancel. Drag to adjust the size and relative position of the controls on the form until you feel fit. The results are shown in Figure 2 (Figure 2).

3. Dictation procedure Core Technology Realization

The realization of the core technology of dictation program is divided into three parts, as follows.

(1) Give command button to soul

command button to give it executable code to become a "live" button.

Double-click the OK button to enter the following:

Private Sub CommandButton1_Click ()

n = Val (TextBox1) ' Gets the number of words spoken aloud

t = Val (TEXTBOX2) ' Gets the number of intervals between readings, in seconds

m = ActiveCell.Row ' Gets the number of rows in the currently active cell

c = Activecell.column ' Gets the number of columns in the currently active cell

b = m + n-1 ' calculates the number of lines that are read aloud from M, with a total of n words, to the last line when required

On Error Resume Next

Call Speakcontrol ' calls the read-aloud control process

Tingxie. Hide

End Sub

Double-click the Cancel button to enter the following:

Private Sub CommandButton2_Click ()

Tingxie. Hide

End Sub

(2) Create read-aloud control process

The function of this reading control process is to determine the reading cell, control the interval between readings, and exit the program if the read task is completed.

Click VBAProject with the mouse (personl. XLSB), and then select the menu "Insert → module", where you can complete the declaration of public variables, create a read-aloud control process, and work on the reading process. Enter the following content:

Public A, B, C, M, N, t as Integer ' defines a common variable

Sub Speakcontrol ()

Dim p, Q

Q = ActiveSheet.Cells (1, 1). SpecialCells (Xllastcell). Row ' Gets the last row of the worksheet

On Error Resume Next

If T < ten Then

p = "00:00:0" & T ' time within 10 seconds, the method of calculating P

Else

p = "00:00:" & T ' time in 10 seconds or more, the method of calculating P

End If

If M > B or M > Q Then ' If the word is spoken to meet the set number of requirements or to the last line, exit the program

Exit Sub

Else

A = Cells (M, c) ' Get the text of the cell you want to read aloud

Application.ontime now + timevalue (p), "Wordspeak" calls the reading process at set intervals

End If

End Sub

Small tip:

The calculation of time interval p is also added here. Since the time interval t may be 1 digits or 2 digits in this range within 60 seconds, the P calculation method is not the same, so we have to determine the number of digits of T and then select the appropriate method for calculating p.

(3) Implementation of specific reading tasks

The above reading control process does not specifically carry out the reading work, the reading of the work by the reading process to complete the task. Below the read-aloud control process, create a read-aloud process for wordspeak, which reads as follows:

Sub Wordspeak ()

On Error Resume Next

Application.Speech.Speak a ' read the text in the Set cell

m = m + 1 ' calculates the number of rows in a read-aloud cell

Call Speakcontrol ' calls the read-aloud control process

End Sub

This process has the ability to read aloud, add 1 rows to the read aloud cell, move down to the next row of cells, and finally return to the read-aloud control process, which determines whether the word continues to be read further down.

(4) The process of creating a startup macro

Although the above already has the form and the related control and the reading process, but also lacks one to start the macro the process. Create a new procedure below the procedure, named dictation, as follows:

Sub Dictation ()

Tingxie. Show ' Shows dictation settings window

End Sub

The contents of each module are shown in Figure 3 (Figure 3). For ease of use, can be downloaded directly through the cloud disk after the copy paste (http://pan.baidu.com/s/1qXcTKFq password 8r2m).

Small tip:

To run a macro, you must first use the Developer tab, click the Macro Security button, and go to Macro settings to select Enable all macros (the bottom option).

After debugging in Excel 2016, you can run the program. First click in the top cell of the word column you want to read aloud, and click the Macro button on the Developer tab. In the Macro selection dialog box that pops up, select the dictation macro, and then click the Run button, and in the Dictation Program Settings dialog box, enter the word quantity setting, and dictation interval. After confirmation, we can have English dictation training.