How to get Excel to speak to you - reading out the contents of cells
Part three of a three-part series of blogs

Deep within its bowels Excel contains a feature allowing it to read out the contents of cells (you can also get your VBA macros to talk to users). This blog goes to those deep places hidden within Excel!

1. Getting Excel to answer back!
2. Getting Excel to read out cell contents
3. Getting Excel VBA to speak to you (this blog)

Posted by Andy Brown on 12 August 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Getting Excel VBA to speak to you

The previous part of this blog showed how you can get Excel to read things out to you, but you can also perform the same trick in VBA.

Newcomers to VBA might like to read the first bit of our Excel VBA tutorial at this point, before proceeding further.

## Basic speaking

Here's a basic subroutine to speak to you:

Sub SaySomethingSimple()

'say something!

Application.Speech.Speak ("Wise Owl")

End Sub

The Speak method has 3 arguments, but only the first one is of any interest, as this Microsoft page demonstrates.

## Reading out the first n primes

If you want to get Excel to do this, you could run this macro!

Dim ThisNumber As Integer

Dim i As Integer

Dim IfPrime As Boolean

'hideously inefficient algorithm!

For ThisNumber = 2 To 100

IfPrime = True

For i = 2 To Int(Math.Sqr(ThisNumber))

If ThisNumber Mod i = 0 Then

IfPrime = False

Exit For

End If

Next i

If IfPrime Then Application.Speech.Speak CStr(ThisNumber)

Next ThisNumber

End Sub

Probably the best use of Excel speech is to read out the contents of cells.  This nifty macro would read out the contents of all of the values in column A, colouring each in as it went:

Dim DataRange As Range

Dim DataCell As Range

'get reference to range of data

Range("A1").Select

Set DataRange = Range( _

ActiveCell.Offset(1, 0), _

ActiveCell.End(xlDown))

'and then colouring in

For Each DataCell In DataRange

DataCell.Interior.Color = RGB(250, 220, 240)

Application.Speech.Speak ( _

" = " & _

DataCell.Value)

Next DataCell

End Sub

Here's the macro in action (the data is actually my scores at an app called Scramble, if you must know):

The macro has read out so far:

As you can see from the above three examples, the world of speech is now your oyster!

1. Getting Excel to answer back!
2. Getting Excel to read out cell contents
3. Getting Excel VBA to speak to you (this blog)