Calculate letters weight

User avatar
SpeakEasy
5StarLounger
Posts: 742
Joined: 27 Jun 2021, 10:46

Re: Calculate letters weight

Post by SpeakEasy »

> It still is nothing like 38 32

Just depends on the base the numbers are being given in.

So a byte, which is 8 binary bits, can represent (in decimal) any integer number from 0 to 255 inclusive.

So let's pretend that we have one byte that currently has the bit pattern that represents decimal 38
And let's pretend that we have another byte that currently has the bit pattern that represents decimal 32

But in hexadecimal (which is nothing more magic than a name for base 16, in the same way that decimal is no more than a name for base 10) those two bytes are (drum roll): 26 and 20

Some code may help. Or maybe not ...

Code: Select all

Option Explicit

Public Type mike
    mychar As Integer
 End Type
 
 Public Type wombat
    byteHi As Byte
    byteLo As Byte
End Type

Public Sub Example()
Dim m As mike
Dim w As wombat

m.mychar = 8230

LSet w = m ' use a hack to copy the underlying storage of the integer into two bytes

Debug.Print "The hex of the number: " & Hex(m.mychar)
Debug.Print "How it is stored; " & Right("00" & Hex(w.byteHi), 2) & " " & Right("00" & Hex(w.byteLo), 2)

End Sub
>one person says Byte A is 65 00 and the other says its 41 00
Both right - but one is decimal the other is hexadecimal. But yes, can be confusing if you are not aware of what base is in use.

>Go above 65535 to 65536 and, in some peoples conventions, you go to 10000
Where 'some peoples conventions' = hexadecimal

>I have not yet seen more than 4 characters in peoples explanation of UTF-16 2 Byte
So we perhaps get to the core of some of your misunderstanding. A Unicode value (whether in UTF-8, UTF-16 or UTF-32) represents a single character, no matter how many bytes are used for that representation. Unlike ASCII/ANSI, where we can safely say that 1 byte = 1 char, we cannot do the same for Unicode. (except for the first 256 characters of UTF-8 which - by design - overlap ANSI)

UTF-16 2 byte (not really a thing, to be honest) is a 16 bit "code unit" and represents a single Unicode code point (although some code points may represent what we humans might consider more than 1 character, for example ligatures). And do not make the mistake of assuming the hexadecimal digits that represent the bytes are themselves characters (i.e FFFF is not 4 characters)

But to get to what I guess is your point - why do you never see more than FFFF when discussing Unicode 16 2 byte. well, because hex FFFF is the largest number that 2 bytes can directly represent If you started talking about hex 10000, then you are talking about 3 (or in reality 4) bytes. See below for a quick primer on binary numbers

I should point out that above code point FFFF UTF-16 get's somewhat messy to deal with (Unicode code point 10000 is not internally represented by hex 10000 for example), and is mostly irrelevant to most people, so most of the time MS seem to like to restrict access to higher code points (especially in VB(A)).

>perhaps Microsoft needed UTF-16 4 Byte to aid them in stuff above that 65535 / 65536
>for example workings involved with the row number

No, you are still seem to mixing Unicode up with some fundamental features of binary computers.

You know all this, but:
Modern binary computers based on 8-bit bytes (and yes, there were indeed computers that used different sized bytes). By setting the bits on and off, a byte can represent 256 different states (2^8) - and we can choose to interpret those states as the numbers 0 to 255 (hex 0 to hex FF). If we want to represent the number 256 we need to start using a second byte. And once we start using that second byte, we can represent 65536 states - or the numbers 0 to 65535 (hex 0 to hex FFFF), which from the 16-bit days is the range of an unsigned integer (what Microsoft now refer to as a short). If we want to represent the next number, 65536, then we need yet another byte (but three bytes is a pain for a binary computer to handle, so we actually use another 2 bytes. And that allows us to represent 4294967296 states - or the numbers 0 TO 4294967295 (hex 0 to hex FFFFFFFF). Which, again not coincidentally, is the range of a Long (or as MS now call it a uint, since the size of a Long has changed ...).

Now as you can see none of this relies on Unicode either directly or indirectly

So it isn't rocket science - older versions of Excel had a limit of 65536 rows because internally they uses an Integer/short to represent the row number.
Oddly, the new limit (where the value is now stored in a long/uint) is 2^20, not 2^32 - but I suspect the view was that sheets larger than this might be limited by memory and/or performance

User avatar
DocAElstein
5StarLounger
Posts: 773
Joined: 18 Jan 2022, 15:59
Location: An Englishman, illegally re-routing rivers, in Hof, Beautiful Bavaria. Rule, Britannia!

Re: Calculate letters weight

Post by DocAElstein »

Hi
Thanks for all that.

I read that a few times and all the previous stuff again a few times.
The penny has finally dropped. :smile:
Let me briefly summarise it all for posterity and future reference

_0) The background stuff
The start point of both this thread and the extended discussions, the main important issue, is something like a two column ( or similar 2 somethings, example 2 similar 1 dimension arrays) representing a list, or similar, of
single (text) characters v a corresponding (decimal) number.
These things (lists) in everyday language go by the name of Ascii character lists, ANSI character lists, Unicode character lists.
( I made a few lists last week at the start of trying to figure out this and some related win32api string stuff
https://www.excelfox.com/forum/showthre ... #post17877
https://www.excelfox.com/forum/showthre ... #post17878
https://www.excelfox.com/forum/showthre ... #post17879
Share ‘WunucodeANSI.xlsm’ https://app.box.com/s/20erozqcjs2ljphkiycvbtah08y85fy9
)

