Excel公式到VB.NET [英] Excel equation to VB.NET

问题描述

大家好，我面前有个艰巨的任务，

我正在创建一个程序，将会员编号转换为同一张卡上的条形码，现在我遇到的问题是，在两者之间进行转换的唯一方法是使用excel公式MOD，我需要能够使用这在VB.NET中却没有找到一种方法..

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

在VB中，我已通过将会员编号"分成个位数输入来设法获得所有编号的总和，

VB(到目前为止的代码)

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)

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 

但是上面的代码返回的负数包含一个以上的整数.

如果有人可以帮助我，那太好了，我一直在寻找2周，但没有成功...

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...

推荐答案

如果您在Excel中进行计算:
=MOD(10-MOD(SUM(Q21;O21;M21;K21;I21;G21)*3+Sum(P21;N21;L21;J21;H21;F21);10);10)
您需要在VB.NET中做同样的事情

不是优雅的解决方案，而是一个主意:

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:

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是一个字符串数组-不好的主意！它应该是目标格式，例如整数值的数组:

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

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

请阅读:
http://www.startvbdotnet.com/language/arrays.aspx [ http://msdn.microsoft.com/en-us/library/wak0wfyt.aspx [ ^ ]

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

这篇关于Excel公式到VB.NET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！