locked
I need some help with VBA for Excel 2016, Mistype error 13 RRS feed

  • Question

  • I cannot seem to find the mistype in the following VBA code. 

    This code came from a previous version of excel 2007 and I want to run it one 2016. When I do I get the runtime error 13 "type mistype" thrown. This is a snippet to automatically change the scale of a chart as new data is added to a column. 

    Sub AxisScale(myChart As Chart)

        With myChart

            With .Axes(xlValue, xlPrimary)

                .MaximumScale = Evaluate("'" & ActiveSheet.Name & "'!chtYmax")

                .MinimumScale = Evaluate("'" & ActiveSheet.Name & "'!chtYmin")

                .MinorUnitIsAuto = False

                .MajorUnit = Evaluate("'" & ActiveSheet.Name & "'!chtYmajor")

                .CrossesAt = -9999999999#

            End With

        End With

    End Sub

    Any help is appreciated,

    Friday, March 18, 2016 12:43 AM

All replies

  • I am surprised that the code actually got to the line you have highlighted. The following test appears to work for me but I cannot be sure that my test chart is the same as your chart. I have dimensions myChart as ChartObject and added dot Chart as follows With myChart.Chart. Note that in the calling sub that I have also dimensioned the variable as ChartObject to call the sub.

    Sub test()
        Dim cht As ChartObject      'Dimension as ChartObject; not Chart
       
        Set cht = ActiveSheet.ChartObjects("Chart 1")
       
        Call AxisScale(cht)
       
    End Sub


    Sub AxisScale(myChart As ChartObject)    'Dimension as ChartObject; not Chart
        
        With myChart.Chart     'Append dot Chart to myChart
            With .Axes(xlValue, xlPrimary)
                .MaximumScale = Evaluate("'" & ActiveSheet.Name & "'!chtYmax")
                .MinimumScale = Evaluate("'" & ActiveSheet.Name & "'!chtYmin")
                .MinorUnitIsAuto = False
                .MajorUnit = Evaluate("'" & ActiveSheet.Name & "'!chtYmajor")
                .CrossesAt = -9999999999#
            End With
        End With

    End Sub


    Regards, OssieMac

    Monday, March 21, 2016 6:55 AM