Clever Geek Handbook

Structures with cells

Rephrased question:

Why does the sheet have to be activated to build range

with cells

?

I have a quick and (hopefully) simple question.

FROM

Dim XRange As Range

Why:

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

and

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

not

It throws 1004 Runtime-Error. Since my Excel is German it says:

Laufzeitfehler '1004':

Anwendungs-oder objektdefinierter Fehler

As there are some hints regarding active sheets:

I am refactoring the code. I am using xRange as data for a chart. I used to set a sheet active, grab the data and bring back another sheet. But this is unpleasant. I wanted to streamline the code 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

In 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 cells, these are different diagrams while I'm in the middle of refactoring]

+3


source to share


2 answers


The reason you are getting the error is because you are using cells from the active table and a range from a given sheet. A reference to your range is considered "qualified" because you specify its parent. The references to your cells are "unqualified", which means that you are allowing the interpreter to provide the parents. You have coded:

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

So, when Pivot is not active, you create Range on Pivot using cells from another sheet.

When you use an unqualified reference, the interpreter picks the parent based on your code. If you are in a standard pod, the unqualified cells and range are relative to the ActiveSheet. If you are in a sheet class module, then any unqualified range reference will be the default for that sheet, regardless of whether it is active. In fact, any link who is the parent is an ActiveSheet or specific sheet, not just ranges.

When you qualify for Cells with Sheets ("Pivot"), you create an unqualified sheet reference. Unqualified references to default worksheets for the ActiveWorkbook if the code is not in the ThisWorkbook module.

The best practice is to qualify your links as thoroughly as possible. I certainly don't go back to the Application object for every link, but I never leave the Workbook, much less the Worksheet, random.

One fix is ​​to use the WITH clause



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

Each sheet has a CodeName property. By referencing a sheet by its codename, you eliminate the risk of someone changing the tab name. You set the CodeName in the Properties field (F4). I prefix my CodeNames sheet 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 did it (but mine doesn't mean the universal "correct" way) is offset and resize

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

Start at the desired cell (or good anchor) and offset to where you want the range to start, then resize to make it correct.

+1


source


Failed to replicate error 1004.

This happens when the sheet is not active. Therefore, when using

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

the "Pivot" sheet must be active. Only when mixing range and cells for any reason

So you can use

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


EDIT, Fixed inactive line issue

I continued this to understand why it doesn't work for inactive sheets.

You need to set the cell sheet ever to use it while it is inactive.

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

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

+2


source






More articles:


All Articles