CODEWITHSUNDEEP
excelvbatype-mismatchexcel-charts
BlackBear
BlackBear

Reputation: 385

Type mismatch error when setting array of dates based on a range

My overall problem is that I want to create dynamic charts with dynamic x-axis (and y-axis) scale. The x-axis contains dates of my observations. To set the minimum and maximum scale of the x-axis I tried to create an array of dates and setting the lower and upper bound as my minimum and maximum scale, respectively.

Running the code below I get an

Error 13: Type mismatch error when defining the ArrDate array.

I tried setting the contents of the array to both to as Variant instead of as Range which is what originally. Below is the code I have problems with (I tried to filter out unnecessary code).

Option Explicit
 Option Base 0

 ' Worksheets and workbooks
 Public ws             As Worksheet
 Public ws_O           As Worksheet
 Public wkb            As Workbook

 ' Integers
 Public i              As Integer

 ' Variants and ranges
 Public Val_NF3        As Range
 Public Val_Barra      As Range
 Public Val_NF3_Date   As Range
 Public Val_Barra_Date As Range
 Public Val_Total_Date As Variant ' Originally set to Range
 Public ArrDate        As Variant
 Public ArrCht         As Variant

 ' String
 Public cht_Name       As String
 Public ws_Name        As String



 Sub Update()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.StatusBar = "Updating graphs ... "



    ' Assign correct sheet and ranges to retrieve data from
    ws_Name = "Data"
    Set wkb = thisworkbook
    Set ws = wkb.Sheets(ws_Name)
    Set ws_O = wkb.sheets("Overview")



    ' Updating graphs
     Debug.Print "Chart loop order by chart name:" ' To show loop order
        ArrCht = Array("Beta", "StDev", "TE")
        For i = LBound(ArrCht) To UBound(ArrCht)
            cht_Name = ArrCht(i)
            Set cht = ws_O.ChartObjects(cht_Name)
            Set Val_NF3 = ws.Range(ws.Cells(2, 4 + i), ws.Cells(200, 4 + i)) ' Set range of values from NF3 (GEM3)
            Set Val_Barra = ws.Range(ws.Cells(201, 4 + i), ws.Cells(500, 4 + i)) ' Set range of values Barra
            Set Val_NF3_Date = ws.Range(ws.Cells(2, 3), ws.Cells(500, 3)) ' Set range of date for NF3 observations
            Set Val_Barra_Date = ws.Range(ws.Cells(201, 3), ws.Cells(500, 3)) ' Set range of date for Barra observations
            Set Val_Total_Date = Union(Val_NF3_Date, Val_Barra_Date)
            Set ArrDate = Array(Val_Total_Date)' <---- CODE FAILS HERE WITH TYPE MISMATCH ERROR
            With cht.Chart
                Debug.Print cht.Name ' Loop order
                Debug.Print ArrDate(1)
                Debug.Print "First observation day:" & LBound(ArrDate, 1)
                Debug.Print "Last observation day:" & UBound(ArrDate, 2)
                .FullSeriesCollection(1).Format.Line.ForeColor.RGB = ws_O.Cells(1 + i, 20).Interior.Color
                .FullSeriesCollection(2).Format.Line.ForeColor.RGB = ws_O.Cells(2 + i, 20).Interior.Color
                .FullSeriesCollection(1).Values = Val_NF3 ' Value series for NF3
                .FullSeriesCollection(2).Values = Val_Barra ' Value series for Barra
                .FullSeriesCollection(1).XValues = Val_NF3_Date
                .FullSeriesCollection(2).XValues = Val_Barra_Date
                 If cht_Name = "Beta" Then ' Defining Beta = 1
                 .FullSeriesCollection(3).Format.Line.ForeColor.RGB = ws_O.Cells(1, 21).Interior.Color '  Color
                 .FullSeriesCollection(3).Values = 1
                 .SeriesCollection(3).XValues = Val_Total_Date
                 End If
                .Axes(xlCategory).CategoryType = xlTimeScale
                .Axes(xlCategory).MajorUnitScale = xlMonths
                .Axes(xlCategory).MajorUnit = 4
                .Axes(xlCategory).MinimumScale = ArrDate(1)
                .Axes(xlCategory).MaximumScale = ArrDate(999)
                .Axes(xlCategory).MinimumScale = LBound(ArrDate, 2)
                .Axes(xlCategory).MaximumScale = UBound(ArrDate, 2)
                ' My failed attempts to scale the x-axis and y-axis (this is the overall problem)
                '.Axes(xlCategory).MaximumScaleIsAuto = True
                '.Axes(xlCategory).MajorUnitIsAuto = True
                '.Axes(xlValue).MinimumScaleIsAuto = True
                '.Axes(xlValue).MaximumScaleIsAuto = True
                '.AutoScaling = True
            End With
        Next i

    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

 End Sub

Upvotes: 0

Views: 473

Answers (1)

Damian
Damian

Reputation: 5174

Arrays don't need to use Set, also you want to give the array the Range.Value so this would do it:

Option Explicit
Sub Test()

    Dim arrDate
    Dim Val_Total_Date As Range

    With ThisWorkbook.Sheets(1)
        Set Val_Total_Date = Union(.Range("A:A"), .Range("D:D"))
        arrDate = Val_Total_Date.Value
    End With


End Sub

Also, public variables are a no go... Except for the worksheet ones (in my opinion) integers and ranges would be risky since any procedure could change them in any moment.

This is if you want to give the array values, if you want to make an array of ranges just arrDate = Array(Val_Total_Date, Range2, Range3...)

Upvotes: 1

Related Questions