No Loop through all items in an array assistance

[johnnyL]

I found a line of code that goes through all items in arrays without looping. The line of code writes the result to a range in a worksheet.

I would like to know if there is a way to write the results, separated by commas, to a string.

I would like three strings in total. One string = to the contents of ArrayA, second string = contents of ArrayB, and a third string = to the results currently displayed to a worksheet.

I can do all of that with loops, but I was wondering if there is a way to change the no loop line of code to save to the strings that I mentioned.

Here is the code example that shows the looping version as well as the one line of code that doesn't loop:

Sub NoLoopThroughArrayValues()

    Dim ArrayCount  As Long
    Dim I           As Long
    Dim ArrayA      As Variant
    Dim ArrayB      As Variant
'
    ArrayA = Array(1, 2, 3, 4, 5)
    ArrayB = Array(2, 4, 6, 8, 10)
    ArrayCount = UBound(ArrayA) + 1
'
'
' Loop version for arrays
    For I = 1 To ArrayCount
        Range("D" & I) = ArrayA(I - 1) * ArrayB(I - 1)
    Next
'
'
' Or No Loop version for arrays equivalent
    Range("E1").Resize(ArrayCount) = Evaluate("{" & Join(ArrayA, ";") & "} * {" & Join(ArrayB, ";") & "}")
'
End Sub

 

[Marc L]

​

As my demonstration is a starter and as you can easily replace the arrays within my codelines by yours !​

​

As a reminder [COLUMN(A:E)] is an array so easy to replace with ArrayA …​

​

And according to the need rather than putting the values in an array just directly allocate a string.​

 

[johnnyL]

According to Excel basics :​

Sub Demo1()
  Const C = ","
    Dim S$, T$, R$
        S = Join([COLUMN(A:E)], C)
        T = Join([COLUMN(A:E)*2], C)        ' This is what I am talking about, the '*2'    <---
        R = Join(Evaluate("{" & S & "}*{" & T & "}"), C)
        MsgBox S & vbLf & vbLf & T & vbLf & vbLf & R
End Sub

I have added a comment to you submitted code to refer to what I am talking about.

 

[Marc L]

​

The same : replace my array by your array variable.​

​

As the better is to not use any array variable but just String …​

 

[johnnyL]

@Marc L Sorry, I just can't figure out how to convert your submitted code to a working code.

And by working code, final result should be:

Items in ArrayAString = 1,12,34,8,11
Items in ArrayBString = 7,5,2,9,6
Items in ArraysResultString = 7,60,68,72,66

 

[johnnyL]

And according to the need rather than putting the values in an array just directly allocate a string.​

That may be something to look into, I'll have to check the larger code.

 

[Marc L]

According to post #14 my demonstration revamped :​

Sub Demo1r()
    Dim S$, T$, R$
        S = "1,12,34,8,11"
        T = "7,5,2,9,6"
        R = Join(Evaluate("{" & S & "}*{" & T & "}"), ",")
        MsgBox R
End Sub

 

[johnnyL]

You have strayed off topic. Topic is about arrays that are manipulated into strings. Your last submission has no arrays.

 

[Marc L]

​

'Cause arrays are useless here ! But if you really wanna start with arrays you already got all the necessary with my first demonstration as explained in post #11 …​

 

[johnnyL]

Got another chance to look at this.

According to Excel basics :​

Sub Demo1()
  Const C = ","
    Dim S$, T$, R$
        S = Join([COLUMN(A:E)], C)
        T = Join([COLUMN(A:E)*2], C)
       [B] R = Join(Evaluate("{" & S & "}*{" & T & "}"), C)[/B]
        MsgBox S & vbLf & vbLf & T & vbLf & vbLf & R
End Sub

Perhaps :

MsgBox Join(Application.Transpose(Evaluate("{" & Join(ArrayA, ";") & "} * {" & Join(ArrayB, ";") & "}")), ",")

Transpose is useless, see post #7 …​

I don't think 'useless' is a friendly term, but I have eliminated the 'transpose' part of the code.

ArraysResultString = Join(Application.Transpose(Evaluate("{" & Join(ArrayA, ";") & "} * {" & Join(ArrayB, ";") & "}")), ",")    ' Save ResultArray to string

Could be written as:

    ArraysResultString = Join(Evaluate("{" & ArrayAString & "}*{" & ArrayBString & "}"), ",")                   ' Save ResultArray to string