31

What is the difference between the VBA code wb.Sheets(1).Cells.Select and wb.Sheets(1).Activate?

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
Bruno
  • 6,211
  • 16
  • 69
  • 104

6 Answers6

50

Select allows selecting several objects at once. Objects that are selected are placed in the Selection object, which permits iteration. Selecting an object (say, a column) activates the object.

Activating an object makes it the active object.

Best way to think of it is "many cells can be selected, but only one may be the active cell at any given time."

Note: They have one thing in common: they are rarely needed and can slow down your code. You can usually work on an object without selecting or activating it.

Best practice is not to use either unless necessary.

DSlomer64
  • 4,234
  • 4
  • 53
  • 88
Gaijinhunter
  • 14,587
  • 4
  • 51
  • 57
  • 2
    +1 for "many cells can be selected, but only one may be the active cell at any given time." – Jean-François Corbett Aug 25 '11 at 06:06
  • This is a good answer. A quirk of `Activate` is that it usually selects the range but not in every case. If `Activate` is used on a range that includes the first cell in `Selection` then it won't actually change the selection. For example, select the range A1:B1, then try `ActiveSheet.Range("A1:B2).Activate` and notice that nothing changes in the selection. It you selected the range by clicking cell B1 and dragging to A1 then B1 was the active cell in that selection. If you look carefully, you will notice that the selection changed to cell A1 when you ran the above line of code. – ChrisB Jul 17 '18 at 16:50
  • One example when Sheet.activate is required: When trying to copy a graph. Found that out the hard way... – Dumitru Daniel Oct 22 '18 at 06:56
8

Here is an explanation from MSDN

You first example wb.Sheets(1).Cells.Select allows you to select multiple cells

The second wb.Sheets(1).Activate makes the sheet active.

There are lots of resources out there to help with Excel VBA.

http://www.excel-vba.com/index.htm#Tutorial%20on%20Excel%20Macros

http://www.excel-vba-easy.com/

http://www.functionx.com/vbaexcel/

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • wb.Sheets(1).Activate makes the *sheet* active: the active cell and selection on that sheet will not change, and there may not even be an active cell on that sheet. – Tim Williams Aug 24 '11 at 18:21
6

The first selects all cells on the first sheet of the workbook wb. It will fail if the sheet is not active.

The second just activates the first sheet of the workbook wb. It does not alter the selection or activecell on that sheet, and in some cases there may be no selected range or activecell (eg. if there's an object on the sheet which is currently selected).

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
1

I found this question while searching, I had the same question. Here is something I noticed:

 Sub Transfer(x As Long)
   Dim Rng, ID as Range
   Dim i, j, n As Long

 Worksheets(5).Activate
 n = Worksheets(5).Range(Range("I88"), Range("I88").End(xlToRight)).Count

Worksheets(x).Select
 Set Rng = Worksheets(3).UsedRange.Find("Element", LookIn:=xlValues).Offset(1, 1)
 Set ElemID = Range(ElemRng.Offset(0, -1), ElemRng.Offset(0, -1).End(xlDown))
 Set ElemRng = Worksheets(3).Range(ElemRng, ElemRng.End(xlToRight))
End Sub

I found that I HAD to put the worksheet.activate (or select) in or the code would run into:

Run-time error: '1004' Application-defined or object-defined error

Reverus
  • 133
  • 9
0

Select - "Selects" Cell(s)

Activate - "Activates" a sheet (kind of like saying to focus on a sheet)

Sometimes u need to specifically ACTIVATE the sheet, in order to make a SELECT

waqasahmed
  • 3,555
  • 6
  • 32
  • 52
-1

Activate is often used for Sheets for Example. The Active sheet wil be shown on the screen... therfore there can only be one active sheet

Select though is can be used for multiple Cells for Example. Range(A1:B3).Select will select multiple cell which is'nt possible with activate

Cornelis
  • 445
  • 3
  • 11
  • 27
  • 2nd paragraph is wrong. Select and Activate accomplish the same thing on single objects. For example, Cells(14, 6) = "HELLO" \ Range("F14:H18").Activate \ MsgBox Selection(1) \ gives "HELLO" as does using Select on the Range. – DSlomer64 Jul 31 '23 at 22:12