Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

**DocAElstein** · 03-05-2024, 01:59 AM

This is post
https://www.excelfox.com/forum/showt...ll=1#post24049
https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=24049&viewfull=1#post24049
https://www.excelfox.com/forum/showt...ge12#post24049
https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff/page12#post24049

In support of these main forum posts
https://www.excelfox.com/forum/showt...ge12#post24048
https://eileenslounge.com/viewtopic....314920#p314920

Similar to a post or two previously , just a bit more complicated, the full text in a cell may have these sort of forms, (4 cell examples are shown)

Code:

Address.Country -- PKEY_Address_Country
//  Type:     String -- VT_LPWSTR  (For variants: VT_BSTR)
//  FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 100
DEFINE_PROPERTYKEY(PKEY_Address_Country, 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8, 100);
#define INIT_PKEY_Address_Country { { 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8 }, 100 }


Audio.Compression -- PKEY_Audio_Compression
//  Type:     String -- VT_LPWSTR  (For variants: VT_BSTR)
//  FormatID: (FMTID_AudioSummaryInformation) {64440490-4C8B-11D1-8B70-080036B11A03}, 10 (PIDASI_COMPRESSION)
//
//  
DEFINE_PROPERTYKEY(PKEY_Audio_Compression, 0x64440490, 0x4C8B, 0x11D1, 0x8B, 0x70, 0x08, 0x00, 0x36, 0xB1, 0x1A, 0x03, 10);
#define INIT_PKEY_Audio_Compression { { 0x64440490, 0x4C8B, 0x11D1, 0x8B, 0x70, 0x08, 0x00, 0x36, 0xB1, 0x1A, 0x03 }, 10 }


IsSendToTarget -- PKEY_IsSendToTarget
//  Type:     Boolean -- VT_BOOL
//  FormatID: (FMTID_ShellDetails) {28636AA6-953D-11D2-B5D6-00C04FD918D0}, 33
//
//  Provided by certain shell folders. Return TRUE if the folder is a valid Send To target.
DEFINE_PROPERTYKEY(PKEY_IsSendToTarget, 0x28636AA6, 0x953D, 0x11D2, 0xB5, 0xD6, 0x00, 0xC0, 0x4F, 0xD9, 0x18, 0xD0, 33);
#define INIT_PKEY_IsSendToTarget { { 0x28636AA6, 0x953D, 0x11D2, 0xB5, 0xD6, 0x00, 0xC0, 0x4F, 0xD9, 0x18, 0xD0 }, 33 }


TitleSortOverride -- PKEY_TitleSortOverride
//  Type:     String -- VT_LPWSTR  (For variants: VT_BSTR)  Legacy code may treat this as VT_LPSTR.
//  FormatID: {F0F7984D-222E-4AD2-82AB-1DD8EA40E57E}, 300 (PIDSI_TITLE_SORT_OVERRIDE)
//  
//  This optional string value allows for overriding the standard sort order of System.Title.
//  This is very important for proper sorting of music files in Japanese which cannot be
//  correctly sorted phonetically (the user-expected ordering) without this field.
//  It can also be used for customizing sorting in non-East Asian scenarios,
//  such as allowing the user to remove articles for sorting purposes.
DEFINE_PROPERTYKEY(PKEY_TitleSortOverride, 0xF0F7984D, 0x222E, 0x4AD2, 0x82, 0xAB, 0x1D, 0xD8, 0xEA, 0x40, 0xE5, 0x7E, 300);
#define INIT_PKEY_TitleSortOverride { { 0xF0F7984D, 0x222E, 0x4AD2, 0x82, 0xAB, 0x1D, 0xD8, 0xEA, 0x40, 0xE5, 0x7E }, 300 }

