Results 1 to 9 of 9

Thread: [RESOLVED] Interval on the Y-axis for an Excel graph

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    7

    Resolved [RESOLVED] Interval on the Y-axis for an Excel graph

    I am trying to set the Interval on the Y-axis for an Excel graph programmayically using VB

    Everything else is working fine.

    Cant find answer on Internet. Can you help please

    Code:
    theFilename = My.Settings.excelFolder & "Book1.xlsx"
    
            Dim excelApp = New Microsoft.Office.Interop.Excel.Application
            Dim myExcel = excelApp.Workbooks.Open(theFilename)
    
    
            Dim xlCharts As Microsoft.Office.Interop.Excel.ChartObjects
            Dim myChart1 As Microsoft.Office.Interop.Excel.ChartObject
            Dim chartPage1 As Microsoft.Office.Interop.Excel.Chart
            Dim chartRangeAll As Microsoft.Office.Interop.Excel.Range
    		
    		
    		
    		xlCharts = myExcel.Sheets("zero").ChartObjects
    
            myChart1 = xlCharts.Add(startPosLeft, chartTopPosition, startPosWidth, startPosHeight)   '  L T W H
    
            chartPage1 = myChart1.Chart
    
    
            chartPage1.Axes(XlAxisType.xlValue).MaximumScale = 8
    
            chartPage1.Axes(XlAxisType.xlValue).MinimumScale = 2
    I have tried
    Code:
    chartPage1.Axes(XlAxisType.xlValue).Interval = 0.5
    and other such combinations, but they come up as errors.

    Thanks in advance

  2. #2
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    602

    Re: Interval on the Y-axis for an Excel graph

    this is what I used in one of my project

    Code:
    Chart1.ChartAreas(0).AxisY.Minimum = MiniY
                Chart1.ChartAreas(0).AxisY.Maximum = MaxiY
                Chart1.ChartAreas(0).AxisY.MajorGrid.IntervalOffset = intervalY
                Chart1.ChartAreas(0).AxisY.MajorGrid.Interval = intervalY
                Chart1.ChartAreas(0).AxisY.MajorTickMark.IntervalOffset = intervalY
                Chart1.ChartAreas(0).AxisY.MajorTickMark.Interval = intervalY
                Chart1.ChartAreas(0).AxisY.LabelStyle.Interval = intervalY
    MiniY, MaxiY and intervalY are my variables in my application.
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    7

    Re: Interval on the Y-axis for an Excel graph

    Thanks for quick reply,

    Your code works if the chart is on a Windows form, but not if on an Excel sheet.

    I think Excel sheets do not have ChartAreas

    Or am I wrong.

    Tried your code and error was
    System.MissingMemberException: 'Public member 'ChartAreas' on type 'Chart' not found.'
    Should I have a ChartArea on the excel sheet ? Chart appears fine without it.

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,776

    Re: Interval on the Y-axis for an Excel graph

    you have to tell which ChartType
    Code:
     chartPage.ChartType = Excel.XlChartType.xl3DBarClustered
    something like this

    Code:
     'create chart
            Dim chartPage As Excel.Chart
            Dim xlCharts As Excel.ChartObjects
            Dim myChart As Excel.ChartObject
            Dim chartRange As Excel.Range
    
            xlCharts = CType(xlWorkSheet.ChartObjects, ChartObjects)
            myChart = xlCharts.Add(10, 80, 300, 250)
    
            chartPage = myChart.Chart
            chartRange = xlWorkSheet.Range("A1", "d5") 'dein Array
            chartPage.SetSourceData(Source:=chartRange)
            chartPage.ChartType = Excel.XlChartType.xl3DBarClustered
    
    'I don't think if Option Strict is set to On 
    'that this will work
            chartPage.Axes(XlAxisType.xlValue).MaximumScale = 8
            chartPage.Axes(XlAxisType.xlValue).MinimumScale = 2

    EDIT:
    here a sample
    Code:
    'Option Strict On
    Imports Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop
    
    
    Public Class chart
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    
            'Dim xlApp As Excel.Application
            Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
    
            'xlApp = New Excel.ApplicationClass
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = CType(xlWorkBook.Sheets("Tabelle1"), Worksheet)
    
            'array data
            xlWorkSheet.Cells(1, 1) = ""
            xlWorkSheet.Cells(1, 2) = "Verkauf1"
            xlWorkSheet.Cells(1, 3) = "Verkauf2"
            xlWorkSheet.Cells(1, 4) = "Verkauf3"
    
            xlWorkSheet.Cells(2, 1) = "Stuttgart"
            xlWorkSheet.Cells(2, 2) = "80"
            xlWorkSheet.Cells(2, 3) = "65"
            xlWorkSheet.Cells(2, 4) = "45"
    
            xlWorkSheet.Cells(3, 1) = "Frankfurt"
            xlWorkSheet.Cells(3, 2) = "78"
            xlWorkSheet.Cells(3, 3) = "72"
            xlWorkSheet.Cells(3, 4) = "60"
    
            xlWorkSheet.Cells(4, 1) = "Köln"
            xlWorkSheet.Cells(4, 2) = "82"
            xlWorkSheet.Cells(4, 3) = "80"
            xlWorkSheet.Cells(4, 4) = "65"
    
            xlWorkSheet.Cells(5, 1) = "München"
            xlWorkSheet.Cells(5, 2) = "75"
            xlWorkSheet.Cells(5, 3) = "82"
            xlWorkSheet.Cells(5, 4) = "68"
    
            'create chart
            Dim chartPage As Excel.Chart
            Dim xlCharts As Excel.ChartObjects
            Dim myChart As Excel.ChartObject
            Dim chartRange As Excel.Range
    
            xlCharts = CType(xlWorkSheet.ChartObjects, ChartObjects)
            myChart = xlCharts.Add(10, 80, 300, 250)
    
            chartPage = myChart.Chart
            chartRange = xlWorkSheet.Range("A1", "d5") 'dein Array
            chartPage.SetSourceData(Source:=chartRange)
            chartPage.ChartType = Excel.XlChartType.xlLineMarkers
    
            chartPage.Axes(XlAxisType.xlValue).MaximumScale = 120
            chartPage.Axes(XlAxisType.xlValue).MinimumScale = 45
    
    
            xlWorkSheet.SaveAs("E:\TestFolder\ChartVerkauf12.xlsx")
            xlWorkBook.Close()
            xlApp.Quit()
    
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
    
            MsgBox("Excel Datei erstellt")
        End Sub
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    End Class
    here a image
    Name:  excelChart.jpg
