Help declaring array limit with variable?

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

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
 
Ed said:
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
Click to expand...

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
 
Thank you, John. That worked! (Now I have another issue, but I'll make a
new thread as it is different.)
Ed
 
Back
Top