What is ChartType -4111?

[iliauk]

Hi All,

I have a script that aims to facilitate the insertion of vertical and horizontal lines to a graph. However I have following dilemma; when I use a line graph: adding a vertical line and then a horizontal line works BUT adding a horizontal line and then a vertical line doesn't work.

In fact, if I add a vertical line then a horizontal line then a vertical line - It works! So something about adding the vertical line in the first instance fixes something in the graph -

Both the lines change the chart type from 4 to -4111 so I am not too sure what the issue is?

Apologies for the long code, but if helpful:

Sub AddLine()
'General declarations
Const strSheetName As String = "t3mp_000"
Dim chartname As String
Dim chartusing As Chart
Dim wsTest As Worksheet
Dim maxinput, mininput, i, j, counter As Long
Dim tick, success As Boolean
Dim arLabels, exLabels As String
Dim lablerange, cell As Range
Dim yarray() As Variant
Dim rsp As VbMsgBoxResult
Dim lininput As Variant


Set wsTest = Nothing
Set chartusing = ActiveChart


'Check Chart is Selected
If ActiveChart Is Nothing Then
    MsgBox "Activate a chart."
    Exit Sub
End If


'Fix certain charts
Debug.Print chartusing.ChartType
'Limited chart types currently supported
If chartusing.ChartType <> 4 And chartusing.ChartType <> -4111 And chartusing.ChartType <> -4169 Then
    rsp = MsgBox("Tested Charts: Line, Scatter" & vbNewLine & _
        "Chart Type not yet supported - continue?", vbYesNoCancel, "Proceed?")
    If rsp <> vbYes Then
        Exit Sub
    End If
End If
   
'Process range:
arLabels = chartusing.SeriesCollection(1).Formula
exLabels = Mid(Mid(arLabels, InStr(arLabels, ",") + 1), 1, InStr(1, Mid(arLabels, InStr(arLabels, ",") + 2), ","))
'Debug.Print exLabels
Set lablerange = Range(exLabels)
'Create corresponding array
counter = 1
success = 0
'Message box to determine which line to draw
rsp = MsgBox("Yes - Horizontal, No - Vertical", vbYesNoCancel, "Add Horizontal Line?")


Application.ScreenUpdating = False


If rsp = vbYes Then
    GoTo horizcode
ElseIf rsp = vbNo Then
    GoTo vertcode
Else
    Exit Sub
End If


horizcode:
    lininput = InputBox("Enter y-value", "Horizontal Line")
    If lininput = vbNullString Then Exit Sub
    
    For Each cell In lablerange
        ReDim Preserve yarray(1 To counter)
            yarray(counter) = cell
            counter = counter + 1
    Next cell
    
    'Preserve various formatting
    If ActiveChart.ChartType = 4 Or ActiveChart.ChartType = -4111 Then
        tick = ActiveChart.Axes(xlCategory).AxisBetweenCategories
    End If
    'Fix the y-axis so that line cuts through
    maxinput = chartusing.Axes(xlValue).MaximumScale
    chartusing.Axes(xlValue).MaximumScale = maxinput
    'Insert line
    chartusing.SeriesCollection.NewSeries.Name = "HorzLine"
    'Delete legend
    On Error Resume Next
        chartusing.Legend.LegendEntries(chartusing.Legend.LegendEntries.count).Delete
        'Temporary store in worksheet
        Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
        If wsTest Is Nothing Then
            ActiveWorkbook.Sheets.Add.Name = strSheetName
        End If
    On Error GoTo 0
    'Hide temp sheet
    ActiveWorkbook.Sheets(strSheetName).Visible = xlSheetHidden
    'Insert on next free row
    i = ActiveWorkbook.Sheets(strSheetName).Range("A65536").End(xlUp).row + 1
    'Fix the horizontal scale (for scatter)
    If ActiveChart.ChartType = -4169 Then
        yarray(LBound(yarray)) = ActiveChart.Axes(xlCategory).MinimumScale
        yarray(UBound(yarray)) = ActiveChart.Axes(xlCategory).MaximumScale
        ActiveChart.Axes(xlCategory).MinimumScale = yarray(LBound(yarray))
        ActiveChart.Axes(xlCategory).MaximumScale = yarray(UBound(yarray))
    End If
    For j = LBound(yarray) To UBound(yarray)
        If ActiveChart.ChartType = -4169 Or ActiveChart.ChartType = -4111 Then
            ActiveWorkbook.Sheets(strSheetName).Cells(i, j) = yarray(j)
        Else
            ActiveWorkbook.Sheets(strSheetName).Cells(i, j) = j
        End If
        ActiveWorkbook.Sheets(strSheetName).Cells(i + 1, j) = lininput
    Next j
    'Insert Line
    With chartusing.SeriesCollection(chartusing.SeriesCollection.count)
        .ChartType = xlXYScatterLinesNoMarkers
        .XValues = ActiveWorkbook.Sheets(strSheetName).Cells(i, LBound(yarray)).Resize(, UBound(yarray))
        .Values = ActiveWorkbook.Sheets(strSheetName).Cells(i + 1, LBound(yarray)).Resize(, UBound(yarray))
        .Format.Line.Visible = msoTrue
        .Format.Line.ForeColor.ObjectThemeColor = msoThemeColorText1
        .MarkerStyle = -4142
    End With
    'Reset temp
    If ActiveChart.ChartType = 4 Or ActiveChart.ChartType = -4111 Then
        ActiveChart.Axes(xlCategory).AxisBetweenCategories = tick
    End If
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Exit Sub


