## Calculate letters weight

YasserKhalil
PlatinumLounger
Posts: 4936
Joined: 31 Aug 2016, 09:02

### Calculate letters weight

Hello everyone

I have two arrays in a udf one array for letters Array("A", "B", "C", .... "Z")
and another array for the weight for each letter Array(1, 5, 3, ...... 2)

How can I calculate the weight for word "ZAC" ?? The output should be 2 + 1 + 3 = 6 so 6 is the expected final output

HansV
Posts: 78666
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Calculate letters weight

Code: Select all

``````Function Weight(s As String) As Long
Dim Letters, Weights
Dim i As Long
Letters = Array("A", "B", "C", ..., "Z")
Weights = Array(1, 5, 3, ..., 2)
For i = 1 To Len(s)
Weight = Weight + Weights(Asc(Mid(s, i, 1)) - 65)
Next i
End Function
``````
With "ZAC" in A1, use =Weight(A1)
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4936
Joined: 31 Aug 2016, 09:02

### Re: Calculate letters weight

Amazing my tutor. Thank you very much for this awesome solution.

DocAElstein
5StarLounger
Posts: 621
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

### Re: Calculate letters weight

Hello,
I was a bit frustrated not understanding how Hans function was working. He has kindly put me out of my misery and explained it.
On the off chance someone else is puzzled, these notes I made myself might help

_.___________________________________________

Possibly a beautiful single line non looping solution might be possible as an alternative. I doubt there would be any performance advantages though. But maybe I will post one later , just out of academic interest, if I do one.

Alan
Last edited by DocAElstein on 24 Jul 2022, 14:47, edited 1 time in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

SpeakEasy
4StarLounger
Posts: 575
Joined: 27 Jun 2021, 10:46

### Re: Calculate letters weight

Assuming lowercase characters have the same weights as their capital counterparts means a minor change will handle upper- and lowercase strings

Modifying

Weight = Weight + Weights(Asc(Mid(s, i, 1)) - 65)

to

Weight = Weight + Weights(Asc(UCase(Mid(s, i, 1))) - 65)

DocAElstein
5StarLounger
Posts: 621
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

### Re: Calculate letters weight

Hello
DocAElstein wrote:
22 Jul 2022, 07:19
...Possibly a beautiful single line non looping solution might be possible as an alternative.
I had a go, and was quite pleased I did, for two reasons.
_1 It was a lot easier than I thought and much shorter than typical for these beautiful , (but often impractical due to long code line length ), things. So it could be a practical alternative in this case. ( It’s not quite a single line, - it needs two code lines, for a strange reason……. )
_2 I noticed something a bit strange, which is interesting and so I think worth “making public”. It prevented me making it a single line.

_.__________________________________________________________

_1The Solution
This is an almost one liner solution,
using the example of text “ZAC

Code: Select all

``````Sub AsKeys()                                                       '  http://www.eileenslounge.com/viewtopic.php?p=297288#p297288
Dim arrZAC() As Byte: Let arrZAC() = StrConv("ZAC", vbFromUnicode) ' https://stackoverflow.com/questions/13195583/split-string-into-array-of-characters
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(arrZAC(), Array(65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90), 0)))
End Sub

' Function version of solution
Sub TestWaits()
MsgBox prompt:=Waits("ZAC")
End Sub
Public Function Waits(ByVal Es As String) As Long
Dim arrZAC() As Byte: Let arrZAC() = StrConv(Es, vbFromUnicode) ' https://stackoverflow.com/questions/13195583/split-string-into-array-of-characters
Let Waits = 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(arrZAC(), Array(65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90), 0)))
End Function``````
( I have used the example of wanting to have weights for the 26 capital letters. It’s easy to modify this solution for a lot more characters – just add their Ascii code to the Ascii code array, and add a corresponding weight in the same position in the weights array. ( The solution does not have a letters array, but instead requires an array of the Ascii codes of the characters you want to have weights for. ) )

Here is the full story :
Comparing to typical standards for these things, its not very long

=.====================================================================================================================

