I have some VBA code which takes information from checkboxes on a form
There are 10 check boxes and basicaly i want to go through a series o
if statements whe

if chkBox1 = True
array(0) = "AA"
if chkBox2 = True
array(1) = "BB"

...etc

But obviously this would only work if all check boxes were selected
how would i go about adding to the end of array no matter how man
values are already stored in it?

I was thinking something like

counter = 0

if chkBox1 = True
array(counter) = "AA"
counter = counter + 1
else
'do nothing
if chkBox2 = True
array(counter) = "BB"
counter = counter +1
else
'do nothing

But was just wondering if there was any kind of add to end of arra
function I could call?

Also, is it possible to return the length of an array? i.e how man
values are stored in it?

Thanks in advanc

--
br_turnbul
-----------------------------------------------------------------------
br_turnbull's Profile: http://www.excelforum.com/member.php...fo&userid=2747
View this thread: http://www.excelforum.com/showthread.php?threadid=47330

ok i've decided to stick with the idea I had, the code goes like:

Dim chosenChkBox() As Variant

Dim chosenCount As Integer
chosenCount = 0

If chkAA = True Then
chosenChkBox(chosenCount) = 0 'POINT A (see below)
chosenCount = chosenCount + 1
End If
If chkBB = True Then
chosenChkBox(chosenCount) = 1
chosenCount = chosenCount + 1
End If
If chkCC = True Then
chosenChkBox(chosenCount) = 2 'POINT B (see below)
chosenCount = chosenCount + 1
End If

.....Etc

But when i run it i get the error 'subscript out of range', dependin
on which checkboxes are selected, it highlights the equivelent part o
the code e.g. if chkAA is true then it starts the debugging at Point
(see above), or if chkCC is true then it starts the debugging at Poin
B (see above), etc.

Any ideas

--
br_turnbul
-----------------------------------------------------------------------
br_turnbull's Profile: http://www.excelforum.com/member.php...fo&userid=2747
View this thread: http://www.excelforum.com/showthread.php?threadid=47330

No add to end of array function

Dim v(1 to 100)
v(ubound(v)) = "value"

would place the value in the last element of the array - but you appear to
be talking about the next empty element and not actually the last element.

Your use of a counter is the way to do it.

Another would be to make the array dynamic and always to a Redim Preserve to
increase the size by 1 and add at the end. However, this is slower.

--
Regards,
Tom Ogilvy


"br_turnbull"
wrote in message
...

I have some VBA code which takes information from checkboxes on a form.
There are 10 check boxes and basicaly i want to go through a series of
if statements whe

if chkBox1 = True
array(0) = "AA"
if chkBox2 = True
array(1) = "BB"

..etc

But obviously this would only work if all check boxes were selected,
how would i go about adding to the end of array no matter how many
values are already stored in it?

I was thinking something like

counter = 0

if chkBox1 = True
array(counter) = "AA"
counter = counter + 1
else
'do nothing
if chkBox2 = True
array(counter) = "BB"
counter = counter +1
else
'do nothing

But was just wondering if there was any kind of add to end of array
function I could call?

Also, is it possible to return the length of an array? i.e how many
values are stored in it?

Thanks in advance


--
br_turnbull
------------------------------------------------------------------------
br_turnbull's Profile:
http://www.excelforum.com/member.php...o&userid=27479
View this thread: http://www.excelforum.com/showthread...hreadid=473304

Dim chosenChkBox() As Variant
Redim chosenChkBox(0 to 10)
Dim chosenCount As Integer
chosenCount = 0

--
Regards,
Tom Ogilvy

"br_turnbull"
wrote in message
...

ok i've decided to stick with the idea I had, the code goes like:

Dim chosenChkBox() As Variant

Dim chosenCount As Integer
chosenCount = 0

If chkAA = True Then
chosenChkBox(chosenCount) = 0 'POINT A (see below)
chosenCount = chosenCount + 1
End If
If chkBB = True Then
chosenChkBox(chosenCount) = 1
chosenCount = chosenCount + 1
End If
If chkCC = True Then
chosenChkBox(chosenCount) = 2 'POINT B (see below)
chosenCount = chosenCount + 1
End If

