Excel VBA Introduction Part 44 - Making Excel Talk

welcome to this vile tutorial on making Excel talk using Excel VBA in this fairly short video we're going to talk about one of excels more unusual features and the speech feature I'm going to show you to start with how to use the speak method to make Excel speak a string of text out loud will give you a bit of information about how you can choose the voice that Excel speaks in which depends a little bit on which version of Windows you're using on which voice packs you've got installed and to make things slightly more practical although still having a bit of fun with Excel here we're going to show you how you can get Excel to speak the contents of cells and we're going to build a little search feature at which Excel will speak the results of rather than display on a message for instance and then the final thing we'll do is have a quick look at linking up the speech feature to some other basic workbook events so a little bit of fun in this video let's get started let's start by showing you the absolute basics of how to make Excel speak a phrase out loud I've got a workbook here that does have some sample data in it this is just a list of the highest-grossing films but for the very first example it doesn't matter if you don't have this exact same example as all we're not going to use any of the data that's in the worksheet so let's start by heading into the visual basic editor so if I head to the Developer tab in the ribbon and choose visual basic or of course you can press the alt + f11 keyboard shortcut and then you can insert a new module into the project and then we can start a quick new sub routine called speak to me the speech component of Excel is a member of the applicational Church let's start by referencing the application so just press ctrl + space on the keyboard there to launch the intellisense list and you'll find the application object and if you enter a full stop then you can find the speech property of the application then finally what we're interested in here is the speak method so application dot speech dot speak followed by a space and you'll see a list of parameters the only compulsory one here is the text parameter which requires a string and whatever string you enter here Excel will speak out loud and yes that does include rude words but I'm not gonna type in any rude words at this point how was a traditional example the traditional traditional example of showing messages or speaking messages it's always hello world but I find that's so boring let's make it say something else in my mum's 60th birthday today I've just been eating some birthday cake so that say something like that like cake for instance and then if I wanted to execute that to make Excel speak out loud all I need to do is run the subroutine so I can do that either by clicking the green triangle of course or pressing the f5 key on the keyboard and if I do that there you go Excel likes cake now the exact voice that you'll hear when you make Excel speak something it depends very much on which version of Windows you are using and within different versions of Windows you have different choices of voices so the default one that I see that I hear here is an American chap called David I believe some using Windows 10 of moments if you wanted to be able to modify which version or which are voice you're hearing then the simplest thing to do is view the control panel so I'm going to do this from Windows 10 just by right-clicking on the start button in the bottom left hand corner and then choosing control panel you may do this in a slightly different way if you're using different versions of Windows and then inside the control panel just switch my view to view large icons just so you can see these a little bit more clearly so I'm going to look at a second a called speech recognition and if I choose that option I'll get another window of hearing and then on the left hand side of that window is knocking called text-to-speech and if I click on that I'll get my list of options for different voices so from this dialog box you can select from a list of the voices you have installed in your machine so as I say I'm using Windows 10 and by default I'll get one called Microsoft David and one called Microsoft zero those are the two that are installed by default with American accents I also have an English language pack installed which means I get Microsoft hazal as well now if I were to choose a different option from this drop-down list it will immediately begin speaking the phrase that's in this text box here which is quite a long window phrase so what I'm going to do is I'm going to quickly switch to a different voice and then just hit the apply button to make it stop talking Sooners use Microsoft 0 and when I click apply it will stop the air the the voice from speaking you actually enter different text in this text box if you want to say make it say something slightly different so let me shorten the Frater it's going to say and then I can click the preview voice button and we speaks in a different voice you can also control the speed of the voice which is can be quite amusing if you ramp up the speed so it speaks incredibly quickly I'm going to choose Microsoft hazal at this point you have selected and then hit apply to stop it from from speaking and then just to prove that once that's been applied that it affects what Excel does I'm going to click quickly click back into Excel in the background into the VB editor so I might speak to me subroutine and then when I take em and there you go so Excel will speak in the different voice based on which one you've selected from the speech properties dialog box one reason I do like having the ability to change the accent and the voice that the windows uses it's because sometimes certain words in certain accents just sound a little bit wrong so a classic example if I stick with the English accent just for the moment and I change the phrase to say let's just make you say aluminium let me spell it correctly first as well aluminium and then if I make hazel so this sounds perfectly normal in an English accent if I change the voice back to David however and then make David say aluminium just sounds a little bit wrong to me so if I want David to pronounce it correctly for his accent I could spell aluminium incorrectly to make him pronounce it correctly um you can have a lot of fun without sound poking in front of my colleagues across the pond there but some yeah you have a lot of fun with changing the voice and using different accents now it may well be if you're working in Windows 10 that you don't actually have the hazel voice installed by default I didn't have it installed by default I had to choose to install that you understand that's actually relatively straightforward to do I just okay this speech properties dialog box and then just about as speech recognition and closed down that window I can then head to the Start menu and choose view settings so if I do that there's a section on the settings dialog box for time and language and if I select time and language there's an option on the left hand side for region and language and then this will list out all the languages that are currently installed on the machine so for instance if I wanted to install the the English voice pack I could head to the English language and then click the options button there and then if I hadn't already installed the hazel voice back there would be a download button in the speech section just as there is for the handwriting and language pack section there as well so you can click the the download button to install that particular voice but just head back a step it's possible to add in new languages as well of course I can click the plus button here and give them a great big long list of different languages and I believe that some of these possibly not all but at least some of them do you have a language pack installed a voice pack installed so I'm going to cancel that because I have all the voices installed that I actually want so let's move on from making Excel just say a constant phrase like a hello world or whatever and make it speak the contents of cells what I'm going to do is I'm going to modify my subroutine so the first thing that it does is it selects rain d2 on my worksheet now it doesn't really matter if you don't have exactly the same set of data as me all I'm all you need to really have in your workbook somewhere is some text and some numbers perhaps it's got a list of different film names and some some numbers representing how much they've made at the box-office and what year they were released in so my ability now is going to select cell b2 and then I could say Avatar was released in followed by what year it was released in something simple like that so do that back in the visual basic editor rather than saying application dot speech dot speak aluminium I'd like it to say the value of the active cell things like active cell dot value and then concatenate that with the phrase was released in and then concatenate that with the value of the cell that's two columns to the right of the active cells such as l dot offset 0 comma 2 dot value and having done that if i were to simply execute the subroutine at this point there we go so it speaks the contents of cells as well as literal text that you enter into the speak method we could take this a little bit further now maybe build a very simple search system which would allow the user to find a film and list and then have the system's speak some information about that film we've done this sort of thing a couple of times in previous videos there's a there's a video on using the find and find next methods in Excel VBA that's part of this series already so I won't go into quite as much detail in this video but just to build up something very simple I'm going to declare a simple variable at the top that says something like film name as string and I would like to be able to do is ask the user to enter the film that they're trying to search for so I'm going to say film name equals and I'm going to use a basic input box and ask user to type in a film name like a type type properly that would help so type in a film name or even part of a film name the find method will find parts of words as well what I then like to do is search for that film in column B of the worksheet and then try to return a reference to a cell which contains the text the user is looking for so to do that I'm going to declare another variable I'm going to say dim film cell as a range and then after I've asked for the film name I'm going to try to set film cell equal to and I can be this in a variety of ways let's say range b2 comma range b1 dot end Excel down and then I want to try to find and the thing that I'm trying to find is film name whatever these type Tim once this line is executed one of two things will have happened either I will extort a reference to the first cell in which I found the film name I was looking for or I haven't found anything at all in which case the film cell variable will content will contain nothing so I can write a quick if statement here that says if film cell is nothing then I like to do two different things I'd like to say that lets say application dot speech speak could not find followed by whatever film they might typed in previously and then I want to make sure I exit the subroutine and then say end if to close off that block if so assuming that we have found herself containing the name of the film I was looking for what I'd like to do next is concatenate a message that will then be spoken by Excel so I'm going to declare another variable at the top I'm going to say something like dim message text as string and then at my if statement I'm going to say message text equals essentially my message types can be equal to the phrases that I can catenate it earlier but rather than using active cell so it's going to copy and paste that part of the statement here rather than using active cell when I want to do instead is use film cell so I'm going to replace active cell with the words film cell instead what I can then do if I remove the range b2 dot select that's irrelevant at this point and then rather than speaking my concatenated message from earlier on I can replace all of that by making the the application speak the sorry speak the message text not the film cell like you Vardhan there we go speak message text the final thing I'm going to do before I give this one a quick test is display a message box which also displays the same text that Excel will be speaking out loud so when was a message box message text so both see and hear the phrase that we've concatenated and having to roll that we can simply give the entire thing a quick test so I should see an input box pop up to begin with you may remember from previous videos on the find method by default it will find parts of cell content as well so I just search for instance for the words star I can say star and then click OK and I should hear the message spoken out loud first Star Wars The Force awakens was released in 2015 and then finally the message box popped up on the screen as well that's a little bit irritating having to wait for the message to to end the spoken part before the the message box appears on the screen it's going to make one small modification to our procedure to make sure that we can both hear accel speaking as well as reading the message at the same time back in the VBR to then and we've got this application not speech speak message text line at the end of that line if I got you to type in a comma at the end you will see the tooltip pop back up with a list of extra parameters the other one the second parameter is speak async or speak asynchronously if we say speak asynchronously then the speech can begin but the rest of the Sybilla team can continue before the speech is finished so I'm going to set the speak async property or parameter to true and having done that let's give this one a quick extra test if we run it one more time and I'll search for let's search for a shorter film than this one let's go for frozen I haven't seen it but I'm sure some of you have so search for frozen so you can see that the speech was continuing while the message box was displayed on the screen so basically if you set the async parameter to true or speak async parameter to true it means that the rest of your sub routine can proceed while the speech is continuing for me the best part of this entire system is what happens if you search for text which doesn't exist in this particularly if that's sort of nonsensical text so when we trigger this at the lift statement to say it could not find what we search for if I run the subroutine again and social something which clearly isn't going to appear in the list and then click OK the results are I think you have absolutely endless hours of fun listening to Excel struggle to pronounce nonsensical strings of text now the speech feature might not be the most useful thing you'll encounter in Excel VBA but you can have a bit of fun with this one of the quite cool thing you can do isn't linked in the speech feature to events of the workbook and worksheet so for instance let's say you wanted to make Excel say hello to the person who'd opened up your workbook you can access the workbooks open event and the simplest way to do that is to just double click that this workbook object in the project Explorer and then again we've done a video I've made a video on workbook and worksheet events as part of it the the main tutorial series so I'm not going to go into a huge amount of detail here but to start with I'm going to go to the drop-down list at the top left and choose the workbook object which gives me the default event handler for the workbook which happens to be the one that I want the workbooks open event I'm good access to a whole host of other events on the right hand side here in this drop-down list but open as I say is the one that I want and all I'm going to make happen is that when the workbook opens I'm going to make the application speech component speak and it's going to say hello sorry spelt hello properly and then concatenate that with the name of the person who's opened up the workbook now you'll get your mileage may vary from this this little technique the environ function is the one that I'm going to use here now environment is short for environment and that refers to the yeah the Windows environment variables and one of the environment variables is the username of the person running the code so this is your the username as they will log into Windows now mine's valium descriptive is andrew gold you may find that your user names in large organizations particularly are just code numbers this might not be particularly useful in those situations but just as a quick demonstration if i've added that simple line of code to the workbooks open event i can save my procedure and i can just close down the visual basic editor and i can close down excel or close down this workbook at least and then whenever I open this workbook back up again as long as my macros are enabled which they are they will speak out loud automatically when the workbook opens um so you can have a bit of fun with with speech this wasn't designed to be the most useful thing you'll ever encounter in Excel VBA but hopefully there's something fun and silly for a Sunday afternoon hope you've enjoyed it and see you for the next video if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials meeting some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 17,694

Rating: 5 out of 5

Keywords: vba, excel, speak, speech, talk, microsoft, wise owl, excel vba

Id: AD2nkTGkO4E

Length: 17min 0sec (1020 seconds)

Published: Sun Oct 16 2016