Excel Mod和VB.NET Mod给出两个不同的答案 [英] Excel Mod and VB.NET Mod give two different answers

问题描述

大家好

我有下面的代码来计算条形码编号的最后一个数字，但是我遇到的问题是Excel给了我正确的最后一个数字，但是VB.NET没有，我的代码是下面的.

Hi guys

I have the code bellow to work out the last number of a barcode number but the issue i am having is that Excel gives me the correct last number but VB.NET does not, my code is bellow.

Dim strMembershipNumber As String
'Clear the console Window
Console.Clear()
'Get the Membership number from user
Console.WriteLine("Membership number to Barcode")
Console.WriteLine("What is the membership number?")
Console.WriteLine("This is the number on the back of the card not the Account Number")
Console.WriteLine("---------------------------------------------------------")
strMembershipNumber = Console.ReadLine

Dim strNumbers(15) As String
Dim intFirstSet As Integer = "279"
Dim intCount As Integer = strMembershipNumber.Length
Dim i As Integer

Do Until i = intCount
    strNumbers(i) = strMembershipNumber.Substring(0, 1)
    strMembershipNumber = strMembershipNumber.Remove(0, 1)
    i = i + 1
Loop



Dim intSum1 As Integer = strNumbers(14) + strNumbers(12) + strNumbers(10) + strNumbers(8)+ strNumbers(6) + 7
Dim intSum2 As Integer = strNumbers(13) + strNumbers(11) + strNumbers(9) + strNumbers(7)  + 9 + 2

Dim intMath As Integer = (intSum1 * 3) + intSum2
Dim intMod As Integer = intMath Mod 10
intMath = 10 - intMod
intMod = intMath Mod 10

Dim strBarcode As String = intFirstSet & strNumbers(6) & strNumbers(7) & strNumbers(8) & strNumbers(9) & strNumbers(10) & strNumbers(11) & strNumbers(12)                                     & strNumbers(13) & strNumbers(14) & intMod
Console.WriteLine(strBarcode)
Console.ReadLine()

上面的代码为6008943507588309返回4.

excel代码是
MOD(10-MOD(SUM(Q21，O21，M21，K21，G21)* 3 + SUM(P21，N21，L21，J21，H21，F21)，10)10)

这将返回7，表示相同的数字6008943507588309.

对于我的一生，我不知道我哪里出了问题，请帮助...

The above code returns 4 for number 6008943507588309.

The excel code is
MOD(10-MOD(SUM(Q21,O21,M21,K21,G21)*3+SUM(P21,N21,L21,J21,H21,F21),10)10)

this returns 7 for the same number 6008943507588309.

For the life of me i don''t know where i have gone wrong, please help...

推荐答案

我在以前的帖子 [

I''ve gave you an answer in the previous post[^].

Because you have an answer, i shoudn''t help you... But i decide to show you how to achieve that using class.

Steps to do:
1) Create new project (Windows application)
2) Place on the form 3 controls:
- Label1
- TextBox1
- Button1 and rename it Name as CmdVerify
3) Insert into project 3 items:
- new class (change its name to: TBarCodeVer)
- new interface (change its name to: IBarCodeVer)
- new module (change its name to: ModMain)
4) copy the below parts of code and paste it into destination modules
IBarCodeVer.vb

Public Interface IBarCodeVer

    Property MemberShipNumber() As String
    Function BarCode() As String

End Interface

TBarCodeVer.vb

TBarCodeVer.vb

Public Class TBarCodeVer
    Implements IBarCodeVer

    Private sNumber As String = String.Empty
    Private Const sFirstSet As String = "279"

    Public Sub New()
        'do nothing ;)
    End Sub

    Public Property MemberShipNumber() As String Implements IBarCodeVer.MemberShipNumber
        Get
            Return sNumber
        End Get
        Set(ByVal value As String)
            sNumber = value
        End Set
    End Property

    Function BarCode() As String Implements IBarCodeVer.BarCode
        Dim sNumbers() As String = Nothing, sTmpNumber As String = String.Empty
        Dim i As Integer = 0, iCount As Integer, sRetVal As String = String.Empty
        Dim iSum As Integer = 0

        Try
            iCount = sNumber.Length
            If iCount <> 16 Then
                MsgBox("The length of MemberShipNumber must be equal 16!", MsgBoxStyle.Exclamation, "Error!")
                Exit Try
            End If

            sTmpNumber = sNumber
            Do Until i = iCount
                ReDim Preserve sNumbers(i)
                sNumbers(i) = sTmpNumber.Substring(0, 1)
                sTmpNumber = sTmpNumber.Remove(0, 1)
                i = i + 1
            Loop

            '=MOD(10-MOD(SUM(Q21;O21;M21;K21;I21;G21)*3+Sum(P21;N21;L21;J21;H21;F21);10);10)
            iSum = (Integer.Parse(sNumbers(14)) + Integer.Parse(sNumbers(12)) + Integer.Parse(sNumbers(10)) + Integer.Parse(sNumbers(8)) + Integer.Parse(sNumbers(6)) + 7) * 3 'previous was:  + Integer.Parse(sNumbers(4)) + Integer.Parse(sNumbers(2))) * 3
            iSum = iSum + Integer.Parse(sNumbers(13)) + Integer.Parse(sNumbers(11)) + Integer.Parse(sNumbers(9)) + Integer.Parse(sNumbers(7)) + 11 'previous was: + Integer.Parse(sNumbers(5)) + Integer.Parse(sNumbers(3)) + Integer.Parse(sNumbers(1))
            iSum = (10 - (iSum Mod 10)) Mod 10

            sRetVal = sFirstSet & sNumbers(6) & sNumbers(7) & sNumbers(8) & sNumbers(9) & sNumbers(10) & sNumbers(11) & sNumbers(12) & sNumbers(13) & sNumbers(14) & iSum.ToString

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
        End Try

        Return sRetVal
    End Function

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub
End Class

ModMain.vb

ModMain.vb

Module ModMain
    'declare variable to store your class object by the interface
    Public oBarCode As IBarCodeVer = Nothing
End Module

Form1.vb

Form1.vb

Public Class Form1

    Private Sub CmdVerify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdVerify.Click
        oBarCode.MemberShipNumber = Me.TextBox1.Text
        MsgBox(oBarCode.BarCode, MsgBoxStyle.Information, "BarCode for number: " & oBarCode.MemberShipNumber)
    End Sub


    Public Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        'initilize class
         oBarCode = New TBarCodeVer
    End Sub

    Protected Overrides Sub Finalize()
        'destroy class
        oBarCode = Nothing
        MyBase.Finalize()
    End Sub
End Class

5)编译项目

上面的代码返回以下数字7:6008943507588309

5) Compile project

The above code returns 7 for the number: 6008943507588309

没有excel方程或strNumbers数组中引用的单元格中的值，没有人能够为您提供帮助.

但是，问题是简单的系统方法调试之一，您应该可以自己解决问题.

将excel方程分解为VB代码中所示的相同子计算就足够了，即intSum1，intSum2，intMath，intMod.比较每个VB和excel的结果，您可能会立即发现错误.

艾伦.

Without the values in the cells referred to in the excel equation or the strNumbers array no one is going to be able to help you.

However the problem is one of simple methodical debugging and you should be able to figure it out for yourself.

It should be sufficient to separate the excel equation into the same sub calculations that are shown in the VB code, i.e. intSum1, intSum2, intMath, intMod. Compare the VB and excel results of each one and you''ll probably spot the error immediately.

Alan.

这篇关于Excel Mod和VB.NET Mod给出两个不同的答案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！