vba error: object variable or with block variable not set

Question

I'm getting a "object variable or with block variable not set" error message when running the following code. It is in Access and refers to an Excel spreadsheet. What is wrong with the code?

wsTest.Range("A11").Activate    

Do Until IsEmpty(ActiveCell)
    intX = intX + wsTest.Cells(ActiveCell.Row, "L") 'error occurs on this line
    intY = intY + wsTest.Cells(ActiveCell.Row, "I")
    ActiveCell.Offset(1, 0).Select ' Step down 1 row to the next cell.
Loop

The first time the code is run, there is no error, only the second time. Closing and reopening Access "fixes" the problem. How can that be related to this code?

ActiveCell refers to an Excel object, so you likely should qualify it with your XL application reference. Eg: xlApp.Activecell — Tim Williams
– Tim Williams, Commented Apr 13, 2011 at 0:21

Lance Roberts · Accepted Answer · 2011-04-13 00:31:05Z

2

You need to define the sheet, like so:

Dim wsTest As Worksheet

Set wsTest = Sheets("Sheet1")

answered Apr 13, 2011 at 0:31

Lance Roberts

22.9k32 gold badges115 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Doug Glancy Over a year ago

Wouldn't it error on the line "wsTest.Range("A11").Activate" if that was the problem?

Lance Roberts Over a year ago

@Doug, that is what it was erroring on for me when I implemented his code, so I added those lines and solved the problem.

Tiago Cardoso · Accepted Answer · 2011-04-13 01:58:06Z

1

As the error occurs in the highlighted line, I believe you're trying to do an invalid sum. Maybe the value returned by wsTest.Cells(oCell.Row, "L") isn't a integer, or you should use wsTest.Cells(oCell.Row, "L").Value instead.

Will be easier to identify the root cause of the problem adding a msgbox to check the cell value, as I added below.

Either way, I'd suggest you to avoid using references to ActiveCell, as they aren't reliable. Maybe this code helps avoiding this error (simply replacing the ActiveCell by a proper cell object).

Dim oCell as excel.range

set oCell = wsTest.Range("A11")

Do Until Len(oCell.Value) = 0
    msgbox(wsTest.Cells(oCell.Row, "L"))
    intX = intX + wsTest.Cells(oCell.Row, "L") 'error occurs on this line
    intY = intY + wsTest.Cells(oCell.Row, "I")
    Set oCell = oCell.Offset(1, 0) ' Step down 1 row to the next cell.
Loop

answered Apr 13, 2011 at 1:58

Tiago Cardoso

2,1061 gold badge16 silver badges34 bronze badges

1 Comment

LFurness Over a year ago

It was an integer - ActiveCell was the problem. The messed up thing is that Microsoft uses ActiveCell in their documentation. Thanks very much for straightening that out!

mwolfe02 · Accepted Answer · 2011-04-12 20:00:28Z

0

Try changing

wsTest.Range("A11").Activate

to

wsTest.Range("A11").Select

answered Apr 12, 2011 at 20:00

mwolfe02

24.3k10 gold badges102 silver badges174 bronze badges

2 Comments

mwolfe02 Over a year ago

Try adding Debug.Print ActiveCell.Address before your intX = line. What's the result?

LFurness Over a year ago

The first time the code is run, when the error does not occur, the result is $A$11, which is correct because the file I tested with only has one row of data. However, the second time the code is run, the error occurs on the do until isempty(activecell) line and debug.print never runs. So this is interesting, the error occurs the second time the code is run, on whatever line first tries to access the active cell. Hmm.

Collectives™ on Stack Overflow

vba error: object variable or with block variable not set

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related