Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
referring to increasing VBA variable name in loop
I guess great minds think alike since everyone recommended the array,
thanks! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
referring to increasing VBA variable name in loop
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to a variable file name | Excel Discussion (Misc queries) | |||
Referring to multiple worksheets via a variable | Excel Programming | |||
Referring to a Worksheet with a variable | Excel Programming | |||
Increasing a range in a loop | Excel Programming | |||
Referring to a worksheet using variable | Excel Programming |