Solved

Transferring code from .NET to VBA

Posted on 2008-10-29
10
186 Views
Last Modified: 2013-11-25
I need to make this .NET code work inVBA, but I'm not really familiar with VBA.

thank you

        Dim tableau() As Char = {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F", "G",  "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "*"}
        Dim sum As Integer
        Dim charValue As Integer
        Dim longueur As Integer
        Dim x As Integer
        Dim code As String
        Dim car As Char
        Dim mod1 As Integer
        Dim checkcar As Char

        code = TextBox1.Text
        longueur = code.Length

        For x = 0 To longueur - 1
            car = code.Substring(x, 1)
            charValue = tableau.IndexOf(tableau, car)
            sum = sum + ((Math.Pow(2, longueur - x) * charValue))
        Next

        mod1 = ((38 - (sum Mod 37)) Mod 37)
        checkcar = tableau.GetValue(mod1)
        TextBox2.Text = checkcar
        TextBox3.Text = code + checkcar

   
0
Comment
Question by:l-ipse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 13

Expert Comment

by:numberkruncher
ID: 22831327
Where are you using VBA? Excel, Word, Access, Visual Basic IDE 1-6, or something else?
0
 

Author Comment

by:l-ipse
ID: 22831419
I'm using the visual basic editor in access 2003

in the ? menu it's written Microsoft Visual basic 6.3 VBA retail 6.5.1024
0
 
LVL 13

Expert Comment

by:numberkruncher
ID: 22831657
I believe that the following might be what your looking for:
Public Function Array_IndexOf(ByRef arr As Variant, ByRef value As String)
    Dim i As Integer
    For i = LBound(arr) To UBound(arr)
        If arr(u) = value Then
            Array_IndexOf = i
            Return
        End If
    Next i
End Function
 
Private Sub Command6_Click()
    Dim tableau As Variant
    Dim sum As Integer
    Dim charValue As Integer
    Dim longueur As Integer
    Dim x As Integer
    Dim code As String
    Dim car As String
    Dim mod1 As Integer
    Dim checkcar As String
 
    tableau = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "*")
    code = TextBox1.value
    longueur = Len(code)
 
    For x = 0 To longueur - 1
        car = Mid(code, x + 1, 1)
        charValue = Array_IndexOf(tableau, car)
        sum = sum + ((2 ^ (longueur - x) * charValue))
    Next
 
    mod1 = ((38 - (sum Mod 37)) Mod 37)
    checkcar = tableau(mod1)
    TextBox2.value = checkcar
    TextBox3.value = code + checkcar
End Sub

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:l-ipse
ID: 22832130
Thank you very much for your help.

Your Array_IndexOf function always return 0, and I dont understand whre the u from arr(u) come from.

Just to give you a liitle mor info

Is we give G123489654321 as input we should have
G123489654321Y as output


0
 
LVL 13

Expert Comment

by:numberkruncher
ID: 22832163
Ah, sorry about that the arr(u) is a typo! It should be arr(i)
0
 

Author Comment

by:l-ipse
ID: 22832372
When I try with arr(i) i got this error message

Execution error 3

Return without GoSub

0
 
LVL 13

Expert Comment

by:numberkruncher
ID: 22832453
Sorry about that!

Exit Function is what you need, I am so used to using "return" in other languages that it slipped my mind.

I have double checked the code in MsAccess and it appears to work. If no text is entered into TextBox1 I have set it to "Default". Obviously you can change this to whatever you want, but if there is no text within the textbox control, the macro will crash because Null isn't a string.

Public Function Array_IndexOf(ByRef arr As Variant, ByRef value As String)
    Dim i As Integer
    For i = LBound(arr) To UBound(arr)
        If arr(i) = value Then
            Array_IndexOf = i
            Exit Function
        End If
    Next i
End Function
 
Private Sub Command6_Click()
    Dim tableau As Variant
    Dim sum As Integer
    Dim charValue As Integer
    Dim longueur As Integer
    Dim x As Integer
    Dim code As String
    Dim car As String
    Dim mod1 As Integer
    Dim checkcar As String
 
    tableau = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "*")
    If TextBox1.value <> Null Then
        code = TextBox1.value
    Else
        code = "default"
    End If
    longueur = Len(code)
 
    For x = 0 To longueur - 1
        car = Mid(code, x + 1, 1)
        charValue = Array_IndexOf(tableau, car)
        sum = sum + ((2 ^ (longueur - x) * charValue))
    Next
 
    mod1 = ((38 - (sum Mod 37)) Mod 37)
    checkcar = tableau(mod1)
    TextBox2.value = checkcar
    TextBox3.value = code + checkcar
End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:numberkruncher
ID: 22832506
If you find you keep getting "default8" or the like then you will have to revert to

code = TextBox1.value

without the surrounding if statement. I just tried the above on my laptop and it always comes out as "default8".
0
 
LVL 13

Accepted Solution

by:
numberkruncher earned 125 total points
ID: 22832532
Ahh... this is better:
    If Not TextBox1.value = "" Then
        code = TextBox1.value
    Else
        code = "default"
    End If

Open in new window

0
 

Author Closing Comment

by:l-ipse
ID: 31511162
Great it work, thank you very much for your help.

keep on rockin
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question