Question Excel Formula to Visual Basic

[vBn008]

Hi guys, I'm trying to make an app using visual studio 2010, I'm really new at this and I'm having a problem so I'm seeking for help here.

I have this excel formula that will calculate a 6 Digit code from a 11 digit serial number.

The excel is pretty old and it consts of math operations such as
RIGHT, LEFT, MID and POWER I managed to "translate" that to
Microsoft.VisualBasic.Right, Microsoft.VisualBasic.Left, Microsoft.VisualBasic.Mid and Math.Pow
but i'm not sure if i did it right, since is not giving me the result i'm looking for, instead of a 6 digit number is giving me something like this: 9.9E+52

I appreciate if you guys point me where my error is thanks.
Here's the code I made for the vB

Microsoft.VisualBasic.Right((Math.Pow(2, 5 + (Microsoft.VisualBasic.Left((Microsoft.VisualBasic.Left(txtESN.Text, 3)), 1) + Microsoft.VisualBasic.Right(Microsoft.VisualBasic.Left(txtESN.Text, 3), 1) + Microsoft.VisualBasic.Mid(Microsoft.VisualBasic.Left(txtESN.Text, 3), 2, 1))) - 1) * ((Microsoft.VisualBasic.Right(txtESN.Text, 3)) + 199) * (23 + (Microsoft.VisualBasic.Left((Microsoft.VisualBasic.Right(txtESN.Text, 8)), 1) + Microsoft.VisualBasic.Right((Microsoft.VisualBasic.Right(txtESN.Text, 8)), 1) + Microsoft.VisualBasic.Mid((Microsoft.VisualBasic.Right(txtESN.Text, 8)), 3, 1) + Microsoft.VisualBasic.Mid((Microsoft.VisualBasic.Right(txtESN.Text, 8)), 5, 1) + Microsoft.VisualBasic.Mid((Microsoft.VisualBasic.Right(txtESN.Text, 8)), 7, 1) + Microsoft.VisualBasic.Mid((Microsoft.VisualBasic.Right(txtESN.Text, 8)), 2, 1) + Microsoft.VisualBasic.Mid((Microsoft.VisualBasic.Right(txtESN.Text, 8)), 4, 1) + Microsoft.VisualBasic.Mid((Microsoft.VisualBasic.Right(txtESN.Text, 8)), 6, 1))), 6)

and this is the excel one

=IF(LEN($B$3)=11,RIGHT((POWER(2,5+(LEFT((LEFT($B$3,3)),1)+RIGHT(LEFT($B$3,3))+MID(LEFT($B$3,3),2,1)))-1)*((RIGHT($B$3,3))+199)* ' (23+(LEFT((RIGHT($B$3,8)),1)+RIGHT((RIGHT($B$3,8)),1)+MID((RIGHT($B$3,8)),3,1)+MID((RIGHT($B$3,8)),5,1) ' +MID((RIGHT($B$3,8)),7,1)+MID((RIGHT($B$3,8)),2,1)+MID((RIGHT($B$3,8)),4,1)+MID((RIGHT($B$3,8)),6,1))),6), ' "Need 11 Digit")

Thanks in advanced guys.

 

[jmcilhinney]

You need to qualify the Right and Left functions but using Microsoft.VisualBasic over and over makes your code hard to read. To help, you should alias the namespace. Add this to the top of your code file:

Imports VB = Microsoft.VisualBasic

you can then refer to just VB.Right and VB.Left, which will make your code far more readable.

 

[vBn008]

Oh yeah that definitely made the code short, so here it is I hope is easier to read now so you can help me out.
Thanks.

Private Function getMSL()
        Dim MSL As Double
        MSL = VB.Right((Math.Pow(2, 5 + (VB.Left((VB.Left(txtESN.Text, 3)), 1) _
            + VB.Right(VB.Left(txtESN.Text, 3), 1) + VB.Mid(VB.Left(txtESN.Text, 3), 2, 1))) - 1) _
            * ((VB.Right(txtESN.Text, 3)) + 199) * (23 + (VB.Left((VB.Right(txtESN.Text, 8)), 1) _
            + VB.Right((VB.Right(txtESN.Text, 8)), 1) + VB.Mid((VB.Right(txtESN.Text, 8)), 3, 1) _
            + VB.Mid((VB.Right(txtESN.Text, 8)), 5, 1) + VB.Mid((VB.Right(txtESN.Text, 8)), 7, 1) _
            + VB.Mid((VB.Right(txtESN.Text, 8)), 2, 1) + VB.Mid((VB.Right(txtESN.Text, 8)), 4, 1) _
            + VB.Mid((VB.Right(txtESN.Text, 8)), 6, 1))), 6)
        Return (MSL)
    End Function

Also I attached the excel so you can see what exactly it does.
View attachment lockcode.zip
Thanks again.

 

[jmcilhinney]

That code is more readable but it's still hard to read. I would suggest breaking it up into parts and using some local variables to store intermediate values. Do you actually understand what the Excel formula is doing? If not, that would be the place to start. Trying to reproduce something that you don't really understand is always a challenge.

 

[vBn008]

Yeah I guess is gonna be hard for me to do this, I don't really understand exactly the excel, I know is a big math operation that calculates that code using different operators but I don't understand it %100 so I'll follow your advice and start from there, I hope it gets easier when I understand it (if I get to that point lol)

 

[vBn008]

Hi, just wanted to let you know that I finally did it
I took apart the excel formula and understand it, then I adapt it to vb and it works wonderful, so thanks jmcilhinney for the advice and I guess you can close the thread or edited to make it solved?