ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Javascript funchtion converting to Excel (https://www.excelbanter.com/excel-programming/382222-javascript-funchtion-converting-excel.html)

Kamila
Javascript funchtion converting to Excel
 
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,

--
Kamila

ANton Petrov[_2_]
Javascript funchtion converting to Excel
 
Is this working in HTML? I mean i fi copy the codein notepad and save it as
..html should it work?

Kamila
Javascript funchtion converting to Excel
 
yes it is working you can check at html
--
Kamila


"ANton Petrov" wrote:

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

ANton Petrov[_2_]
Javascript funchtion converting to Excel
 
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

Kamila
Javascript funchtion converting to Excel
 
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

--
Kamila


"ANton Petrov" wrote:

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

NickHK
Javascript funchtion converting to Excel
 
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

"Kamila" wrote in message
...
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,

--
Kamila

ANton Petrov[_2_]
Javascript funchtion converting to Excel
 
where should i copy this code in Excel?
Thanks :)

NickHK
Javascript funchtion converting to Excel
 
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

"ANton Petrov" wrote in message
...
where should i copy this code in Excel?
Thanks :)


All times are GMT +1. The time now is 11:30 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com