Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
547 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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
Search our website
We also send out useful tips in a monthly email newsletter ...
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!):
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. |
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.