Microsoft Excel is a powerful spreadsheet application. Many people miss out on the true power of the program by not capitalizing on everything it can do. Visual Basic for Applications (VBA) is a programming development platform that is installed with every Microsoft Office application. Visual Basic is a take on the old Basic programming language. With VBA, a simple Microsoft Excel spreadsheet can be made into a dynamic application that automatically carries out functions and procedures. In this article, we will add simple code to an Excel 2007 spreadsheet to make it talk. This is the perfect project for you to acquaint yourself with the VBA environment. You will need to know something about programming before doing this project.
Step 1
Open a spreadsheet. You can use a spreadsheet that you currently have on your computer or you can create the spreadsheet shown here. If you are using a pre-made one, make sure there is a totals column.
Video of the Day
Step 2
Make the Developer tab appear in the Ribbon. Most likely, you will not see the Developer tab when you open Excel. You will need to click on the "Microsoft Office" button at the top of the screen, and then click "Excel Options". From the Popular screen, choose "Show Developer Tab in the Ribbon" and click "OK" to close this form. Click on the "Developer" tab once it is visible from the Excel screen.
Step 3
Click on "Visual Basic." The Visual Basic Editor will appear. On the right hand side, double-click on Sheet 1, since this is the sheet we will be working with. A new blank sheet is added to the right-hand side of the screen.
Step 4
Type in "Option Explicit" at the top of the screen. You should do this with ever program you create in VBA. This ensures that you name your variables before assigning data to them. Think of variables as small containers that will hold information. They come in handy when the information you are using is constantly changing. For instance, you need to work with the January totals. Since this amount will change from time to time, you assign it to a variable. You can always call on that variable even if you do not know the exact amount of data it holds.
Step 5
Type in the following function: Function TalkIt(txtTotal) Application.Speech.Speak(txtTotal) TalkIt=txtTotal End Function This is the function that will control the spreadsheet talking. What we did was use the built-in text-to-speech generator that comes with Excel. The first line of the function declares the function ("Function"), gives it a name ("TalkIt"), and then states what data we will pass to it ("txtTotal"). Now that the function is defined, you must reference it and send the data for it to work.
Step 6
Go back to the spreadsheet and add a button. From the Developer tab, click "Insert" and then press the button (the first icon at the top. Draw the button on the spreadsheet anywhere you want. A new window appears; name the button "cmd_Total" and click "OK." You can change the text of the button by right-clicking the button and then clicking "Edit Text." I typed "Talk" in my text.
Step 7
Go back to the Visual Basic Editor to add the last piece of code. Private Sub cmdTotal_Click() Dim intTotal As Integer 'declare a new variable to hold text Dim txtTotal As String intTotal = WorksheetFunction.Sum(Cells.Range("B3", "B14")) 'use the If...else statement to control the value of txtTotal variable If intTotal < 2500 Then txtTotal = "Goal Not Reached" Else txtTotal = "Goal Reached" End If TalkIt (txtTotal) End Sub
What the code above means is that when the button is clicked, the code will see if the total of the "Hats" column in the worksheet is less than $2500. If it is, then Excel's synthized voice will say "Goal Not Reached." If the total is $2500 or more, then the voice will speak "Goal Reached." That's it. You've just created your first program in Microsoft Excel.
Video of the Day