_2 The strange problem
I will probably look at this again in more detail when I have time, as figuring these things out often reveals useful things. …_ For now I thought I would just mention it, out of interest. Perhaps someone has an idea what’s going on?
….._
So … usually the way I do these solutions is not mind boggling: I start with a long code with lots of variables to get things, and then usually end up with a final code line at the end using all those variables. ( I try to make each variable got from a single code line).
So then I just go through carefully replacing the variables in the last code line with the code line used to get that variable. Then the variables and the coding to get them is redundant. So you trash that all, and are left with just that final code line.

One thing is not working in this attempt of mine to make a one liner.
Strange.
I wont go into boring details now, as I have not fully investigated the problem myself yet. So I will just give a quick demo code for now, for anyone that is interested.
In the following coding snippet, there is a code line that errors. ( I 'commented it out ) It shouldn’t, or rather I don’t know why it errors.
What I do there is , as usual, is replacing a variable in a code line with the code used to get that variable.
It errors in this case. That is responsible for me having to have for now , my two line code solution given above..

Code: Select all

`````` Sub SumfinkStrageGoingOn()               '  https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=16654&viewfull=1#post16654
' The following code line gets me a 3 element, 1 dimensional array of the  Ascii codes  for  the three characters  Z  A  and  C  in that order
Dim arrZAC() As Byte: Let arrZAC() = StrConv("ZAC", vbFromUnicode) ' = {90, 65, 67} '  Watch : + : arrZAC() :  : Byte(0 to 2) : Module1.SumfinkStrageGoingOn       ' https://stackoverflow.com/questions/13195583/split-string-into-array-of-characters
' No problem so far....

' The numbers in the second Match argument below are the  Ascii codes for  "C","A", "Z"
Dim MtchRes() As Variant: Let MtchRes() = Application.Match(arrZAC(), Array(67, 65, 90), 0) ' WORKS as expected  -  result is an array  {3, 2, 1}
' Still no problem. The problem comes when trying to do a similar code line with the code line used to get arrZAC(),   in place of that variable, arrZAC()
'Let MtchRes() = Application.Match(StrConv("ZAC", vbFromUnicode), Array(67, 65, 90), 0)   ' THIS WILL ERROR ?????

Let MtchRes() = Application.Match(Array(90, 65, 67), Array(67, 65, 90), 0)  ' WORKS

Let arrZAC(0) = 90: arrZAC(1) = 65: arrZAC(2) = 67
Let MtchRes() = Application.Match(arrZAC(), Array(67, 65, 90), 0)   '  WORKS

End Sub
``````

Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

SpeakEasy
4StarLounger
Posts: 575
Joined: 27 Jun 2021, 10:46

### Re: Calculate letters weight

Alan,

You probably need to make it clear that your 1-2 liner only works in Excel as the key functions you are using belong to Excel not VBA (.Index and .Match) and are not available in other VBA hosts (nor in classic VB).

Ok, so StrConv does NOT result in a byte array; it results in a string. VBA has the ability to coerce the type of certain variables, and that's what you have done with:

Let arrZAC() = StrConv("ZAC", vbFromUnicode)

You have coerced a string (the return from StrConv) into a byte array (because you declared arrZAC as a byte array) by carrying out the assignment

In the one-liner version, no such coercion can occur, so you end up passing a string to the Match method (an ANSI one in this case), and thus you are trying to match a string against bytes, so naturally no match is found - which generates an Excel #N/A error

DocAElstein
5StarLounger
Posts: 621
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

### Re: Calculate letters weight

Thanks, that’s an interesting explanation. Sounds good, - I doubt I would ever have thought of all that, or rather not for a very long time anyway: Solving these strange little mysteries is often quite revealing, but usually take me a few years to figure them out, - so thanks for sharing your knowledge here, ( and saving me a lot of time!).

I have come across VBA coercing stuff, - often very conveniently, ( for example I often find it particularly useful that VBA usually takes strings that look like numbers as if they were numbers.( I prefer to keep numbers as strings. Just a strange personal preference: For one thing it seems to help stop Excel deciding what format or type, and/ or annoyingly changing it wrongly or guessing wrongly what I want ) ). But I would never have figured that coercing had something to do with the strange thing I was observing.
I would never have figured that coercing, and the other stuff you mentioned about using Excel, had something to do with the strange thing I was observing, because
_a) I would never have thought that it was capable of coercing a string to an array. Not sure why, but coercing a string to an array just sounds to a Computer Layman like me as a different thing to changing a variable Type. I tend not to think of an array itself as a Type, although I have come across it referred to as such.
Looking at it from a Layman / Computer idiot point of view: I can have different types of cars, but if I have a lot of cars, all of the same type, I don’t refer to having lots of cars as a Type.
Question: What type of Car have you got, mate?
Answer: My type of car is lots of the same type
That’s nonsense. ( Or sounds like an answer from Den Dennis, for any cultured people of my generation who remember the Rock band Bad News )
_b) I would never have thought of it in terms of Excel not coercing. I had not really thought of it in terms of me using Excel stuff. But that thinking direction of me using Excel does make some sense to me:

