. Hi
. Sorry for a late follow-up (Struggling with very old Notebook after my main computer has “died”)
.
. I think I almost have the understanding clear now. I will not labor the point again here, I maybe should have read my previous “colorful explanations”, just a couple of which only I could search out:
http://www.mrexcel.com/forum/excel-questions/696820-quotes-visual-basic-applications.html
post #9 here:
http://www.mrexcel.com/forum/excel-...le-references-indicate-range-cells-merge.html
. Not repeating those ideas again here, but in short one important idea was that of coming in an out spreadsheet and VBA worlds, a possibility with Evaluate, wildly used but badly documented and explained. Some conclusions I drew there and possibly forgot myself, was that to aid in getting syntax correct and ease of building up complicated “Strings” for the Evaluate one should at least initially stay with the explicit ides of taking the fundamental form of evaluate as
. Evaluate(“
______”) where
_______ is what one would in a spreadsheet write after the =
. Evaluate(“
______”) is then effectively a
= equivalent.
. But, the less clearly in literature explained possibility is to go into VBA world with
. “
& ------- & “ where
------- is VBA World giving syntax
. Evaluate(“
___“
& ------- & “”
___”)
And importantly
. Evaluate(“
___“
& ------- & “”)
Which can be simplified to
. Evaluate(“
___“
& -------)
. as is most often done. This simplification is valid only at the ( or ) end..
. Working again independently based on the above I came up with this that I think I can understand.
Code:
[color=blue]Sub[/color] arisConcatenating2b()
[color=blue]Dim[/color] wks [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks = ThisWorkbook.Worksheets("Aris") [color=lightgreen]'Sheet Info[/color]
[color=blue]Dim[/color] lr [color=blue]As[/color] Long: [color=blue]Let[/color] lr = wks.Cells(Rows.Count, 1).End(xlUp).Row 'Last Row in Sheet
[color=blue]Dim[/color] rngD [color=blue]As[/color] Range: [color=blue]Set[/color] rngD = wks.Range("D1:D" & lr & "") [color=lightgreen]'Output Range[/color]
rngD.Clear 'Just so I know the conctnating lines work!!
[color=lightgreen]'Concatenate with Evaluate[/color]
rngD.Value = Evaluate("'" & wks.Name & "'!A1:A" & lr & "" & "&"" ; ""&" & "'" & wks.Name & "'!B1:B" & lr & "" & "&"" ; ""&" & "'" & wks.Name & "'!C1:C" & lr & "")
rngD.Value = Evaluate("" & Replace("'" & wks.Name & "'!A1:A#" & "&"" ; ""&" & "'" & wks.Name & "'!B1:B#" & "&"" ; ""&" & "'" & wks.Name & "'!C1:C#", "#", "" & lr & "") & "")
rngD.Value = Evaluate("" & Replace("" & Replace("@A1:A#" & "&"" ; ""&" & "@B1:B#" & "&"" ; ""&" & "@C1:C#", "@", "'" & wks.Name & "'!"), "#", "" & lr & "") & "")
[color=lightgreen]'Final Simplification[/color]
rngD.Value = Evaluate(Replace(Replace("@A1:A#&"" ; ""&@B1:B#&"" ; ""&@C1:C#", "@", "'" & wks.Name & "'!"), "#", lr))
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'arisConcatenating2b[/color]
. I confess on my small screen I have really strained the eyes on this, and I accept will further be extremely difficult in the Thread Code Window to follow. Hope I have not confused by my last “lurking – in” here.
. For developing the Code line by substitution, I find that extra & “” very important. The very experienced eye of course does this as they go along.. Respect!!
Alan
P.s.
......... this eliminates a horrendous number of concatenations and makes constructing the String argument for the Evaluate function much easier.
Agreed!!!!
.........
Note that I use stand in characters for the worksheet name and last row and then replace them with their actual values using two Replace function calls... .
(and would emphasise what you said that VBA actully takes lr and does not put lr in but puts in the actual "Evaluated" value, . Another thing that was confusing my initial attempt at simple substitution to develop the string.)
Words of wisdom again
