Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help declaring array limit with variable?
I'm trying to modify John Walkenbach's code for creating an option-button
UserForm programmatically (http://j-walk.com/ss/excel/tips/tip76.htm). He shows it as creating a Form with an array of option buttons for each month, and it works great. I am trying to modify it to create an array of option buttons for each row of a table, when the number of rows is not known. John's code is: Sub DemoGetOption() Dim Ops(1 To 12) As String ' Create an array of month names For i = 1 To 12 Ops(i) = Format(DateSerial(1, i, 1), "mmmm") Next i UserChoice = GetOption(Ops, 1, "Select a month") MsgBox UserChoice End Sub I tried: Sub DemoGetOption() cnt = tbl.Rows.Count Dim Ops(1 To cnt) As String but the code errored, saying I need a constant as the second term in the array declaration. I also tried: Sub DemoGetOption() cnt = tbl.Rows.Count Dim Ops(1 To 999) As String For i = 1 To cnt Ops(i) = doc2.tbl.Cell(i,1).Range.Text Next i UserChoice = GetOption(Ops, 1, "Select a month") MsgBox UserChoice End Sub That worked, but since I only have five rows, it gave me lots of empty option buttons! How do I either (a) declare my array limits using the "cnt" varable?, or (b) limit the number of option buttons to only those used? Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help declaring array limit with variable?
Ed wrote: I'm trying to modify John Walkenbach's code for creating an option-button UserForm programmatically (http://j-walk.com/ss/excel/tips/tip76.htm). He shows it as creating a Form with an array of option buttons for each month, and it works great. I am trying to modify it to create an array of option buttons for each row of a table, when the number of rows is not known. John's code is: Sub DemoGetOption() Dim Ops(1 To 12) As String ' Create an array of month names For i = 1 To 12 Ops(i) = Format(DateSerial(1, i, 1), "mmmm") Next i UserChoice = GetOption(Ops, 1, "Select a month") MsgBox UserChoice End Sub I tried: Sub DemoGetOption() cnt = tbl.Rows.Count Dim Ops(1 To cnt) As String but the code errored, saying I need a constant as the second term in the array declaration. I also tried: Sub DemoGetOption() cnt = tbl.Rows.Count Dim Ops(1 To 999) As String For i = 1 To cnt Ops(i) = doc2.tbl.Cell(i,1).Range.Text Next i UserChoice = GetOption(Ops, 1, "Select a month") MsgBox UserChoice End Sub That worked, but since I only have five rows, it gave me lots of empty option buttons! How do I either (a) declare my array limits using the "cnt" varable?, or (b) limit the number of option buttons to only those used? Ed Try Dim Ops as Variant Then use ReDim when needed: ReDim Ops(1 to cnt) Variants are good for creating arrays at runtime. I think you could also try Dim Ops() as String, and then ReDim as before, but I tend to just use Variants in my code for this sort of thing. -John Coleman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help declaring array limit with variable?
Thank you, John. That worked! (Now I have another issue, but I'll make a
new thread as it is different.) Ed "John Coleman" wrote in message oups.com... Ed wrote: I'm trying to modify John Walkenbach's code for creating an option-button UserForm programmatically (http://j-walk.com/ss/excel/tips/tip76.htm). He shows it as creating a Form with an array of option buttons for each month, and it works great. I am trying to modify it to create an array of option buttons for each row of a table, when the number of rows is not known. John's code is: Sub DemoGetOption() Dim Ops(1 To 12) As String ' Create an array of month names For i = 1 To 12 Ops(i) = Format(DateSerial(1, i, 1), "mmmm") Next i UserChoice = GetOption(Ops, 1, "Select a month") MsgBox UserChoice End Sub I tried: Sub DemoGetOption() cnt = tbl.Rows.Count Dim Ops(1 To cnt) As String but the code errored, saying I need a constant as the second term in the array declaration. I also tried: Sub DemoGetOption() cnt = tbl.Rows.Count Dim Ops(1 To 999) As String For i = 1 To cnt Ops(i) = doc2.tbl.Cell(i,1).Range.Text Next i UserChoice = GetOption(Ops, 1, "Select a month") MsgBox UserChoice End Sub That worked, but since I only have five rows, it gave me lots of empty option buttons! How do I either (a) declare my array limits using the "cnt" varable?, or (b) limit the number of option buttons to only those used? Ed Try Dim Ops as Variant Then use ReDim when needed: ReDim Ops(1 to cnt) Variants are good for creating arrays at runtime. I think you could also try Dim Ops() as String, and then ReDim as before, but I tend to just use Variants in my code for this sort of thing. -John Coleman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
declaring public variable value | Excel Programming | |||
Declaring a Public variable as a Range and its lifetime | Excel Programming | |||
Declaring variable as a dynamic array? | Excel Programming | |||
Declaring Variable as VBConstant | Excel Programming | |||
Declaring a variable? | Excel Programming |