Excel – VBA reference to a table in different worksheet

Some examples of how to avoid select

Use Dim'd variables

Dim rng as Range

Set the variable to the required range. There are many ways to refer to a single-cell range:

Set rng = Range("A1")
Set rng = Cells(1, 1)
Set rng = Range("NamedRange")

Or a multi-cell range:

Set rng = Range("A1:B10")
Set rng = Range("A1", "B10")
Set rng = Range(Cells(1, 1), Cells(10, 2))
Set rng = Range("AnotherNamedRange")
Set rng = Range("A1").Resize(10, 2)

You can use the shortcut to the Evaluate method, but this is less efficient and should generally be avoided in production code.

Set rng = [A1]
Set rng = [A1:B10]

All the above examples refer to cells on the active sheet. Unless you specifically want to work only with the active sheet, it is better to Dim a Worksheet variable too:

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1, 1)
With ws
    Set rng = .Range(.Cells(1, 1), .Cells(2, 10))
End With

If you do want to work with the ActiveSheet, for clarity it's best to be explicit. But take care, as some Worksheet methods change the active sheet.

Set rng = ActiveSheet.Range("A1")

Again, this refers to the active workbook. Unless you specifically want to work only with the ActiveWorkbook or ThisWorkbook, it is better to Dim a Workbook variable too.

Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")

If you do want to work with the ActiveWorkbook, for clarity it's best to be explicit. But take care, as many WorkBook methods change the active book.

Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

You can also use the ThisWorkbook object to refer to the book containing the running code.

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")

A common (bad) piece of code is to open a book, get some data then close again

This is bad:

Sub foo()
    Dim v as Variant
    Workbooks("Book1.xlsx").Sheets(1).Range("A1").Clear
    Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = ActiveWorkbook.Sheets(1).Range("A1").Value
    Workbooks("SomeAlreadyOpenBook.xlsx").Activate
    ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v
    Workbooks(2).Activate
    ActiveWorkbook.Close()
End Sub

And it would be better like:

Sub foo()
    Dim v as Variant
    Dim wb1 as Workbook
    Dim  wb2 as Workbook
    Set wb1 = Workbooks("SomeAlreadyOpenBook.xlsx")
    Set wb2 = Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = wb2.Sheets("SomeSheet").Range("A1").Value
    wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
    wb2.Close()
End Sub

Pass ranges to your Subs and Functions as Range variables:

Sub ClearRange(r as Range)
    r.ClearContents
    '....
End Sub

Sub MyMacro()
    Dim rng as Range
    Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10")
    ClearRange rng
End Sub

You should also apply Methods (such as Find and Copy) to variables:

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10")
Set rng2 = ThisWorkbook.Worksheets("SomeSheet").Range("B1:B10")
rng1.Copy rng2

If you are looping over a range of cells it is often better (faster) to copy the range values to a variant array first and loop over that:

Dim dat As Variant
Dim rng As Range
Dim i As Long

Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10000")
dat = rng.Value  ' dat is now array (1 to 10000, 1 to 1)
for i = LBound(dat, 1) to UBound(dat, 1)
    dat(i,1) = dat(i, 1) * 10 ' Or whatever operation you need to perform
next
rng.Value = dat ' put new values back on sheet

This is a small taster for what's possible.

You don't need to use Parcelable to pass an object from one activity to another. You can just store a reference to the object in a static member variable, like this:

public class Globals {
     public static MyObject myObject;
}

Now, in the code that has the object, you just do:

Globals.myObject = object;

and in the new activity, you can get it like this:

doSomethingWith(Globals.myObject);

Now, having said that, you need to be aware of the following:

Android can kill your process if your application is in the background pretty much any time it wants to. When the user then returns to your application, Android will create a new process for your application and then it will recreate only the activity that was on the top of the activity stack (ie: the one that was showing). In that case, the newly created activity will not be able to get the iobject by accesing Globals.myObject because the process has been newly created and that member variable is null.

To get around this you can either:

1. Determine that your process has been killed and restarted (by checking Globals.myObject == null and react accordingly - Tell the user he needs to go back, or just go back yourself, or show a dialog or whatever)
2. Save the object when Android calls onSaveInstanceState() in your activity (which Android will do before sending your app to the background) and restore the object in onCreate()

Hopefully this both answers your question and explains what to do about it.

EDIT: Add more information about why Intents contain serialized (Parcelable) objects and not the objects themselves

1. When you call startActivity() or startService() Android may end up starting the activity or service in another process. In this case, if you passed an object in the Intent, Android would somehow need to serialize that object to pass it to the other process. Because of the "implicit Intent resolution" that Android uses to determine which component gets to handle the Intent, the caller may or may not know which component will get started.

2. Android saves the contents of Intents for various reasons:

   A. Android can kill a process at any time. If it does that and the user wants to return to the application, Android creates a new process and then recreates the activities in that process as needed. To create the activities Android also needs to make the Intents available to the activities. If the process has been killed then any "objects" in the Intents would have to be saved and restored. Because the Intents contain serialized objects, it isn't a problem to recreate these as needed. B. PendingIntents are use by Android as a way for the Operating System to act as a proxy for the sender of an Intent. An Android component can create a PendingIntent and give that to the Operating System so that it can trigger the sending of that Intent at some later time. The sending component may or may not be active at the time that the PendingIntent is actually sent. This means that any object that could be passed in a PendingIntent must be able to be serialized so that Android can hold on to it even if the calling component no longer exists.

3. Intents are not intended as a general "parameter passing" mechanism between components. Of course you can use it like that, but you can also use other (easier) mechanisms. Within a given process you can pass objects around using standard Java mechanisms. There is nothing wrong with using static (class) variables for this.