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