Custom X-axis

**SCFM** · 03-02-2010, 12:06 PM

I have a table as follows:
Field1 | Field2
A | 1.5
B | 3.4
C | 0.5

For that table I built a default chart using VB where X-axis = 1,2,3 and Y-axis = 0,2,4.
Y-axis it's Ok for me. My problem is X-axis. I need to have X-axis = A,B,C.
How to do using VB? Thanks.

**Andy Pope** · 03-02-2010, 12:30 PM

What chart type?
What code are you using currently?

**SCFM** · 03-02-2010, 12:45 PM

Originally Posted by Andy Pope

What chart type?
What code are you using currently?

'Start Excel and create a new workbook
Set myXL = CreateObject("Excel.application")
Set myBook = myXL.Workbooks.Add
Set mySheet = myBook.Worksheets.Item(1)

Set myRange = Worksheets("Sheet1").Range("A1:A65536")
cNumCols = 2
cNumRows = Application.WorksheetFunction.Max(myRange) + 1

ReDim aTemp1(1 To cNumRows, 1 To 1)
ReDim aTemp2(2 To cNumRows, 2 To 2)

‘Data
For iRow = 1 To cNumRows
aTemp1(iRow, 1) = Cells(iRow, "D")
aTemp2(iRow, 2) = Cells(iRow, "E")
Next iRow

mySheet.Range("A1").Resize(cNumRows, 1).Value = aTemp1
mySheet.Range("B1").Resize(cNumRows, 1).Value = aTemp2

'Add a chart objects
Set myChart = mySheet.ChartObjects.Add(150, 30, 450, 300).Chart
myChart.HasTitle = True
myChart.ChartTitle.Characters.Text = Ws1.Cells(2, "A")
myChart.Axes(xlCategory, xlPrimary).HasTitle = True
myChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = Ws1.Cells(1, "B")
myChart.Axes(xlValue, xlPrimary).HasTitle = True
myChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Ws1.Cells(1, "F")
myChart.ChartType = xl3DColumnStacked
myChart.SetSourceData Source:=mySheet.Range("A1").Resize(cNumRows, 1)
myChart.ApplyDataLabels ShowValue:=1
myChart.SetSourceData Source:=mySheet.Range("B1").Resize(cNumRows, 1)
myChart.ApplyLayout (5)

'Make Excel Visible:
myXL.Visible = True

myXL.UserControl = True

**Andy Pope** · 03-02-2010, 12:59 PM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

So you are using VB6 to automate excel?

Give your described data and layout this line will return 1 as the labels are in column A.

Code:

cNumRows = Application.WorksheetFunction.Max(myRange) + 1

Here you set the source data as column A and then change it to be column B, which would then remove the axis labels.

Code:

myChart.SetSourceData Source:=mySheet.Range("A1").Resize(cNumRows, 1)
myChart.ApplyDataLabels ShowValue:=1
myChart.SetSourceData Source:=mySheet.Range("B1").Resize(cNumRows, 1)

Instead try this single use of the SetSourceData method.

Code:

myChart.SetSourceData Source:=mySheet.Range("A1").Resize(cNumRows, 2)

**SCFM** · 03-02-2010, 01:23 PM

http://img186.imageshack.us/img186/738/excel1.jpg

Here is what I'm getting now with:
myChart.SetSourceData Source:=mySheet.Range("A1").Resize(cNumRows, 2)
instead of:
myChart.SetSourceData Source:=mySheet.Range("A1").Resize(cNumRows, 1)
myChart.ApplyDataLabels ShowValue:=1
myChart.SetSourceData Source:=mySheet.Range("B1").Resize(cNumRows, 1)
Thanks again.

**SCFM** · 03-02-2010, 02:00 PM

Notice that if A1 contains numbers, automatically Excel take that numbers as a serie and fixs
X-axis as 1,2,3.....but if A1 contains Strings (like A,B,C..) Excel fixs X-axis as A, B, C.....weird.
So, to solve my problem I should format A1 as strings instead of numbers. I'll try that!