我的总体问题是我想创建具有动态 x 轴（和 y 轴）刻度的动态图表。x 轴包含我观察的日期。为了设置 x 轴的最小和最大比例，我尝试创建一个日期数组并将下限和上限分别设置为我的最小和最大比例。
运行下面的代码我得到一个
错误 13：定义
ArrDate数组时类型不匹配错误。
我尝试将数组的内容设置为两者，as Variant而不是as Range最初的内容。下面是我有问题的代码（我试图过滤掉不必要的代码）。
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
数组不需要使用Set，你也想给数组，Range.Value这样就可以了：
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
此外，公共变量是行不通的......除了工作表的那些（在我看来）整数和范围是有风险的，因为任何程序都可能在任何时候改变它们。
这是如果您想提供数组值，如果您只想制作一个范围数组 arrDate = Array(Val_Total_Date, Range2, Range3...)
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句