Solved

Transferring code from .NET to VBA

Posted on 2008-10-29
10
179 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now