....Etc

But when i run it i get the error 'subscript out of range', depending
on which checkboxes are selected, it highlights the equivelent part of
the code e.g. if chkAA is true then it starts the debugging at Point A
(see above), or if chkCC is true then it starts the debugging at Point
B (see above), etc.

Any ideas?


--
br_turnbull
------------------------------------------------------------------------
br_turnbull's Profile:
http://www.excelforum.com/member.php...o&userid=27479
View this thread: http://www.excelforum.com/showthread...hreadid=473304

I've solved it by doing:

Dim loopCount As Integer
loopCount = 0

Dim chkTrue(9) As Integer

If chkAA = True Then
chkTrue(loopCount) = 0
loopCount = loopCount +1
End If

If chkAA = True Then
chkTrue(loopCount) = 1
loopCount = loopCount +1
End If

I then pass the loopCount to a Do...Untill Loop that only loops as many
time as loopCount (It will never exceed 10) so making that so for the
chkTrue array will suffice, but it also means it wont look at any
further variables in the array say if only 2 entries are made.

Thanks for your help though.


--
br_turnbull
------------------------------------------------------------------------
br_turnbull's Profile: http://www.excelforum.com/member.php...o&userid=27479
View this thread: http://www.excelforum.com/showthread...hreadid=473304

I see. Most times it is better to use a method you understand.

--
Regards,
Tom Ogilvy


"br_turnbull"
wrote in message
...

I've solved it by doing:

Dim loopCount As Integer
loopCount = 0

Dim chkTrue(9) As Integer

If chkAA = True Then
chkTrue(loopCount) = 0
loopCount = loopCount +1
End If

If chkAA = True Then
chkTrue(loopCount) = 1
loopCount = loopCount +1
End If

I then pass the loopCount to a Do...Untill Loop that only loops as many
time as loopCount (It will never exceed 10) so making that so for the
chkTrue array will suffice, but it also means it wont look at any
further variables in the array say if only 2 entries are made.

Thanks for your help though.


--
br_turnbull
------------------------------------------------------------------------
br_turnbull's Profile:
http://www.excelforum.com/member.php...o&userid=27479
View this thread: http://www.excelforum.com/showthread...hreadid=473304

If you want to try something different use a Collection as shown below.
I grows dynamically.

Option Explicit

Public Sub Test()

Dim chosenChkBox As Collection
Dim chkAA As Boolean
Dim chkBB As Boolean
Dim chkCC As Boolean
Dim i As Integer
Dim strTemp As String

chkAA = True
chkBB = False
chkCC = True

Set chosenChkBox = New Collection

If chkAA Then chosenChkBox.Add "AA"
If chkBB Then chosenChkBox.Add "BB"
If chkCC Then chosenChkBox.Add "CC"

For i = 1 To chosenChkBox.Count
strTemp = strTemp + chosenChkBox.Item(i) + vbCrLf
Next

MsgBox strTemp

Set chosenChkBox = Nothing

End Sub



*** Sent via Developersdex http://www.developersdex.com ***

br_turnbull wrote:
. . . how would i go about adding to the end of array no matter how many
values are already stored in it?
. . . .

If the stored values are all in the lowest indexed elements of a
one-dimensional array

MyArray(Application.CountA(MyArray)+1) = "addedValue"

Alan Beban

Alan Beban wrote:
br_turnbull wrote:

. . . how would i go about adding to the end of array no matter how many
values are already stored in it?
. . . .


If the stored values are all in the lowest indexed elements of a
one-dimensional array

MyArray(Application.CountA(MyArray)+1) = "addedValue"

Alan Beban
I should have said "if the stored values are in the lowest indexed
elements of a one-dimensional *1-based* array.

To generalize to a 1-based or 0-based array

MyArray(Application.CountA(MyArray)+LBound(MyArray )) = "addedValue"

Alan Beban