1

I am attempting to build an array accordingly to whether a set of CheckBoxs are ticked (in a Userform), from another array. So the code is currently:

   Dim MyArray(10) As Integer
   MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
  Dim IntrnlArray() As Variant

  For i = 1 To 10
  If Me.Controls("CheckBox" & i).Value = True Then
  ' some code I cant figure out!
  End If
Next

The resulting (possibly smaller) new array, IntrnlArray(), would then be used elsewhere. So the new array would contain the number of which checkbox has been ticked. These numbers would then be used elsewhere in further calculations, not to tell he user which checkbox has been ticked. Any help would be much appreciated!!!

Improve this question
4
  • I am sure one of the solutions offered here will suit your needs: stackoverflow.com/questions/7000334/… Similar to the author of this post I believe that a collection might be a better fit for your needs. Alternatively you can also just concatenate the numbers in a string (comma separated) and afterwards read from the string using the Split() method. Commented Mar 22, 2016 at 12:19
  • Out of interest, I have never used a collection before. How would I go about coding this? Thank you for your patience! Commented Mar 22, 2016 at 12:58
  • The following might be a bit too much: excelmacromastery.com/Blog/index.php/… But it is very structured and like this you can only get the bits and peaces you really want / need. There is also a comparison to arrays and when to use which: excelmacromastery.com/Blog/index.php/… As the author mentions: arrays are better when you have a fixed size. Since you wish to dynamically adjust your size I'd recommend using a collection. Commented Mar 22, 2016 at 13:09
  • Grand. Thanks for the link Commented Mar 22, 2016 at 13:16

2 Answers 2

Reset to default
1

Since @Mr-Nemo asked in a comment I am also providing an example using a collection:

Public Sub UsingCollections()

Dim MyCollection As New Collection

For i = 1 To 10
    If Me.Controls("CheckBox" & i).Value = True Then
        MyCollection.Add i
    End If
Next i

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Note: At the end you'll have a collection of as many items as there are check-boxes whit a True value.
He changed his mind and you steal me 15 rep points ;-) @MrNemo I would still let the NZ() I set on the checkbox value otherwise you'll get an error on triple-state checkboxes that can return true, false and NULL
@ThomasG That's why I upvoted your answer to at least give you 10 points cause I felt guilty. I wish Mr-Nemo would also learn to upvote. Then you'd have 20 by now. BTW, NZ() works only for MS Access. Since I do not see any indication of MS Access you should note the alternative use of Iif in case of Excel.
Ah yes you are right about the NZ on Excel, on another hand, he never specified it was for Excel :p
1

Not totally sure of what you want exactly but I guess this would be enough:

Dim IntrnlArray() As boolean

For i = 1 To 10
        redim IntrnlArray(i)
        IntrnlArray(i) = nz(Me.Controls("CheckBox" & i).Value,false)
Next

You don't need 2 arrays. This code will assign the value of each check box (true or false) to the corresponding element of the array

Improve this answer

1 Comment

Thank you so much!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.