BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 13 March 2020
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Integrating speech recognition with VBA
This page shows you how to create a simple form:
You'll be able to say something to Excel, and it will enter the text into your worksheet and for good measure repeat it back to you.
You can download the finished workbook here, should you want an easy life!
Creating the form and basic code
I'm going to assume you know a little bit - but not too much - about VBA. First create a form in VBA:
Create a new user form in VBA.
Call your form SpeechForm, and add a couple of controls:
I've added a textbox and a command button.
Optionally, create a subroutine to display your form:
This simple subroutine would bring the form up on screen.
You could then create a button on a worksheet:
Add a clickable button to a worksheet.
When you click on this button, your form should appear:
Assign your ShowSpeechForm macro to the button.
Linking to the speech recognition object library
The next step is to expand the horizons of Excel, so that it knows about speech recogntion. From the VBA menu select this option:
Choose to add a reference to another object library (that's the technical description for what you're doing).
There are two speech recognition libraries to choose from:
Make sure you tick the one which is for the file sapi.dll.
Writing the code to get speech recognition working
Time now for the difficult bit! Double-click on the background of your form:
Double-click on your form in VBA to assign code to its events.
Delete any code which appears:
Delete any code apart from Option Explicit.
Paste in the following code (it's explained below!):
Private WithEvents ListeningSession As SpSharedRecoContext
Private Grammar As ISpeechRecoGrammar
Private Sub UserForm_Initialize()
If (ListeningSession Is Nothing) Then
Set ListeningSession = New SpSharedRecoContext
Set Grammar = ListeningSession.CreateGrammar(1)
Private Sub ListeningSession_Recognition( _
ByVal StreamNumber As Long, _
ByVal StreamPosition As Variant, _
ByVal RecognitionType As SpeechLib.SpeechRecognitionType, _
ByVal Result As SpeechLib.ISpeechRecoResult)
'store this in the textbox on your form on a new line
Me.TextBox1.Value = Me.TextBox1.Value & vbNewLine & _
Private Sub CommandButton2_Click()
'store results in top left cell of current sheet
Range("A1").Value = Me.TextBox1.Text
'speak them aloud to confirm
Application.Speech.Speak ("You said " & Me.TextBox1.Text)
There are three separate routines to explain:
|Routine||What it does|
|UserForm_Initialize||When you load the form, this routine runs. It creates a new listening session, so that you can talk to VBA.|
|ListeningSession_Recognition||This handles the Recognition event for the listening session object that you've created. What this means is that when you speak a phrase, the speech recognition software will detect this and run this macro. In our case it will append what you've said onto any text already displayed in the form's textbox, with a preceding carriage return.|
|CommandButton2_Click||When you've finished, clicking on the command button (I'm not sure why mine is numbered 2, not 1). This will write the accumulated text into cell A1, then for good measure read it back out to you (make sure you've got your sound turned on). For more on getting Excel to speak to you, see this older blog.|
Trying out your system
You should now be able to click on the button on your worksheet to try out what you've created!
OK, I confess I cheated! I had to record the first line 5 times to get the word owl to appear, and the second line was meant to say hooting!
Don't forget to press Ctrl and the Windows key to turn speech recognition on before you start!
I hope this will be the start of a beautiful new relationship between you and VBA in which you can talk to each other!