VBA 将范围转换为数组并格式化日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8323704/
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
VBA convert range to array and format date
提问by BuZz
I am struggling with a conversion to array in VBA :
我正在努力在 VBA 中转换为数组：
Dim list As Range
Set list = Range(series, series.End(xlDown))
list.Select
Dim table() As Variant
Set table = list.value
My understanding is that valuereturns an array right ? I don't get why VBA tells me that I "can't assign to array".
我的理解是value返回一个数组对吗？我不明白为什么 VBA 告诉我我"can't assign to array".
My listrange looks like that in Excel at the Select, and i aim at having that as an array so that I can format my dates using something like Format(table.Rows(i).Columns(1).value, "yyyy-mm-dd"), looping on my table.
我的list范围在 Excel 中看起来像 ，Select我的目标是将它作为一个数组，以便我可以使用类似的东西来格式化我的日期Format(table.Rows(i).Columns(1).value, "yyyy-mm-dd")，在我的桌子上循环。
02-Sep-09 1.00
18-Sep-09 1.00
16-Oct-09 1.00
20-Nov-09 1.00
18-Dec-09 1.00
19-Mar-10 1.00
18-Jun-10 1.00
By the way, is it possible to modify the table in place ?
顺便说一句，是否可以就地修改表格？
Thanks !
谢谢 ！
回答by Tony Dallimore
There are a number of problem here.
这里有很多问题。
Problem 1
问题一
Set table = list.value
table is not an object so you cannot set it. Try:
table 不是对象，因此您无法设置它。尝试：
table = list.value
Problem 2
问题二
series is a VBA keyword associated with charts. Please pick a name, such as MyWSTable, which means nothing to VBA.
series 是与图表关联的 VBA 关键字。请选择一个名称，例如 MyWSTable，这对 VBA 没有任何意义。
Problem 3
问题三
A worksheet name is not itself a range. Try:
工作表名称本身不是一个范围。尝试：
Dim Table() As Variant
Table = Names("MyWSTable").RefersToRange.Value
Note: you do not need variable list nor do you need to select the range.
注意：您不需要变量列表，也不需要选择范围。
Answer to formatting question
格式问题的答案
The following code will reformat your dates:
以下代码将重新格式化您的日期：
For inxrow = 1 To UBound(Table, 1)
For inxcol = 1 To UBound(Table, 2)
Table(inxrow, 1) = "ddd d mmm yyyyy"
Table(inxrow, 2) = ""
Next
Next
Names("MyWSTable").RefersToRange.NumberFormat = Table
回答by JMax
You can remove your select: list.Select
您可以删除您的选择： list.Select
To be sure you won't get errors when assigning a range to an array, you'd better declare your array as a variant:
为了确保在为数组分配范围时不会出错，最好将数组声明为变体：
Dim table As Variant
table = Range(series, series.End(xlDown)).Value
回答by CaBieberach
That error message is popping because you are using SETto fill the array. Drop the SET and it should load.
该错误消息正在弹出，因为您正在使用SET来填充数组。放下 SET，它应该加载。
You can fil directly the array as follows:
您可以按如下方式直接填充数组：
Dim table() As Variant
table=Range(series, series.End(xlDown))
Omiting the Selectstep makes your code safer and faster.
省略Select步骤使您的代码更安全、更快。
Unfortunately, you can not load the values directly as dates. You will have to loop throu every item of the array and turn them into date.
不幸的是，您不能直接将值加载为日期。您必须遍历数组的每个项目并将它们转换为日期。
回答by phoog
You are going about this incorrectly. You should use the NumberFormatproperty of the range to specify the display format. Something like this:
你这样做是错误的。您应该使用NumberFormat范围的属性来指定显示格式。像这样的东西：
Range("A:A").NumberFormat = "yyyy-mm-dd"