Views: 50
Size:  44.9 KB
    Last edited by ChrisE; Apr 17th, 2021 at 01:59 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    7

    Re: Interval on the Y-axis for an Excel graph

    ChrisE, you have the Y-axis inteval set to 10.

    How would you cahnge it to 5 or 20 ?

    Thanks for your help

  6. #6
    Frenzied Member
    Join Date
    Jul 2011
    Location
    UK
    Posts
    1,333

    Re: Interval on the Y-axis for an Excel graph

    Quote Originally Posted by evan66 View Post
    ChrisE, you have the Y-axis inteval set to 10.

    How would you cahnge it to 5 or 20 ?

    Thanks for your help
    It looks like you need the Axis.MajorUnit property.

    So building on ChrisE's code above, you could use something like:
    Code:
    Dim ax = DirectCast(chartPage.Axes(XlAxisType.xlValue), Excel.Axis)
    ax.MaximumScale = 120
    ax.MinimumScale = 45
    ax.MajorUnit = 20

  7. #7

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    7

    Re: Interval on the Y-axis for an Excel graph

    Thats it Inferrd
    Thank you all, I woild never have worked that out for myself.

    All as I want it now.

    Thank you

  8. #8
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    602

    Re: Interval on the Y-axis for an Excel graph

    Quote Originally Posted by evan66 View Post
    Thanks for quick reply,

    Your code works if the chart is on a Windows form, but not if on an Excel sheet.

    I think Excel sheets do not have ChartAreas

    Or am I wrong.

    Tried your code and error was

    Should I have a ChartArea on the excel sheet ? Chart appears fine without it.
    Arg! I was focused on the problem and didn't paid attention to the context (excel sheet and not chart), sorry for that and for the wrong answer.
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)

  9. #9

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    7

    Re: [RESOLVED] Interval on the Y-axis for an Excel graph

    No problem. Thanks for your help anyway.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width