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

Question

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

Answer 1

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

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.

Answer 2

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.

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 each element without iterating through each item in the collection. The words iteration (and consequently, loop) and each enjoy each other's company very much.

Now, is it possible, at higher layers of abstraction, to present to the programmer a method/function/procedure that looks like 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 each item in a collection using only one statement.

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 some level of abstraction, the program will have to iterate through each item in the list to perform the change.

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)
    intLen = intU - intL
    ReDim arrDbl(intLen)
    intCounter = 0
    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

Answer 3

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.

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