referring to increasing VBA variable name in loop

[ucdcrush]

I am using VBA to put together some phrases to be spoken with the text
to speech feature in excel.

What I've got is a list of string variables,

Phrase1
Phrase2
Phrase3
....
Phrase30

which are set equal to different words or phrases such as
Phrase1 = "Hello"

I can hard-code a variable name into the speech code such as:

XL.Speech.Speak Phrase1

this will successfully speak the string assigned to Phrase1.

What Im having trouble with is referring to the Phrase1-30 variables
in a for loop, something like

for i = 1 to 30
xl.speech.speak phrase & i
next

This code doesn't produce any speech, I'm assuming because the
variable name isn't being passed as intended. How can I refer to
variables within a loop, when the loop # (i) is part of the variable
name?

Thanks.

 

[sebastienm]

Hi,
Try using an array of strings:
'''--------------------------------
Dim i as long
Dim Phrase() as string

Redim Phrase(1 to 30)
Phrase(1)="Hello"
Phrase(2)= ...
...
Phrase(30) = "End"

''' speak
For i =1 to 30
XL.Speech.Speak Phrase(i)
Next
''' ----------------------------------------

 

[Dave Peterson]

I'd use an array:

Dim Phrase(1 to 30) as string
phrase(1) = "what you want1"
....
Phrase(30) = "what you want30"

Then you could use:

Dim iCtr as long
....
for ictr = lbound(phrase) to ubound(phrase)
xl.speech.speak phrase(ictr)
next ictr

 

[Rick Rothstein \(MVP - VB\)]

I think you should be using an array for this...

Dim Phrase(1 To 30) As String
Phrase(1) = "Hello"
Phrase(2) = "Good-bye"
etc....
.......
For i = 1 To 30
xl.Speech.Speak Phrase(i)
Next

Rick

 

[Charles Chickering]

An array seems like it would be well suited for your application. Try
something like this:
Dim Phrase(1 to 30) As String
Phrase(1) = "Hello"
'Repeat filling phrases 2 - 30
For cnt = 1 to 30
Application.Speech.Speak Phrase(cnt)
Next

 

[ucdcrush]

I guess great minds think alike since everyone recommended the array,
thanks!

 

[Alan Beban]

I guess great minds think alike since everyone recommended the array,
thanks!

I make no claim for its utility, but I'm curious whether the following
works (I don't have a more current version of xl):

Sub abtest4()
Names.Add Name:="phrase1", RefersTo:="Hello"
Names.Add Name:="phrase2", RefersTo:="Goodbye"
For i = 1 To 2
XL.Speech.Speak Evaluate("phrase" & i)
Next
End Sub

 

[Dave Peterson]

It worked fine for me in xl2003.

I make no claim for its utility, but I'm curious whether the following
works (I don't have a more current version of xl):

Sub abtest4()
Names.Add Name:="phrase1", RefersTo:="Hello"
Names.Add Name:="phrase2", RefersTo:="Goodbye"
For i = 1 To 2
XL.Speech.Speak Evaluate("phrase" & i)
Next
End Sub

 

[Rick Rothstein \(MVP - VB\)]

If I change the XL reference to Application, then it works in XL2007 also.

Rick

 

[Alan Beban]

It worked fine for me in xl2003.

Thanks for the feedback.

Alan

 

[Alan Beban]

If I change the XL reference to Application, then it works in XL2007 also.

Rick

Thanks for the feedback.

Alan