Excel freezes while running macro

[ak5]

Hello all

I have a module which gets data from a web API and then copies that data to the excel sheet, while this macro is running, all the other excel sheets freezes for a couple of seconds. Any help to optimize this code would be greatly appreciated. I just want this macro to run in the background without any interference with other excel instances.

Below is the code

Sub GetDataFromWebApi()

 Application.ScreenUpdating = False
 Application.EnableEvents = False
 Application.Cursor = xlDefault

Set httpRequest = CreateObject("MSXML2.XMLHTTP")

 webApiUrl = "web api url goes here"
 blnAsync = True

With httpRequest
 .Open "GET", webApiUrl, blnAsync
 .SetRequestHeader "Content-Type", "application/json"
 .Send
While httpRequest.readyState <> 4
  DoEvents
Wend
  jsonResponse = .ResponseText
End With

Dim parsedJson As Object
Set parsedJson = JsonConverter.ParseJson(jsonResponse)

arrayIndex = 0
ReDim arrFreq(200), arrAhead(200), arrLTP(200)

For Each pass1 In parsedJson.Keys
 If pass1 = "data" Then
 For Each jsonData In parsedJson(pass1)
  If IsNull(jsonData("1")) Then
     jsonData("1") = ""
  End If

   arrFreq(arrayIndex) = jsonData("1")
   arrAhead(arrayIndex) = jsonData("2")
   arrLTP(arrayIndex) = jsonData("3")

  Else
   arrLTP(arrayIndex) = jsonData("3")
  End If

  arrayIndex = arrayIndex + 1

  Next jsonData
  End If

Next pass1

Application.EnableEvents = True

If marketName = "1" Then
ThisWorkbook.Sheets("Sheet1").Range("B3:B120") = Application.WorksheetFunction.Transpose(arrFreq)
ThisWorkbook.Sheets("Sheet1").Range("C3:C120") = Application.WorksheetFunction.Transpose(arrAhead)
ThisWorkbook.Sheets("Sheet1").Range("D3:D120") = Application.WorksheetFunction.Transpose(arrLTP)

ElseIf marketName = "3" Then
ThisWorkbook.Sheets("Sheet2").Range("B3:B120") = Application.WorksheetFunction.Transpose(arrFreq)
ThisWorkbook.Sheets("Sheet2").Range("C3:C120") = Application.WorksheetFunction.Transpose(arrAhead)
ThisWorkbook.Sheets("Sheet2").Range("D3:D120") = Application.WorksheetFunction.Transpose(arrLTP)

 End If

Next cell

    runtime = Now + TimeValue("00:00:15")
        Application.OnTime runtime, "GetDataFromWebApi"

Application.ScreenUpdating = True


End Sub

 

[rlv01]

Sometimes adding a DoEvents statement can help with that. I.e.

  arrayIndex = arrayIndex + 1
  DoEvents
  Next jsonData