Multi cell.value input with array and resize

Question

I'm currently using this structure in a code:

With Worksheets("Vendredi jour")
.Unprotect
.Cells(15, 43).Resize(1, 6).Value = Array(B, C, D, E, F, G) ' Mets les minutes dans le tableau de compilations
.Protect
End With

Since it been given to me like a gift of god, I don't understand how it works, which is problematic since I want to apply it to this :

  With Worksheets("Vendredi jour")
    .Unprotect
    
    .Cells(9, 41).Value = MyArray(0, 0) ' (x,0) = Employé
    .Cells(10, 41).Value = MyArray(1, 0)
    .Cells(11, 41).Value = MyArray(2, 0)
    .Cells(12, 41).Value = MyArray(3, 0)
    .Cells(13, 41).Value = MyArray(4, 0)

    .Cells(9, 44).Value = MyArray(0, 1) ' (x,1) = Passes
    .Cells(10, 44).Value = MyArray(1, 1)
    .Cells(11, 44).Value = MyArray(2, 1)
    .Cells(12, 44).Value = MyArray(3, 1)
    .Cells(13, 44).Value = MyArray(4, 1)

    .Cells(9, 46).Value = MyArray(0, 2) ' (x,2) = Pertes
    .Cells(10, 46).Value = MyArray(1, 2)
    .Cells(11, 46).Value = MyArray(2, 2)
    .Cells(12, 46).Value = MyArray(3, 2)
    .Cells(13, 46).Value = MyArray(4, 2)
    
    .Protect
    End With

Is there any documentation that explains it?

Tim Williams · Accepted Answer · 2020-08-14 18:34:04Z

1

With Worksheets("Vendredi jour")
    .Unprotect
    .Cells(15, 43).Resize(1, 6).Value = Array(B, C, D, E, F, G) 
    .Protect
End With

is functionally the same as

Worksheets("Vendredi jour").Unprotect
Worksheets("Vendredi jour").Cells(15, 43).Resize(1, 6).Value = Array(B, C, D, E, F, G) 
Worksheets("Vendredi jour").Protect

The version using With is easier to read and has some slight performance benefits.

See VBA - What is the purpose of the `With` statement if you want more details

answered Aug 14, 2020 at 18:34

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

6 Comments

Patates Pilées Over a year ago

any hints on how to resize with my 2D array? Something like resize(5,1)= Myarray(all,0) or something? i know in matlab i could do (:,1) which mean all row in 1st column, is there a way to do as such? like, the question is more on how to input array, with statement is all understood thanks to that exact answer

Tim Williams Over a year ago

There are no array "slicing" functions in Excel VBA

Patates Pilées Over a year ago

So the way i wrote my second code is the most optimal one?

Tim Williams Over a year ago

It's OK but maybe not the most concise. You could write your own function to return a "column" from your main array and assign that in one shot to the range.

Tim Williams Over a year ago

If you delete your question then I've wasted my time...

|

Collectives™ on Stack Overflow

Multi cell.value input with array and resize

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related