Javascript funchtion converting to Excel

K

Kamila

Hi
I am very new to use with Excel programming but have some idea how to use
Excel. I have question
I have 3000 ID numbers. Length of ID number is must be 11 and i have one
formula for checking of right IDs and i want to use that formula in Excel
2003 to check right ID and give me result of wrong or right IDs.

How can i use that forumula in the Excel? I make formula in the Java script
and dont know how to use in Excel 2003.

<html>
<head>
<title> JScript for checking ID no </title>
</head>
<body>
<form action="" name="pnform">
<input type="button" value="Persnr" onclick="ValidateID()">
</form>

<script type="text/jscript">

function ValidateID()
{
var ID="22037035991" //!! ID no must be 11 sifer, it is right ID no.
var TmpStr
var Tmpr
var vekt1 = "376189452"
var vekt2 = "5432765432"
var tot = 0
var base = 11
var value
var vekt
var TmpStr2
var Tmpr2
var vekt2 = "5432765432"
var tot2= 0
var base = 11

// for first number
for (i=0;i<9 ;i++)
{
value = ID.substr(i,1)
vekt = vekt1.substr(i,1)
tot = tot + (parseInt(value) * parseInt(vekt))
}
i=tot/11
i=tot-(parseInt(i)*11)
i=11-i
TmpStr = ID
TmpStr = ID.substr(9,1)
Tmpr=(ID.length)

// for second number
for (y = 0; y < 10 ; y++)
{
value = ID.substr(y,1)
vekt = vekt2.substr(y,1)
tot2 = tot2 + (parseInt(value) * parseInt(vekt))
}
y=tot2/11
y=tot2-(parseInt(y)*11)
y=11-y
TmpStr2 = ID
TmpStr2 = ID.substr(10,1)
Tmpr2=(ID.length)

// integer check

if (base!=Tmpr)
{
document.write("You must enter 11 number for ID (0 - 9) ")
}
else if ( parseInt(i)==TmpStr && parseInt(y)==TmpStr2)
{
document.write("Thanks for right ID no.")
}
else
{
document.write("Wrong ID no.")
}

}

</script>
</body>
</html>


These are some correct ID Numbers
06084718767
05056021762
10065032304
02077539088

This number is also correct "22037035991"

These are some wrong ID Numbers
31074227037
09055119719
30468331842
15196221735

I just findout bad or wrond ID numbers.

best regards,
 
A

ANton Petrov

Is this working in HTML? I mean i fi copy the codein notepad and save it as
..html should it work?
 
A

ANton Petrov

when i copy the code in notepad and then save it as 1.html, open the page
with IE6 there is only one buton and nothing else
 
K

Kamila

If you view soruce then you can find out Id. As you can at the third line
under then function ValidateID.

function ValidateID()
{
var ID="22037035991" //!! ID no must be 11 sifer, it is right
ID no.
var TmpStr

brgd
 
N

NickHK

Kamila,
This should get you started. Certainly no guaranetee:

Dim ID As String
Dim TmpStr As Long
Dim Tmpr As Long
Dim vekt1 As String
Dim vekt2 As String
Dim tot As Long
Dim base As Long
Dim Valu As String
Dim vekt As String
Dim TmpStr2 As Long
Dim Tmpr2 As Long
Dim tot2 As Long

Dim i As Long
Dim y As Long

ID = "22037035991" '//!! ID no must be 11 sifer, it is right ID no.
vekt1 = "376189452"
vekt2 = "5432765432"
tot = 0
base = 11
vekt2 = "5432765432"

'// for first number
For i = 1 To 9
Valu = Mid(ID, i, 1)
vekt = Mid(vekt1, i, 1)
tot = tot + CLng(Valu) * CLng(vekt)
Next

i = tot / 11
i = tot - (CLng(i) * 11)
i = 11 - i

'TmpStr = ID
TmpStr = CLng(Mid(ID, 9, 1))
Tmpr = Len(ID)

'// for second number
For y = 1 To 10
Valu = Mid(ID, y, 1)
vekt = Mid(vekt2, y, 1)
tot2 = tot2 + CLng(Valu) * CLng(vekt)
Next

y = tot2 / 11
y = tot2 - CLng(y) * 11
y = 11 - y
'TmpStr2 = ID
TmpStr2 = CLng(Mid(ID, 10, 1))
Tmpr2 = Len(ID)

'// integer check
If base <> Tmpr Then
MsgBox "You must enter 11 number for ID (0 - 9) "
ElseIf (i = TmpStr) And (y = TmpStr2) Then
MsgBox "Thanks for right ID no."
Else
MsgBox "Wrong ID no."
End If

NickHK
 
N

NickHK

Anton,
Add a module to an Excel project, then you can make into a function, passing
the number (or 2 numbers, I'm sure how you implementation works) and return
a string or Boolean. e.g.

Public function TestID (ID1 as string) as String
'etc
ID=ID1
'etc...
If base <> Tmpr Then
TestID = "You must enter 11 number for ID (0 - 9) "
ElseIf (i = TmpStr) And (y = TmpStr2) Then
TestID = "Thanks for right ID no."
Else
TestID = "Wrong ID no."
End If

End function

call it from a worksheet with:
=TestID(A1)

Depends where vekt1& vekt2 are coming from.

NickHK
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

challenge! javascript function into excel function 2
Use of java script Formula in the Access 2003 3
Can't manipulate data send from one form to another 1
Can one include data from javascript in form 1
External JavaScript File 1
Need assistance with secondary axis in Excel 2007 0
newbie trying to use data access web page (Access 2003) 0
Exporting Access to Excel on Multiple Named extra sheets created 1

Top