SpeakToMe: A Speaking Excel



INTRODUCTION

Computer: “Good morning Boss!”
User: “How’s my system?”
Computer: “Good sir! Nothing to report!, how about you, how was your sleep last night?”
User: “Yeah, I’m good!, Is my breakfast ready?”
Computer: “That’s nonsense question sir, I don’t even know how to cook. Cook for yourself!”
User: “Yeah yeah, I’ll cook you later”

Talking to your computer? It sounds like crazy conversation, your just like talking to yourself. But actually, not like that, this SpeakToMe will assist you in complicated situations, such as helping you solve the problem without looking at your screen, also you can monitor your work in progress, many errors that we can’t solve, and lot more …..aaanndd it’s totally AWESOME! 😀

Let us begin with,

“while opening your excel file and let the SpeakToMe run”

SETTING UP YOUR VBA

Open your workbook (Microsoft Excel File)
Enable your Developer Tab (see here how to Show your Developer Tab on the ribbon)

You have to allow or enable the settings for macro to run the code
(File –> Option –> Trust Center –>Trust Center Settings –> Macro Settings –> Check “Enable all macros”)


HOW TO WORK IT OUT

Writing the Code

 Look the Developer Tab on the ribbon

Click View Code

 Under Microsoft Excel Objects (left pane)

Click “ThisWorkbook”

Then write this code;

Private Sub Workbook_Open()
Dim SpeakToMe As Object
Set SpeakToMe = CreateObject(“sapi.spvoice”)
SpeakToMe.Speak (“Good morning Boss!”)
End Sub

Note: You can change “SpeakToMe” to your preferred name)
(I’ll add code snippet, soon)

Testing your work

To test it out, hit F5 button
(Check, your volume so that you can hear the voice)

Save your file as SpeakToMe  (Excel Macro-Enabled Workbook)

Save as type Excel Macro-Enabled Workbook


Hit Enter

Close the file and Open it again

MORE MORE MORE

You probably noticed, She (computer) said “Good morning Boss!”

Hmmm but what if, our time now is in the afternoon period, you opened the file in the afternoon, let’s say 5 pm
She said “Good morning Boss!”, ooohh how ignorant your computer is!

Well it seems that, some cases may consider this, like in businesses, even in the afternoon period you said to your customer “good morning”, this is acceptable for them but not in our case, we have to be accurate in our output. If it is morning period then say good morning, or if it is afternoon period then say good afternoon and so on.

For simplified statement

If it is morning (12 mn to 11:59 am) then
Good morning!
If it is afternoon (12:00 nn to 5:59 pm) then
Good afternoon!
If it is evening (6:00 pm to 11:59 pm) then
Good evening!

 For the Code

Private Sub Workbook_Open()

Dim SpeakToMe As Object ‘Declare as object
Dim TmeFmt As Integer ‘Declare our time as integer

TmeFmt = Format(Time, “h”)
Set SpeakToMe = CreateObject(“sapi.spvoice”)

If TmeFmt < 12 Then
    SpeakToMe.Speak (“Good morning, Boss!”)
ElseIf TmeFmt < 18 Then
    SpeakToMe.Speak (“Good afternoon, Boss!”)
ElseIf TmeFmt < 24 Then
    SpeakToMe.Speak (“Good evening, Boss!”)
End If
End Sub

Code


Testing your work

Close the code editor

Re-open your workbook

Note: Make sure you have speakers or headset, and set it to right volume

Explanation

‘—————————–EXPLANATION——————————-
‘*1 We declared SpeakToMe to Object
‘*2 We declared TmeFmt to Integer

  • ‘Declaration of a variable is much simple as declaring an item to different kinds of group
  • ‘Say, I declare Item A as group B, which means Item A is in Group B
  • ‘or I declare Item A as group A, which means Item A is in Group A
  • ‘   For practice, Run this code
  • ‘   Create 2 commandbuttons

For commandbutton1:

Private Sub CommandButton1_Click()

Dim Trial As Integer
Trial = 1000 / 3
MsgBox Trial
End Sub


          RESULT -> 333

For commandbutton2:

Private Sub CommandButton2_Click()
 Dim Trial As Double
 Trial = 1000 / 3
 MsgBox Trial
End Sub

RESULT -> 333.333333333333
See the difference

If you want to learn more, click this url http://www.excel-easy.com/vba/variables.html


‘*3 TmeFmt = Format(Time, “h”)

  • ‘ As a default the format of Time Function is e.g., “7:34:01 pm” try this code; make another CommandButton

MsgBox Time

  • ‘ The output is your time now
  • ‘But if you modify the time format like i did in the above code
  • ‘Format(Time, “h”) which means I only include “h” as hour (this is a 24-hour format) that is, if my time period is 7:41:00 pm my “h” will be 19 but if my time period is 7:41:00 am my “h” will be 7
  • ‘You can try this code; insert commandbutton

MsgBox Format(Time, “h”)

Click this link to learn more https://msdn.microsoft.com/en-us/library/office/gg251755.aspx

For CreateObject Function please refer to this link, below

https://msdn.microsoft.com/en-us/library/7t9k08y5(v=vs.90).aspx



That’s it, that was easy, now you have a talking excel whenever you open your file 🙂

ADDITIONAL


VBS: Using Notepad

You can also try this on a visual basic script or vbs using notepad

Procedures:

Open your notepad
Copy the code below then paste it to notepad and save it as SpeakToMe.vbs, 
Execute it! (double click)

TmeFmt = Hour(Time) 

Set SpeakToMe = CreateObject(“sapi.spvoice”) 

If TmeFmt < 12 Then    SpeakToMe.Speak (“Good morning, Boss!”)ElseIf TmeFmt < 18 Then    SpeakToMe.Speak (“Good afternoon, Boss!”)ElseIf TmeFmt < 24 Then    SpeakToMe.Speak (“Good evening, Boss!”) 

End If

Note: Be sure you’d save it as .vbs

This is the image of Visual Basic Script

Note: You see there is a difference between the codes

 VBA code: TmeFmt = Format(Time, “h”)

VBS code: TmeFmt = Hour(Time)

 If you would like to run the code at the start up of windows you can refer to this link

DOWNLOAD SAMPLE FILE

Download SpeakToMe.vbs (Notepad)
Download SpeakToMe.xlsm (Microsoft Excel)

(if you don’t trust these files, scan first using your anti-virus or anti-malware)


Don’t forget to subscribe or follow me on google + for more future posts

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s