vertcode:
    lininput = InputBox("Enter x-value", "Vertical Line")
    If lininput = vbNullString Then Exit Sub
    
    For Each cell In lablerange
        ReDim Preserve yarray(1 To counter)
            If cell Like lininput Then
            yarray(counter) = cell
            success = 1
            Exit For
            End If
    Next cell
    
    If success = 0 Then
        yarray(1) = lininput
    End If
    
    'Preserve various formatting
    If ActiveChart.ChartType = 4 Or ActiveChart.ChartType = -4111 Then
        tick = ActiveChart.Axes(xlCategory).AxisBetweenCategories
    End If
    'Fix the y-axis so that line cuts through
    maxinput = chartusing.Axes(xlValue).MaximumScale
    chartusing.Axes(xlValue).MaximumScale = maxinput
    'Insert line
    chartusing.SeriesCollection.NewSeries.Name = "VertLine"
    'Delete legend
    On Error Resume Next
        chartusing.Legend.LegendEntries(chartusing.Legend.LegendEntries.count).Delete
        'Temporary store in worksheet
        Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
        If wsTest Is Nothing Then
            ActiveWorkbook.Sheets.Add.Name = strSheetName
        End If
    On Error GoTo 0
    'Hide temp sheet
    ActiveWorkbook.Sheets(strSheetName).Visible = xlSheetHidden
    'Insert on next free row
    i = ActiveWorkbook.Sheets(strSheetName).Range("A65536").End(xlUp).row + 1
    ActiveWorkbook.Sheets(strSheetName).Range("A" & i & ":" & "B" & i).Value = yarray(LBound(yarray))
    'Insert Line
    With chartusing.SeriesCollection(chartusing.SeriesCollection.count)
        .ChartType = xlXYScatterLinesNoMarkers
        .XValues = ActiveWorkbook.Sheets(strSheetName).Range("A" & i & ":" & "B" & i)
        .Values = Array(maxinput, 0)
        .Format.Line.Visible = msoTrue
        .Format.Line.ForeColor.ObjectThemeColor = msoThemeColorText1
        .MarkerStyle = -4142
    End With
    'Reset temp
    If ActiveChart.ChartType = 4 Or ActiveChart.ChartType = -4111 Then
        ActiveChart.Axes(xlCategory).AxisBetweenCategories = tick
    End If
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Exit Sub
End Sub

 

[Andrew Poulsom]

I can't find an xlChartType constant with the value -4111 in the VBE Object Browser.

 

[iliauk]

Thanks for your reply - I couldn't find it either

Nonetheless:

Debug.Print chartusing.ChartType
"-4111"

 

[Andy Pope]

-4111 = xlCombination

Sub X()

    Dim Ser As Series
    
    With ActiveChart
        Debug.Print "Chart type is ", .ChartType
        If .ChartType = xlCombination Then
            For Each Ser In .SeriesCollection
                Debug.Print Ser.ChartType
            Next
        End If
    End With
    
End Sub

If you have a combination chart then you can check the chart type of each series