Concatenating your Balls

**DocAElstein** · 08-22-2014, 03:37 AM

Originally Posted by Excel Fox

OK. The conditional evaluation returns an array of values, whereas a direct non-conditional evaluation would return a single value 'in some cases'. There could be a valid theory behind that, but that is not something I am privy to. It's probably the way EVALUATE function works. So anyway, to understand that difference a bit more clearly, you can test the following to codes. Both are actually trying to do the same time, but since the evaluate function 'in some cases' only returns a single value in the second code, the entire output in A1:A10 becomes the same. Having said that, in this case below, both the codes seem to be working fine in some systems, and not in some other systems. I will get back to you with a suitable example.

Code:

Range("A1:A10") = Evaluate("IF(1," & Range("A1:z10").Columns(2).Address & "&"" - ""&" & Range("A1:z10").Columns(3).Address & "&"" - ""&" & Range("A1:z10").Columns(4).Address & ")")

Code:

Range("A1:A10") = Evaluate(Range("A1:z10").Columns(2).Address & "&"" - ""&" & Range("A1:z10").Columns(3).Address & "&"" - ""&" & Range("A1:z10").Columns(4).Address)

And by the way, you don't neces.......................

Hi „Fox“,
. I am working through the code again from Rick that I did not understand, getting good clued up on the exact syntax in complex Evaluation codes (A Tricky one!!!), and working through all the info you sent. I am almost there and expect to post here soon what I think would be a useful reply to this Thread. I noticed something along the way, which I do not quite understand. I applied it to your codes, (or rather some very similar) and got similar results. So while I am finishing the main reply I thought I might just post that as something to be going on with in the meantime……

