Special concatenation

[snb]

Stimmt doch, meinte ich auch: Office-Loesung.de - Forum zu Microsoft Excel, Outlook, Word, PowerPoint, Access

[DocAElstein]

Stimmt doch, meinte ich auch: Office-Loesung.de - Forum zu Microsoft Excel, Outlook, Word, PowerPoint, Access

ich habe keine Ahnung was da soll ist / ist …. Ich komme Lieder nicht mit, stehe auf die Leiter denk ich…was sol ,dann das ….. Aktuelles Datum und Uhrzeit: 03. Mai 2014, 13:06

scheinbar ist das Forum Zeit lange abgestellt / Zu / geschlossen, Tote Hosen ??




oder meinen sie das:
http://www.office-loesung.de/p/

[snb]

diese:

Office Forum • Forum anzeigen - Excel Forum (provisorisch)

[DocAElstein]

diese:

Office Forum • Forum anzeigen - Excel Forum (provisorisch)

hat schon bemerkt, Danke.

[DocAElstein]

Hi...
.. i have a feeling I will be looking once a year at building strings for Evaluate and other Excel and VBA things, and trying to relearn. It seems that the mastering of quotes in Excel and VBA is the key to the understanding. Once you master that, the VBA Evaluate Method seems to become very useful.

.......To 'walk' inyour approach:...

.. I think I need to go through at a Snail's Pace….
. Maybe a small follow up here could be appropriate to share my thoughts. This Thread did set off my yearly thoughts again….( and to keep it relevant: see****** right at the end, for yet another 2 Codes to meet the OP's Post # 1 original request) ...
.......

. 1) Basics

. It helps at the outset, I feel to try and maintain a colour convention where possible to try and distinguish betweenExcel Spreadsheet andVBA "Worlds" as I call them..
.. ( For code lines here I will drop my irritating habit of explicitly referencing all Ranges so , In a normal module, an unqualified Range call equates to Application.Range, which in most cases is the ActiveSheet.Range. Or alternatively put the code in the sheet module of the sheet to which it relates, and that will work I think that an unqualified Range call equates to that sheetAnd I will mostly let Let out for snb_..)
....
. Fundamentally a quote seems to inform / prepare / warnExcel Spreadsheet and VBA that a string is coming.

Hence we have

Dim v As Variant
Let v = "3" ' Results in a Variant variable containing astring value "3"
Let v = 3 ' Results in a Variant variable containing a Long Number 3 ( actually an Integer ? )
...

. Coming to understanding the Evaluate, whichhas become a strange obsession with me. A very basic, common , but not reallycorrect IMO explanation is that Evaluate allows VBA to do whatever one can doby writing in some ( string ) of characters, numbers, mathematical operators, Functionsand the like within aExcelSpreadsheetcell.
. This leads to a syntax
= Evaluate("___________")
. often verbally given as Evaluate ( Stringexpression )
. where string expression is what one writes ina cell . At the outset i like to try and stay explicit, not missing bits outwhich VBA often allows you to do as a sort of recognised shorthand..
. So the classic example is
Excel Spreadsheet =A1 -------- VBA =Evaluate ( "= A1 " )

