Solved

Transferring code from .NET to VBA

Posted on 2008-10-29
10
184 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

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!

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

734 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