asked on

Understanding Excel Visual Basic Codes Within A Macro

Below are codes for a macro written in VBA. I have to modify the Macro to perform a few additional steps but don't know what the codes mean to make the changes. I am very new to programming and is trying to learn as much as possible instead of asking co workers to make the changes. My formal training in a classroom session will start in another week.
I included a copy of the code below.
what does "MyScn.PutString account, 14, 35" mean?
I don't understand how the numbers are generated in programming.

Public Sub JournalIt()
Range("a2").Activate

        MyScn.SendKeys "<Clear>"                            'hit clear twice
        MyScn.SendKeys "<Clear>"
        MyScn.SendKeys "scma<ENTER>"                        'type sjen
    
        Do While MyScn.OIA.XStatus <> 0                     'wait until the screen comes up
            DoEvents
        Loop
        account = ActiveCell.Value
        MyScn.PutString account, 14, 35
        
        MyScn.SendKeys "<ENTER>"
        Do While MyScn.OIA.XStatus <> 0                     'wait until the screen comes up
            DoEvents
        Loop
        
Do While Not IsEmpty(ActiveCell)

        MyScn.PutString "3", 21, 9
        
        MyScn.SendKeys "<ENTER>"
        Do While MyScn.OIA.XStatus <> 0                     'wait until the screen comes up
            DoEvents
        Loop
        
        MyScn.PutString "Y", 7, 23
        MyScn.PutString "Y", 10, 39
        
        MyScn.SendKeys "<ENTER>"
        Do While MyScn.OIA.XStatus <> 0                     'wait until the screen comes up
            DoEvents
        Loop
        
        If MyScn.GetString(24, 17, 7) = "SUCCESS" Then
            ActiveCell.Offset(0, 1) = "OK"
        Else: ActiveCell.Offset(0, 1) = "NOT PROCESSED"
        End If
        
        If IsEmpty(ActiveCell.Offset(1, 0)) Then Exit Do
        
        ActiveCell.Offset(1, 0).Activate
        account = ActiveCell.Value
        MyScn.PutString account, 23, 17
        
        MyScn.SendKeys "<ENTER>"
        Do While MyScn.OIA.XStatus <> 0                     'wait until the screen comes up
            DoEvents
        Loop
Loop
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    

End Sub

Open in new window

SOLUTION

Lee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

It looks like this code is updating a worksheet that has cellupdate events.

One normally doesn't code like this if they can help it.

please post the workbook.

ArisaAnsar

ASKER

Workbook attached. Yes, the worksheet is being updated if the account was process correctly.
The Macro actually logs on to another application (a mainframe application) and updates a few fields by putting "Y" and "Y" in those field. I am trying to determine how to update additional fields on the same screen? I don't understand how to tell the Macro to add the fields that have to be modified.

In addition, I also have to access a completely different screen on the application and modify a field on that screen as well. If I am not able to modify this screen, then the account should not get coded and give me a "Not Process" message.

Really appreciates everyone's help. I'm tryin to learn but its so confusing!!!!
Account-adjustment-tool-0-2--2--.xls

aikimark

Note to all:
Extrasessions and Extrascreen are Attachment references.

Found it here:
http://www.tek-tips.com/faqs.cfm?fid=4619

ArisaAnsar

ASKER

You guys are amazing!!! How do you find things so quickly??? I looked on the internet for days but could not find it. In either case, while I understand some of the coding, the others don't make sense and understanding the entire codes is like trying to understand a foreign language. I'll get there one day hopefully!!!

If you can explain how the macro is identifing the field to update, that would make it a lot easier for me.

aikimark

@ArisaAnsar

A couple of early recommendations.
1. Add Option Explicit to the general declarations sections of all areas with code, such as Module1, Module2, Sheet1

2. Remove Class1. It isn't being used.

3. Compile the project (under the Debug menu). You will find one or more variables that need defining, such as Account, Branch, Target.
(note: there are some local variables that need defining as well)

aikimark

@ArisaAnsar

I'm still looking at the code. I got a clean compile, after making the Attachmate referenced objects into the more general Object data type. (and the aforementioned variable declarations)

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ArisaAnsar

ASKER

I am trying to understand primarily how the author of the macro came up with 14, 35 in the main frame application, Attachmate? I still don't understand how he did that.

aikimark

these are probably rows and columns on the mainframe screen