I have often heard it said that these .Match and .Index things that I like to play within VBA coding are worksheet functions, and that regardless of if I use the Application or Application.WorksheetFunction version. I had not quite twigged that it was not visual basic or VBA stuff, or rather had not quite got the significance of that, such as it helping to explain my strange thing.

A bit off topic, but similar mystery direction , a bit: These things I like to play with, ( of using an array as an argument where more usually a single value is used) , usually only seem to work with the Application version and not the Appication.WorksheetFunction version.
People a lot smarter than me , ( note: that still doesn’t necessarily mean they are very clever , Lol ), often throw in the explanation , “.. its all to do with the different way they do error handling…” but they either can’t or wont elaborate on that so it takes me no further. The only concrete thing I know about in that respect is that if Application.Match does not find a match it returns some error thing, ( which can be convenient to test for), whereas Appication.WorksheetFunction actually “really errors” if it does not find a match. But that does not take me further in understanding the behaviour of the things I like to play with either…

Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

StuartR
Posts: 12633
Joined: 16 Jan 2010, 15:49
Location: London, Europe

### Re: Calculate letters weight

I really wish that ALL coercion had to be explicit. I have seen so many errors due to people not realising that this is happening.
StuartR

SpeakEasy
4StarLounger
Posts: 575
Joined: 27 Jun 2021, 10:46

### Re: Calculate letters weight

>I tend not to think of an array itself as a Type

Because it isn't. Arrays effectiovely have types, though. So you might have an array of String, an array of bytes, and array of longs - that's how you declare them ...

So

Question: What type of Car have you got, mate?
Answer: My type of car is lots of the same type

Might be

Question: What type of Car have you got, mate?
Answer: I have lots of cars; the are all the same type: Porsche 911s

> would never have thought that it was capable of coercing a string to an array. Not sure why,

So, here's the thing. Without going into all the low-level detail, a VB string is just a chunk of sequential memory containing the bytes representing the characters in the string (I know I said I wouldn 't get low level, but In VBA's case this happens to be a Unicode-16 representation of an ANSI string,). And an array of bytes ... is just a chunk of sequential memory containing bytes. In other words they are pretty much the same thing, just a different context. So coercing from one to the other is actually very straightforward, and works both ways. Try

Dim arry() As Byte

arry = "DocAElstein" ' coerce string to array of bytes
MsgBox arry ' coerce array of bytes to a string
Stop ' inspect local variable arry here to see how a VB string is actually represented internally

SpeakEasy
4StarLounger
Posts: 575
Joined: 27 Jun 2021, 10:46

### Re: Calculate letters weight

>strings that look like numbers as if they were numbers.

Be careful with that. For example, what would you expect the result of

"5" + "6"

to be?

StuartR
Posts: 12633
Joined: 16 Jan 2010, 15:49
Location: London, Europe

### Re: Calculate letters weight

56?
StuartR

SpeakEasy
4StarLounger
Posts: 575
Joined: 27 Jun 2021, 10:46

### Re: Calculate letters weight

>its all to do with the different way they do error handling…

Application.WorksheetFunction.aFunction raises an traditional error if the function fails, which can be handled using the normal VBA error handling tools (On Error etc.)

Application.aFunction does NOT raise an error. Instead it returns a Variant of type Error. Ideally, one would dim the variable receiving the result of Application.aFunction as a variant, which you can then query to see if it is type Error using VarType

