Excel VBA. When is it necesary to activate a Sheet?

Question

A general answer would be perfect, b I have this simple code, that only works if Sheet2 is active when running the macro.

Otherwise it gives an error "defined by the application or object":

Sub PasteArray()
Dim arr(1 To 3) As Variant
Dim n As Integer
Dim ws As Worksheet

arr(1) = 4
arr(2) = 6
arr(3) = 8
n = UBound(arr) - LBound(arr) + 1
Sheets("Sheet2").Range(Cells(1, 1), Cells(n, 1)) = WorksheetFunction.Transpose(arr)
End Sub

It works if I activate the sheet first, but that requires that I save the current active sheet to come back at the end.

Sub PasteArray()
Dim arr(1 To 3) As Variant
Dim n As Integer
Dim ws As Worksheet

arr(1) = 4
arr(2) = 6
arr(3) = 8
n = UBound(arr) - LBound(arr) + 1
Set ws = ActiveSheet
Worksheets("sheet2").Activate
Range(Cells(1, 1), Cells(n, 1)) = WorksheetFunction.Transpose(arr)
ws.Activate

Is it always necesary to do something like this? I am pretty sure that I have seen some code in which changes to cells in an unactive sheet were made.

Thank you

For all intents and purposes, it is **never** important to activate a worksheet unless you want to show something to the person in the chair. — , Oct 19 '18 at 13:53
See [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Also see [What is the default scope of worksheets and cells and range?](https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984) for why it might *seem* necessary from your code example. — Comintern, Oct 19 '18 at 13:57
... and [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) — , Oct 19 '18 at 14:11

score 2 · Answer 1 · answered Oct 19 '18 at 13:55

2

You don't need to activate a sheet really ever unless you want the user to see something.

You receive an error because of your references, always remember to include Sheet or Worksheet with Ranges:

Sub PasteArray()
    Dim arr(1 To 3) As Variant
    Dim n As Integer
    Dim ws As Worksheet

    arr(1) = 4
    arr(2) = 6
    arr(3) = 8
    n = UBound(arr) - LBound(arr) + 1
    Sheets("Sheet2").Range(Sheets("Sheet2").Cells(1, 1), Sheets("Sheet2").Cells(n, 1)) = WorksheetFunction.Transpose(arr)
End Sub

Added Sheets("Sheets2") before the Cells parts

Honestly would suggest googling how to use With statement in VBA, it would help lots in situations like this

answered Oct 19 '18 at 13:55

Kubie

1,551
3
12
23

`Sheets` is an implicit reference to `ActiveWorkbook`, and repeatedly hammering the `Sheets` collection to find `"Sheet2"` is extremely poor from a performance standpoint - It should either be stored or referenced from a `With` block. This code is only marginally better than the OP's. – Comintern Oct 19 '18 at 13:59
@Comintern great points, but this is why I mentioned the googling of `With` above. Also just wanted to demonstrate why there was an error and this way makes it easy to visualize for somebody who appears to obviously not have much experience with VBA – Kubie Oct 19 '18 at 14:02

Excel VBA. When is it necesary to activate a Sheet?

1 Answers1