Excel VBA: cannot set range

Question

I'm having some trouble troubleshooting a simple border function. The funny thing is, I thought this worked yesterday. But when I opened the workbook today (saved from yesterday), something isn't working.

I can compile the project containing the code below. However, when running, I get the following error: "Run-time error '91': Object variable or With block variable not set"

Helpful, no? Well, when I hit "debug" the line where I assign my_range is highlighted. The locals window shows no value is assigned to my_range. Why would this range have trouble taking an assignment like this?

I'm using Excel 2007 on a Win7 box. Here's the full procedure code:

Public Sub borders_test()

'activate the sheet
Worksheets("Sheet1").Activate

'create a range, and assign to it
Dim my_range As Range
my_range = ActiveSheet.Cells(2, 3)

'set a red border around the range
With my_range.Borders
  .Color = RGB(255, 0, 0)
  .Weight = xlMedium
End With

'now delete the border
'With my_range.Borders
'  .LineStyle = xlNone
'End With

End Sub

Taking the lines out from underneath the my_range assignment makes no difference.

use `Set my_range = ActiveSheet.Cells(2, 3)` instead. When you assign _object_ to variable, you should always use `Set` keyword — Dmitry Pavliv, Mar 27 '14 at 18:28
that's got it, if you post as an answer i can mark as answered. thank you. — Aaron Thomas, Mar 27 '14 at 18:29

score 0 · Accepted Answer · edited May 23 '17 at 12:28

0

Use this one instead:

Dim my_range As Range
Set my_range = ActiveSheet.Cells(2, 3)

When you assign object to variable, you should always use Set keyword.

Btw, this post may help you in future: how to avoid using Select/Active statements:)

edited May 23 '17 at 12:28

Community

1
1

answered Mar 27 '14 at 18:30

Dmitry Pavliv

35,333
13
79
80

Excel VBA: cannot set range

1 Answers1