. I have a modified definition, and why not, ExcelandVBA seems so badly documented that no one really "knows" for sure anymore:what goes on Evaluate encompasses a lot of what Excel Spreadsheetand VBA [COLOR=#000000]is about and if it was not for the annoying 255character string limit[FONT="Verdana"][SIZE=3] ( . 4 ) ( IMPORTANT : that is the string it actuallysees

[DocAElstein]

. Coming back to the relevance to this Thread.. Understanding quotes in VBA ( ( .1 ) ) seems to play a big role in understanding wot's going on here..
............One last useful observation here: One can of course do just VBA . Following on from that last code line, a classic example given is using VBA to get the=Evaluate ( " = A1 ") . The corresponding mainly or exclusively VBA Code Evaluate would be
Excel Spreadsheet =A1 = Evaluate("=" &ws.Range("A1").Address& "")'Explicit Version
Excel Spreadsheet =A1 = Evaluate("" &ws.Range("A1").Address & "")'Implicit Default
A characteristic of theVBA Evaluate Method, ( or rather The building of its String Argument ), which can be helpful in particular when trying to understand the tricky Theme of the different type of quotes in a long complicated string Argument, is that the pair of effectively empty VBA Evaluate quote pairsused in conjunction with an ampersand to link The Spreadsheet"world" to theVBA CodeWorld can have any amount of spaces between them with no effect on the code line.
. So that last line May be rewritten
Let ws.Range("B2").Value = Evaluate("big empty space" & ws.Range("A1").Address& "big empty space") '
. As long as there is something in the total string that can be evaluated this will not error. The explanation would be that within theVBA Evaluate " "pair nothing is being evaluated. It is a dead space.
There will be situations where this can help later to identify which quotes are which, or to which "world" they belong**................ so further
...................................

. To try and maintain some relevance to this Thread, let me say i have a simplified start point in A1 and in attempt to get just the start of the Final output ( originally intended in sheet2 ) in the same sheet in I1
Using Excel 2007

Row\Col	A	B	C	D	E	F	G	H	I
1	1								1;"

EvaluateQuotes

. The code might be useful to follow stepping through in F8. I put the code Here, as well as in the last post here of these rambling Posts...
Post 4
http://www.excelfox.com/forum/f17/appendix-thread-codes-for-other-threads-html-tables-etc-2056/#post9507

( and A file with most of the codes in I also upload.. )

...........................

[DocAElstein]

. 2) Some Detailed code line anylysis

. VBA will always try fundamentally to pair up quotes and see a string within it. And in the simplest Evaluate example given we had then the basic definition of the Evaluate Method and a simple string within the(“___________”) looking like that in a spreadsheet cell is what the Evaluate Method is happy to “Evaluate”.
. The further definition by me proposed requires then full understanding of gluing the combination of things together along witht he tricky allowance for quotes. I have tackled it before ( ( .5 ) ) .....
.. At the end of the day , or thread., I want to produce a string, ( by wotever combination ofSpreadsheet and &and & andVBA code) which Excel will see as it does see what is written in a cell.

. This simplest example as in the references at the end of this Thread I have done many times now:

.10 - Excel Spreadsheeti in cell H1 =A1 &A1 -------- VBA =Evaluate ("= A1"&"&"& "A1")' Result in H1 is 11



