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

问题描述

 暗淡myArray的（2）为VariantmyArray的（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中。

我想是这样的，但它不工作：

  intArray = Array.ConvertAll（字符串数组，功能（STR）Int32.Parse（STR））
 

解决方案

通过数组循环和转换值一个接一个是速度不够快。这里是code的一个片段，显示转换循环的速度是相对于执行你的手机I / O：

 私人const的最大电流= 10000
私人常量J_MAX = 200
私人声明函数库的GetTickCountkernel32.dll中（）只要子ticktest（）
    昏暗的蜱长，我只要，J只要
    昏暗V（）为Variant
    暗淡了（）只要VBA整数内部存储为多头
    昏暗 -  [R作为范围    集合R = [A1] .Resize（最大电流，J_MAX）
    REDIM一（1至最大电流，1到J_MAX）    蜱=的GetTickCount
    V =  -  [R
    Debug.Print从范围读出：＆放大器;的GetTickCount  - 蜱    Debug.Print在V值的类型（）：＆放大器;类型名（V（1，1））    蜱=的GetTickCount
    对于i = 1到最大电流
        对于j = 1到J_MAX
            一个（I，J）= V（I，j）的
        下面j
    接下来，我
    Debug.Print与投复制到龙：＆放大器;的GetTickCount  - 蜱    蜱=的GetTickCount
    对于i = 1到最大电流
        对于j = 1到J_MAX
            V（I，J）= CLng函数（V（I，j））后
        下面j
    接下来，我
    Debug.Print就地投地变/长：＆放大器;的GetTickCount  - 蜱    蜱=的GetTickCount
    R = A
    Debug.Print从多头排列写：＆放大器;的GetTickCount  - 蜱    蜱=的GetTickCount
    R = V
    Debug.Print自变量数组写：＆放大器;的GetTickCount  - 蜱    对于i = 1到最大电流
        对于j = 1到J_MAX
            V（I，J）= CStr的（V（I，j））后
        下面j
    接下来，我
    R = V
结束小组
 

使用这些值最大电流和 J_MAX 我们得到2,000,000＆ndash的;进入数组，其中从阅读和写作工作表约4倍比每个条目转换慢变/字符串到龙：

 读取距离范围：749
在V（）值类型：String
采用铸造复制到长：546
就地投地变/长：842
从多头排列写：921
从变量数组写：1248阅读范围：749
在V（）值类型：String
采用铸造复制到长：546
就地投地变/长：827
从多头排列写：905
从变量数组写：1248阅读范围：749
在V（）值类型：String
采用铸造复制到长：531
就地投地变/长：826
从多头排列写：905
从变量数组写：1279
 

因此​​，有没有真正的需要来避免环路，有一点需要注意：如果你的细胞I / O 的一个循环中，性能变得糟糕透顶。这里，例如， R的环（I，J）= V（I，J）需要一个完整的的（这是10万蜱，或2000倍的转换回路的时间）来完成。

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.

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

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

解决方案

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

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

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屋！