VB/Excel repetitive use of Range object
Posted on 1998-12-24
Given an Excel worksheet via CreateObject, two Range variables and the following VB5 code fragment within a loop in which x defines a row number (these rows contain sub-totals which are to be charted):
If I = 1 Then
Set RngAll = Range(.Cells(x, 2), .Cells(x, 14))
Set RngPart = Range(.Cells(x, 2), .Cells(x, 14))
Set RngAll = Union(RngPart, RngAll)
(Later RngAll is used as the data source for the chart).
The first time this runs, everything is fine. The second time, even given exactly the same conditions (apparently) it falls over on the first Set RngAll and gives the error "Method 'Range' of object '_Global' failed".
By the way, RngAll and RngPart are set to Nothing after each run and the Excel object is newly created for each run and properly closed afterwards. The application does a lot of other Excel work so References are set.
Can someone offer an explanation and a solution?