Text to speech in Excel visual basic macro

Question

I've been trying to write a macro in excel that can recognize changing values of a column of cells and trigger an alert using text to speech for the corresponding row different column. Below are two sets of code I've used and have produced results, however I need to finetune the code as I've run into a few obstacles:

the first code updates too frequently (each time there is an external update to the sheet, fires off this code).

Sub Worksheet_Calculate()
Dim myText As String
For Each c In Range("bf4:bf45")
If c.Value = 1 Then
myText = c.Offset(0, -57).Text
Application.Speech.Speak (myText)
End If
Next
End Sub

the second code fires off only when I manually hit the enter key in the range defined.

 Sub worksheet_change(ByVal target As Range)
 If target = 1 And _
 target.Column = 58 And _
 target.Row >= 4 And _
 target.Row <= 45 Then _
 Application.Speech.Speak target.Offset(0, -57).Text

 End Sub

I am trying to add code that can either provide a timer for this code ( so every 4 minutes the text to speech alert will go off) or each time the defined column's values change there will be an auto text to speech alert of corresponding row.

Thanks!

Edited to include code of worksheet calculate and code for .ontime

Sub Worksheet_Calculate()
Dim myText As String
For Each c In Range("bf4:bf45")
If c.Value = 1 Then
myText = c.Offset(0, -57).Text
Application.Speech.Speak (myText)
End If
Next
End Sub



 Public Sub Updatetextspeech()
'Clock that prompts running of text to speech alert
 Sheets("ALERTS").Select
 Call myText
 Nexttick = Now + TimeValue("00:03:00")
 Application.OnTime Nexttick, "Updatetextspeech"

        If Time >= TimeValue("16:00:00") Then
        Application.OnTime Nexttick, "Updatetextspeech", , False
    End If
End Sub

Additional code updated 9/12/2014 in response to DavidZemens code

Option Explicit

Dim oldValues As Variant
Dim rng As Range

Sub Main()

'## Define our range to monitor, modify as needed
 Set rng = Sheets("ALERTS").Range("be4:be45")

'## Store its values in the array
oldValues = rng.Value

'## Initialize the UpdateTextSpeech
' I use a shorter interval for debugging, modify as needed
Application.OnTime Now + TimeValue("00:00:10"), "UpdateTextSpeech"


End Sub

For some reason below section of the code sub "updatetextspeech" is causing alot of breaks, of the different code I tried I would either get object not defined, or compile errors, or argument not optional below are the few different codes for the application.speech I tried Sub UpdateTextSpeech()

 Dim r As Long

 **'## Iterate the range**
 For r = 1 To rng.Rows.Count
 'Check if its value has changed AND the adjacent cell
 If rng.Cells(r, 1).Value <> oldValues(r, 1) And rng.Cells(r, 1).Offset(0, 1).Value = 1 Then

    **'This is where your speech app goes:** 
     Application.speech.speak.cells(r,1).text  'OR

    Application.speech.speak.value.text        'OR

    Application.speech.speak (updatetextspeech).text  'OR

    End If
 Next

'Provide a way to escape the OnTime loop:
 If MsgBox("Continue monitoring cell changes?", vbYesNo) = vbYes Then
'update the "old" values
oldValues = rng.Value
Application.OnTime Now + TimeValue("00:00:10"), "Updatetextspeech"
End If


End Sub

Answer 1

Let's try something like this.

First, declare a module-level variable to represent the range of cells you want to monitor, and also their values can be stored as a module level variant.

We'll initially store the values. Then we start our timer loop using Application.OnTime method. Each interval we will compare the current values to the values stored at the last interval. If the value has changed and if the formula equals 1, then you can do the speech. A prompt will ask the user if s/he wants to continue. If yes, then we store the new values in the variant and those will be compared against the next interval.

I use a shorter interval, and a message box instead of the speech application, but you should be able to modify this.

Option Explicit

Dim oldValues As Variant
Dim rng As Range

Sub Main()

'## Define our range to monitor, modify as needed
Set rng = Sheets("ALERTS").Range("A2:A10")

'## Store its values in the array
oldValues = rng.Value

'## Initialize the UpdateTextSpeech
' I use a shorter interval for debugging, modify as needed
Application.OnTime Now + TimeValue("00:00:10"), "UpdateTextSpeech"


End Sub
Sub UpdateTextSpeech()

Dim r As Long

'## Iterate the range
For r = 1 To rng.Rows.Count
    'Check if its value has changed AND the adjacent cell
    If rng.Cells(r, 1).Value <> oldValues(r, 1) And rng.Cells(r, 1).Offset(0, 1).Value = 1 Then

        'This is where your speech app goes:
        MsgBox rng.Cells(r, 1).Address & " has changed." & vbCrLf & vbCrLf & _
            "Old value: " & oldValues(r, 1) & vbCrLf & _
            "New value: " & rng.Cells(r, 1).Value

    End If
Next

'Provide a way to escape the OnTime loop:
If MsgBox("Continue monitoring cell changes?", vbYesNo) = vbYes Then
    'update the "old" values
    oldValues = rng.Value
    Application.OnTime Now + TimeValue("00:00:10"), "Updatetextspeech"
End If


End Sub