SpeakEasy
4StarLounger
Posts: 575
Joined: 27 Jun 2021, 10:46

### Re: Calculate letters weight

>56

Sure, if they are strings and you are (vaguely) aware that the + operator has been (foolishly) overloaded to do concatentation (when VBA already has a perfectly good concatenatrion operator) - but Alan was extolling the virtues of being able to handle strings that look like numbers as number, which would suggest some people might anticipate the result should be "11"

StuartR
Posts: 12633
Joined: 16 Jan 2010, 15:49
Location: London, Europe

### Re: Calculate letters weight

That is why I hate implicit coercion
StuartR

HansV
Posts: 78666
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Calculate letters weight

We're straying far from the original subject, but since + as a concatenation operator came up: it behaves differently from & in one respect:

"Eileen" & Null returns "Eileen"
"Eileen" + Null returns Null
Best wishes,
Hans

StuartR
Posts: 12633
Joined: 16 Jan 2010, 15:49
Location: London, Europe

### Re: Calculate letters weight

Wow!
StuartR

SpeakEasy
4StarLounger
Posts: 575
Joined: 27 Jun 2021, 10:46

### Re: Calculate letters weight

>+ as a concatenation operator

Well not quite. I mentioned it is overloaded, and only acts as a concatenator given certain arguments. This instance is not one of those, and it is acting as addition. And in the world of computer maths x + null = null

DocAElstein
5StarLounger
Posts: 621
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

### Re: Calculate letters weight

Hello
SpeakEasy wrote:
25 Jul 2022, 10:42
>I tend not to think of an array itself as a Type
Because it isn't. Arrays effectiovely have types, ...
This is what I thought, originally, but had heard different. I had sometimes seen it referred to as a type.
Possibly I was mislead by a few things.
For example,
_ the word type may have been used and I mistakenly took it literally, - I expect I often have seen something of the form …. Its an array type… used , when it was meaning more something like …. Its an array sort of thing
_ also, some documentation mislead me a bit: I have not used VarType very much for anything other than the most common simple variable, and then when I used that once on an array, it gave me the number 8209 which means you have an array. VarType does not tell you what variable types are in the array; Along similar lines, if you look quickly at this documentation you may in error, as I probably did, think it was listed there as the last data type - it isn’t listed there as the last data type, but if you just quickly scroll through, its easy to make that mistake.,

So I think I got it now that an array is not a variable type, which is what I originally thought but got mislead along the way.
I expect if you gave this , Dim arrZAC() As Byte , to a 100 people and asked them what arrZAC() is, 30 would say its an array, 30 would say its an array with elements of byte type, and 30 might say ( incorrectly ), the variable arrZAC() is an array type

Anyway, that’s cleared that one up for me, now, thanks.

_.____________________________________________
SpeakEasy wrote:
25 Jul 2022, 10:49
..what would you expect the result of "5" + "6"
I would have put my money on "56"
I would have put my money on "56" , just because I have seen the + sometimes used in a text string as an alternative to the &
I also thought it was a bit silly to let it be used like that as well.
( I have not yet seen a situation where VBA would take "5" + "6" as 11 . But there might be one or more occasions I don’t know about? )

My own, not by any means vast, experience that led me to say …. I often find it particularly useful that VBA usually takes strings that look like numbers as if they were numbers… was based mostly on putting string variables with contents looking like numbers, into functions or formulas expecting numbers.
So far, in my experience, if I see something in VBA enclosed in a pair of “ “ , then that has always told VBA that what inside that “ “ is to be taken as the text it is, and nothing else. But once again, there could be example I don’t know about that don’t always follow that rule

_.____________________________________________________

SpeakEasy wrote:
25 Jul 2022, 10:42
...
..
....coercing from one to the other ...
Thanks, you’ve set me straight on what coercing is. I mistakenly thought it was just to do with changing variable types.

Perhaps another way to think of what coercing is, is as follows:
Maybe it’s a sort of pseudo error handling? Perhaps in some situations, there is a list of things for VBA to try if something does not fit. So it tries them to see if it can avoid the error by doing so. - Just an idea on how to think about it, that’s all.
What I mean is, looking at that example , "5" + "6"
Initially that looks nonsense to VBA, or looks like some sort of type mismatch, so then VBA goes to its list of things to try, and the first one it has in its list of things to try in this sort of situatuion, is ….“try changing + to a &”

