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

ArisaAnsarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
PutString puts a value in the specified cell. In this example the value from ActiveCell.Value is stored in the variable called account. The contents of account is put in 14, 35.

You need to start reading up on the Excel Object Model here: http://msdn.microsoft.com/en-us/library/wss56bz7.aspx. This is specifically Excel 2007 but a similar page exists for 2003 here http://msdn.microsoft.com/en-us/library/aa176808%28office.11%29.aspx

Lee

0
aikimarkCommented:
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.
0
ArisaAnsarAuthor Commented:
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
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

aikimarkCommented:
Note to all:
Extrasessions and Extrascreen are Attachment references.

Found it here:
http://www.tek-tips.com/faqs.cfm?fid=4619
0
ArisaAnsarAuthor Commented:
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.    
0
aikimarkCommented:
@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)
0
aikimarkCommented:
@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)
0
aikimarkCommented:
Notes to all:
* the timeGetTime API is never referenced.
* the Attachmate Sendkeys strings differ from the VB SendKeys strings
* there are several places where the END command is used.  This is generally a bad idea and might cause processing problems.

* I've formatted the code in Module1 and Module2 to help make the code a bit more understandable.
'Module1
Option Explicit
'These are Attachmate referenced objects
Public System As Object  'ExtraSystem
Public Sessions As Object  'ExtraSessions
Public Sess0 As Object  'ExtraSession
Public MyScn As Object  'ExtraScreen

Declare Function timeGetTime Lib "winmm.dll" () As Long   'not used

Public account As Variant   'not declared in originally posted code
Public branch As Variant
Public target As Variant

Public Function ConnectToSession(sessionName)
    Dim x As Long
    
    'Extra Objects
    Set System = CreateObject("EXTRA.System")                   ' Gets the EXTRA system object
    If (System Is Nothing) Then
        MsgBox "Session not found. Make sure the session screen is open."
        End   '*****
    End If
    Set Sessions = System.Sessions
    If (Sessions Is Nothing) Then
        MsgBox "Session not found. Make sure the session screen is open."
        End   '*****
    End If
    Set Sess0 = Sessions.Item(0)

    x = 0
    Do Until Sess0.Name = sessionName
        Sessions.JumpNext
        Set Sess0 = System.ActiveSession
        x = x + 1
        If x > 10 Then Exit Do
    Loop
    
    If (Sess0 Is Nothing) Then
        MsgBox "Session not found. Make sure the session screen is open."
        End  '****
    End If
    
    Set ConnectToSession = Sess0.Screen
    
End Function
Public Function DropConnection()   'Gets rid of the Extra system object

    Set Sessions = Nothing
    Set System = Nothing
    Set Sess0 = Nothing
    Set DropConnection = Nothing

End Function
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

Sub typeAndCheck()
    'target = "fci1"
    'Set MyScn = ConnectToSession(target)                    'connect to the fci1 session
    Range("a3").Activate
    While Not IsEmpty(ActiveCell)              'get the type and check digit for each line
        branch = ActiveCell.Offset(0, 3)
        account = ActiveCell.Offset(0, 4)
        GetTypeCheck branch, account
        ActiveCell.Offset(1, 0).Activate
    Wend
End Sub

Public Sub GetTypeCheck(branch, account)

    If MyScn.GetString(2, 5, 14) <> "WELCOME TO FCI" Then   'if this isn't the fci screen
        MyScn.SendKeys "<Clear>"                            'hit clear twice
        MyScn.SendKeys "<Clear>"
        MyScn.SendKeys "fci<ENTER>"                        'type fci
        Do While MyScn.OIA.XStatus <> 0                     'wait until the screen comes up
            DoEvents
        Loop
    
    End If
    
    MyScn.PutString "a", 23, 2                              'type a
    MyScn.PutString account, 23, 7                          'type account number
    MyScn.PutString branch, 23, 32                          'type a
    MyScn.SendKeys "<ENTER>"
    
    Do While MyScn.OIA.XStatus <> 0                     'wait until the screen comes up
      DoEvents
    Loop
    
    If IsNumeric(MyScn.GetString(2, 16, 1)) Then
        ActiveCell.Offset(0, 5) = MyScn.GetString(2, 16, 1)                    'get type
    End If
    
    If IsNumeric(MyScn.GetString(2, 18, 1)) Then
        ActiveCell.Offset(0, 6) = MyScn.GetString(2, 18, 1)                     'get check
    End If
    
    Do While MyScn.OIA.XStatus <> 0                         'wait until the screen comes up
        DoEvents
    Loop

End Sub

Public Sub ProcessStuff()

    target = "fci1"
    Set MyScn = ConnectToSession(target)                    'connect to the fci1 session
    
    'mydata = GetAccessData("S:\Smith Barney\DAILY GL\access gl.mdb")
    'typeAndCheck
    JournalIt                                                'enter in SJEN
    
    'ActiveWorkbook.SaveCopyAs "G:\Mutual Funds Ops\Dividends\Cleanup Journals\" & Format(Now, "mm-dd-yyyy_hhmmss") & ActiveWorkbook.Name

    Set MyScn = DropConnection()                        'disconnect from the session

End Sub

'Module2
Option Explicit

Public Sub killit()
    Dim user As String
    Dim teststring As String
    
    target = "fci1"
    Set MyScn = ConnectToSession(target)                'connect to the fci1 session
    MyScn.SendKeys "<Clear>"                            'hit clear twice
    MyScn.SendKeys "<Clear>"
    MyScn.SendKeys "sjob<ENTER>"                        'type sjen

    Do While MyScn.OIA.XStatus <> 0                     'wait until the screen comes up
        DoEvents
    Loop
    
    user = "sh21975"
    MyScn.PutString user, 4, 35
    MyScn.SendKeys "<ENTER>"                            'type sjen

    Do While MyScn.OIA.XStatus <> 0                     'wait until the screen comes up
        DoEvents
    Loop
    
    teststring = MyScn.GetString(10, 4, 1)
    Do While teststring <> " "
        MyScn.PutString "s", 10, 2
        MyScn.SendKeys "<ENTER>"                        'type sjen
        
        Do While MyScn.OIA.XStatus <> 0                 'wait until the screen comes up
            DoEvents
        Loop
        
        MyScn.PutString "d", 22, 23
        MyScn.SendKeys "<ENTER>"                        'type sjen
        
        Do While MyScn.OIA.XStatus <> 0                 'wait until the screen comes up
            DoEvents
        Loop
        MyScn.SendKeys "<ENTER>"                        'type sjen
        
        Do While MyScn.OIA.XStatus <> 0                 'wait until the screen comes up
            DoEvents
        Loop
        MyScn.SendKeys "<ENTER>"                        'type sjen

        Do While MyScn.OIA.XStatus <> 0                 'wait until the screen comes up
            DoEvents
        Loop
        teststring = MyScn.GetString(10, 4, 1)
    Loop
    
    Set MyScn = DropConnection()                        'disconnect from the session
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ArisaAnsarAuthor Commented:
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.
0
aikimarkCommented:
these are probably rows and columns on the mainframe screen
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.