Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

Transferring code from .NET to VBA

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
l-ipse
Asked:
l-ipse
  • 6
  • 4
1 Solution
 
numberkruncherCommented:
Where are you using VBA? Excel, Word, Access, Visual Basic IDE 1-6, or something else?
0
 
l-ipseAuthor Commented:
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
 
numberkruncherCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
l-ipseAuthor Commented:
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
 
numberkruncherCommented:
Ah, sorry about that the arr(u) is a typo! It should be arr(i)
0
 
l-ipseAuthor Commented:
When I try with arr(i) i got this error message

Execution error 3

Return without GoSub

0
 
numberkruncherCommented:
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
 
numberkruncherCommented:
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
 
numberkruncherCommented:
Ahh... this is better:
    If Not TextBox1.value = "" Then
        code = TextBox1.value
    Else
        code = "default"
    End If

Open in new window

0
 
l-ipseAuthor Commented:
Great it work, thank you very much for your help.

keep on rockin
0

Featured Post

Technology Partners: 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!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now