Excel freezes while running macro

ak5

New Member
Joined
May 21, 2020
Messages
1
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

VBA 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
 

Some videos you may like

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.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
Sometimes adding a DoEvents statement can help with that. I.e.

VBA Code:
  arrayIndex = arrayIndex + 1
  DoEvents
  Next jsonData
 

Watch MrExcel Video

Forum statistics

Threads
1,114,240
Messages
5,546,683
Members
410,754
Latest member
IainClover
Top