It is very rare that you'll ever want to use
Activate in your code, but some Excel methods do require a worksheet or workbook to be activated before they'll work as expected.
If you're just starting to learn VBA, you'll often be suggested to record your actions using the macro recorder, then go look at the code. For example, I recorded actions taken to enter a value in cell D3 on Sheet2, and the macro code looks like this:
Option Explicit Sub Macro1() ' ' Macro1 Macro ' ' Sheets("Sheet2").Select Range("D3").Select ActiveCell.FormulaR1C1 = "3.1415" '(see **note below) Range("D4").Select End Sub
Remember though, the macro recorder creates a line of code for EACH of your (user) actions. This includes clicking on the worksheet tab to select Sheet2 (
Sheets("Sheet2").Select), clicking on cell D3 before entering the value (
Range("D3").Select), and using the Enter key (which is effectively "selecting" the cell below the currently selected cell:
There are multiple issues with using
.Select()is slow. Even if
Application.ScreenUpdatingis set to
False, this is an unneccessary operation to be processed.
.Select()is unruly. If
Application.ScreenUpdatingis left to
True, Excel will actually select the cells, the worksheet, the form... whatever it is you're working with. This is stressful to the eyes and really unpleasant to watch.
.Select()will trigger listeners. This is a bit advanced already, but unless worked around, functions like
Worksheet_SelectionChange()will be triggered.
When you're coding in VBA, all of the "typing" actions (i.e.
Select statements) are no longer necessary. Your code may be reduced to a single statement to put the value in the cell:
'--- GOOD ActiveWorkbook.Sheets("Sheet2").Range("D3").Value = 3.1415 '--- BETTER Dim myWB As Workbook Dim myWS As Worksheet Dim myCell As Range Set myWB = ThisWorkbook '*** see NOTE2 Set myWS = myWB.Sheets("Sheet2") Set myCell = myWS.Range("D3") myCell.Value = 3.1415
(The BETTER example above shows using intermediate variables to separate different parts of the cell reference. The GOOD example will always work just fine, but can be very cumbersome in much longer code modules and more difficult to debug if one of the references is mistyped.)
**NOTE: the macro recorder makes many assumptions about the type of data you're entering, in this case entering a string value as a formula to create the value. Your code doesn't have to do this and can simply assign a numerical value directly to the cell as shown above.
**NOTE2: the recommended practice is to set your local workbook variable to
ThisWorkbook instead of
ActiveWorkbook (unless you explicitly need it). The reason is your macro will generally need/use resources in whatever workbook the VBA code originates and will NOT look outside of that workbook -- again, unless you explicitly direct your code to work with another workbook. When you have multiple workbooks open in Excel, the
ActiveWorkbook is the one with the focus which may be different from the workbook being viewed in your VBA Editor. So you think you're executing in a one workbook when you're really referencing another.
ThisWorkbook refers to the workbook containing the code being executed.