. Firstly , just
. a) for clarity in the thread response editor, and
. b) so that it suits the example I am working with in regard to Rick’s code,
. – I have slightly modified the exact ranges to suit this Table (where column H is the output column and the other yellow highlighted area is my input data.

. Correspondingly your codes, modified by me, look like this:

Code:

Sub Fox1lmr()
Range("H2:H10") = Evaluate("IF(1," & Range("A2:D10").Columns(2).Address & "&"" - ""&" & Range("A2:D10").Columns(3).Address & "&"" - ""&" & Range("A2:D10").Columns(4).Address & ")")
End Sub
Sub Fox2lmr()
Range("H2:H10") = Evaluate(Range("A2:D10").Columns(2).Address & "&"" - ""&" & Range("A2:D10").Columns(3).Address & "&"" - ""&" & Range("A2:D10").Columns(4).Address)
End Sub

. Either of these codes gives the results shown in the table above.

If I modify your codes to this

Code:

Sub Fox1lm()
Range("H2:H10") = Evaluate("IF(1," & Range("A2:D10").Columns(2).Address & "&"" - ""&" & Range("A2:D10").Columns(3).Address & ")")
End Sub
Sub Fox2lm()
Range("H2:H10") = Evaluate(Range("A2:D10").Columns(2).Address & "&"" - ""&" & Range("A2:D10").Columns(3).Address)
End Sub

. then as expected I only get the l and m values. But again both Codes work.

If I modify the codes again to this

Code:

Sub Fox1l()
Range("H2:H10") = Evaluate("IF(1," & Range("A2:D10").Columns(2).Address & ")")
End Sub
Sub Fox2l()
Range("H2:H10") = Evaluate(Range("A2:D10").Columns(2).Address)
End Sub

. then I expect to get only the l values . BUT in fact after running both codes they both give me blank cells in column G.

. However this code

Code:

Sub Fox1lRow()
Range("H2:H10") = Evaluate("IF(ROW()," & Range("A2:D10").Columns(2).Address & ")")
End Sub

. Does give me the expected results.

. I hope to explain in my final reply that Ricks code does not require the Row() as I had initially thought. With the above results along with some very careful Syntax considerations I hope to be able to prove and explain why that is. (In the second reference you gave me I think they may have slightly “glossed over” a full explanation of this by being careful exactly what ranges they were using as well as changing them to suit as they went along!!!)

Alan

P.s. As I did some test in your Tests just now in your test forum I hit on some space / size limitation which I have not experienced in MrExcel? Can I possibly have my space, or does that come automatically when I become a “Senior” member or whatever the next bit up is??

**DocAElstein** · 08-22-2014, 03:40 AM

Try the Table again

**DocAElstein** · 08-22-2014, 03:46 AM

**DocAElstein** · 08-23-2014, 03:34 AM

Originally Posted by DocAElstein

............ Correspondingly your codes, modified by me, look like this
..................................

Today I tried your exact Codes. Got the same results

**DocAElstein** · 08-23-2014, 03:47 AM

Re: EvaluatingConcatenating in VBA (and the Row() stuff)!

. Sorry if I am confusingly / mixing up Themesin this Thread!

. I will try to get some of my thoughtsstraight on this. (I might be repeating wot The “Fox” said above, but this is now how it is coming out ofmy brain now that I am beginning to understand it!). I start with a bit ofbasic Evaluate syntax as that can get a bit confusing if you are not careful!!
. We probably all know that, simplified said,the Application.Evaluate Method in VBA gives a way of using the Normal Excelfunctions in VBA. Again very simplifiedthe Evaluate in VBA sort of does wot the = does in a Normal ExcelWorksheet(Spreadsheet). So, for a randomexample, to get the value of B2 into Cell H2:
. In Excel, in cell H2, one would type
=B2
. In VBA code one would write
Sub TestVBA1()

Range("H2").Value= Range("B2").Value

End Sub

( Note in passing : This code isequivalent, that is to say VBA reads it the same:
Sub TestVBA1()
Range("H2").Value =Range("$B$2").Value
End Sub )

… It is veryimportant to note the exact syntax, as it is easy in more complicated stuff toget confused with the number of “
……You type this Evaluate(“ “), and then in the space you type exactlywhat you would in the Spreadsheet cell but omitting the =
………In theparticular code that was giving me trouble to understand, this basic code wastaken a bit further, that is to say the Address of the cell B2 was got by the VBA code Property .Address , in our case this would look like this
Range(“B2”).Address
This code can bewritten within the Evaluate Function. The syntax for this is “ & to get into acode bit and then & “ to get out ofit!, if that makes sense!!
. So the codewould be

SubTestEvaluateVBA1()
Range("H2")= Evaluate("" & Range("B2").Address & "")
End Sub

( Note in passing : This code isequivalent, that is to say VBA reads it the same:
Sub TestEvaluateVBA1()
Range("H2") = Evaluate(" " &Range("B2").Address & " ")
End Sub
This is because nothing is happening in thiscase or read by VBA between the pair of quotes at the start and the end!, ifthat makes sense!?)

. Staying for now with the Syntax Theme, beforeI go on to my main problem. If I wish to get involved now with another simpleexample that involves three cells, B2 and C2 And D2 the values of which I wishto put in that one cell H2, then in an Excel spreadsheet the formula syntax wemay know would be
=B2 & C2& D2
. We must be very careful now with syntax andtry to think about how Excel and VBA are thinking!. We have now 5 things beingdone after the =
. The evaluate function in VBA does allow youto “evaluate” more than one thing within a single evaluate, but just to confuseus, the & is used to link separate things! We need then our 5things within “ “ and linked by a & . Just to help us get a little less confusedlets use & for the VBA and & for the excel Spreadsheet!

…TO NEXT REPLYBECAUSE OF SIZE CONSTRAINTS HERE!

**DocAElstein** · 08-23-2014, 03:49 AM

So theEquivalent code would be

Sub TestEvaluaten1_1n2n3()
Range("H2")= Evaluate("B2" & "&" & "C2" & "&" & "D2")
End Sub

Typically we wouldprefer to re-write the Excel Spreadsheet formula to make things a bit easier tosee:
=B2&" "&C2&" "&D2 or =B2&" - "&C2&" - "&D2 etc.

For theequivalent code we must again think like wot VBA is! After the "&" bit VBA iseffectively thinking it is in an excel Spread sheet and will except our " " , but then it needs a following "&" bit to get back out to the point where it went in.So the code is

SubTestEvaluate2_n1n2n3()
Range("H2")= Evaluate("B2" & "&"" ""&" & "C2" & "&"" ""&" & "D2")
End Sub

And to penultimately complete this bit of preliminary syntax Stuff, using that example ofa combination of Spreadsheet Functions and VBA we have correspondingly:

Sub TestEvaluateVBA1_n1n2n3()
Range("H2")= Evaluate(" " &Range("B2").Address & " " & "&"" ""&" & " " & Range("C2").Address & " " & "&"" ""&" & " " & Range("D2").Address & " ")
End Sub

I said Pen ultimately as if one carefully looks at the last Code, and againtries to think how VBA thinks, in this particular code we go out of VBA codewith & " " & inorder to use "&" , thenafter our Excel spreadsheet space " " we go back in with & " "& . This is perfectly OK and correct. However specifically here inthis example we have two blue & andeffectively ending up where we started. That takes a bit of thinking about. Butonce you get it you realize that & " " & can bereplaced in this instance with & or&
. So an Ultimate code would be
Sub TestEvaluateVBA1b_n1n2n3()
Range("H2") =Evaluate(" "&Range("B2").Address & "&"" ""&" &Range("C2").Address & "&"" ""&" &Range("D2").Address & "")
End Sub
. I personally would stick with thePenultimate. It helps me understand wot is going on.

…TO NEXT REPLYBECAUSE OF SIZE CONSTRAINTS HERE!

**DocAElstein** · 08-23-2014, 03:51 AM

………………
Finally gettingback to the Concatenating stuff and my original Problem:

(. Actually Iwas concatenating eventually above and used two methods: The spreadsheet methodand the VBA code method!)

. The Penultimate line could be (as it is inthe actual Code giving me problems to understand) re-written to includemultiple ranges (And in the practice is often sowritten because of speed advantages over a loop method in repeated calculations – but hat is a separate Theme notdirectly related to my problem)

SubTestEvaluateVBA3_n1n2n3()
DimRangeH3H4 As Range, RangeB3B4 As Range,RangeC3C4 As Range, RangeD3D4 As Range
Set RangeH3H4 =Range("H3:H4")
Set RangeB3B4 =Range("B3:B4")
Set RangeC3C4 =Range("C3:C4")
Set RangeD3D4 =Range("D3:D4")
RangeH3H4 =Evaluate(" " &RangeB3B4.Address & " "& "&"" ""&" & " " & RangeC3C4.Address & "" &"&"" ""&" & "" & RangeD3D4.Address &"")
End Sub
The above codeworks .

. It works for any number of concatenations. Going backwards forexample to just 2 numbers the code would be

SubTestEvaluateVBA3_n1n2()
DimRangeH3H4 As Range, RangeB3B4 As Range,RangeC3C4 As Range
Set RangeH3H4 =Range("H3:H4")
Set RangeB3B4 =Range("B3:B4")
Set RangeC3C4 =Range("C3:C4")
RangeH3H4 =Evaluate(" " &RangeB3B4.Address & " "& "&"" ""&" & " " & RangeC3C4.Address & "")
End Sub
The above code workstoo!

The code for onesimple concatenation is

SubTestEvaluateVBA3_n1()
DimRangeH3H4 As Range, RangeB3B4 As Range
Set RangeH3H4 =Range("H3:H4")
Set RangeB3B4 =Range("B3:B4")
RangeH3H4 =Evaluate(" " &RangeB3B4.Address & "")
End Sub

BUT IT DOES NOT WORK?!?!

…TO NEXT REPLYBECAUSE OF SIZE CONSTRAINTS HERE!

**DocAElstein** · 08-23-2014, 03:53 AM

…TO NEXT REPLYBECAUSE OF SIZE CONSTRAINTS HERE!

. Now we hit on a really tricky problem…I have rackedmy brain and tried various example files and there appears to be no consistentanswer. The nearest to date is probably

Originally Posted by ExcelFox

……………The conditional evaluation returns an array of values, whereas adirect non-conditional evaluation would return a single value 'in some cases'.There could be a valid theory behind that, but that is not something I am privyto……………….

. I get the feeling nobody is "privy" to this theory. There are some ideasalong the lines…… " ….The reason is that if the Excel functionused in Evaluate does not accept an array, the Evaluate function will not returnan array…. " That seems just a general not always correct answer.
. For example, if the last Code was concerned with simple numbers,then a modification to this code results in it working. The modification issimply to multiply the nuber by 1!!
. Here the code:
SubTestEvaluateVBA3_n1_x1()
DimRangeH3H4 As Range, RangeB3B4 As Range
Set RangeH3H4 =Range("H3:H4")
Set RangeB3B4 =Range("B3:B4")
RangeH3H4 =Evaluate(" " &RangeB3B4.Address & "" & "*1")
End Sub

…..It works!!

. Similarly, concatenating with more complexcalculations I found not to work!?!

. Without making this thread even moreunreadable because of its length, I will only briefly touch on the theory whichsounds half believable despite not explaining all the results I have. This sayscrudely translated too everyday language something like:- Adding an IF ROW() bit ( or IF Column() bit for that matter) to theline will return a Boolean 1 which somehow forces it to work. If in ourexample we should theoretically use Row(B3:B4) but in the practice ROW() ( orColumn() ) seems to work just as well

. You must be careful again with the Syntax change for the IF bit:

Original Codeline:
= Evaluate( Original_Bit_Within_Evaluate_Brackets )

Modified codelineWith IF bit:
= Evaluate( "IF(Row()," & Original_Bit_Within_Evaluate_Brackets &")" )

. So maybe I am sort of partly there with ananswer to my original question in a long round about way, )even if along theway I almost forgot what it was!!).
. The code line originally giving me problemswas this:

