This my data on excel
Column A : test1,test2,test3,test4,test5,test6,test7,test8,test9,test10,test11
Column B : 1,1,1,1,1,1,1,0,1,0,1
I want to create chart with my VBA code below
Sub createChart()
Dim ws As Worksheet
Dim chartObj As ChartObject
Dim testNames As Range, results As Range, chartData As Range
Dim anchorCell As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
Set testNames = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
Dim categoryValues As Variant
categoryValues = testNames.Value
Set results = ws.Range("B2:B" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
Set chartData = ws.Range("A1:B" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
' Set anchor cell to Q2 to determine chart position
Set anchorCell = ws.Range("Q2")
Set chartObj = ws.ChartObjects.Add(Left:=anchorCell.Left, Top:=anchorCell.Top, Width:=1320, Height:=724)
With chartObj.chart
.ChartType = xlLineMarkers
.SetSourceData Source:=chartData
.HasLegend = False
.chartTitle.Text = "GGG"
If IsArray(categoryValues) Then
' Multiple categories
.Axes(xlCategory).CategoryNames = categoryValues
Else
' Only one category, convert scalar to array
.Axes(xlCategory).CategoryNames = Array(categoryValues)
End If
With .Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 1
.MajorUnit = 1
.CrossesAt = 0
End With
With .SeriesCollection(1)
.MarkerStyle = xlMarkerStyleCircle
.MarkerSize = 8
.MarkerBackgroundColor = RGB(0, 0, 255)
.MarkerForegroundColor = RGB(0, 0, 255)
.Format.Line.Visible = msoFalse
End With
End With
End Sub
but I try to adjust width between 0 and 1. but it's not middle of y-axis
my expected result according my attach image enter image description here
my logic to adjust width between 0 and 1 to middle is code below
With .Axes(xlValue)
.MinimumScale = -1
.MaximumScale = 2
.MajorUnit = 1
.CrossesAt = -1
End With
but it's almost work but it's show 2 and -1 according my second imageenter image description here. How to show only 1 and 0?
This is not a solution, but a workaround.
If you do not want to show the value 2
on the Y
axis, you can limit the values to e.g. 1.9
.
If you don't want to show -1
at the bottom, you can use axis number formatting to hide negative numbers.
With .Axes(xlValue)
.MinimumScale = -1
.MaximumScale = 1.9
.MajorUnit = 1
.CrossesAt = -1
.TickLabels.NumberFormat = "0;;0"
End With