CODEWITHSUNDEEP
excelvba
Oliver
Oliver

Reputation: 604

Building Ranges with Cells

Rephrased question:

Why does a sheet has to be activated to build a range with cells?

I have a quick and (hopefully) simple question.

With

Dim XRange As Range

Why does:

Set XRange = ActiveWorkbook.Sheets("Pivot").Range("K20:L26")

work and:

Set XRange = ActiveWorkbook.Sheets("Pivot").Range(Cells(21, 11), Cells(22, 26))

doesn't?

It throws an 1004 Runtime-Error. Since my Excel is german, it says:

Laufzeitfehler '1004':

Anwendungs- oder objektdefinierter Fehler

Since there is some hint concerning active sheets:

I am refactoring some code. I use xRange as data for a chart. I used to set the sheet active, grab the data and set back the other sheet active. But that is anoying. I wanted to streamline the code, from something like:

With myChart
    .SeriesCollection.NewSeries
    ActiveWorkbook.Sheets("Pivot").Activate
    .SeriesCollection(1).Values = Range("H6:H18")
    .SeriesCollection(1).XValues = Range("K6:L18")
    ActiveWorkbook.Sheets("Ausgabe").Activate
    .SeriesCollection(1).Name = "28 days"
End With

Into something like

With myChart
    For i = 0 To 6
        .SeriesCollection.NewSeries
        .SeriesCollection(i + 1).Values = ActiveWorkbook.Sheets("History").Range(Cells(i + 52, StartColumn).Address, Cells(i + 52, EndColumn).Address)
        .SeriesCollection(i + 1).XValues = XRange
        .SeriesCollection(i + 1).Name = ActiveWorkbook.Sheets("History").Range(Cells(i + 52, 1).Address)
    Next i
End With

[Please ignore the actual referred cells, those are different charts while I am mid in refactoring]

Upvotes: 3

Views: 149

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33145

The reason you get the error is because you're using Cells from the activesheet and Range from a specified sheet. Your Range reference is considered 'qualified' because you specify its parent. Your Cells references are 'unqualified' meaning you allow the interpreter to supply the parent. You coded:

Set XRange = ActiveWorkbook.Sheets("Pivot").Range(ActiveSheet.Cells(21, 11), ActiveSheet.Cells(22, 26))

So when Pivot isn't active, you're building a Range on Pivot using cells from a different sheet.

When you use an unqualified reference, the interpreter picks the parent based on where your code is. If you're in a Standard module, unqualified Cells and Range refer to the ActiveSheet. If you're in a sheet's class module, then any unqualified range reference will default to that sheet, regardless if it's active. In fact, any reference who's parent is a Worksheet will default to the ActiveSheet or the specific sheet, not just ranges.

When you qualify Cells with Sheets("Pivot"), you are creating an unqualified sheet reference. Unqualified sheet references default to ActiveWorkbook unless the code is in a ThisWorkbook module.

Best practice is to qualify your references as fully as necessary. I certainly don't go back to the Application object for every reference, but I don't ever leave the Workbook, much less Worksheet, to chance.

One fix is to use a With clause

With ActiveWorkbook.Sheets("Pivot")
    Set XRange = .Range(.Cells(), .Cells())
End With

Every worksheet has a CodeName property. If you refer to the sheet by its CodeName, you eliminate the risk that someone will change the name of the tab. You set the CodeName in the Properties box (F4). I prefix my sheet CodeNames with 'wsh'.

With wshPivot
    Set XRange = .Range(.Cells(), .Cells()).Value
End With

I rarely use Range and Cells together. Another option for you, and probably the way I would do it (but my no means universally the 'correct' way) is Offset and Resize

Set XRange = wshPivot.Cells(21, 11).Resize(2,15)

Start with the cell you want (or that is a good anchor) and Offset to where you want the range to start, then Resize to make it the right size.

Upvotes: 1

Sam
Sam

Reputation: 948

Managed to replicate the 1004 error.

It occures when the sheet is not active. So when using

ActiveWorkbook.Sheets("Pivot")range(cells(....))

the sheet "Pivot" has to be active. Only happens when mixing range and cells for some reason

So you could use 

Activeworkbook.sheets("Pivot").activate
Set XRange = Range(Cells(21, 11), Cells(22, 26))

EDIT, Fixed the sheet not active problem

I've kept at this to see why it doesnt work for non active sheets.

You need to set the cells sheet ever time to use it while not active.

so Set XRange = ActiveWorkbook.Sheets("Pivot").Range(Cells(21, 11), Cells(22, 26))

would become Set XRange = ActiveWorkbook.Sheets("Pivot").Range(sheets("Pivot").Cells(21, 11), sheets("Pivot").Cells(22, 26))

Upvotes: 2

Related Questions