FinalTableFirstColumnRange.Offset(0,1) = Evaluate("IF(ROW()," & SourceTableRange.Columns(2).Address& "&"" - ""&" &SourceTableRange.Columns(3).Address & "&"" -""&" & SourceTableRange.Columns(4).Address &")")

. Rewritten with full (The "Penultimate type") Syntax and highlighting the IF bit looks like this,

. FinalTableFirstColumnRange.Offset(0, 1) = Evaluate("IF(Row()," & " " & SourceTableRange.Columns(2).Address& " " & "&"" - ""&" & " " & SourceTableRange.Columns(3).Address & " " & "&"" - ""&" & " " & SourceTableRange.Columns(4).Address & " " &")")

. As it is a "simple" concatenating line the IF bit can be removed to simplify the code like this

. FinalTableFirstColumnRange.Offset(0, 1) = Evaluate(" " & SourceTableRange.Columns(2).Address & " " & "&"" - ""&" & " " & SourceTableRange.Columns(3).Address & " " & "&"" - ""&" & " " & SourceTableRange.Columns(4).Address & " ")

…TO NEXT REPLYBECAUSE OF SIZE CONSTRAINTS HERE!

**DocAElstein** · 08-23-2014, 03:54 AM

. The final codeworks. Rick probably knew that. But because of the uncertainties in if when wotwith this Evaluation thing it is probably best just always to leave it in to beon the safe side. For nowthat is my answer!!!!

. Sorry to have rambled a bit. But it helped meto answer my very first Question about one line in a code from Rick. Sorry ifit confuses any other “viewers”, but maybe for any one “concatenating” in VBAor looking for a method of getting their Multiple columns in to single columnin may be a worthwhile contribution. Lets rely on the “Power of Google” to get such a person here!?

Alan Elston
Bavaria

**DocAElstein** · 09-20-2014, 03:31 AM

Hi,

VBA Evaluate Range and VLOOKUP

(This is a copy of the Thread here: P.s. I will also post this Thread Here: VBA Evaluate Range and VLOOKUP)

As a beginner I may be going a bit above my head!!

After studying and participating at some considerable length in Threads and links to do with using the Evaluate function to speed things up, I thought I understood it. Here are some of those links and Threads. (www.excelfox.com/forum/f22/concatenating-balls-1891/ VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate | Useful Gyaan )

. So … the following simplified example File (XL2007 .xlsm)
https://app.box.com/s/pr78mhna00advvhsrmvi
has a Spreadsheet LEFT Function and a Spreadsheet VLOOKUP Function

The results look good! (That is to say wot I expect!) :-