referring to increasing VBA variable name in loop
 

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.

referring to increasing VBA variable name in loop
 

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
''' ----------------------------------------
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


" wrote:

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.

referring to increasing VBA variable name in loop
 

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



wrote:

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.

--

Dave Peterson

referring to increasing VBA variable name in loop
 

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


wrote in message
...
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.

referring to increasing VBA variable name in loop
 

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
--
Charles Chickering

"A good example is twice the value of good advice."


" wrote:

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.

referring to increasing VBA variable name in loop
 

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

referring to increasing VBA variable name in loop
 

referring to increasing VBA variable name in loop
 

It worked fine for me in xl2003.



Alan Beban wrote:

wrote:
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

referring to increasing VBA variable name in loop
 

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

Rick

"Dave Peterson" wrote in message
...
It worked fine for me in xl2003.



Alan Beban wrote:

wrote:
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

referring to increasing VBA variable name in loop
 

Dave Peterson wrote:
It worked fine for me in xl2003.


Thanks for the feedback.

Alan


Alan Beban wrote:

wrote:

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

referring to increasing VBA variable name in loop
 

Rick Rothstein (MVP - VB) wrote:
If I change the XL reference to Application, then it works in XL2007 also.

Rick

Thanks for the feedback.

Alan

"Dave Peterson" wrote in message
...

It worked fine for me in xl2003.



Alan Beban wrote:


wrote:
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