in VBA Why does the Selection Object act differently when indexed?

Question

hopefully you can help me with this!

I am writing a code in VBA and I am having issues with the Selection object acting differently than I expect.

When I write a for loop as in

For Each Cell in Selection
    MsgBox Cell.Value
Next Cell

It works as expected, but then I try to index it and it acts differently. Especially it is a non-contiguous cell selection.

Like this;

For i = 0 to 5
    MsgBox Selection(i).Value
Next i

It gives pretty random values. any insight would be great!

Edit:

Thanks for the input everyone, it seems I need to find another way of doing the following. I have a piece of code that takes a user's selected cells and uses those values for calculations. Right now, I have been trying to make it so they can select non-contiguous cells. Basically, I need to make an array of these values, and my thoughts were to make a for loop as follows

For I = 0 To 5
    Array(i) = Selection(i).Value
Next I

I'm not sure if there is another way of doing this. If anyone has some suggestions, I am interested!

what you do is not recommended at all, since this is not how you use `selection'. The returned object type depends on the current selection (for example, if a cell is selected, this property returns a Range object) — Ibo, Sep 17 '18 at 21:16
What exactly are you trying to accomplish and why is the `Selection` object important to the solution (to the user, actually)? As mentioned, the user can select many different things and expect "actions" to happen. You'll either have to choose a different way for the user to interact with your code, or your code will have to validate the selected objects to make sure you're working with the object types you expect. — PeterT, Sep 17 '18 at 21:21
Thank everyone, I have a piece of code that takes a user's selected cells and uses those values for calculations. Right now, I have been trying to make it so they can select non-contiguous cells. Basically, I need to make an array of these values, and my thoughts were to make a for loop with 'For I = 0 To 5 Array(i) = Selection(i).Value Next i' I'm not sure if there is another way of doing this. — SRT HellKitty, Sep 18 '18 at 12:07
Please look at the edit in the original post for better readability — SRT HellKitty, Sep 18 '18 at 12:15

score 1 · Accepted Answer · answered Sep 17 '18 at 21:44

As others have stated, you should not use Selection this way. But to answer your question...I did some quick testing based on your example use-case.

It appears that when you loop cell in Selection it goes left to right, then top to bottom by range area. So for example if you have two non-contiguous ranges selected then if will look something like this:

When you index it, it does not seem to make the jump between range areas. instead it will continue iterating left to right top to bottom within the column bounds of the first range area:

So if you slected 2 columns, iterating on the index would just continue down the first column, even if it is outside of the selection.

My test code:

Private Sub testing()
    Dim i As Integer
    For i = 1 To Selection.Cells.Count
        Selection(i).Value = i
    Next i
End Sub

Private Sub testing2()
    Dim c As Range
    For Each c In Selection.Cells
        c.Value = c.Column
    Next c
End Sub

Thank you for the explanation!! It makes sense now what my macro is doing. I have edited the original question with my use for this, if you have any suggestions I would really appreciate it. — SRT HellKitty, Sep 18 '18 at 12:17

in VBA Why does the Selection Object act differently when indexed?

1 Answers1