COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
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!

  1. Using your voice to fill in a VBA form
  2. Setting up speech recognition
  3. Integrating speech recognition with VBA (this blog)

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:

Simple VBA 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:

Creating a user form

Create a new user form in VBA.

Call your form SpeechForm, and add a couple of controls:

Adding controls

I've added a textbox and a command button.

Optionally, create a subroutine to display your form:

Showing your form

This simple subroutine would bring the form up on screen.

You could then create a button on a worksheet:

Creating a button

Add a clickable button to a worksheet.

 

When you click on this button, your form should appear:

The clickable button

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:

Changing references

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:

Speech recognition library

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 to write code

Double-click on your form in VBA to assign code to its events.

 

Delete any code which appears:

Deleting event handler

Delete any code apart from Option Explicit.

 

Paste in the following code (it's explained below!):

Option Explicit

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)

Grammar.DictationLoad

End If

Grammar.DictationSetState (SpeechRuleState.SGDSActive)

End Sub

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 & _

Result.PhraseInfo.GetText

End Sub

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)

End Sub

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!

Testing the system

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! 

  1. Using your voice to fill in a VBA form
  2. Setting up speech recognition
  3. Integrating speech recognition with VBA (this blog)
This blog has 0 threads Add post