_1) Byte bits in computers
This _1) I also finally got very clear in my head a week or so ago …. When we talk about computer Byte things to hold those (decimal) numbers, we start usually thinking of 7 - 8 lots of on an off things, (a Byte is 8 of them) arranged like this first sketch: Here I am demonstrating 4 different Bytes; This sketch illustrates 4 examples, each can be thought of a Byte. They represent these decimal numbers
0
31
127
255

Code: Select all

 '  2^7 2^6  2^5  2^4   2^3  2^2  2^1  2^0
'    128 64   32   16    8    4    2    1  

'    0    0    0    0     0    0    0    0           Binary  way to show 0
'    0 +  0 +  0 +  0 +   0  + 0 +  0 +  0  =   0    Calculating the  Decimal 0 from the Binary code

'    0    0    0    1     1    1    1    1            Binary  way to show 31
'   0 +   0  + 0  + 16 +  8 +  4 +  2 +  1  =   31   Calculating the Decimal 31 from the Binary code

'    0   1    1    1     1    1    1    1            Binary way to show  127
'    0 +  64 + 32 + 16 +  8 +  4 +  2 +  1  =  127   Calculating the Decimal 127 from the Binary code

'    1   1    1    1     1    1    1    1            Binary way to show  255
'  128 +  64 + 32 + 16 +  8 +  4 +  2 +  1  =  255   Calculating the Decimal 255 from the Binary code

'  Nöte that this right left order is most common generally 
Any Byte bits we are talking about can be considered to look "behind the scenes" something like any of those 4 examples.
That 8 column binary row arrangement is what we could call base 2 up to 8 Bits. - That is like what it actually is, in a wider maths sense

