calculate a checksum

[LtB]

Hello,

I need a function to calculate a checksum om a number with variable number of digits N:
1 x N1 +2 x N2 +3 x N3 etc.
How can I do such a thing?
So far I have never programmed in VBA, only recorded a macro, and installed a freeware function (ReverseString()).
:-(
I think I need the number of digits, put it in a counter, and then successively multiply the fund digits with the counter, add that to a variable, decrease he counter, and exit when the counter is zero.
But how to get the right syntax?
How to build it in a function?

[mikerickson]

You don't need vba. Something like this should work

=SUMPRODUCT({1,2,3,4,5,6,7,8,9,10}*MID(TEXT(A1,"0000000000"),{1,2,3,4,5,6,7,8,9,10},1))

[LtB]

You don't need vba. Something like this should work

=SUMPRODUCT({1,2,3,4,5,6,7,8,9,10}*MID(TEXT(A1,"0000000000"),{1,2,3,4,5,6,7,8,9,10},1))
Eh.. This is a construction that I don'recognize :-(
That means,that I can't modify it, because in reality it is abit more complicated.
The string is a CAS number, an identifier for a chemical substance.
Up to now the string can have the form #9#8#7#6#5#4 - #3#2 - #1
Notice the dashes present! (The spaces are added here for readability)
The digits 9,8,7 and 6 need not be present, a digit #10 is not valid up to now, but that will change in a few months.

I need the sum of #2 + 2*#3 +3*#4 etc.
The last figure of this checksum must equal digit #1 in order to have a valid CAS-number.

I am sure that you guys will able to build this functionality, compete with error messages, in the formula, but I could not maintain that when ACS changes format rules by adding extra numbers when necessary.
A function in VBA would be better readable, and be available in all files that I need to check (a dozen a week, now up to 140.000 substances already present. Part of the numbers (7 difgits) have been interpreted as dates by wrong formatting by ignorant secretaries. Also a few times a figure 1 has been replaced by rhe characters I or l.
So I am sure that a lot of correction will be necessary.

When I get a proper function, I could modify it for validation of the EC numbers (Also identifiers, format ###-###-#, but I don't yet know the algorhitm to check these).
And then the Index numbers of the UN GHS system for packaging and labeling of chemicals...

That's why I am seeking a VBA-solution.

[Paul_Hossler]

Crude, and no error checking, but possible starting point


Option Explicit
Function Checksum(ID As String, Pattern As String) As Long
Dim i As Long, n As Long
Dim iTotal As Long, iMax As Long


iTotal = 0
For i = 1 To Len(ID)
If Mid(Pattern, i, 1) = "#" Then
n = n + 1
iTotal = iTotal + n * CLng(Mid(ID, i, 1))
End If
Next i

Checksum = iTotal
End Function

Sub test()
MsgBox Checksum("123-456-78", "###-xxx-##")
MsgBox (1 * 1) + (2 * 2) + (3 * 3) + (4 * 7) + (5 * 8)

End Sub


You might Google for VBA that uses a Hash code generator which might generate a more reliable number

Paul

[snb]

Is this what you mean ?


sub snb()
c00=3627983

for j= 1 to len(c00)-1
x=x+j*mid(c00,1,1)
next

msgbox right(c00,1)=x
end sub

[LtB]

Crude, and no error checking, but possible starting point


Option Explicit
Function Checksum(ID As String, Pattern As String) As Long
Dim i As Long, n As Long
Dim iTotal As Long, iMax As Long

iTotal = 0
For i = 1 To Len(ID)
If Mid(Pattern, i, 1) = "#" Then
n = n + 1
iTotal = iTotal + n * CLng(Mid(ID, i, 1))
End If
Next i

Checksum = iTotal
End Function

Sub test()
MsgBox Checksum("123-456-78", "###-xxx-##")
MsgBox (1 * 1) + (2 * 2) + (3 * 3) + (4 * 7) + (5 * 8)

End Sub


I have spent the weekend with a basic VBA-course, and this morning I have prepared a test-sheet with correct CAS#s of various length and an invalidated copy of them: changed the check-digit.
Your entry comes right in time to explore the various aspects.
Little mistake: CAS# has format [####]##-##-#, not [####]#-#-###-#




You might Google for VBA that uses a Hash code generator which might generate a more reliable number

Paul
I will remember that. First get aquainted to the VBE-environment.

[LtB]

Is this what you mean ?


sub snb()
c00=3627983

for j= 1 to len(c00)-1
x=x+j*mid(c00,1,1)
next

msgbox right(c00,1)=x
end sub
This came in while I answeed Paul Hossler.
I do not understand this (yet), but if it works, it is apparently very short, efficient code...

Thanks.

[snb]

This came in while I answered Paul Hossler.

I don't think so: look at the time stamps.....

[Paul_Hossler]

Little mistake: CAS# has format [####]##-##-#, not [####]#-#-###-#


When I get a proper function, I could modify it for validation of the EC numbers (Also identifiers, format ###-###-#, but I don't yet know the algorhitm to check these).
And then the Index numbers of the UN GHS system for packaging and labeling of chemicals...


In order to handle different patterns, I decided it'd be more flexible to pass the ### pattern in the call. No matter where the #'s are in the pattern, it's only that position in the ID that get's check-summed

Paul

[LtB]

I don't think so: look at the time stamps.....

I saw already that you are right. But at least I only saw it at that moment. :-)

[LtB]

In order to handle different patterns, I decided it'd be more flexible to pass the ### pattern in the call. No matter where the #'s are in the pattern, it's only that position in the ID that get's check-summed
Paul Well, I am still busy interpreting the syntax. Maybe this is still too advanced a level...
I will work my way to a satisfying working solution. And for sure, also too sophisticated solutions contribute to that.
Up to now I have a function that works. Now adaptation to various fault causes, then do smartthings with the result, e.g. check hundreds of lines in one go, and make an external error report.

Programming is fun. It's been almost 20 years that I did that, in Clipper... If only I did not need the result so quickly...

Thanks anyway!