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

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

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