_2)
My original question, (bottom of this post ) centred around how to figure out how a decimal 8230 got represented by 38 32, ( I did understand the maths Hans explained , which can be used to get the numbers, 38 32, but I did not understand much more. I did not know an Ascii from a Unicorn from a rabid dog Bite back then, so anything else was lost on me.

_3)
This is the answer I was after. It comes from doing something similar to the sketch above. But with 3 differences
_ I got 2 columns, not 8
_ I got base 256, not base 2
_ the columns are the other way around
( each column is a Byte, so "hidden" behind each one is something looking like the Byte examples in the last sketch above

Code: Select all

 '    256^0    256^1
'      1       256              
      38        32                     base 256 way, ( in a less conventional left  right order) to show decimal 8230 (using two Bytes - each one can show 0 – 127 decimal as per last diagram
'     38   + (32*256)  =  8230  calculating the decimal 8230 from the ????? code 
_.______________

That is all I needed to know. I confused the issue with things like bringing hexadecimal into the picture. That was forced upon me from reading other stuff all over the place where people were not at all clear what number base or whatever else they where talking about.
The simplest short answer that might have turned the light bulb on for me would have been
_ that it is like a 2 column base 256 the wrong way around, like that second diagram above,
_ and if that did not work then including something like the previous diagram first would have done it , I think,

(The point about hexadecimal would come best after the above was explained, since at this point we have not really got that far. I still have a feeling Hex somehow comes about to keep the numbers ( 38 or 32 in the example ) neatly at 2 digits ( 2x 2 digits = 4 digits for 2 byte representation ) as the value of the number increases
Obviously hexadecimal follows logically and neatly on from this point, - it's all to do with powers of 8, 16 … 256 etc. )
( I may have used the word character once in my previous post when I meant digit. It is clear to me that as you go up in numbers, and more exotic characters, more bytes will be used for a single character. I don’t know how far the technology is, but I suppose one day a common picture will be a character, and you will have thousands or more of bytes, to somehow represent it, in other words to give you some way to either represent it/ and/or give it a Unicode list number. Maybe not. We are as far as Smilies I think, 4 Bytes )
_.____________

All the other stuff is not at all wasted on me though – It never is - I came back here two years later and got a lot of useful stuff to help me on my current VB String musings. Some of the new other stuff may already find their way onto this post , which I can now get on with *now that I finally got this decimal 8230 represented by 38 32 finally sussed. And you know I will probably spend a month trying to figure out how that Wombat Byte hack works, before I give up and ask you.
*It will all have to wait a few days while I wear my wrist out on the yearly Xmas card and accompanying letter marathon. I might possibly manage a last quick post on that VB String thread today, just to keep that simmering nicely. Maybe not

Thanks again, Much appreciated, Mr Wombat, Sir
Regards , Ālan , DocÆlstein :england: , :germany:

User avatar
SpeakEasy
5StarLounger
Posts: 742
Joined: 27 Jun 2021, 10:46

Re: Calculate letters weight

Post by SpeakEasy »

Some references to some of your other posts in teh ExcelFox forum. I had more, but these will do.

>ASCII itself tends to be related to some numbers, very very approximately around 200
Nope. ASCII is a 7-bit encoding, and represents 128 values, of which 31 are not characters, but control codes

>all API calls are made with ANSI strings
No, they are not

>it makes it impossible to pass a UNICODE string from VB to a DLL via an argument typed As String in a Declare statement
No it doesn't. Although it does involve a little subterfuge at the VBA end.

User avatar
DocAElstein
5StarLounger
Posts: 773
Joined: 18 Jan 2022, 15:59
Location: An Englishman, illegally re-routing rivers, in Hof, Beautiful Bavaria. Rule, Britannia!

Re: Calculate letters weight

Post by DocAElstein »

Yeh, that wod of excelfox musings on API and VB strings is unfortunately left a bit incomplete and left hanging while I get the Xmas letter marathon finally going, but thanks for taking a look, shame it was not a bit further when you took a look. I will tidy it up, - I have left some string page " Buffers " to fill in. I got a lot of ideas I did not manage to get out at run ( or jogging/aka fast staggering walk ) time last night, but by setting aside/ passing over the empty posts and pages will hopefully prevent me leaving an undetermined memory reserve causing a brain memory leak when my head inflates and explodes… (bad enough that in the last week the laptop I was mostly using for my api experiments is doing one strange thing after the other. Every day Bleach- Bit is finding about 4GB to get rid of, whereas normally it’s about 1 GB every week or two. (Once or twice I tried CClean instead just as an alternative, and that even cleared out a few 100MBs, whereas that usually hardly cleans much at all these days. )
I tripped up for a day or two caught on that 38 32 issue and it has set me back.

I don’t disagree with what you said about Ascii , I say the same somewhere, initially- the very very approximately around 200 was in some later wider context making a comparison perhaps with bigger things. I got the point that ANSI is usually well defined to 255, Ascii was originally 7-bit encoding, represents 128 values, but somewhere in the many things I read there were sometimes people talking of some Ascii extensions or something that went 8-bit to something similar to ANSI. That is how and why the very very approximately 200 came about in a later wider context. Good feedback though I will bold up the very yery or correct it in some other way
Nice to catch what you said about the first 30 or so – I was wondering a bit about those

I am not quite sure how to better word my rhetoric musings on the ANSI api VB string issues, I will keep at it and your comments are always very helpful. I have to try to translate things like subterfuge sometimes, but I am getting better. I (only) managed to translate the first quarter of the Dan Appleton chapter on VB Strings so far but in parallel have had some very interesting results messing with pointers where strings usually are, but not enough to make any worthwhile discussions on it or conclusions yet.
(My notes already on subterfugeing start on page 4 , but that is only the very first bit I managed last night , that needs a lot more thought and experimental research, and editing - The only bit I like so far is ……
…… Behold: I have seen a API World, where direct ANSI Strings stuff is often avoided, or at least cropped back / restrained a bit, and that World is good.) …. I will try to work in subterfuge into that and we may get the Nobel prize next time around

I must go now and not come back until my right ( writing! ) wrist can't take it anymore
( my extra Christmas present arrived from England surprisingly much quicker than I expected last week, and the Postman set it there under one of my stolen outside Xmas trees . In the meantime I found the other Dan Appleton book, - as expected it was doing something structurally useful wedged firmly between a heavy solid wood cupboard and the wall preventing an unhealthy tilt. They are both spending at least the extended Xmas period on the work bench … but whether I decide they are both more useful set structurally doing something somewhere I am not sure yet.
Regards , Ālan , DocÆlstein :england: , :germany:

User avatar
SpeakEasy
5StarLounger
Posts: 742
Joined: 27 Jun 2021, 10:46

Re: Calculate letters weight

Post by SpeakEasy »

>Ascii was originally 7-bit encoding
Still is. it is an internationally defined standard. Any extensions are not ASCII. And to make it worse, technically the term ANSI as applied to an 8-bit character encoding that includes the ASCII characters is not a thing, far less a standard. Microsoft are at least partially to blame for this common error (Microsoft's 8-bit character encoding for the latin character set is actually called Windows-1252 - or cp1252, since we have now moved into the world of Code Pages -, which doesn't really trip of the tongues as easily as ANSI). As they themselves say "The term ANSI as used to signify Windows code pages is a historical reference, but is nowadays a misnomer that continues to persist in the Windows community."

(Microsoft are also pretty much to blame for people mixing up the term Unicode with the actual encoding format used by Windows - whenever Microsoft say Unicode in Windows, they almost always actually mean UTF-16 - to be clear Unicode and UTF-16 are not the same thing; Loosely, Unicode is a text encoding standard that defines characters used in various ordinary, literary, academic, and technical contexts in various languages and assigns them abstracted code points. The encoding formats (UTF-8, UTF-16 AND UTF-32), on the other hand, define how to translate the standard's abstracted codes for characters into sequences of bytes and thus how they are actually stored in memory)

One additional point that may be worth remembering is that UTF-16 is not backwards compatible with ASCII (or any of the ASCII-inclusive 8-bit character encodings). UTF-8, on the other hand is 100% backwards compatible with ASCII

User avatar
DocAElstein
5StarLounger
Posts: 773
Joined: 18 Jan 2022, 15:59
Location: An Englishman, illegally re-routing rivers, in Hof, Beautiful Bavaria. Rule, Britannia!

Re: Calculate letters weight

Post by DocAElstein »

That all sounds along the lines of the notes I made, but not yet "published", in the buffer here , with some extra stuff. It confirms most of what I have, which is useful since the internet is not an academic controlled source of info, and also some bits I make up as I go along, but sounds like I got it mostly right this time which is useful to know
ANSI I have pencilled in as being around as a standards place since 1910, but only starting to think of computer standards in the 1980s. A lot of people write that Microsoft then hijacked it a bit before the standards chaps were finished their ponderings on it.
ANSI and Unicode are the same from 32 to 127 I think, and after that , up to 255 , in MS Office, the "ANSI” , using the term lightly, will depend on your code page. They are all similar I believe with just a few differences. This is my code page number . I am not sure where / if there are lists anywhere of all of them. I assume what I have would be like the list I get from Chr(128-255)as I got in the share file WunucodeANSI.xlsm that I made and am adding to as I go along

Another thing that can confuse perhaps is that the Asc() function gives the opposite of Chr() function up to 255, and you sometimes read things like that the Asc() function gives the Ascii number of the character you give, which is not quite correct for above 127, (unless you say that very very very approximately said, the Asc() function gives the Ascii number of the character you give up to very very approximately around 200 or so or there abouts give or take very very approximately 50 or so……


( ** Edit P.S. 1 if you ever want to register there at excelfox as Mr Wombat or whatever/whoever, I will make you a moderator so you can, for fun if you are in the mood ever, edit / type red corrections / suggestions on my stuff, delete it if annoys you too much,… or even ban me if you like, if anything you find too disgusting. Or I can PM you a ready made moderator account )

Edit PS. 2
The thing that threw really threw me off for a few days is that an awful lot of tutorials and stuff that explain these things make some obvious errors / incorrect statements to keep it all simple and easier to understand: When explaining a typical Unicode encoding format of the …. 16 …. sort, ( whether they use the terms Unicode and Unicode encoding correctly or not or at all), is that they almost always start with a simple Word like
ALAN
Then they will explain how the numbers for that of the form
65 76 65 78
( or
41 4C 41 4E )
They then go on to explain it looking then in some 2 Byte form as something of the form
65 00 76 00 65 00 78 00
They then frequently talk about that spare 00 Byte that is never used and that this "Unicode" representation , ( using the term "Unicode" very very lightly, and incorrectly mostly ), always has a spare never used 00 Byte position. Then they will go on to talk about this representation as a sequence of numbers , ( even if they use hex with some capital letters there ) that always have a unused 00, Byte , or an unused zero, in between

I know only with hindsight that they are concentrating on the lower number end so a lot of what they say is technically not too incorrect.

Any tutorial worth its salts should go on to take a higher number example as well. The problem is that they then would have to explain more detailed the whole issue, which no one and more importantly these days, no Bot likes, .

Some otherwise very good and useful YouTube videos make this mistake. They have to to get better views.
There may be some good videos that don’t make that mistake, but I could not find them , as that place is also getting overcrowded, mostly with crap. The problem is that I discovered that they have a cunning AI Bot thing monitoring comments, making it impossible to give any useful feedback. It does a form of extended shadow banning to all but the simple short happy sounding comments as this is good for views and business. The majority of people will never see any longer useful comment you post, including the channel author, but you and a small number of IP addresses that are through the greater Google & co. spy somehow connected to you will see most of the comments you do (unless they are obviously bad spam with links etc.)
Also longer tutorials are also discouraged, (just as long posts are at forums, because the learning algorithms of Chat GPT and other question answering AI development, which will shortly replace us all, prefers very short simple answers , that are in some way technically correct, or seen to be by the masses as technically correct. So real knowledge will be lost, one way or another. In this respect, most main forum regulars and moderators are digging their own graves and/ or the mass grave for mankind
Regards , Ālan , DocÆlstein :england: , :germany:

User avatar
SpeakEasy
5StarLounger
Posts: 742
Joined: 27 Jun 2021, 10:46

Re: Calculate letters weight

Post by SpeakEasy »

>ANSI and Unicode are the same from 32 to 127 I think
You are perpetrating the same error as Microsoft with that statement.

The Unicode control codes block U+0000 through U+001F plus U+007F and the basic Latin block code points U+0020 through U+007E match ASCII characters with the values from hex 00 through hex 7F, but the binary representation of the Unicode code points depends on the encoding in use (UTF-8, UTF-16, UTF-32).

So the short version might be
The characters represented by ASCII values 0 through 127 are the same as the numerically equivalent code points in the Unicode basic Latin code block

>I am not sure where / if there are lists anywhere of all of [the code pages]
https://learn.microsoft.com/en-us/windo ... dentifiers
Although you'll need to use the identifiers in a further search to see the actual character sets the code page represents

>after that , up to 255 , will depend on your code page.
Nope. A code page defines all the 256 values in the 8-bit space (including control codes). Many of those code pages extend ASCII. But not all.

>They are all similar I believe with just a few differences.
1) Not all code pages are ASCII extensions. Have a look at EBCDIC code pages, for example.
2) try looking at some of the Arabic code pages

>sometimes read things like that the Asc() function gives the Ascii number of the character
I believe the Microsoft documentation for the function has for many years stated "Returns an Integer representing the character code" (although it is actually a little smarter than that)

User avatar
DocAElstein
5StarLounger
Posts: 773
Joined: 18 Jan 2022, 15:59
Location: An Englishman, illegally re-routing rivers, in Hof, Beautiful Bavaria. Rule, Britannia!

Re: Calculate letters weight

Post by DocAElstein »

Hi, I am back to my current learnings after getting ready for Xmas
SpeakEasy wrote:
16 Dec 2024, 12:25
………. historical reference, …… misnomer
>ANSI and Unicode are the same from 32 to 127 I think
You are perpetrating the same error as Microsoft with that statement
.

The Unicode control codes block U+0000 (dec 0) through U+001F dec (31) plus U+007F (dec 127) ( delete ), and the basic Latin block code points U+0020(dec 32) through U+007E (dec 126) match ASCII characters with the values from hex 00 ( dec 0) through hex 7F (dec 127) , but the binary representation of the Unicode code points depends on the encoding in use (UTF-8, UTF-16, UTF-32).

So the short version might be
The characters represented by ASCII values 0 through 127 are the same as the numerically equivalent code points in the Unicode basic Latin code block
Yes, thanks that sounds good. Given a week I might have worded it like that, I was almost there. It helps me get there a little quicker when you say it.
I have got now as far as concluding my ANSI, Ascii, Unicode stuff, (and I added a section about the importance of perpetrating ANSI historical reference misnomers towards the bottom of this , and the top of this
I corrected / updated a few things based on some of your other last few comments.
_.______________________
SpeakEasy wrote:
16 Dec 2024, 12:25
>sometimes read things like that the Asc() function gives the Ascii number of the character
I believe the Microsoft documentation for the function has for many years stated "Returns an Integer representing the character code" (although it is actually a little smarter than that)
I had overlooked that. Interesting. Many blogs and Tutorials used the term Ascii very loosely when talking about that Asc() function
What is also interesting is that when considering The Chr() and Asc()functions, my conclusion was that the opposite to the Chr() might be better having been given a name something like ChrCode or similar. Some time after I noticed that the spreadsheet equivalent is indeed named the CODE function. So I was maybe on the right lines of thinking. ( Appropriately, using the conventional perpetration of ANSI historical reference misnomers, they refer to the character set used by the CODE function as ANSI ……

(Another thing, or things, I noticed, suggesting I may not be totally weeing on the wrong tree, is that my recent diversions and musings were sparked off to get some better background to Chapter 6 of that recommended book . Having forced myself to start reading the earlier chapters I find that it is in fact tackling many of the background issues that I decided to research on. So perhaps before I go further I will go off now and force myself into reading up to chapter 6, as it might be worth it in the long run.
( Perhaps that book and the other VB api reference stuff I recently got recommended here is what I was after back then when first delved into VBA winAPI stuff )

Alan
Regards , Ālan , DocÆlstein :england: , :germany:

User avatar
DocAElstein
5StarLounger
Posts: 773
Joined: 18 Jan 2022, 15:59
Location: An Englishman, illegally re-routing rivers, in Hof, Beautiful Bavaria. Rule, Britannia!

Characters at Xmas (Part 1)

Post by DocAElstein »

Hi, Merry Xmases
As a spin off from my recent follow up questions here and discussions on the whole Ascii Unicode ANSI issues involved, the extra knowledge forced painfully into my brain has made a better solution to the original question obvious to me.

So here is that timely Xmas present to bring happiness to all mankind.
It is actually an improved version of my previous solution . ( Every sane person would likely use a looping version like what Hans did , but an interesting one ( well almost one ) Liner Evaluate Range type solution alternative was offered by me, and I can improve on that now knowing a bit more of the Ascii Unicode ANSI issues involved.

Summary reminder of original question and my original solution
Original Question:
I have two arrays …..one array for letters Array("A", "B", "C", .... "Z")
and another array for the weight for each letter Array(1, 5, 3, ...... 2)
…. calculate the weight for word "ZAC" ?? The output should be 2 + 1 + 3 = 6 so 6 is the expected final output

My original solution:
Here is the full solution
Briefly, and in particular the stuff relevant to the alternative solution I present here: …
I did not use the letters Array("A", "B", "C", .... "Z"), although I would have liked to. I would have liked to, because my solution idea initially was to try and turn something like "ZAC" into an array like Array("Z", "A", "C"). This could then be used as the first argument in Application.Match , with the second argument of the letters Array("A", "B", "C", .... "Z"), to get an array of positions along required in the weight for each letter Array(1, 5, 3, ...... 2)
What I did find was a way to easily get an array of this form , Array(90, 65, 67). Those numbers I only understood at the time as some sort of "Ascii numbers for those characters". I know now they are in fact the mapped decimal code points for any 1 Byte, 8 Binary Bit character encoding which uses the ASCII 7-bit encoding, for the first 128 values, … of course… :innocent: (We may also use the convention of perpetrationing ANSI historical reference misnomer and refer to these as the ANSI character numbers)
My matching idea was still possible, but required making an extra array of the form Array(65, 66, 67, .... 90) in place of the letters Array("A", "B", "C", .... "Z") ……. and so on … – full description here:


I can see more clearly now, that the Array(90, 65, 67) was obtained by something of a hack through a function, strConv. The workings of the strConv seem not too clearly documented , but by choice of two of its arguments it can be used in a form of linking/converting between
_ the 1 Byte, 8 Binary Bit character encoding decimal code points determined by your code page
and
_ some of the 2 byte 16 Bit used by Microsoft in their version of Unicode encoding.
( Or in the convention of perpetrationing of ANSI historical reference misnomer, we can say that by choice or two of its arguments strConv() can be used in a form of linking/converting between ANSI and Unicode )
My research suggest that a simple 1 to 1 conversion from one direction to the other is not easily obtainable, possibly not possible, but messing around in every way possible threw out at some point a single line converting something like "ZAC" into "Z" & vbNullChar & "A" & vbNullChar & "C" & vbNullChar
Now, having that, if you whip off the last vbNullChar, then Split that resulting string by a vbNullChar, ….. then, Bingo, Full House, - in one pretty line you got "ZAC" changed to an Array("Z", "A", "C").
The rest of my new coding offering follows the same as the previous one.
Here is a full coding and full coding description https://www.excelfox.com/forum/showthre ... #post21746

….and… shortened code examples in next post…
Last edited by DocAElstein on 24 Dec 2024, 20:36, edited 3 times in total.
Regards , Ālan , DocÆlstein :england: , :germany:

User avatar
DocAElstein
5StarLounger
Posts: 773
Joined: 18 Jan 2022, 15:59
Location: An Englishman, illegally re-routing rivers, in Hof, Beautiful Bavaria. Rule, Britannia!

Characters at Xmas (Part 2) Sample coding

Post by DocAElstein »

…….. continued from lasts post
Here a few shortened pretty one liner final versions, and a function version

Code: Select all

Sub ChrMas_pressie2()  '    https://eileenslounge.com/viewtopic.php?p=323516#p323516  https://www.excelfox.com/forum/showthread.php/2872/page53#post21746
Dim Some As Long: Let Some = Application.Sum(Application.Index(Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2), 1, Application.Match(Split(Left(StrConv("ZAC", Conversion:=vbUnicode), Len(StrConv("ZAC", Conversion:=vbUnicode)) - 1), vbNullChar), Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"), 0)))     ' 6
End Sub
'
Sub ChrMas_pressie1()
Rem 1 These arrays are according to the original question Available
Dim Weights() As Variant, Letters() As Variant
'                      A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z
 Let Weights() = Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2)
 Let Letters() = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
Dim ZAC As String ' 1b) test string example
 Let ZAC = "ZAC" ' This is a demo example text string
Dim Some As Long: Let Some = Application.Sum(Application.Index(Weights(), 1, Application.Match(Split(Left(StrConv(ZAC, Conversion:=vbUnicode), Len(StrConv(ZAC, Conversion:=vbUnicode)) - 1), vbNullChar), Letters(), 0)))     ' 6
End Sub

Sub TestFuncWaitsAString()
 Debug.Print WaitsAString("ZAC") '   6
End Sub
Function WaitsAString(ByVal StrIn As String) As Long
 Let WaitsAString = Application.Sum(Application.Index(Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2), 1, Application.Match(Split(Left(StrConv(StrIn, Conversion:=vbUnicode), Len(StrConv(StrIn, Conversion:=vbUnicode)) - 1), vbNullChar), Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"), 0)))     ' 6
End Function 
This offering has two improvements over the previous one of mine:
_ It uses the Letters() array rather than having to make the corresponding Array(65, 66, 67, ….. ) , (with the further advantage of not having to make another if the Letters() array is changed)
_ This does not have the problem which restricted the previous solution to a minimum of two code lines, so it is finally a beautiful single line solution.

Merry Xmases ,
:-)
Alan
Regards , Ālan , DocÆlstein :england: , :germany:

User avatar
SpeakEasy
5StarLounger
Posts: 742
Joined: 27 Jun 2021, 10:46

Re: Calculate letters weight

Post by SpeakEasy »

That being said ...

Hans' solution has the advantage that it works in all VBA hosts, whereas yours only works in Excel.

And secondly "single line, non-looping" - Excel's Sum function loops over the values you pass. Essentially in VBA it would be something like:

Code: Select all

Public Function mySum(ParamArray intScores() As Variant) As Double
    Dim i
    For Each i In intScores
        mySum = mySum + i
    Next
End Function
which is very much a loop, albeit cunningly concealed

But it is an interesting approach.

User avatar
DocAElstein
5StarLounger
Posts: 773
Joined: 18 Jan 2022, 15:59
Location: An Englishman, illegally re-routing rivers, in Hof, Beautiful Bavaria. Rule, Britannia!

Re: Calculate letters weight

Post by DocAElstein »

It’s interesting to know what actually happens behind the scenes, with the sum. The Sum has rarely played any role in the pretty one liner App.Index and App.Match with Array arguments things that I have taken an interest in. If I had taken a totally uneducated guess, I might have said they, (Sum things), maybe do something in a way that is less efficient than what these App.Index and App.Match with Array arguments things do. I am not being clear in what I am saying here as I don’t really know what I am talking about. Its just gut feelings that these App.Index and App.Match with Array arguments things, ( along with the non looping Evaluate(" ") Range things I also take an interest in), work efficiently in some way, perhaps some sort of synchronisying with a screen update, which can be done for something doing some similar actions across a range, but something like a Sum is like a Snake or a Ladder in a the board game Snakes and ladders, except that these snakes and Ladders may make it slower than taking the more ordered route, along the columns, next row, along the columns etc.
Or
maybe better said, the Sum has to get involved with keeping track of something or a few things, before giving an answer, rather than just doing something them moving on to do the same again. This last vague idea ties up a bit perhaps with your function

Don’t ask me what I mean with all that. Just vague ideas, - I don’t understand myself where my brain goes sometimes.

It took me ages to figure out how to do these one-liners, - there was no blogs or documentation that explained them in any detail, at least I could not find any. Once I figured it out it seems now very very easy, and I can develop them and explain them very easily: It's another subject I have tons of musings on that need sorting out for…. Er … well I am not really sure what for yet. But it would be an advancement for mankind I expect, if done carefully, it usually is eventually, whatever I do.

I like these novel approaches out of interest, but I am less keen to use them myself often. I prefer using the looping idea because of the possibility to do step debug mode to check out things and problems.
The working of these novel approaches is something like the so called spreadsheet array formulas I think. A neat spreadsheet array formula is sometimes a good start point in developing these novel approaches. I never liked excel formulas and got into VBA instead perhaps sooner than many people after starting with Excel, or rather before I really had enough normal formula experience. With hindsight though, I recon. JIMHO, a basic Excel courses should get into VBA , maybe do a simple UDF, early on. Otherwise you don’t half miss a lot of potentially useful stuff.
Regards , Ālan , DocÆlstein :england: , :germany:

User avatar
SpeakEasy
5StarLounger
Posts: 742
Joined: 27 Jun 2021, 10:46

Re: Calculate letters weight

Post by SpeakEasy »

>I never liked excel formulas

And off we go in another direction ... (although I thjnk it already came up in this post): Application.Match (and WorksheetFunction.Match) are the excel functions. Not VBA. Just a way if using them in VBA (and yes, some internal calling conventions mean that the Application functions and the WorksheetFunctions actually operate fractionally differently from each other in VBA

>work efficiently in some way
They work more efficiently when dealing with blocks of Excel data becasue they work natively with that data (up to the point you need to do something VBAish with it, such as display it in a messagebox), whilst VBA functions need to extract the data before they can operate on it.

User avatar
DocAElstein
5StarLounger
Posts: 773
Joined: 18 Jan 2022, 15:59
Location: An Englishman, illegally re-routing rivers, in Hof, Beautiful Bavaria. Rule, Britannia!

Aldi Gewürz Spekulatius make you fat

Post by DocAElstein »

Yep, we have the historical guises of things like the Excel Match covered in this thread , ( possibly the first time the question of the difference in Application. and Application.WorksheetFunction was ever answered with more than a quick, unelaboratebaled "they handle errors a bit different".
It may be a fractional difference in their operation but it has the effect of determining whether array arguments work in the arguments nominally defined as taking a single value. (Application. version does work, Application.WorksheetFunction doesn’t.). It is the difference that makes these interesting approaches possible or not.

I probably should have worded ….I never liked excel formulas … a bit differently: What I really meant to say is something more specific along the lines, of , I was annoyed that I had not got into VBA even quicker than I did, as I had some big complicated spreadsheets full or long complicated formulas, and that would have been a lot easier to get done, correct, and modify if I had done a lot of the stuff in VBA instead. As a home user, or someone pertaining to maybe educate the home users one day, efficiency may be not so important, but I think the things you can do and the ease in which you can do them rises asymptotically as you learn some basic VBA.
( I would not suggest introducing these novel approaches early on: they are perhaps best introduced at some later academic/curiosity learning level)

Lots of useful bits in this thread, maybe another I need another quick check through and I will have scraped out the last gems of info, and will be finished here for this year, or so … so … Happy Xmas Day to you and everyone, :-) , (and be careful with all that unhealthy food: I bought a lot of packets of some Aldi "Gewürz-Spekulatius" (some sort of spicy biscuit) , just because I found the packets so Xmasey pretty coloured and fun to play with like pretty Xmas present building blocks, But I swear I am seriously putting on at least the weight of every packet I eat each day. I think the stuff must get stuck somewhere inside?, maybe they need some alcohol to dilute them/ break down the fat? – maybe that should be the next Xmas research experiment…. :cheers: :king: )

Alan
Regards , Ālan , DocÆlstein :england: , :germany: