Check For Array In VBA

Question

I have a list - that I just found this syntax that will permit multi-selects from my list:

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Address = "$B$1" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
    If Oldvalue = "" Then
      Target.Value = Newvalue
    Else
      Target.Value = Oldvalue & ", " & Newvalue
    End If
  End If
End If
Application.EnableEvents = True

Exitsub:
Application.EnableEvents = True

I can tell that the List of values selected will be stored in the variable Target.Value - but how do I:

1) Check the length of Target.Value (so I know if I have 1 selected or multi?)

2) Iterate each selection?

Do you mean you want the length of the string as in a numeric value? Len(target.value) — Andreas
– Andreas, Commented Nov 21, 2016 at 12:58
@Andreas - Let's say the List Values are Red, Green, Blue - I need a way of knowing what was selected something like foeach (string pick in Target.Value) msgBox.Show (pick) next — SmallFries BigGuys
– SmallFries BigGuys, Commented Nov 21, 2016 at 12:59

arcadeprecinct · Accepted Answer · 2016-11-21 13:49:13Z

2

Without assigning an array you can use

Target.Rows.Count    'number of rows
Target.Columns.Count 'number of columns
Target.Cells.Count   'number of cells

You can loop through them using indices or

Dim cl As Range
For Each cl In Target.Cells 'For Each loops are much faster then looping using indices
    'do something with cl
Next cl

Also note Thomas Inzina's comment that this way you will get all the cells even if you have a discontinuous Range.

Edit: The For Each loop is faster that looping through the cells using indices, i.e.

For i = 1 To Target.Rows.Count
    For j = 1 To Target.Columns.Count
        'do something with Target.Cells(i, j)
    Next j
Next i

Using an array as luke_t suggested might be even faster.

edited Nov 21, 2016 at 13:49

answered Nov 21, 2016 at 13:05

arcadeprecinct

3,7771 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user6432984 Over a year ago

For each loops are much faster than using indices if you are comparing apples to apple. luke_t is using an array of values, where as you are iterating over cells. My guess is that the array would be faster. However, you have the correct answer. 1) If the Target is a non-contiguous range then only the first area of the range will be copied to the array. 2) It is more intuitive to work with the cells directly as opposed to having to edit an array and copy the values back. +1

arcadeprecinct Over a year ago

@ThomasInzina Yes I should have specified that it's faster than looping through the cells using indices.

luke_t · Accepted Answer · 2016-11-21 14:18:27Z

1

You need to assign Target.Value to a Variant variable. Remember to include parentheses after the variable name, to denote that you're assigning an Array.

You can then find the dimensions of the array using LBound and UBound, you can also iterate through the array. Pretty sure this is what you're trying to do.

Sub get_vals()

    Dim arr() As Variant
    Dim i As Long

    arr = Range("A1:A5").Value

    Debug.Print UBound(arr, 1) ' Print rows
    Debug.Print UBound(arr, 2) ' Print columns

    For i = LBound(arr, 1) To UBound(arr, 1) ' Iterate through the rows of the array

        Debug.Print arr(i, 1)

    Next i

End Sub

Edit

As raised, you would not be able to assign a one cell range to a Array Variant. You may wish to just use Dim arr As Variant. This will allow you to assign a single cell range to the variable. You can then check the type to determine if you need to iterate an array or just work with a single string/integer.

If TypeName(arr) = "Variant()" Then
    ' Iterate
Else
    ' Work with single string/integer
End If

edited Nov 21, 2016 at 14:18

answered Nov 21, 2016 at 13:03

luke_t

2,9874 gold badges26 silver badges39 bronze badges

2 Comments

arcadeprecinct Over a year ago

There is a caveat with this solution: If the target range is only one cell you will get a type mismatch because .Value will return a variant and not a variant array. Depending on the application it might be necessary to check if the range contains multiple cells first.

luke_t Over a year ago

Very true, @arcadeprecinct. A check would need to be made prior to assigning the variable.

Collectives™ on Stack Overflow

Check For Array In VBA

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related