What I want is the SCIDS, aHoy, aHoy ( https://www.youtube.com/watch?v=DDg1hy6VhxY&t=110s
https://eileenslounge.com/viewtopic....314905#p314905 )

I took a more fuller look those,

Code:

 '   https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24049&viewfull=1#post24049
Sub CheckCharacters()  '  Cells  A2  A8  A288  A377
 Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A2").Value)
 Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A8").Value)
 Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A288").Value)
 Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A377").Value)
End Sub  '  see worksheet  WotchaGotInString  4 Mrz Results

For the results, see worksheet WotchaGotInString 4 Mrz Results in the uploaded workbook
Here again parts around the 4 examples simple text forms
Cell A2

Code:

FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 100
112           I	73
113           D	68
114           :	58
115            	32
116           {	123
117           C	67
118           0	48
119           7	55
120           B	66
121           4	52
122           1	49
123           9	57
124           9	57
125           -	45
126           E	69
127           1	49
128           D	68
129           F	70
130           -	45
131           4	52
132           4	52
133           9	57
134           3	51
135           -	45
136           B	66
137           1	49
138           E	69
139           1	49
140           -	45
141           D	68
142           E	69
143           5	53
144           9	57
145           4	52
146           6	54
147           F	70
148           B	66
149           5	53
150           8	56
151           F	70
152           8	56
153           }	125
154           ,	44
155            	32
156           1	49
157           0	48
158           0	48
"159           
"	13
"160           
"	10
161           D	68
162           E	69
163           F	70

Cell A8

Code:

FormatID: (FMTID_AudioSummaryInformation) {64440490-4C8B-11D1-8B70-080036B11A03}, 10 (PIDASI_COMPRESSION)
116           I	73
117           D	68
118           :	58
119            	32
120           (	40
121           F	70
122           M	77
123           T	84
124           I	73
125           D	68
126           _	95
127           A	65
128           u	117
129           d	100
130           i	105
131           o	111
132           S	83
133           u	117
134           m	109
135           m	109
136           a	97
137           r	114
138           y	121
139           I	73
140           n	110
141           f	102
142           o	111
143           r	114
144           m	109
145           a	97
146           t	116
147           i	105
148           o	111
149           n	110
150           )	41
151            	32
152           {	123
153           6	54
154           4	52
155           4	52
156           4	52
157           0	48
158           4	52
159           9	57
160           0	48
161           -	45
162           4	52
163           C	67
164           8	56
165           B	66
166           -	45
167           1	49
168           1	49
169           D	68
170           1	49
171           -	45
172           8	56
173           B	66
174           7	55
175           0	48
176           -	45
177           0	48
178           8	56
179           0	48
180           0	48
181           3	51
182           6	54
183           B	66
184           1	49
185           1	49
186           A	65
187           0	48
188           3	51
189           }	125
190           ,	44
191            	32
192           1	49
193           0	48
194            	32
195           (	40
196           P	80
197           I	73
198           D	68
199           A	65
200           S	83
201           I	73
202           _	95
203           C	67
204           O	79
205           M	77
206           P	80
207           R	82
208           E	69
209           S	83
210           S	83
211           I	73
212           O	79
213           N	78
214           )	41
"215           
"	13
"216           
"	10

Cell A288

Code:

 FormatID: (FMTID_ShellDetails) {28636AA6-953D-11D2-B5D6-00C04FD918D0}, 33
84           I	73
85           D	68
86           :	58
87            	32
88           (	40
89           F	70
90           M	77
91           T	84
92           I	73
93           D	68
94           _	95
95           S	83
96           h	104
97           e	101
98           l	108
99           l	108
100           D	68
101           e	101
102           t	116
103           a	97
104           i	105
105           l	108
106           s	115
107           )	41
108            	32
109           {	123
110           2	50
111           8	56
112           6	54
113           3	51
114           6	54
115           A	65
116           A	65
117           6	54
118           -	45
119           9	57
120           5	53
121           3	51
122           D	68
123           -	45
124           1	49
125           1	49
126           D	68
127           2	50
128           -	45
129           B	66
130           5	53
131           D	68
132           6	54
133           -	45
134           0	48
135           0	48
136           C	67
137           0	48
138           4	52
139           F	70
140           D	68
141           9	57
142           1	49
143           8	56
144           D	68
145           0	48
146           }	125
147           ,	44
148            	32
149           3	51
150           3	51
"151            "	13
"152           
"	10

Cell A377

Code:

 FormatID: {F0F7984D-222E-4AD2-82AB-1DD8EA40E57E}, 300 (PIDSI_TITLE_SORT_OVERRIDE)
155           a	97
156           t	116
157           I	73
158           D	68
159           :	58
160            	32
161           {	123
162           F	70
163           0	48
164           F	70
165           7	55
166           9	57
167           8	56
168           4	52
169           D	68
170           -	45
171           2	50
172           2	50
173           2	50
174           E	69
175           -	45
176           4	52
177           A	65
178           D	68
179           2	50
180           -	45
181           8	56
182           2	50
183           A	65
184           B	66
185           -	45
186           1	49
187           D	68
188           D	68
189           8	56
190           E	69
191           A	65
192           4	52
193           0	48
194           E	69
195           5	53
196           7	55
197           E	69
198           }	125
199           ,	44
200            	32
201           3	51
202           0	48
203           0	48
204            	32
205           (	40
206           P	80
207           I	73
208           D	68
209           S	83
210           I	73
211           _	95
212           T	84
213           I	73
214           T	84
215           L	76
216           E	69
217           _	95
218           S	83
219           O	79
220           R	82
221           T	84
222           _	95
223           O	79
224           V	86
225           E	69
226           R	82
227           R	82
228           I	73
229           D	68
230           E	69
231           )	41
"232            "	13
"233           
"	10

The conclusion from examination of those text parts, is that there are no surprises such as strange "invisible coding". The line separator is the typical pair, vbCr & vbLf
So we can simply look at these 4 lines, when thinking of how to get at the SCIDS, (and the second one I show again the whole cell of it, just to remind of how the full strings containing the lines of interest look like)

Code:

 FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 100
FormatID: (FMTID_AudioSummaryInformation) {64440490-4C8B-11D1-8B70-080036B11A03}, 10 (PIDASI_COMPRESSION)
FormatID: (FMTID_ShellDetails) {28636AA6-953D-11D2-B5D6-00C04FD918D0}, 33
FormatID: {F0F7984D-222E-4AD2-82AB-1DD8EA40E57E}, 300 (PIDSI_TITLE_SORT_OVERRIDE) 

Audio.Compression -- PKEY_Audio_Compression
//  Type:     String -- VT_LPWSTR  (For variants: VT_BSTR)
//  FormatID: (FMTID_AudioSummaryInformation) {64440490-4C8B-11D1-8B70-080036B11A03}, 10 (PIDASI_COMPRESSION)
//
//  
DEFINE_PROPERTYKEY(PKEY_Audio_Compression, 0x64440490, 0x4C8B, 0x11D1, 0x8B, 0x70, 0x08, 0x00, 0x36, 0xB1, 0x1A, 0x03, 10);
#define INIT_PKEY_Audio_Compression { { 0x64440490, 0x4C8B, 0x11D1, 0x8B, 0x70, 0x08, 0x00, 0x36, 0xB1, 0x1A, 0x03 }, 10 }

So we will check that out in the next post

**DocAElstein** · 03-06-2024, 02:18 AM

Later

**DocAElstein** · 03-06-2024, 02:18 AM

Later

**DocAElstein** · 03-09-2024, 10:10 PM

later

**DocAElstein** · 03-09-2024, 10:11 PM

this is post
https://www.excelfox.com/forum/showt...ll=1#post24055
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24055&viewfull=1#post24055
https://www.excelfox.com/forum/showt...ge60#post24055
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page60#post24055

Problems with vbCr & vbLf in formulas and Evaluate(" ")

Review/ Introduction of what we are doing
In this post we will consider the first parts of the demo coding from the last post. This will take us up to the point of the issue of vbCr & vbLf in formulas and Evaluate(" "), and so serve as an introduction and review of relevant concepts required to be understood before Tackling the issue

Rem 1 Puts stuff in a cell, fairly straight forward, ( but even that is not so well understood. I understand it better than anyone at the moment. https://www.excelfox.com/forum/showt...-Value-Anomaly ). We can best say that the simplest syntax of the LHS, as we have there, says "put in the cell, what is on the RHS, like wot we might do via writing something in a spreadsheet cell and then hitting Enter". That tends to imply putting text, (and / or numbers) in a cell. But I think if we want to learn quicker and better Excel and VBA, then it is advisable not to think like that too much, and rather to leave it a bit open in the mind at "put in the cell, what is on the RHS, as example, as you might do via writing something in and then hitting Enter". Best not tie the thinking down too much on that one, or further revelations I give may not get so easily in the brain )
As far as the RHS is concerned: In Excel spreadsheets, within a formula, we would enclose text we wanted to appear as text, in a quote pair, as this signalises to Excel that we want a literal text, rather than that the text is a name, function or some other specific thing that the text represents in Spreadsheet syntax.
For VBA it is the same: In other words, what is within a quote, will not be regarded directly as part of the coding, but rather refers to some actual text that will be used in some way, depending on what the coding associated with it is about. For us here, the text within the quotes is the text that we finally want to see

' 1b
This gives an initial indication that
_____ = ____ Evaluate("Char(13)&Char(10)")
and
_____ = ______ vbCr & vbLf
may be equivalent

' 1c(i)
This gives an initial indication that
_____ = ____ Evaluate("Char(13)&Char(10)")
and
_____ = ______ vbCr & vbLf
may not be equivalent

' 1c(ii)
This is our first dealing with the issue this Post and the next few Posts is about: We seem to have got over the problem of ' 1c(i)
The issue of this Post and the next few Posts is about that I am trying to explain that.

But first we will do some similar experiments, putting a formula in, ( but that also requires some careful consideration of Quotes in VBA , ( or an Excel cell, for that matter )

But before we go let’s have a Results Summary Conclusion attempt
https://i.postimg.cc/4dNrgdff/Rem-1-...vided-by-b.jpg
Rem 1 Put in cell text like a divided by b.JPG

Rem 1d Some conclusions
Maybe….
There are some similarities with Evaluate(" ") and the more familiar Range(" ")
These both return an object where the text inside the " " is recognised Excel spreadsheet syntax convention.
For Range(" ") it does not go much further than that.
There continues to be some shared features. For example, I suggest there is some wired in feature that allows some attempt to:
include, if not there;
, or to remove, if there
,an =
I suggest this is done to see if that makes the text something acceptable in Excel spreadsheet syntax convention.
For Range(" ") this can explain the interesting feature we tripped over here , since a formula is allowed, but only if it returns a range object reference, in other words a name recognised in Excel spreadsheet syntax convention. This last bit sets the limitation of Range(" ")
However, I think the Evaluate(" ") does not share this limitation, and so allows a value, text or numeric to be returned.
Perhaps the Evaluate(" ") can be regarded as doing this pseudo coding
_ If the text is some recognised name or excel reference, Then
_____ If it is a closed workbook reference, then error, since it allows Alan to do things better than most complicated data base stuff, and that’s embarrassing, Error Exit
_____ Else Return the object, run it, or some similar action doing something, Exit
_ Else
__ put a = in front of it and see if that is somehow recognised by Excel spreadsheet syntax convention, and allow it to return anything that a Variant variable will return. Exit

Ref
https://eileenslounge.com/viewtopic....280997#p280997
https://www.myonlinetraininghub.com/...#comment-84892

**DocAElstein** · 03-13-2024, 04:13 PM

Quotes in VBA

Rem 2 Puts formula in cell(Quotes in VBA )
' 2a
At this point we see for the first time the confusing world of quotes in VBA, and Excel in general.
In Excel spreadsheets, within a formula, we would enclose text we wanted to appear as text, in a quote pair, as this signalises to Excel that we want a literal text, rather than that the text is a name, function or some other specific thing that the text represents in Spreadsheet syntax.

Quotes in VBA
For VBA it is the same: In other words, what is within a quote, will not be regarded directly as part of the coding, but rather refers to some actual text that will be used in some way, depending on what the coding associated with it is about. For us here, the text within the quotes is the text that we finally want to see.
Working in VBA, ( or in a spreadsheet cell), to produce a string that we want to show a quote, becomes problematic. It is not clear if anybody really knows how and why we can get over this problem. There is no real documentation, although you might find the simple rile or explanation along the lines of…. Strings in VBA are delimited with quotation marks. If you want to include quotation marks in your string, you need to double them. Otherwise, the VBA compiler will not recognize that they should not end the string….
Well, that may be a correct statement, but I am not sure if that is a good explanation and whether it works by design or accident.
For me the following ideas helps to remember and possibly have an explanation or at least a theory that can explain the results and behaviour: It’s not too distant or different to that statement, it is an idea that helps me to remember, and possibly an
explanation:
The syntax will not allow a single quote within a quote pair, as the complier requires a pair. The syntax of VBA coding would also understandably find these strange.
__________ = "___ "____"___ "
__________ = "xyz"____"xyz"
But consider what might happen when we bring the inner quote pair together
__________ = "xyz""xyz"
I am thinking that we may have some sort of strange dynamic equilibrium situation, whereby simultaneously the quote pair syntax is satisfied and we have two joined pairs of text, ( "intimately joined"), which VBA sees on the compile as something like this
xy"xyz""
xyzxy""xyz"
, but because of this intimacy of the join, the position of the two inner quotes appears in the same place, meaning that the string is joined,- a quote may be defined twice, but in the same place, resulting in the final string as seen by VBA as
xyzxyz"xyz
Another way perhaps for thinking about is that they are actually a bit further than intimately joined, a character is entwined. By definition, the actual final literal string starts or stops on the other side of the quote, either after or before the quote. If you look at some of those coloured sketches you can perhaps imagine that the stop and start of the two literal bits of literal text is in the same place, which further joins them by two quotes merged into one.

Using Debug.Print to aid in string construction
Even with a good understanding of quotes in VBA, it is very easy to make a mistake. This general rule can help:
Printing out the string you have attempted to construct in the immediate window, will show it as VBA sees it after compile, and shows what VBA, after compile, will be trying to put in a cell via a code line like
Let Range("C1") = The string you have constructed
So, that string shown in the immediate window must be syntaxly kosher for an Excel cell, in otherwords it must look like the string as you would manually write it in a cell. So for putting a formula in a cell, something like this would be OK
= "a" & "b"
, resulting in the value
ab
showing in the cell, ( and in the formula bar you will see = "a" & "b" , just as in the immediate window )
On the other hand, something like these showing for a Debug.Print of your constructed string, would likely error when used
= "a" & b"
= "a" & ""b"
= ""a"" & "b"

So finally, in ' 2a we have a string construction that we are happy with to put a formula in a cell which will be the Excel spreadsheet formula, ="a"&Char(10)&Char(13)&"b" , as indeed we see both in the formula Bar, and in the immediate window if using
Debug.Print "=" & """a""" & "&" & "Char(13)&Char(10)" & "&" & """b""" & ""
To briefly explain that final string construction in words
______ "=" & """a""" & "&" & "Char(13)&Char(10)" & "&" & """b""" & ""
The outer most quotes are those always required in VBA to signalise that we are giving literal text.
Within those outer quotes, in VBA coding we may build up the string from further string elements. (The string can also be built up from VBA things returning strings or numbers, as we will do in the next post. Here we are only using other string elements), The syntax of VBA will require further enclosing quotes for every further string elements, and furthermore, VBA syntax requires an ampersand symbol, _ & _ , to join these strings, thus
______ "__" & "¬¬¬¬____" & "asskasasaf"
In green is the literal text, as we would write it in a cell.
Excel spreadsheet syntax requires in the final written cell formula text ampersands, & , which will be here literal text, hence must be enclosed with VBA quote pairs, thus
"&"
Finally, the Excel spreadsheet syntax requires that a literal text such as the character a, must be enclosed in the cell in quotes, but as we discussed in quotes in VBA something like this would error , ""a"" , we require to effectively double quote where we want a final single quote in the final string , """a"""

The final value in the cell would be
a
b
We have there 4 characters, pseudo like, (and it’s a bit subjective/ arbitrary of me, how I show them here in this pseudo form, just for explanation purposes: )
a vbCr
vbLf b

Some final minor notes:

_ If we are in in non English Excel, as I am, we can type either of these in the Immediate window to get the English syntax.
? range("C1").Formula
Debug.Print range("C1").Formula
_ Once we have the final string, we can often simplify, mainly by removing some VBA joining bits, since they are redundant when linking only literal text – the text can simply appear together.
______ "=""a""&Char(13)&Char(10)&""b"""
However, from experience I would only do this, if ever, at a final stage, since the careful explicit constructions are usually easier to build up and debug. Indeed, with hindsight, I might separate all the final cell texts of "a" and "b", as shown within the VBA construction, into the 3 characters of a quote, an a , and a final quote, just so as to make them clearly correspond to the explanations given of the quotes in VBA
______ "=" & """" & "a" & """" & "&" & "Char(13)&Char(10)" & "&" & """" & "b" & """" & ""
In words, just as you would write in the cell ,
=
and
a quote "
and
a
and
a quote "
and
a ampersand &
and
a quote "
and
the two joined text (functions), Char(13)&Char(10)
and
a ampersand &
and
a quote "
and
b
and
a quote "

https://i.postimg.cc/j2SJH7tY/a-CHAR-13-CHAR-10-b.jpg
= a &CHAR(13)&CHAR(10)& b.JPG

So we have done the background revision and can move on in the next post to the problem / issues with vbCr & vbLf in formulas and Evaluate(" ")