How do I set Excel XYScatter x-axis as xlValue and not xlCategory?

[murchak]

This has been bugging me for a while now. I'm using ActiveX to generate an Excel XYScatter plot. But I cannot set the x-axis (horizontal axis) as xlValue. I did this manually and recorded the macro in Excel. It looks as follows:

ActiveSheet.ChartObjects("Chart 1").Activate

...

    ActiveChart.Axes(xlCategory).AxisTitle.Select

    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "X"

...   

    ActiveChart.Axes(xlValue).AxisTitle.Select

    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Y"

This shows that Excel is setting the x-axis as xlValue. So I tried to do the same in VI below, but it does not work. I get an error and no axes titles appear in the plot. The part in the Disable Diagram Structure works fine, but it is using xlCategory as x-axis and not xlValue.

[ben64]

The problem is that you're converting an axes object to an axes object. Convert to an axis object and remove the redundant axes.item method.

Ben64

[murchak]

Thanks. I definitely got that. The code that I have under Diagram Disable Structure is essentially what you recommended, and it works. The problem is how to make the x-axis of the type xlValue and not xlCategory. This is the essence of my question. As I understand it, xlValue type can be applied to to either x-axis (horizontal) or y-axis (vertical) in the XYScatter chart type. 

I am interested in plotting a continuous and numeric dependent variable (as y-axis) against a continuous and numeric independent variable (as x-axis) AND have the same formatting flexibility for both axes (like min, max, scale, etc.).

What limitations will I face if I used xlCategory for x-axis as opposed to xlValue? If there is a way to define the x-axis as xlValue, how would Excel distinguish between x-axis and y-axis if both types are xlValue?

[ben64]

The x axis type of an xy scatter plot is always value type, you setted it when you chose this plot type. xlCategory in this context is used to identify the x axis. I don't have access to LabVIEW right now but if you can set the x axis to non integer values then it is value type.

Note that the axis type property will still return xlCategory as specified here. That might explain the error you get when you try to set it to xlValue.

• Ben64

[ben64]

---

@ben64 wrote:

The x axis type of an xy scatter plot is always value type, you setted it when you chose this plot type. xlCategory in this context is used to identify the x axis. I don't have access to LabVIEW right now but if you can set the x axis to non integer values then it is value type.

Note that the axis type property will still return xlCategory as specified here. That might explain the error you get when you try to set it to xlValue.

• Ben64

 I realized that this is not true. Excel can use non integer values as categories. The simple way to check if your axis is by category or by value is to use values that are not in numerical order for your x axis. As an example, if you use x = 2,1,3 and your x axis displays as 2,1,3 then your x axis is by Category, if it displays as 1,2,3 then your are by Value.

Ben64

---

[murchak]

That is what I had suspected that in the context of using XYScatter plot, that the xlCategory is merely used to distinguish the x-axis from the y-axis which is also xlValue. I like your simple test of using a non-monotonic sequence to see if Excel correctly orders it. That would for sure tell whether the type is xlValue or not.