How do I pass worksheet and ranges as variables?

Question

I want to pass the names of sheets and ranges between subroutines. The following throws a "Subscript out of range" error:

Sub This()

    x = "Sheet1"
    y = "D3"

    MsgBox (x.Range(y).Value)

End Sub

This is a sample of my Project Explorer.

Answer 1

Your worksheet's .Name property is Valuation; its Worksheet .CodeName property is Sheet1.

dim x as string, y as string
dim xx as worksheet, yy as range

x = "Valuation"
set xx = worksheets(x)
y = "D3"
set yy = xx.range(y)
debug.print yy.address(0,0, external:=true)  '<~~returns Valuation!D3 (with the workbook name)

debug.print Sheet1.name    '<~~returns Valuation from CodeName