Solved

Transferring code from .NET to VBA

Posted on 2008-10-29
10
183 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
  • 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

820 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