Enable #Excel Formulas to Give an Audio Result by David Hager

 

Most people reading this article have seen how to document a formula with the N function. Here is an example.

=SUM(A1:A3)+N(“This formula sums the first 3 values in Column A”)

The technique presented here uses a similar concept (the result is not affected by the additional formula).

However, instead of documenting the formula, it adds the ability to provide an audio result each time the formula is recalculated. I am sure that the use of this UDF will find wide usage throughout the Excel community.

Here is the VBA Function code for doing this. Make sure to add the Microsoft Speech Object Library (sapi.dll version) under Tools, Reference for this to work.

Function GiveVocalResult(Optional Person As String = “Him”, Optional bVolatile As Boolean = False, _

Optional Rate As Long = 1, Optional Volume As Long = 60)

Dim Voc As SpeechLib.SpVoice

Set Voc = New SpVoice

Dim sAddress As String

Application.Volatile bVolatile

With Voc

If Person = “Him” Then

Set .voice = .GetVoices.Item(0) ‘male

ElseIf Person = “Her” Then

Set .voice = .GetVoices.Item(1) ‘female

Else

End If

.Rate = Rate

.Volume = Volume

sAddress = Application.Caller.Address

rResult = Evaluate(Mid(Range(sAddress).Formula, 1, InStr(1, Range(sAddress).Formula, “&Give”) – 1))

.Speak rResult

End With

End Function

After numerous attempts to create the desired UDF, I finally came up with a solution. It is based on these two line of VBA code.

sAddress = Application.Caller.Address

rResult = Evaluate(Mid(Range(sAddress).Formula, 1, InStr(1, Range(sAddress).Formula, “&Give”) – 1))

Obtaining the address containing the formula proved to be the right path. The use of the caller address in the Evaluate function afforded the result of the “first” formula. There are two examples that illustrate the use of the GiveVocalResult UDF. Note that all of the arguments of the function are optional.

In the first example, shown in the following figure, a formula that looks for the second largest value in a range “reads” the result in a female voice.

In D1   =LARGE(A1:A10,2)&GiveVocalResult(“Her”)

When values are changed in the range A1:A10, the result of the formula is vocalized. The use of “Her” in the first function argument changed the default value of Him to Her.

In the second example, the value in cell C2 is set by using a data validation drop down list. When changed, the formula in D2 (=C2&GiveVocalResult()) reads the result in a male voice.

The last 3 arguments in the function are all set with default values. bVolatile is set to False, Rate is set to 1 (can vary between -10 to 10, and Volume is set to 60 (can vary from 0 to 100). Experiment with the settings and enjoy.

Because of what I consider to be a groundbreaking technique, please reference my web site when you use it.

https://dhexcel1.wordpress.com/

The example file can be downloaded here.

VoiceVar