And likewise, for a space between, ( or rather for the OP’s case a ; between , or at the end

. 20 - This was found not to work: VBA =Evaluate(" = A1" & "&"" & ";" & ""&" & " A1 ")In fact it gave a syntax error. It appears a conflict with the generalEvaluate ( String expression ) – a sort ofunmatched pair of quotes..........

then further , I confess slightly by trial and error, this did work...


. 30 - ExcelSpreadsheet =A1& ";"&A1------------VBA = Evaluate("=A1" &"&"";""&"& "A1"") Result1;1
. 40 - Excel Spreadsheet =A1& ";"------------VBA = Evaluate("=A1" &"&"";""") Result 1;


The start of the whole understanding for me comes with consideration of the intimacy the"" in"&"";""&" . Immediately after "&"seems to be still in thespreadsheet “world “ and sees the “or rather the total“;“ Any**Attempt to break that pair with a space between errors. Indeed Debug.Print in the Immediate Window shows me=A1&";"&A1 or =A1&";" . However this only works with the matched end of "" and missing out one" puts a spanner in the works again........
. However my reasoning was / is that I can break in and out of VBA World at any text Point as usual with" & "

. 50 - Excel Spreadsheet =A1& ";"------------VBA = Evaluate("=A1" &"&"";" & """")

And this is fine as an alternative Evaluate string. Result in cell I1is 1;

[DocAElstein]

The next few explanations I confess I do not understand my own logic but which never the less seems valid?!?
Somehow the logic so far suggested to me that I could happily introduce a"" ""grouping at some Points such as

. 60 ------------------------------------------- VBA = Evaluate("=A1" & "&"";"" """ & """")
. 70 (or this---------------------------------------- VBA = Evaluate("=A1" & "&"";"";""" & """") )
.It seemed ok with this syntaxly but errored ( Spreadsheet =A1&";" "" or =A1&";" "")


This however was OK syntaxly and did not error, giving a last quote in the final output
. 80 ------------------------------------------- VBA = Evaluate("=A1" & "&"";""""" & """")

.. So some warped thinking by me was that I have with this new 4 Spreadsheet grouping"""" introduced a pair of expectable Going in when I am already in and out again ofSpreadsheetworld. But I have done nothing and have no completed String. Maybe I have fooled VBA into bringing up a " , the basic thing it does to define a string, but then abruptly stopped it , not completing a string, having no string. It gets "hung up" at the " point and givers me a " .
.
. well surprise, surprise
. 90-- VBA =Evaluate("=A1" &"&"";""" & """" & """")
........does not error .. and Result 1;" !!!!!!!!!!!!!!
.... Further more Debug.Print ( which in my code i used all along to get "what VBA sees" - so what isSpreadsheetworld)
GivesSpreadsheet=A1&";"""
...............................

[DocAElstein]

. 3 ) Some conclusions “Quotes in VBA” ...

. Taking the lastcode lines, - I may happily remove the two extra" & "which as discussedare just linking text elements.. to give simplified finally

. 100 .-- VBA =Evaluate("=A1" &"&"";""""""")

.. my final solution isI have ( I think ) some theoretical argument given to a convention to produce aquote in a VBA Evaluate string, as well as seeing a similar logic for theSpreadsheet equivalent, that is to say for theSpreadsheet an extra "" to what one would usually write : Similarargument again : once in the String you write in the cell for example
=";" ( Result in cell is ; )
Or for an extra “ youwrite
=""";" ( Result in cell is "; )

SummaryAdding a Quote After a text bit that should beoutputted as Final result:

. 3 a) VBA Evaluate ( Wot you write in the String for the Evaluate ):
.
. ""TextBit""""""( The last" is removed from my above example as that is just thelast end “ at the end “) ). In words: the Text Bit and 6 quotes all green . ( Note if already within a long string with the "start text" and "stop text" pair &""____""& then 4 qoutes are needed """" )
Or
for no text 8 quotes all green.
. For nothing but a quote in a cell using evaluate,8 quotes all green in the middle, two blue quotes, one on each end, 10 intotal!!. .. ("""""""""") _____ ("" & """""""""" & "")
.
. 3 b ) Spreadsheet World ( Wot you write jn the cell ):
.
. TextBit""
.
......................
. A lot of people know those two summaries, But I do not think they have a theory as to why...( I have at least one I think has some logic in ... maybe.. ? )

. Thanks for watching
. Alan
.....

[DocAElstein]

P.s.
@ snb

.......

To 'walk' in your approach:

........
   For j = 2 To UBound(sn)
     For jj = 1 To UBound(sn, 2)
       c00 = c00 &IIf(jj = 1, sn(j, jj) & ";", """" &IIf(jj = 4, Format(sn(j, jj), "0.00"), sn(j, jj)) &""";")
....... MsgBox Replace(c00, ";" &vbLf, vbLf)
........

or

....
   For j = 2 To UBound(sn)
    c00 = c00 &"""" & vbLf & Replace(Join(Application.Index(sn,j), """;"""), """","", , 1)
 ..MsgBox Replace(Mid(c00, 3),"""0""","""0,00""")
......

….. I think You have very nicely walked me through how to get the stringwhat I would type in a[SIZE=3]Spreadsheet.[COLOR=#222222][FONT="Verdana"]