Visual Basic - X-axis value of Excel chart

Author

X-axis value of Excel chart

Kate

2005-05-22, 3:55 pm

I have problem with assigning x-axis value of a chart.

This is how I assign y-axis value. LastCell is a variable.
xlCht1.SeriesCollection(1).XValues = xlWshtS.Range("E4:E" & LastCell)

I know this can assign values for both axes, but it can't use LastCell.
xlCht1.SeriesCollection(1).XValues = xlWshtS!"R4C1:R10C1"

Any suggestion?

Thanks,
Kate

Kevan Hughes

2005-05-22, 8:55 pm

Kate,

Try switching off the R1C1 reference style.. Are you using Visual Basic 6 or
VBA ?

Kevan

"Kate" wrote:

> I have problem with assigning x-axis value of a chart.
>
> This is how I assign y-axis value. LastCell is a variable.
> xlCht1.SeriesCollection(1).XValues = xlWshtS.Range("E4:E" & LastCell)
>
> I know this can assign values for both axes, but it can't use LastCell.
> xlCht1.SeriesCollection(1).XValues = xlWshtS!"R4C1:R10C1"
>
> Any suggestion?
>
> Thanks,
> Kate

Kate

2005-05-23, 3:55 pm

I use VB6. How to switch off R1C1? and even if it's switched off, I still
don't know how to assign value for x-axis...

Thanks,
Kate

"Kevan Hughes" wrote:
[color=darkred]
> Kate,
>
> Try switching off the R1C1 reference style.. Are you using Visual Basic 6 or
> VBA ?
>
> Kevan
>
> "Kate" wrote:
>

Kevan Hughes

2005-05-23, 3:55 pm

In Excel use TOOLS, OPTIONS then General.. Settings show R1C1.. uncheck the
box.. I gather you want to create a graph ?

"Kate" wrote:
[color=darkred]
> I use VB6. How to switch off R1C1? and even if it's switched off, I still
> don't know how to assign value for x-axis...
>
> Thanks,
> Kate
>
> "Kevan Hughes" wrote:
>

Kate

2005-05-24, 8:55 am

R1C1 is already off.
Yes, I'm trying to create a chart. Sorry that I didn't state it clearly.

For example, the chart type is xlColumnClustered. The range of y-axis is
colB, colC, and colD, from row1 to rowVariable. The range of x-axis is colA,
from row1 to rowVariable. How to create a chart like that?

Now, I use:
xlCht1.ChartType = xlColumnClustered
xlCht1.SeriesCollection(1).XValues = xlWshtS.Range("B1:B" & rowVariable)
The chart looks fine, except that the value of x-axis is missing.

Thanks
Kate

"Kevan Hughes" wrote:

> In Excel use TOOLS, OPTIONS then General.. Settings show R1C1.. uncheck the
> box.. I gather you want to create a graph ?
>

Kevan Hughes

2005-05-24, 8:55 am

Kate, take a look at a sample.. You be missing a string declaration. Sample
is loaded when installing VB locally.. should be \VB98\SAMPLES\VB98\CHRSAMP
folder. The MODCHART.BAS is the code you should look at, DATE VALUES are used
for x-axis labels/values.. IS this useful ?

Public Sub PopOneArray(thisarray As Variant, col As String)
' This procedure just populates arrays.
Dim i As Integer
ReDim thisarray(1 To intRows, 1 To 2)
For i = 1 To intRows
' Get the Date values.
thisarray(i, 1) = CStr(rngGas.Range("A" & i + 1).Value)
' Get values.
thisarray(i, 2) = Format(rngGas.Range(col & i + 1).Value, "##.##")
frmChart.prgArrays.Value = i
Next i

End Sub

"Kate" wrote:

> R1C1 is already off.
> Yes, I'm trying to create a chart. Sorry that I didn't state it clearly.
>
> For example, the chart type is xlColumnClustered. The range of y-axis is
> colB, colC, and colD, from row1 to rowVariable. The range of x-axis is colA,
> from row1 to rowVariable. How to create a chart like that?
>
> Now, I use:
> xlCht1.ChartType = xlColumnClustered
> xlCht1.SeriesCollection(1).XValues = xlWshtS.Range("B1:B" & rowVariable)
> The chart looks fine, except that the value of x-axis is missing.
>
> Thanks
> Kate
>
> "Kevan Hughes" wrote:
>
>

Kate

2005-05-26, 3:55 am

Kevan,
I think it would be useful, but I don't have the SAMPLES folder...
Would you attach the MODCHART.BAS to me?
Thanks,
Kate

"Kevan Hughes" wrote:

> Kate, take a look at a sample.. You be missing a string declaration. Sample
> is loaded when installing VB locally.. should be \VB98\SAMPLES\VB98\CHRSAMP
> folder. The MODCHART.BAS is the code you should look at, DATE VALUES are used
> for x-axis labels/values.. IS this useful ?
>
> Public Sub PopOneArray(thisarray As Variant, col As String)
> ' This procedure just populates arrays.
> Dim i As Integer
> ReDim thisarray(1 To intRows, 1 To 2)
> For i = 1 To intRows
> ' Get the Date values.
> thisarray(i, 1) = CStr(rngGas.Range("A" & i + 1).Value)
> ' Get values.
> thisarray(i, 2) = Format(rngGas.Range(col & i + 1).Value, "##.##")
> frmChart.prgArrays.Value = i
> Next i
>
> End Sub

Kevan Hughes

2005-05-27, 3:55 pm

kevanhughes@hotmail.com

I can send the all the files to you if you email me above.. It is useful to
see the complete application in action..

"Kate" wrote:

> Kevan,
> I think it would be useful, but I don't have the SAMPLES folder...
> Would you attach the MODCHART.BAS to me?
> Thanks,
> Kate
>
> "Kevan Hughes" wrote:
>
>

Sponsored Links