excelvbachartsoffice365

How to adjust width or position between label on y-axis?


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?


Solution

  • 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