Excel equation to VB.NET

Question

4.00/5 (1 vote)

See more:

Hi guys i have a huge task in front of me,

I am creating a program that turns a Member Number into a bar code on the same card, now the issue i am having is the fact that the only way to convert between both is using the excel equation MOD, i need to be able to use this in VB.NET but have not found a way to do so..

Excel
=MOD(10-MOD(SUM(Q21;O21;M21;K21;I21;G21)*3+Sum(P21;N21;L21;J21;H21;F21);10);10)

In VB i have managed to get the sum of all the numbers by splitting the Member Number into single digit entries,

VB (Code so far)

VB

Dim strMemberNumber As String
Dim intLength As Integer = strMemberNumber.Length
Dim strMumberNum() AS String
Dim i As Integer = 0

Do Until i = intLength
strMemberNum(i) = strMemberNumber.substring(0,1)
strMemberNumber = strMemberNumber.remove(0,1)
i = i + 1
Loop

Dim strBarCode As String
'Creates the bar code
strBarCode = "279" & strMemberNum(6) & strMemberNum(7) & strMemberNum(8) & strMemberNum(9) & strMemberNum(10) & strMemberNum(11) & strMemberNum(12) & strMemberNum(13) & strMemberNum(14)

'Work Out last number
Dim intLastNumber As Integer
intLastNumber = Int(10 - Int(( strMemberNum(14) + strMemberNum(12) + strMemberNum(10) + strMemberNum(8) + strMemberNum(6) + strMemberNum(4) + strMemberNum(2)) * 3 + ( strMemberNum(13) + strMemberNum(11) + strMemberNum(9) + strMemberNum(7) + strMemberNum(5) + strMemberNum(3) + strMemberNum(1))/10)/10)

strBarCode = strBarcode & intLastNumber

But the above code returns back a negative number with more then one integer.

If some one could help me out that would be great i have been looking for 2 weeks now and no sucess...

Posted 10-May-12 10:15am

FusionOz

Add a Solution

Comments

Ed Nutting 10-May-12 16:22pm

You call Int( instead of MOD( - they arent the same. Int will return your the integer part of a number i.e. 10.1 = 10, 12.5 = 12 etc. - it does not do Modulo and your method of dividing by 10 certainly wont work. You need to use the VB.Net Mod operator - see this page for how to use it: MSDN Mod keyword.

Hope this helps,
Ed

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2012-05-10T11:00:00

If you make the calculations in Excel:
=MOD(10-MOD(SUM(Q21;O21;M21;K21;I21;G21)*3+Sum(P21;N21;L21;J21;H21;F21);10);10)
you need to do the same in VB.NET

Not elegant solution, but an idea:

VB

int mymodulo AS Integer = 0
'first step
intLastNumber = CInt(strMemberNum(14) + strMemberNum(12) + strMemberNum(10) + strMemberNum(8) + strMemberNum(6) + strMemberNum(4) + strMemberNum(2)) * 3 
'second step
intLastNumber = intLastNumber + CInt( strMemberNum(13) + strMemberNum(11) + strMemberNum(9) + strMemberNum(7) + strMemberNum(5) + strMemberNum(3) + strMemberNum(1))
'3 step
mymodulo = intLastNumber mod 10
'4 step
intLastNumber = 10 - mymodulo
'5 step
mymodulo = intLastNumber mod 10
'6 step
intLastNumber= mymodulo

strMemberNum is an array of string - bad idea! It should be destination format, for example array of integer values:

VB

Dim iMemberNum = New List(Of Integer)
Dim i As Integer = 0
For i = 0 To 13
    iMemberNum.Add(i)
Next

Please, read it:
http://www.startvbdotnet.com/language/arrays.aspx[^]
http://msdn.microsoft.com/en-us/library/wak0wfyt.aspx[^]