Looping through a string

[jdmumper]

I need to calculate a checksum on a string in a cell. I want to take the CODE() of each character and multiply it by a constant and sum the results. Ideas?

[Ivan F Moala]

On 2002-05-20 06:19, jdmumper wrote:
I need to calculate a checksum on a string in a cell. I want to take the CODE() of each character and multiply it by a constant and sum the results. Ideas?

One way to do this is via a
User Defined function (UDF)

Try Cutting & Pasting this code into a Std
Module. If you require something a little
Diff eg. Formula or require further help
then Repost.

Function CheckSum(Rg As Range, K As Double) As Double

Dim dTmp As Double

Dim i As Integer



For i = 1 To Len(Rg.Text)

    dTmp = dTmp + Asc(Mid(Rg.Text, i, 1))

Next

CheckSum = dTmp * K

End Function

[jdmumper]

I knew this could be done via VBA, but I'd prefer to use a formula -- thanks for the VBA suggestion tho.

[Corni]

This is an ongoing discussion about when VBA code should kick in our work. From my experience, whenever the sheet ranges are static, i.e. they do not increase or decrease in size, I use formulas, but when the size of the working ranges is changing, i.e. is dynamic, I start using VBA code. As a rule of thumb, whenever looping statements are needed, you are forced of getting into VB. String manipulation is a typical example.

[Mark W.]

{=SUM(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROUND(GROWTH({10;1},{2;1},LEN(A1)+1-ROW(INDIRECT("1:"&LEN(A1)))),0))}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermose braces, {}, are supplied by Excel to indicate a properly entered array formula.

[Mark W.]

...As a rule of thumb, whenever looping statements are needed, you are forced of getting into VB. String manipulation is a typical example.

No loop is required.

[ldry]

On 2002-05-20 10:20, Mark W. wrote:

No loop is required.

But it is a good example of a VB solution being preferable to a worksheet formula solution.

[Mark W.]

On 2002-05-20 17:30, ldry wrote:

But it is a good example of a VB solution being preferable to a worksheet formula solution.

I'm not quite sure how you came to this conclusion, but the above VBA doesn't distinguish between words comprised of the same letters...

{"Word","Formula","VBA"
;"miles",1206925,538
;"smile",1270681,538}

And, by its very nature the VBA has the additional overhead of incrementing and checking loop counters.

[ This Message was edited by: Mark W. on 2002-05-21 11:04 ]