VBA code only runs fast in a newly opened workbook

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
156
Office Version
  1. 2016
Platform
  1. Windows
I have this extremely strange issue with one of my VBA script JSON codes where it only runs fast (about 1 min and 20 seconds) when I open up a new workbook and insert my code and add Microsoft script reference right there. However, besides this it takes forever to run. Also if I save this file and reopen it exactly how it was it no longer runs fast. This code always worked fine on Excel 2016 but now I have 2019 and this is the issue. I have been troubleshooting this for 2 weeks now with no luck. Any assistance would be appreciated. Thank you!

VBA Code:
Private Sub Pull_Shipments()

   Call LudicrousMode(True)

    Dim obj_http As Object, Json As Object, JSON1 As Object, x64 As Object, s As Object, key, keyring As Object, arr(),
 JSON2, SCC_Response As Object,
    inner_key, inner_keyring

    Set obj_http =

  CreateObject("WinHTTP.WinHTTPRequest.5.1")

    Set s =
    CreateObject("ScriptControl")
    s.Language = "JScript"
    s.AddCode "function keys(O) {
    var k = new Array(); for (var
    x in O) { k.push(x); } return k; } "


  Vrid_col = 3

  site = UCase(Sheets("Main").Range("B30"))

  'Reset info
   With Sheets("Shipments")
Row = 2
.Range("A:H").ClearContents

'set headers
.Cells(1, 1) = "Truck"
.Cells(1, 2) = "Tracking ID"
.Cells(1, 3) = "State"
.Cells(1, 4) = "Size"
.Cells(1, 5) = "Area"
.Cells(1, 6) = "Section"
.Cells(1, 7) = "Date"
.Cells(1, 8) = "Cycle"

'Set URLs
SC_URL = "work website "
API_URL = "work website"

'request SCC Get
obj_http.Open "GET", SC_URL
obj_http.SetAutoLogonPolicy 0
obj_http.setRequestHeader "Food", FoodJar
obj_http.send
obj_http.WaitForResponse

For line_haul = 2 To Application.CountA(Sheets("Site Trucks").Columns(2 + Vrid_col))
    haul_id = Sheets("Site Trucks").Cells(line_haul, 1 + Vrid_col)
    package_body = "{""resourcePath"":""/ivs/getPackageList"",""httpMethod"":""post"",""processName"":""induct"",""requestBody"":{""nodeId"":""" & site & """,""Truck"":""" & haul_id & """,""status"":""ALL"",""filters"":{""Cycle"":[],""Date"":[],""OtherAttributes"":[],""Section"":[],""Size"":[]}}}"
 
    'Post APU request
    obj_http.Open "POST", API_URL
    obj_http.setRequestHeader "Cookie", CookieJar
    obj_http.SetClientCertificate "CURRENT_USER\MY\" & Environ("USERNAME")
    obj_http.SetAutoLogonPolicy 0
    obj_http.setRequestHeader "Accept", "*/*"
    obj_http.setRequestHeader "Content-Type", "application/json"
    obj_http.send package_body
 
    Set SCC_Response = s.Eval("(" & obj_http.responseText & ")")
    Set Json = CallByName(SCC_Response, "packageList", VbGet)
    Set keyring = s.Run("keys", Json)

        'parse vehicle info
        For Each key In keyring
            .Cells(Row, 1) = haul_id
            .Cells(Row, 2) = CallByName(CallByName(Json, key, VbGet), "ID", VbGet)
            .Cells(Row, 3) = CallByName(CallByName(Json, key, VbGet), "state", VbGet)
            .Cells(Row, 4) = CallByName(CallByName(Json, key, VbGet), "size", VbGet)
            .Cells(Row, 6) = CallByName(CallByName(Json, key, VbGet), "section", VbGet)
            .Cells(Row, 7) = CallByName(CallByName(Json, key, VbGet), "date", VbGet)
            .Cells(Row, 8) = CallByName(CallByName(Json, key, VbGet), "cycle", VbGet)
     
            If CallByName(CallByName(Json, key, VbGet), "Area", VbGet) <> "" Then
                .Cells(Row, 5) = Split(CallByName(CallByName(Json, key, VbGet), "Area", VbGet), ".")(0)
            End If
            Row = Row + 1
        Next key
 
Next line_haul
End With

 Call LudicrousMode(False)

End Sub
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

=ODIN=

Active Member
Joined
Dec 3, 2009
Messages
295
What do you mean by this: "and add Microsoft script reference right there." Are you manually selecting a reference? If so, what is the exact name of the reference you are selecting.
 

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
156
Office Version
  1. 2016
Platform
  1. Windows
What do you mean by this: "and add Microsoft script reference right there." Are you manually selecting a reference? If so, what is the exact name of the reference you are selecting.
Yes, manually adding Microsoft Script runtime reference
 

Forum statistics

Threads
1,176,201
Messages
5,901,890
Members
434,928
Latest member
mvbuccheri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top