560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Talking to Excel to fill in your input forms, using speech recognition and VBA
Part three of a three-part series of blogs
What happens when you combine the magic of speech recognition with the power of VBA? A user form which you can fill in without a keyboard!
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!
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.
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.
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.|
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!
|Parts of this blog|
25 Aytoun Street