anbhavane
New Member
- Joined
- Jul 12, 2021
- Messages
- 20
- Office Version
- 365
- Platform
- Windows
- MacOS
- Mobile
- Web
Hello I have been trying to retrieve data from a data feed server but code is taking almost 10 minutes to get the data. When macro is executed through a button, excel freezes for 10 mins. I want to speed up the process within 2 minutes. It generates almost 8000 records but takes 10 mins approx. Following is the code.
Sub OptionsDataHistory()
Application.Calculation = xlManual
Application.ScreenUpdating = False
Range("D33").Formula = "=Nimble_GetHistory(""NFO"",F8,G8,H8,I8,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D182").Formula = "=Nimble_GetHistory(""NFO"",F9,G9,H9,I9,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D331").Formula = "=Nimble_GetHistory(""NFO"",F10,G10,H10,I10,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D480").Formula = "=Nimble_GetHistory(""NFO"",F11,G11,H11,I11,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D629").Formula = "=Nimble_GetHistory(""NFO"",F12,G12,H12,I12,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D778").Formula = "=Nimble_GetHistory(""NFO"",F13,G13,H13,I13,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D927").Formula = "=Nimble_GetHistory(""NFO"",F14,G14,H14,I14,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D1076").Formula = "=Nimble_GetHistory(""NFO"",F15,G15,H15,I15,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D1225").Formula = "=Nimble_GetHistory(""NFO"",F16,G16,H16,I16,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D1374").Formula = "=Nimble_GetHistory(""NFO"",F17,G17,H17,I17,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D1523").Formula = "=Nimble_GetHistory(""NFO"",F18,G18,H18,I18,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D1672").Formula = "=Nimble_GetHistory(""NFO"",F19,G19,H19,I19,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D1821").Formula = "=Nimble_GetHistory(""NFO"",F20,G20,H20,I20,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D1970").Formula = "=Nimble_GetHistory(""NFO"",F21,G21,H21,I21,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D2119").Formula = "=Nimble_GetHistory(""NFO"",F22,G22,H22,I22,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D2268").Formula = "=Nimble_GetHistory(""NFO"",F23,G23,H23,I23,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D2417").Formula = "=Nimble_GetHistory(""NFO"",F24,G24,H24,I24,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D2566").Formula = "=Nimble_GetHistory(""NFO"",F25,G25,H25,I25,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D2715").Formula = "=Nimble_GetHistory(""NFO"",F26,G26,H26,I26,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D2864").Formula = "=Nimble_GetHistory(""NFO"",F27,G27,H27,I27,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D3013").Formula = "=Nimble_GetHistory(""NFO"",F28,G28,H28,I28,""False"")"
Application.Wait Now + TimeValue("00:00:01")
'Range("D3264").Formula = "=Nimble_GetHistory(""NFO"",F29,G29,H29,I29,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D3413").Formula = "=Nimble_GetHistory(""NFO"",F30,G30,H30,I30,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D3562").Formula = "=Nimble_GetHistory(""NFO"",F31,G31,H31,I31,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D3711").Formula = "=Nimble_GetHistory(""NFO"",F32,G32,H32,I32,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D3860").Formula = "=Nimble_GetHistory(""NFO"",F33,G33,H33,I33,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D4009").Formula = "=Nimble_GetHistory(""NFO"",F34,G34,H34,I34,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D4158").Formula = "=Nimble_GetHistory(""NFO"",F35,G35,H35,I35,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D4307").Formula = "=Nimble_GetHistory(""NFO"",F36,G36,H36,I36,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D4456").Formula = "=Nimble_GetHistory(""NFO"",F37,G37,H37,I37,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D4605").Formula = "=Nimble_GetHistory(""NFO"",F38,G38,H38,I38,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
Range("P33").Formula = "=Nimble_GetHistory(""NFO"",R8,S8,T8,U8,V8)"
Application.Wait Now + TimeValue("00:00:01")
Range("P182").Formula = "=Nimble_GetHistory(""NFO"",R9,S9,T9,U9,V9)"
Application.Wait Now + TimeValue("00:00:01")
Range("P331").Formula = "=Nimble_GetHistory(""NFO"",R10,S10,T10,U10,V10)"
Application.Wait Now + TimeValue("00:00:01")
Range("P480").Formula = "=Nimble_GetHistory(""NFO"",R11,S11,T11,U11,V11)"
Application.Wait Now + TimeValue("00:00:01")
Range("P629").Formula = "=Nimble_GetHistory(""NFO"",R12,S12,T12,U12,V12)"
Application.Wait Now + TimeValue("00:00:01")
Range("P778").Formula = "=Nimble_GetHistory(""NFO"",R13,S13,T13,U13,V13)"
Application.Wait Now + TimeValue("00:00:01")
Range("P927").Formula = "=Nimble_GetHistory(""NFO"",R14,S14,T14,U14,V14)"
Application.Wait Now + TimeValue("00:00:01")
Range("P1076").Formula = "=Nimble_GetHistory(""NFO"",R15,S15,T15,U15,V15)"
Application.Wait Now + TimeValue("00:00:01")
Range("P1225").Formula = "=Nimble_GetHistory(""NFO"",R16,S16,T16,U16,V16)"
Application.Wait Now + TimeValue("00:00:01")
Range("P1374").Formula = "=Nimble_GetHistory(""NFO"",R17,S17,T17,U17,V17)"
Application.Wait Now + TimeValue("00:00:01")
Range("P1523").Formula = "=Nimble_GetHistory(""NFO"",R18,S18,T18,U18,V18)"
Application.Wait Now + TimeValue("00:00:01")
Range("P1672").Formula = "=Nimble_GetHistory(""NFO"",R19,S19,T19,U19,V19)"
Application.Wait Now + TimeValue("00:00:01")
Range("P1821").Formula = "=Nimble_GetHistory(""NFO"",R20,S20,T20,U20,V20)"
Application.Wait Now + TimeValue("00:00:01")
Range("P1970").Formula = "=Nimble_GetHistory(""NFO"",R21,S21,T21,U21,V21)"
Application.Wait Now + TimeValue("00:00:01")
Range("P2119").Formula = "=Nimble_GetHistory(""NFO"",R22,S22,T22,U22,V22)"
Application.Wait Now + TimeValue("00:00:01")
Range("P2268").Formula = "=Nimble_GetHistory(""NFO"",R23,S23,T23,U23,V23)"
Application.Wait Now + TimeValue("00:00:01")
Range("P2417").Formula = "=Nimble_GetHistory(""NFO"",R24,S24,T24,U24,V24)"
Application.Wait Now + TimeValue("00:00:01")
Range("P2566").Formula = "=Nimble_GetHistory(""NFO"",R25,S25,T25,U25,V25)"
Application.Wait Now + TimeValue("00:00:01")
Range("P2715").Formula = "=Nimble_GetHistory(""NFO"",R26,S26,T26,U26,V26)"
Application.Wait Now + TimeValue("00:00:01")
Range("P2864").Formula = "=Nimble_GetHistory(""NFO"",R27,S27,T27,U27,V27)"
Application.Wait Now + TimeValue("00:00:01")
Range("P3013").Formula = "=Nimble_GetHistory(""NFO"",R28,S28,T28,U28,V28)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P3264").Formula = "=Nimble_GetHistory(""NFO"",R29,S29,T29,U29,V29)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P3413").Formula = "=Nimble_GetHistory(""NFO"",R30,S30,T30,U30,V30)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P3562").Formula = "=Nimble_GetHistory(""NFO"",R31,S31,T31,U31,V31)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P3711").Formula = "=Nimble_GetHistory(""NFO"",R32,S32,T32,U32,V32)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P3860").Formula = "=Nimble_GetHistory(""NFO"",R33,S33,T33,U33,V33)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P4009").Formula = "=Nimble_GetHistory(""NFO"",R34,S34,T34,U34,V34)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P4158").Formula = "=Nimble_GetHistory(""NFO"",R35,S35,T35,U35,V35)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P4307").Formula = "=Nimble_GetHistory(""NFO"",R36,S36,T36,U36,V36)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P4456").Formula = "=Nimble_GetHistory(""NFO"",R37,S37,T37,U37,V37)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P4605").Formula = "=Nimble_GetHistory(""NFO"",R38,S38,T38,U38,V38)"
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
Sub OptionsDataHistory()
Application.Calculation = xlManual
Application.ScreenUpdating = False
Range("D33").Formula = "=Nimble_GetHistory(""NFO"",F8,G8,H8,I8,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D182").Formula = "=Nimble_GetHistory(""NFO"",F9,G9,H9,I9,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D331").Formula = "=Nimble_GetHistory(""NFO"",F10,G10,H10,I10,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D480").Formula = "=Nimble_GetHistory(""NFO"",F11,G11,H11,I11,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D629").Formula = "=Nimble_GetHistory(""NFO"",F12,G12,H12,I12,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D778").Formula = "=Nimble_GetHistory(""NFO"",F13,G13,H13,I13,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D927").Formula = "=Nimble_GetHistory(""NFO"",F14,G14,H14,I14,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D1076").Formula = "=Nimble_GetHistory(""NFO"",F15,G15,H15,I15,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D1225").Formula = "=Nimble_GetHistory(""NFO"",F16,G16,H16,I16,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D1374").Formula = "=Nimble_GetHistory(""NFO"",F17,G17,H17,I17,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D1523").Formula = "=Nimble_GetHistory(""NFO"",F18,G18,H18,I18,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D1672").Formula = "=Nimble_GetHistory(""NFO"",F19,G19,H19,I19,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D1821").Formula = "=Nimble_GetHistory(""NFO"",F20,G20,H20,I20,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D1970").Formula = "=Nimble_GetHistory(""NFO"",F21,G21,H21,I21,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D2119").Formula = "=Nimble_GetHistory(""NFO"",F22,G22,H22,I22,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D2268").Formula = "=Nimble_GetHistory(""NFO"",F23,G23,H23,I23,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D2417").Formula = "=Nimble_GetHistory(""NFO"",F24,G24,H24,I24,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D2566").Formula = "=Nimble_GetHistory(""NFO"",F25,G25,H25,I25,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D2715").Formula = "=Nimble_GetHistory(""NFO"",F26,G26,H26,I26,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D2864").Formula = "=Nimble_GetHistory(""NFO"",F27,G27,H27,I27,""False"")"
Application.Wait Now + TimeValue("00:00:01")
Range("D3013").Formula = "=Nimble_GetHistory(""NFO"",F28,G28,H28,I28,""False"")"
Application.Wait Now + TimeValue("00:00:01")
'Range("D3264").Formula = "=Nimble_GetHistory(""NFO"",F29,G29,H29,I29,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D3413").Formula = "=Nimble_GetHistory(""NFO"",F30,G30,H30,I30,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D3562").Formula = "=Nimble_GetHistory(""NFO"",F31,G31,H31,I31,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D3711").Formula = "=Nimble_GetHistory(""NFO"",F32,G32,H32,I32,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D3860").Formula = "=Nimble_GetHistory(""NFO"",F33,G33,H33,I33,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D4009").Formula = "=Nimble_GetHistory(""NFO"",F34,G34,H34,I34,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D4158").Formula = "=Nimble_GetHistory(""NFO"",F35,G35,H35,I35,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D4307").Formula = "=Nimble_GetHistory(""NFO"",F36,G36,H36,I36,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D4456").Formula = "=Nimble_GetHistory(""NFO"",F37,G37,H37,I37,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
'Range("D4605").Formula = "=Nimble_GetHistory(""NFO"",F38,G38,H38,I38,""False"")"
'Application.Wait Now + TimeValue("00:00:01")
Range("P33").Formula = "=Nimble_GetHistory(""NFO"",R8,S8,T8,U8,V8)"
Application.Wait Now + TimeValue("00:00:01")
Range("P182").Formula = "=Nimble_GetHistory(""NFO"",R9,S9,T9,U9,V9)"
Application.Wait Now + TimeValue("00:00:01")
Range("P331").Formula = "=Nimble_GetHistory(""NFO"",R10,S10,T10,U10,V10)"
Application.Wait Now + TimeValue("00:00:01")
Range("P480").Formula = "=Nimble_GetHistory(""NFO"",R11,S11,T11,U11,V11)"
Application.Wait Now + TimeValue("00:00:01")
Range("P629").Formula = "=Nimble_GetHistory(""NFO"",R12,S12,T12,U12,V12)"
Application.Wait Now + TimeValue("00:00:01")
Range("P778").Formula = "=Nimble_GetHistory(""NFO"",R13,S13,T13,U13,V13)"
Application.Wait Now + TimeValue("00:00:01")
Range("P927").Formula = "=Nimble_GetHistory(""NFO"",R14,S14,T14,U14,V14)"
Application.Wait Now + TimeValue("00:00:01")
Range("P1076").Formula = "=Nimble_GetHistory(""NFO"",R15,S15,T15,U15,V15)"
Application.Wait Now + TimeValue("00:00:01")
Range("P1225").Formula = "=Nimble_GetHistory(""NFO"",R16,S16,T16,U16,V16)"
Application.Wait Now + TimeValue("00:00:01")
Range("P1374").Formula = "=Nimble_GetHistory(""NFO"",R17,S17,T17,U17,V17)"
Application.Wait Now + TimeValue("00:00:01")
Range("P1523").Formula = "=Nimble_GetHistory(""NFO"",R18,S18,T18,U18,V18)"
Application.Wait Now + TimeValue("00:00:01")
Range("P1672").Formula = "=Nimble_GetHistory(""NFO"",R19,S19,T19,U19,V19)"
Application.Wait Now + TimeValue("00:00:01")
Range("P1821").Formula = "=Nimble_GetHistory(""NFO"",R20,S20,T20,U20,V20)"
Application.Wait Now + TimeValue("00:00:01")
Range("P1970").Formula = "=Nimble_GetHistory(""NFO"",R21,S21,T21,U21,V21)"
Application.Wait Now + TimeValue("00:00:01")
Range("P2119").Formula = "=Nimble_GetHistory(""NFO"",R22,S22,T22,U22,V22)"
Application.Wait Now + TimeValue("00:00:01")
Range("P2268").Formula = "=Nimble_GetHistory(""NFO"",R23,S23,T23,U23,V23)"
Application.Wait Now + TimeValue("00:00:01")
Range("P2417").Formula = "=Nimble_GetHistory(""NFO"",R24,S24,T24,U24,V24)"
Application.Wait Now + TimeValue("00:00:01")
Range("P2566").Formula = "=Nimble_GetHistory(""NFO"",R25,S25,T25,U25,V25)"
Application.Wait Now + TimeValue("00:00:01")
Range("P2715").Formula = "=Nimble_GetHistory(""NFO"",R26,S26,T26,U26,V26)"
Application.Wait Now + TimeValue("00:00:01")
Range("P2864").Formula = "=Nimble_GetHistory(""NFO"",R27,S27,T27,U27,V27)"
Application.Wait Now + TimeValue("00:00:01")
Range("P3013").Formula = "=Nimble_GetHistory(""NFO"",R28,S28,T28,U28,V28)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P3264").Formula = "=Nimble_GetHistory(""NFO"",R29,S29,T29,U29,V29)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P3413").Formula = "=Nimble_GetHistory(""NFO"",R30,S30,T30,U30,V30)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P3562").Formula = "=Nimble_GetHistory(""NFO"",R31,S31,T31,U31,V31)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P3711").Formula = "=Nimble_GetHistory(""NFO"",R32,S32,T32,U32,V32)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P3860").Formula = "=Nimble_GetHistory(""NFO"",R33,S33,T33,U33,V33)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P4009").Formula = "=Nimble_GetHistory(""NFO"",R34,S34,T34,U34,V34)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P4158").Formula = "=Nimble_GetHistory(""NFO"",R35,S35,T35,U35,V35)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P4307").Formula = "=Nimble_GetHistory(""NFO"",R36,S36,T36,U36,V36)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P4456").Formula = "=Nimble_GetHistory(""NFO"",R37,S37,T37,U37,V37)"
'Application.Wait Now + TimeValue("00:00:01")
'Range("P4605").Formula = "=Nimble_GetHistory(""NFO"",R38,S38,T38,U38,V38)"
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub