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

Question

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

Damian · Accepted Answer · 2019-05-20 09:26:22Z

1

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...)

answered May 20, 2019 at 9:26

Damian

5,1741 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

BlackBear Over a year ago

The code above doesn't work for me. The values passed onto the array are the elements position, whereas I want the original value (dates). My debug.print provides: First observation day:1 instead of 1 it should say 30/04/2014

Damian Over a year ago

The code above was just an example on how you should change your's. When you enter a range into the array it becomes 2D so every column will be an y position and every row a x position. What is inside it I can't know.

BlackBear Over a year ago

UBound(ArrDate, 1) returns 499 while Ubound(ArrDate, 2) returns 1 which is what led me to believe the array doesn't take in the actual values of my range.

Damian Over a year ago

Arrays don't usually get along with Union, I would use a workaround such as paste the Val_Total_Date into a temporal sheet and get the UsedRange from that sheet to the array. Though to you will need some tweaks to paste the info back to your sheet if that is the case.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related