vba 一次将数组的所有元素从字符串转换为双倍VBA

Converting all elements of an array from string to double at once VBA

提问by SomeCodee

Is there a way to convert all elements of an array from string to double without having to convert each element one by one. I want to avoid to use a loop if possible. I'd like to know that for VBA not VB.Net

有没有一种方法可以将数组的所有元素从字符串转换为双精度值，而不必一个一个地转换每个元素。如果可能，我想避免使用循环。我想知道对于 VBA 而不是 VB.Net

回答by Ben McCormack

I'm trying to think conceptually how it is possible, at any layer of abstraction, to "do something" on each(the keyword here is each) item in an array without processing it one at a time.

我试图从概念上思考如何在任何抽象层上对数组中的每个（这里的关键字是each）项目“做某事”，而不是一次处理一个。

At the lowest levels of abstraction concerning a single CPU, each item in an array is always going to be processed one at a time. The CPU can't take a collection and magically transform eachelement without iteratingthrough eachitem in the collection. The words iteration(and consequently, loop) and eachenjoy each other's company very much.

在关于单个 CPU 的最低抽象级别上，数组中的每个项目总是一次处理一个。CPU不能采取收集和神奇变换的每个元件，而不迭代通过每一个集合中的项目。单词迭代（因此，循环）和每个人都非常享受彼此的陪伴。

Now, is it possible, at higher layers of abstraction, to present to the programmer a method/function/procedure that lookslike it's acting on an entire collection? Yes, it's very possible. LINQ (in .NET) does this a lot. However, all LINQ does is provide a way for a programmer to act on eachitem in a collection using only one statement.

现在，是否有可能在更高的抽象层向程序员呈现一个方法/函数/过程，它看起来像是作用于整个集合？是的，很有可能。LINQ（在 .NET 中）做了很多这件事。但是，LINQ 所做的只是为程序员提供了一种仅使用一条语句对集合中的每一项进行操作的方法。

Even if VBA had a way to convert the elements in an array from one type to another (which I don't believe it does), at somelevel of abstraction, the program will have to iteratethrough eachitem in the list to perform the change.

即使VBA有办法在一个阵列到另一个元素从一种类型（我不相信它），在转换一些抽象层次，程序将不得不迭代通过每一个项目在执行列表改变。

That being said, I think you're stuck doing a loop. The best thing you could do is wrap this functionality within a Function. Here's a sample function with some test code:

话虽如此，我认为你被困在循环中。您能做的最好的事情是将此功能包装在一个函数中。这是一个带有一些测试代码的示例函数：

Function ConvertArray(arrStr() As String) As Double()
    Dim strS As String
    Dim intL As Integer
    Dim intU As Integer
    Dim intCounter As Integer
    Dim intLen As Integer
    Dim arrDbl() As Double
    intL = LBound(arrStr)
    intU = UBound(arrStr)
    ReDim arrDbl(intL To intU)
    intCounter = intL
    Do While intCounter <= UBound(arrDbl)
        arrDbl(intCounter) = CDbl(arrStr(intCounter))
        intCounter = intCounter + 1
    Loop

    ConvertArray = arrDbl

End Function

Sub Test()
    Dim strS(0 To 2) As String
    Dim dblD() As Double
    Dim dbl As Variant
    strS(0) = "15.5"
    strS(1) = "12"
    strS(2) = "4.543"

    dblD = ConvertArray(strS)

    For Each dbl In dblD
        Debug.Print dbl
    Next dbl

End Sub

回答by Oorang

In some scenarios you could use CopyMem to move data between arrays of different types. (For instance Strings to Integer Arrays.) But this won't work with String and Doubles as equivilant values are stored differently at a byte level. So a String Binary "1" is not the same set of 1s and 0s as Double Binary and vice versa.

在某些情况下，您可以使用 CopyMem 在不同类型的数组之间移动数据。（例如字符串到整数数组。）但这不适用于字符串和双精度值，因为等效值在字节级别的存储方式不同。因此，字符串二进制“1”与双二进制不同的 1 和 0 集不同，反之亦然。

Generally speaking you will need to do it with a conversion function:

一般来说，您需要使用转换函数来完成：

Public Sub Test()
    Const clUprBnd As Long = 9&
    Dim asTest(clUprBnd) As String
    Dim adTest() As Double
    Dim lIndx As Long
    For lIndx = 0& To clUprBnd
        asTest(lIndx) = CStr(lIndx)
    Next
    adTest = StringArrayToDoubleArray(asTest)
    MsgBox adTest(5)
End Sub

Private Function StringArrayToDoubleArray(ByRef values() As String) As Double()
    Dim lIndx As Long, lLwrBnd As Long, lUprBnd As Long
    Dim adRtnVals() As Double
    lLwrBnd = LBound(values)
    lUprBnd = UBound(values)
    ReDim adRtnVals(lLwrBnd To lUprBnd) As Double
    For lIndx = lLwrBnd To lUprBnd
        adRtnVals(lIndx) = CDbl(values(lIndx))
    Next
    StringArrayToDoubleArray = adRtnVals
End Function

回答by jtolle

The answer to that exact question is "no". There is no built in VBA operator that works on typed arrays like that.

这个确切问题的答案是“不”。没有内置的 VBA 运算符可以处理这样的类型化数组。

However, you can have an array of variants, and that can contain elements that are strings or doubles (or other things of course). So if your concern is being able to pass arrays around or use individual elements without having to do explicit conversions, you can do something like:

但是，您可以拥有一个变体数组，并且可以包含字符串或双精度（当然也可以是其他元素）的元素。因此，如果您关心的是能够在不必进行显式转换的情况下传递数组或使用单个元素，您可以执行以下操作：

Public Sub passesStuff()
    Call expectsNumericStuff(Array("1", "2", "3"))
    Call expectsNumericStuff(Array(1, 2, 3))
End Sub

Public Sub expectsNumericStuff(arr)
    Debug.Assert IsArray(arr)
    Debug.Assert IsNumeric(arr(1))
    Debug.Print arr(1) * 42
End Sub

Obviously all of the advantages and disadvantages of variants then apply, and should be kept in mind.

显然，变体的所有优点和缺点都适用，并且应该牢记在心。