( Now that I come to think about it, I may have seen coercing other than changing variable type before, of a sort. I have sometimes put an object variable where a string was expected and some sort of string reference is returned. Maybe that is somehow related to pointing to some interface to expose, or some such codswallop I like to confuse myself with from time to time… - Named ranges and named things and path names and path references seem to have some low level relationship to the object they refer to , or some such something I don’t know what I am talking about again

_._______________________________________________________________
Last edited by DocAElstein on 27 Jul 2022, 11:28, edited 2 times in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

DocAElstein
5StarLounger
Posts: 621
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

### Re: Calculate letters weight

SpeakEasy wrote:
25 Jul 2022, 10:42
.. Try
Dim arry() As Byte
Let Arry() = "Alan" ' coerce string to array of bytes
MsgBox arry() ' coerce array of bytes to a string

' inspect local variable arry here to see how a VB string is actually represented
I had a look at that, and a few variations.
I see it gives me the Ascii Code for each character in my name, but also includes a 0 after each one, so I get like for Alan
a 8 element array with the values in it of
65
0
108
0
97
0
110
0

So maybe that is telling me that Alan is represents internally by such an array. I don’t know why it needs an extra Chr(0), ( but I know that someone messing with some Chr(0)s did some naughty messing at my forum once , but I did not know what all that was about. Someone else had to fix it for me. Clever low level stuff , I expect )

One thing that I don’t understand yet, which if I did might help to understand what is going on with all this, or might not…
… So, These “tricks” , or the messing about here with the Byte array stuff, all came about as I wanted to turn a text into an array of the characters in the text…._

Code: Select all

``````Dim Harry() As Byte
Let Harry() = "Alan" ' coerce string to array of bytes

Dim arrAlan() As Byte: Let arrAlan() = StrConv("Alan", vbFromUnicode)
``````
_.... This only works if the array elements type is Byte
Declare the type as Long or Double, for example, and it won’t work.

Is this just because VBA has just been written to coerce for us conveniently when it sees the attempt to assign a string to an array for the case of a Byte element type array. Or is there some deeper significance which I am missing?

This limitation to a Byte array might be inconvenient to someone wanting to play around with the AscW stuff?

Or I could be missing something more fundamental

_._____________________________________________________

SpeakEasy wrote:
25 Jul 2022, 11:27
>its all to do with the different way they do error handling

Application.WorksheetFunction.aFunction raises an traditional error if the function fails, which can be handled using the normal VBA error handling tools (On Error etc.)

Application.aFunction does NOT raise an error. Instead it returns a Variant of type Error. Ideally, one would dim the variable receiving the result of Application.aFunction as a variant, which you can then query to see if it is type Error using VarType
But it was not taking me any further in understanding something, which was the point I was trying to get across…
I probably did not make myself clear…
What I was talking about was that, these things I like to play with work for Application.x but not for Application.WortksheetFunction.x

Code: Select all

`````` Sub MatchAndIndexWithArrayArgumentsStuff()
Dim varTemp As Variant
Let varTemp = Application.Index(Array(1, 2), Array(1, 1), Array(2, 1)) ' WORKS  Returns array {2, 1}
Let varTemp = Application.Match(Array(1, 2), Array(1, 2), 0)           ' WORKS  Returns array {1, 2}

' Let varTemp = Application.WorksheetFunction.Index(Array(1, 2), Array(1, 1), Array(2, 1)) ' This will error
' Let varTemp = Application.WorksheetFunction.Match(Array(1, 2), Array(1, 2), 0)           ' This will error
End Sub
``````
Some smart people have explained that away by saying … its all to do with the different way they do error handling
But they can’t or wont elaborate on that, so either I missed something obvious, or something else, but in any case , I am none the wiser.

Its not a major issue for me. Just curious why Application.x works but not Application.WortksheetFunction.x .
As ever solving these little issues sometimes reveals some useful stuff, that’s all

Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(