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. |
#4
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
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 |