This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Using your voice to fill in a VBA form Setting up speech recognition Integrating speech recognition with VBA (this blog)

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!

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

Posted by Andy Brown on 13 March 2020

In this blog

Creating the form and basic code
Linking to the speech recognition object library
Writing the code to get speech recognition working
Trying out your system

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!):

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!

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
Using your voice to fill in a VBA form Setting up speech recognition Integrating speech recognition with VBA (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Integrating speech recognition with VBA

Creating the form and basic code

Linking to the speech recognition object library

Writing the code to get speech recognition working

Trying out your system

Head office

London

Manchester