Excel VBA: can't define range, get error object with block variable not set

Question

I'm trying to define a Range using VBA in Excel,

Sub convertePerc()
Dim separador As String
Dim linhaInicial, linhaFinal, colunaInicial, colunaFinal, numAnos As Integer
Dim origem, destino As Range

    separador = "DRES(G)"

    colunaFinal = Sheets(separador).Cells(6, 5).End(xlToRight).Column
    linhaFinal = 40
    numAnos = 10
    origem = Sheets(separador).Range(Cells(10, 4), Cells(linhaFinal, colunaFinal))

    colunaInicial = CInt(4 + numAnos + 1)
    colunaFinal = CInt(numAnos + colunaFinal + 1)
    destino = Sheets(separador).Range(Cells(10, 4), Cells(11, 5))


End Sub

The first range origem is correctly defined without errors, but the second destino is throwing the error:

Object with block variable not set

On line:

destino = Sheets(separador).Range(Cells(10, 4), Cells(11, 5))

Can someone please explain me why, and how to fix this?

Thanks in advance!

Add a `set` infront? Also `origem` is declared as variable type variant fyi. — findwindow, Oct 13 '15 at 16:57
`Dim origem, destino As Range` only defines `destino` as a Range type object. **be explicit** with your declarations. Otherwise, you need to use the `Set` keyword when assigning to object variables. — David Zemens, Oct 13 '15 at 16:59
and FYI, origem isn't a range but a variant. Same with linhaInicial, linhaFinal, colunaInicial, and colunaFinal. It may resolve to an Integer, but at declaration, they aren't Integers and origem isn't a range. — sous2817, Oct 13 '15 at 16:59
Perhaps counter-intuitive, but `Dim linhaInicial, linhaFinal, colunaInicial, colunaFinal, numAnos As Integer` does **not** declare all those variables as `Integer`. Only `numAnos` is declared as an `integer`. You have to put the `as Integer` after each variable name. Without it, they'll be `variant` types. — BruceWayne, Oct 13 '15 at 17:00
Also, since you're using multiple worksheets, when using a `Range(cells())` reference, you need to be explicit and make sure you tell each part of that (both the `Range()` and `Cells()`) what worksheet you're working with. I.e. `destino = Sheets(separador).Range(Sheets(separador).Cells(10, 4), Sheets(separador).Cells(11, 5))` — BruceWayne, Oct 13 '15 at 17:01
@BruceWayne or OP can use `Sheets(separador).Range(Cells(10, 4).Address, Cells(11, 5).Address)` :) — David Zemens, Oct 13 '15 at 17:02
And FYI, the first range `origem` is **not** correctly assigned a range object. It's actually a variant, which represents the `.Value` property of the range object. In this case it is an array, which may *work*, but make no mistake that it is not actually a range object and if you treat it as such, it will cause more errors. — David Zemens, Oct 13 '15 at 17:05
@DavidZemens - wouldn't that though still leave some ambiguity as to which `Cells()` to use? Say he has `Sheet(superman)` as the "Active Sheet", and then uses that line. Won't the `cells(10,4).address` reference the active sheet's (`Sheets(superman)`) `Cells()` instead of `Sheets(separador)`? — BruceWayne, Oct 13 '15 at 17:07
@BruceWayne It doesn't matter which sheet if you use the the `.Address` property (string). You can try this and confirm: `set rng = Range(Sheet1.Cells(1,1).Address, Sheet2.Cells(1,3).Address)` will not throw an error, because the `.Address` property resolves to a valid string irrespective of the sheet. You can then use the address string to define the range on any sheet. — David Zemens, Oct 13 '15 at 17:09

score 1 · Accepted Answer · answered Oct 13 '15 at 17:03

1

Ranges are object variables, and object variable require Set. Also, unqualified references to Range or Cells are to the active worksheet. So

With Sheets(separador)
  Set destino = Range(.Cells(10, 4), .Cells(11, 5))
End With

answered Oct 13 '15 at 17:03

shg

323
2
7

Ah! Don't forget the `.` in front of `Range`...`Set destino = .Range(.Cells(10,4),.Cells(11,5))`. – BruceWayne Oct 13 '15 at 20:22
1

It's not necessary unless the first argument is a literal string, e.g., "D10" – shg Oct 13 '15 at 20:25
Hmm - so is that because (and sorry for the lack of vocab), `Range()` looks to what's inside the `( )` to determine where the `Range` is (what worksheet)? So, since we specified we're using the `Cells()` in `Sheets(separador)`, the `Range` part understands? Whereas using `Range("A10")` doesn't specify a sheet anywhere, it'll default to Active Sheet? – BruceWayne Oct 13 '15 at 20:57
1

Range(cell1, cell2) defines a range by its corners, and both corners must be on the same worksheet (any sheet). The dot operator before Range confirms that a literal cell address argument refers to the worksheet referenced by the operator. And I should have said either argument, not just the first. – shg Oct 13 '15 at 21:56

Excel VBA: can't define range, get error object with block variable not set

1 Answers1