I Need a Macro to Execute Faster

[ryanJsully]

Hi all, first time poster. I've been learning VBA for a couple of weeks now and I've generated a code with macro record that uses a button on my spreadsheet to send clipboard data to another application(Attachmate Extra! X-treme). I generated the code with Attachmate's Macro Recorder and then copied this code in Excel VBA, the code is executed by a button which then sends the F2 key to activate the text field and then pastes the clipboard data to the active Host Screen.

The code is very functional, but the macro runs slowly, I timed it at 6+ seconds to execute. I would be grateful if anyone could look at my code below and tell if there is a way to modify it so that the macro executes more quickly. Any help is greatly appreciated

Sub Button14_Click()
'--------------------------------------------------------------------------------
' Get the main system object
    Dim Sessions As Object
    Dim System As Object
    Set System = CreateObject("EXTRA.System")   ' Gets the system object
    If (System Is Nothing) Then
        MsgBox "Could not create the EXTRA System object.  Stopping macro playback."
        Stop
    End If
    Set Sessions = System.Sessions

    If (Sessions Is Nothing) Then
        MsgBox "Could not create the Sessions collection object.  Stopping macro playback."
        Stop
    End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
    g_HostSettleTime = 3000     ' milliseconds

    OldSystemTimeout& = System.TimeoutValue
    If (g_HostSettleTime > OldSystemTimeout) Then
        System.TimeoutValue = g_HostSettleTime
    End If

' Get the necessary Session Object
    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
    If (Sess0 Is Nothing) Then
        MsgBox "Could not create the Session object.  Stopping macro playback."
        Stop
    End If
    If Not Sess0.Visible Then Sess0.Visible = True
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    
' This section of code contains the recorded events
    
    Sess0.Screen.SendKeys ("
")
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    Sess0.Screen.Paste

    System.TimeoutValue = OldSystemTimeout
End Sub

Thanks,
Sully</pf2>

 

[xenou]

Hi,

The code is so simple already that I don't think there is any problem in the VBA as such. It's just that *what it does* is slow. Could be networking issues, calculation in Excel, creating the Extras object, time required for authentication or a response from the other system ... any number of things.

ξ

 

[ryanJsully]

Thank you for your response, I found out how to do it. I changed the value of <code>g_HostSettleTime</code> to 50 milliseconds to make it .5 seconds as opposed to the default Attachmate Macro Time of 3000(3 seconds). This helped tremendously

<code>
g_HostSettleTime = 3000     ' milliseconds
</code>

Thanks,
Sully