vba Excel 2010:将字符串数组转换为 int 数组 [英] vba Excel 2010: convert string array into int array

问题描述

Dim myarray(2) as Variant

myarray(0)="3"
myarray(1)="4"
myarray(2)="5"
myarray(3)="9"

我想让它变成

myarray(0)=3
myarray(1)=4
myarray(2)=5
myarray(3)=9

有什么方法可以使它没有循环，只在 1 行中?这适用于 Excel 2010.

What there any way to make it with no loops, just in 1 line? This is for Excel 2010.

我想要这样的东西，但它不起作用:

I want something like this, but it doesn't work:

intArray = Array.ConvertAll(stringArray , Function(str) Int32.Parse(str))

推荐答案

遍历数组并逐一转换值已经足够快了.下面是一段代码，用于显示转换循环相对于执行单元 I/O 的速度:

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:

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

有了这些 I_MAX 和 J_MAX 的值，我们得到了一个 2,000,000–entry 数组，其中读取和写入工作表的速度比转换每个条目慢 4 倍Variant/String 到 Long:

With these values for I_MAX and J_MAX we 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/String to a 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

因此没有必要避免循环，但有一个警告:如果您在循环中执行单元 I/O，性能会变得很糟糕.例如，这里的 r(i, j) = v(i, j) 循环需要整整 100 秒(即 100,000 个滴答声，或转换的 2,000 倍)循环时间)完成.

So there's no real need to avoid loops, with one caveat: If you do the cell I/O in 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.

这篇关于vba Excel 2010:将字符串数组转换为 int 数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！