在数组中使用变体会导致 Excel VBA 类型不匹配错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22243179/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using variant in array causes Excel VBA type mismatch error
提问by AaronK
I'm trying to set slicer values based on code to select a range of 13 consecutive months. If I use the variable in the code, it creates a type mismatch error. If I debug.print the variable and paste the values instead of the variable, the code works just fine. Am I missing something when trying to use the variable myStr?
我正在尝试根据代码设置切片器值以选择连续 13 个月的范围。如果我在代码中使用该变量，则会产生类型不匹配错误。如果我 debug.print 变量并粘贴值而不是变量，代码工作得很好。尝试使用变量 myStr 时是否遗漏了什么？
Code that Errors out:
出错的代码：
Sub Test()
Dim wb As Workbook
Dim myStr As Variant
Dim str2 As String
Set wb = ThisWorkbook
myStr = wb.Worksheets("Filters").Range("G57").Value
str2 = wb.Worksheets("filters").Range("D32").Value
If str2 = "All" Then
wb.SlicerCaches("Slicer_Month1").ClearManualFilter
Else
wb.SlicerCaches("Slicer_Month1").VisibleSlicerItemsList = Array(myStr)
End If
End Sub
Code that works (Values in lieu of myStr are from a ?mystr :
有效的代码（代替 myStr 的值来自 ?mystr ：
Sub Test()
Dim wb As Workbook
Dim myStr As Variant
Dim str2 As String
Set wb = ThisWorkbook
'myStr = wb.Worksheets("Filters").Range("G57").Value
str2 = wb.Worksheets("filters").Range("D32").Value
If str2 = "All" Then
wb.SlicerCaches("Slicer_Month1").ClearManualFilter
Else
wb.SlicerCaches("Slicer_Month1").VisibleSlicerItemsList = Array("[Date].[Month].& _
[2014-02-01T00:00:00]", "[Date].[Month].&[2014-01-01T00:00:00]", "[Date].[Month].& _
[2013-12-01T00:00:00]", "[Date].[Month].&[2013-11-01T00:00:00]", "[Date].[Month].& _
[2013-10-01T00:00:00]", "[Date].[Month].&[2013-09-01T00:00:00]", "[Date].[Month].& _
[2013-08-01T00:00:00]", "[Date].[Month].&[2013-07-01T00:00:00]", "[Date].[Month].& _
[2013-06-01T00:00:00]", "[Date].[Month].&[2013-05-01T00:00:00]", "[Date].[Month].& _
[2013-04-01T00:00:00]", "[Date].[Month].&[2013-03-01T00:00:00]", "[Date].[Month].& _
[2013-02-01T00:00:00]")
End If
End Sub
EDIT:
编辑：
My original code had a Join(Transpose(Range),",") (see below)to create the array instead of using a consolidated string in cell G57. I concatenated the results into G57 to basically create the desired string in a troubleshooting effort.
我的原始代码有一个 Join(Transpose(Range),",") （见下文）来创建数组，而不是在单元格 G57 中使用合并字符串。我将结果连接到 G57 中，以在故障排除工作中基本上创建所需的字符串。
mystr = _
Join(Application.WorksheetFunction.Transpose(Worksheets("Filters").Range("G43:G55")),",")
.VisibleSlicerItemList = Array(myStr)
I can't post a screenshot since I do not have enough points yet, but cells G43:G55 contain the correct time/date stamp required to set the slicer values. The format is "[Date].[Month].&[YYYY-MM-DD&"T"&HH:MM:SS]". The values in G57 are concatenated results from G43:G55 separated by commas and includes quotes.
我无法发布屏幕截图，因为我还没有足够的点数，但单元格 G43:G55 包含设置切片器值所需的正确时间/日期戳。格式为“[日期].[月].&[YYYY-MM-DD&“T”&HH:MM:SS]”。G57 中的值是来自 G43:G55 的连接结果，由逗号分隔并包括引号。
EDIT 2:
编辑2：
It's not a real solution and is completely inelegant, but time is money so I cheated and declared variables for each of the 13 possible date values as a temporary solution. Part of the reporting package shows monthly values based on a selected month and the 12 months prior, so at least the number of possible values is fixed. It doesn't really solve the problem for others who are in need of a similar solution -- imagine 100+ possible selections, cringe!
这不是一个真正的解决方案并且完全不雅，但时间就是金钱，所以我欺骗并为 13 个可能的日期值中的每一个声明变量作为临时解决方案。报告包的一部分显示基于选定月份和之前 12 个月的月度值，因此至少可能值的数量是固定的。对于需要类似解决方案的其他人来说，它并没有真正解决问题——想象一下 100 多种可能的选择，畏缩！
Private Sub Worksheet_Calculate()
Dim wb As Workbook
Dim str2 As String
Dim mstr1 As Variant
Dim mstr2 As Variant
Dim mstr3 As Variant
Dim mstr4 As Variant
Dim mstr5 As Variant
Dim mstr6 As Variant
Dim mstr7 As Variant
Dim mstr8 As Variant
Dim mstr9 As Variant
Dim mstr10 As Variant
Dim mstr11 As Variant
Dim mstr12 As Variant
Dim mstr13 As Variant
'Other code runs here
Set wb = ThisWorkbook
mstr1 = wb.Worksheets("Filters").Range("G43").Value
mstr2 = wb.Worksheets("Filters").Range("G44").Value
mstr3 = wb.Worksheets("Filters").Range("G45").Value
mstr4 = wb.Worksheets("Filters").Range("G46").Value
mstr5 = wb.Worksheets("Filters").Range("G47").Value
mstr6 = wb.Worksheets("Filters").Range("G48").Value
mstr7 = wb.Worksheets("Filters").Range("G49").Value
mstr8 = wb.Worksheets("Filters").Range("G50").Value
mstr9 = wb.Worksheets("Filters").Range("G51").Value
mstr10 = wb.Worksheets("Filters").Range("G52").Value
mstr11 = wb.Worksheets("Filters").Range("G53").Value
mstr12 = wb.Worksheets("Filters").Range("G54").Value
mstr13 = wb.Worksheets("Filters").Range("G55").Value
str2 = wb.Worksheets("filters").Range("D32").Value
If str2 = "All" Then
wb.SlicerCaches("Slicer_Month1").ClearManualFilter
Else
wb.SlicerCaches("Slicer_Month1").VisibleSlicerItemsList = Array(mstr1, mstr2, mstr3, mstr4, mstr5, mstr6, mstr7, mstr8, mstr9, mstr10, mstr11, mstr12, mstr13)
End If
'More code runs here
ErrHandler:
RestoreXL
Exit Sub
End Sub
回答by Tim Williams
Try this (based on values in G43:G55)
试试这个（基于 G43:G55 中的值）
Sub Test()
Dim wb As Workbook
Dim shtF as Worksheet
Dim myArray
Dim str2 As String
Set wb = ThisWorkbook
Set shtF = wb.Worksheets("Filters")
str2 = shtF.Range("D32").Value
myArray = Get1DArray( shtF.Range("G43:G55") )
With wb.SlicerCaches("Slicer_Month1")
If str2 = "All" Then
.ClearManualFilter
Else
.VisibleSlicerItemsList = myArray
End If
End With
End Sub
'get a 1-D zero-based array of values from a single-row/column
' range of cells
Function Get1DArray(rng As Range)
Dim arr
arr = Application.Transpose(rng.Value)
If rng.Rows.Count = 1 Then arr = Application.Transpose(arr)
'convert from 1-based to zero-based array
Get1DArray = Split(Join(arr, vbNull), vbNull)
End Function
回答by L42
Edit1:
编辑1：
If indeed all your Filtersare in G57 as what Tim pointed out, you can also try below :)
如果确实Filters如蒂姆所指出的那样，您都在 G57 中，您也可以尝试以下:)
Sub AnotherPossibleWay()
Dim str1 As String, str2 As String
Dim myStr As Variant
Dim wb As Workbook
Set wb = Thisworkbook
str1 = Replace(wb.Worksheets("Filters").Range("G57").Value, ",", """,""")
str1 = """" & str1 & """"
myStr = Evaluate("{" & str1 & "}")
str2 = wb.Worksheets("Filters").Range("D32").Value
If str2 = "All" Then
wb.SlicerCaches("Slicer_Month1").ClearManualFilter
Else
wb.SlicerCaches("Slicer_Month1").VisibleSlicerItemsList = myStr
End If
End Sub
Hope this doesn't confuse you in a way.
The main thing is, convert your Range Valueto Arraycorrectly.
希望这不会让您感到困惑。
最重要的是，将您Range Value的Array正确转换为。
回答by Chen
Edited below. Tim is correct. Try this:
编辑如下。蒂姆是对的。尝试这个：
Sub Test()
Dim wb As Workbook
Dim myStr As Variant
Dim str2 As String
Set wb = ThisWorkbook
myStr = Split(wb.Worksheets("Filters").Range("G57").Value, ",")
str2 = wb.Worksheets("filters").Range("D32").Value
If str2 = "All" Then
wb.SlicerCaches("Slicer_Month1").ClearManualFilter
Else
wb.SlicerCaches("Slicer_Month1").VisibleSlicerItemsList = myStr
End If
End Sub
