CODEWITHSUNDEEP
excelvba
Chris
Chris

Reputation: 238

Cannot set range variable in VBA

I'm brand new to VBA and trying to learn variables. I have a simple piece of code that should set a variable to a range:

Sub Test()
    Dim DataRange As Range
    DataRange = Workbooks("Rooms.csv").Sheets(1).Range("A3", "AK17068")
End Sub

When I run I get Run-time error '91': Object variable or With block variable not set which.... isn't that what I did with the Dim statement? What is this referring to?

I saw in the MS docs that in certain cases (I don't fully understand when) I should use Option Strict On but when I try to use this I get a syntax error (Expected: Base or Compare or Explicit or Private). I think I am fundamentally misunderstanding how this should work (am C++ dev by training)

Upvotes: 1

Views: 1177

Answers (3)

Error 1004
Error 1004

Reputation: 8220

Using With Statement referring to the workbook & worksheet we are getting the advantage of not repeating workbook & worksheet names if you are planning to set more ranges. See below:

Option Explicit

Sub Test()

    Dim DataRange As Range, DataRange2 As Range

    With Workbooks("Rooms.csv").Worksheets("Sheet1") '<- Using *With Statement* referring to the workbook & worksheet
        Set DataRange = .Range("A3", "AK17068")
        Set DataRange2 = .Range("A1") '<- For example purpoces
    End With

End Sub

Upvotes: 1

Badja
Badja

Reputation: 875

You need to set the data range

Set DataRange = Workbooks("Rooms.csv").Sheets(1).Range("A3", "AK17068")

Upvotes: 4

Damian
Damian

Reputation: 5174

Here:

Sub Test()
    Dim DataRange As Range
    Set DataRange = Workbooks("Rooms.csv").Sheets(1).Range("A3", "AK17068")
End Sub

ObjectVariables are Set with Set

Upvotes: 8

Related Questions