Add to an Array

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 where:

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

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?

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" <br_turnbull.1wfbqd_1128506721.3742@excelforum-nospam.com>
wrote in message
news:br_turnbull.1wfbqd_1128506721.3742@excelforum-nospam.com...
>
> 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 where:
>
> 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
>

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.

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

--
Regards,
Tom Ogilvy

"br_turnbull" <br_turnbull.1wfhaf_1128513940.098@excelforum-nospam.com>
wrote in message
news:br_turnbull.1wfhaf_1128513940.098@excelforum-nospam.com...
>
> 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 see. Most times it is better to use a method you understand.

--
Regards,
Tom Ogilvy


"br_turnbull" <br_turnbull.1wfk2h_1128517550.538@excelforum-nospam.com>
wrote in message
news:br_turnbull.1wfk2h_1128517550.538@excelforum-nospam.com...
>
> 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