using UNION via Automation = invalid range result!?

[Mark Burns]

Excel Gurus: HELP!

I am automating Excel via VBA from Access.
1) I have valid objects for the Applicaiton, and workbook, and i *know* the
ranges I am working with are NOT EMPTY.
2) the ranges ARE all on the same worksheet.

the problem:
I am building a chart, and the chart has multiple series of data to graph.
The chart has a category X axis.
The lists of Series Categories aren't necessarily all identical, so I need
to build a range which encompases all the unique category values across all
the series.

My method is to build an initial list of known-unique names (a subset of the
final list)
and use a little code in nested for each loops to identify all those items
in subsequent ranges which aren't already in the initial range, and UNION the
cell.Address(External:=True) to the previous range (making a discontiguous
range that points to a complete but also unique list of category values.

The problem I am having is that when I call the UNION method as follows, the
result is an INVALID range address string!
Ex:
Debug.Print "Pre-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress
Debug.Print "n.address="; n.Address(External:=True)

oTempDRI.RangeAddress = _
xlA.Union( _
xlA.Range(oTempDRI.RangeAddress), _
xlA.Range(n.Address(External:=True)) _
).Address(External:=True)

Debug.Print "Post-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress

Result in debug window:
Pre-UNION
oTempDRI.RangeAddress=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14
n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$26
Post-UNION oTempDRI.RangeAddress=
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26

Pre-UNION oTempDRI.RangeAddress=
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26
n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$28

The 2nd pass through my loop, the .Range method blows up on the
UNION-supplied address string (even with (External:=True) being used?!

Any thoughts/advice?
....or am I just going to have to do some string surgery to manually stuff
the "[...]...!" part of the address back into the resultant string after the
last "," myself?

 

[Peter T]

Just reading the subject line I anticipated the problem was going to be not
qualifying Excel's Union function with a reference to the Excel Application,
but I see you have done that.

I can't make out from your code if the arguments in the Union function refer
to valid ranges on the same sheet or if they are returned addresses. I can't
see for the clutter but I suspect not, ie they should be range objects and
not addresses.

Regards,
Peter T

 

[Mark Burns]

Peter,

If they were only addresses, the UNION method would error out.
It "succeeds".

I have since discovered that even of you do "fixup" the external references
yourself, if you subsequently pass even those fixed-up external addresses
into UNION again later, *all* the external addressing semantics are lost
except for the FIRST one!

However, this is all moot, as I also subsequently discovered that the
charting component apparently must have CONTIGUOUS ranges to use for its
axes/categories/labels range arguments.

Just reading the subject line I anticipated the problem was going to be not
qualifying Excel's Union function with a reference to the Excel Application,
but I see you have done that.

I can't make out from your code if the arguments in the Union function refer
to valid ranges on the same sheet or if they are returned addresses. I can't
see for the clutter but I suspect not, ie they should be range objects and
not addresses.

Regards,
Peter T

Excel Gurus: HELP!

I am automating Excel via VBA from Access.
1) I have valid objects for the Applicaiton, and workbook, and i *know* the
ranges I am working with are NOT EMPTY.
2) the ranges ARE all on the same worksheet.

the problem:
I am building a chart, and the chart has multiple series of data to graph.
The chart has a category X axis.
The lists of Series Categories aren't necessarily all identical, so I need
to build a range which encompases all the unique category values across all
the series.

My method is to build an initial list of known-unique names (a subset of the
final list)
and use a little code in nested for each loops to identify all those items
in subsequent ranges which aren't already in the initial range, and UNION the
cell.Address(External:=True) to the previous range (making a discontiguous
range that points to a complete but also unique list of category values.

The problem I am having is that when I call the UNION method as follows, the
result is an INVALID range address string!
Ex:
Debug.Print "Pre-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress
Debug.Print "n.address="; n.Address(External:=True)

oTempDRI.RangeAddress = _
xlA.Union( _
xlA.Range(oTempDRI.RangeAddress), _
xlA.Range(n.Address(External:=True)) _
).Address(External:=True)

Debug.Print "Post-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress

Result in debug window:
Pre-UNION
oTempDRI.RangeAddress=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14
n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$26
Post-UNION oTempDRI.RangeAddress=
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26

Pre-UNION oTempDRI.RangeAddress=
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26
n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$28

The 2nd pass through my loop, the .Range method blows up on the
UNION-supplied address string (even with (External:=True) being used?!

Any thoughts/advice?
...or am I just going to have to do some string surgery to manually stuff
the "[...]...!" part of the address back into the resultant string after the
last "," myself?

 

[Peter T]

Afraid I didn't concentrate while reading your post.

FWIW, generally charts do not require ranges to be contigous or even same
size.
eg following for a single series with values in discontiguous ranges -

s = "=(" & rng.Areas(1).Address(, , xlR1C1, True)
For i = 2 To rng.Areas.Count
s = s & "," & rng.Areas(i).Address(, , xlR1C1, True)
Next
s = s & ")"
sr.Values = s ' similarly sr.XValues

where rng is a multi-area range and sr is a Series object

Of course not necessary to Union & create a multi area range, other ways to
build a similar string.

Regards,
Peter T

Peter,

If they were only addresses, the UNION method would error out.
It "succeeds".

I have since discovered that even of you do "fixup" the external references
yourself, if you subsequently pass even those fixed-up external addresses
into UNION again later, *all* the external addressing semantics are lost
except for the FIRST one!

However, this is all moot, as I also subsequently discovered that the
charting component apparently must have CONTIGUOUS ranges to use for its
axes/categories/labels range arguments.

Just reading the subject line I anticipated the problem was going to be not
qualifying Excel's Union function with a reference to the Excel Application,
but I see you have done that.

I can't make out from your code if the arguments in the Union function refer
to valid ranges on the same sheet or if they are returned addresses. I can't
see for the clutter but I suspect not, ie they should be range objects and
not addresses.

Regards,
Peter T

Excel Gurus: HELP!

I am automating Excel via VBA from Access.
1) I have valid objects for the Applicaiton, and workbook, and i

*know*
the

ranges I am working with are NOT EMPTY.
2) the ranges ARE all on the same worksheet.

the problem:
I am building a chart, and the chart has multiple series of data to graph.
The chart has a category X axis.
The lists of Series Categories aren't necessarily all identical, so I need
to build a range which encompases all the unique category values

across
all

the series.

My method is to build an initial list of known-unique names (a subset

of
the

final list)
and use a little code in nested for each loops to identify all those items
in subsequent ranges which aren't already in the initial range, and

UNION
the

cell.Address(External:=True) to the previous range (making a discontiguous
range that points to a complete but also unique list of category values.

The problem I am having is that when I call the UNION method as

follows,
the

result is an INVALID range address string!
Ex:
Debug.Print "Pre-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress
Debug.Print "n.address="; n.Address(External:=True)

oTempDRI.RangeAddress = _
xlA.Union( _
xlA.Range(oTempDRI.RangeAddress), _
xlA.Range(n.Address(External:=True)) _
).Address(External:=True)

Debug.Print "Post-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress

Result in debug window:
Pre-UNION
oTempDRI.RangeAddress=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14
n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$26
Post-UNION oTempDRI.RangeAddress=
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26

Pre-UNION oTempDRI.RangeAddress=
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26
n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$28

The 2nd pass through my loop, the .Range method blows up on the
UNION-supplied address string (even with (External:=True) being used?!

Any thoughts/advice?
...or am I just going to have to do some string surgery to manually stuff
the "[...]...!" part of the address back into the resultant string

after
the

last "," myself?