vba Excel 2010：将字符串数组转换为 int 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18091413/
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 Excel 2010: convert string array into int array
提问by Haradzieniec
Dim myarray(2) as Variant
myarray(0)="3"
myarray(1)="4"
myarray(2)="5"
myarray(3)="9"
I want it to become
我想让它变成
myarray(0)=3
myarray(1)=4
myarray(2)=5
myarray(3)=9
What there any way to make it with no loops, just in 1 line? This is for Excel 2010.
有什么方法可以使它没有循环，只在 1 行中？这适用于 Excel 2010。
I want something like this, but it doesn't work:
我想要这样的东西，但它不起作用：
intArray = Array.ConvertAll(stringArray , Function(str) Int32.Parse(str))
回答by Chel
Looping through the array and converting the values one-by-one is fast enough. Here's a snippet of code to show how fast a conversion loop is relative to performing your cell I/O:
循环遍历数组并一一转换值已经足够快了。这是一段代码，用于显示转换循环相对于执行单元 I/O 的速度：
Private Const I_MAX = 10000
Private Const J_MAX = 200
Private Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub ticktest()
Dim ticks As Long, i As Long, j As Long
Dim v() As Variant
Dim a() As Long 'VBA integers are internally stored as longs
Dim r As Range
Set r = [A1].Resize(I_MAX, J_MAX)
ReDim a(1 To I_MAX, 1 To J_MAX)
ticks = GetTickCount
v = r
Debug.Print "Read from range: " & GetTickCount - ticks
Debug.Print "Type of values in v(): " & TypeName(v(1, 1))
ticks = GetTickCount
For i = 1 To I_MAX
For j = 1 To J_MAX
a(i, j) = v(i, j)
Next j
Next i
Debug.Print "Copy with cast to Long: " & GetTickCount - ticks
ticks = GetTickCount
For i = 1 To I_MAX
For j = 1 To J_MAX
v(i, j) = CLng(v(i, j))
Next j
Next i
Debug.Print "In-place cast to Variant/Long: " & GetTickCount - ticks
ticks = GetTickCount
r = a
Debug.Print "Write from long array: " & GetTickCount - ticks
ticks = GetTickCount
r = v
Debug.Print "Write from variant array: " & GetTickCount - ticks
For i = 1 To I_MAX
For j = 1 To J_MAX
v(i, j) = CStr(v(i, j))
Next j
Next i
r = v
End Sub
With these values for I_MAXand J_MAXwe get a 2,000,000–entry array, where reading from and writing to the worksheet is about 4x slower than converting each entry from a Variant/Stringto a Long:
使用I_MAXand 的这些值，J_MAX我们得到一个 2,000,000 条目的数组，其中读取和写入工作表的速度比将每个条目从 a 转换Variant/String为 a慢约 4 倍Long：
Read from range: 749
Type of values in v(): String
Copy with cast to Long: 546
In-place cast to Variant/Long: 842
Write from long array: 921
Write from variant array: 1248
Read from range: 749
Type of values in v(): String
Copy with cast to Long: 546
In-place cast to Variant/Long: 827
Write from long array: 905
Write from variant array: 1248
Read from range: 749
Type of values in v(): String
Copy with cast to Long: 531
In-place cast to Variant/Long: 826
Write from long array: 905
Write from variant array: 1279
So there's no real need to avoid loops, with one caveat: If you do the cell I/Oin a loop, performance becomes abysmal. Here, for example, a loop of r(i, j) = v(i, j)takes a full 100 seconds(that's 100,000 ticks, or 2,000x the conversion loop time) to complete.
因此，没有真正需要避免循环，但有一个警告：如果您在循环中执行单元 I/O，则性能会变得很糟糕。例如，这里的循环r(i, j) = v(i, j)需要整整100 秒（即 100,000 个滴答声，或转换循环时间的 2,000 倍）才能完成。
