Excel VBA crashes after a larger number of rows
I have created an Excel macro to run an analysis for a larger dataset (~24,000 lines). The macro is working well for the first c. 2,000 requests/lines but thereafter generally crashes. The request/line number, at which the crash occurs, thereby varies.
The debugger, which is appearing at that point, highlights that the code line leading to the crash is:
Cells(i, 7).Value = var1.innerText
I have already checked other threads and solutions on this topic, including a suggested change from 'Int' to 'Long' etc - none of these worked however. Can anyone help why the macro is working well for smaller requests but failing after a certain number of lines?
The code I am using is pasted below. Many thanks in advance.
Sub Gethits()
Dim url As String, lastRow As Long
Dim XMLHTTP As Object, html As Object, objResultDiv As Object, objH3 As Object, link As Object
Dim start_time As Date
Dim end_time As Date
Dim var As String
Dim var1 As Object
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Dim cookie As String
Dim result_cookie As String
start_time = Time
Debug.Print "start_time:" & start_time
For i = 1654 To lastRow
url = "https://www.google.com/search?q=" & Cells(i, 4) & "&source=lnt&tbs=cdr%3A1%2Ccd_min%3A" & Cells(i, 5) & "%2Ccd_max%3A" & Cells(i, 6) & "&tbm=nws"
Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
XMLHTTP.Open "GET", url, False
XMLHTTP.setRequestHeader "Content-Type", "text/xml"
XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
XMLHTTP.send
Set html = CreateObject("htmlfile")
html.body.innerHTML = XMLHTTP.responseText
Set objResultDiv = html.getElementById("rso")
Set var1 = html.getElementById("resultStats")
Cells(i, 7).Value = var1.innerText
DoEvents
Next
end_time = Time
Debug.Print "end_time:" & end_time
Debug.Print "done" & "Time taken : " & DateDiff("n", start_time, end_time)
MsgBox "done" & "Time taken : " & DateDiff("n", start_time, end_time)
End Sub
excel vba excel-vba crash
asked Nov 24 '18 at 17:35
M. S.M. S.
375
add a comment |
I have created an Excel macro to run an analysis for a larger dataset (~24,000 lines). The macro is working well for the first c. 2,000 requests/lines but thereafter generally crashes. The request/line number, at which the crash occurs, thereby varies.
The debugger, which is appearing at that point, highlights that the code line leading to the crash is:
Cells(i, 7).Value = var1.innerText
I have already checked other threads and solutions on this topic, including a suggested change from 'Int' to 'Long' etc - none of these worked however. Can anyone help why the macro is working well for smaller requests but failing after a certain number of lines?
The code I am using is pasted below. Many thanks in advance.
Sub Gethits()
Dim url As String, lastRow As Long
Dim XMLHTTP As Object, html As Object, objResultDiv As Object, objH3 As Object, link As Object
Dim start_time As Date
Dim end_time As Date
Dim var As String
Dim var1 As Object
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Dim cookie As String
Dim result_cookie As String
start_time = Time
Debug.Print "start_time:" & start_time
For i = 1654 To lastRow
url = "https://www.google.com/search?q=" & Cells(i, 4) & "&source=lnt&tbs=cdr%3A1%2Ccd_min%3A" & Cells(i, 5) & "%2Ccd_max%3A" & Cells(i, 6) & "&tbm=nws"
Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
XMLHTTP.Open "GET", url, False
XMLHTTP.setRequestHeader "Content-Type", "text/xml"
XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
XMLHTTP.send
Set html = CreateObject("htmlfile")
html.body.innerHTML = XMLHTTP.responseText
Set objResultDiv = html.getElementById("rso")
Set var1 = html.getElementById("resultStats")
Cells(i, 7).Value = var1.innerText
DoEvents
Next
end_time = Time
Debug.Print "end_time:" & end_time
Debug.Print "done" & "Time taken : " & DateDiff("n", start_time, end_time)
MsgBox "done" & "Time taken : " & DateDiff("n", start_time, end_time)
End Sub
excel vba excel-vba crash
asked Nov 24 '18 at 17:35
M. S.M. S.
375
I wrote a class for [Retrieve data from eBird API and create multi-level hierarchy of locations ](codereview.stackexchange.com/a/196922/171419) that handles 50+ requests at a time. It will drastically speed up your code.
– TinMan
Nov 24 '18 at 17:49
For now I loading the information into an array and writing all the rows at once is your best bet. It is also possible that the server is limiting the number of requests in a given time period.
– TinMan
Nov 24 '18 at 17:51
What's the error, specifically? Your code is assumingvar1isn'tNothing, could it be error 91?
– Mathieu Guindon
Nov 24 '18 at 18:06
1
And have your XMLHTTP object created outside the loop. You can add code to introduce waits at specified step intervals in your loop to help mitigate hitting with lots of requests too quickly.
– QHarr
Nov 24 '18 at 18:22
2
Option Explicitevery time. Every time - at the top of every module.
– AJD
Nov 24 '18 at 19:34
add a comment |
I have created an Excel macro to run an analysis for a larger dataset (~24,000 lines). The macro is working well for the first c. 2,000 requests/lines but thereafter generally crashes. The request/line number, at which the crash occurs, thereby varies.
The debugger, which is appearing at that point, highlights that the code line leading to the crash is:
Cells(i, 7).Value = var1.innerText
I have already checked other threads and solutions on this topic, including a suggested change from 'Int' to 'Long' etc - none of these worked however. Can anyone help why the macro is working well for smaller requests but failing after a certain number of lines?
The code I am using is pasted below. Many thanks in advance.
Sub Gethits()
Dim url As String, lastRow As Long
Dim XMLHTTP As Object, html As Object, objResultDiv As Object, objH3 As Object, link As Object
Dim start_time As Date
Dim end_time As Date
Dim var As String
Dim var1 As Object
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Dim cookie As String
Dim result_cookie As String
start_time = Time
Debug.Print "start_time:" & start_time
For i = 1654 To lastRow
url = "https://www.google.com/search?q=" & Cells(i, 4) & "&source=lnt&tbs=cdr%3A1%2Ccd_min%3A" & Cells(i, 5) & "%2Ccd_max%3A" & Cells(i, 6) & "&tbm=nws"
Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
XMLHTTP.Open "GET", url, False
XMLHTTP.setRequestHeader "Content-Type", "text/xml"
XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
XMLHTTP.send
Set html = CreateObject("htmlfile")
html.body.innerHTML = XMLHTTP.responseText
Set objResultDiv = html.getElementById("rso")
Set var1 = html.getElementById("resultStats")
Cells(i, 7).Value = var1.innerText
DoEvents
Next
end_time = Time
Debug.Print "end_time:" & end_time
Debug.Print "done" & "Time taken : " & DateDiff("n", start_time, end_time)
MsgBox "done" & "Time taken : " & DateDiff("n", start_time, end_time)
End Sub
excel vba excel-vba crash
asked Nov 24 '18 at 17:35
M. S.M. S.
375
I have created an Excel macro to run an analysis for a larger dataset (~24,000 lines). The macro is working well for the first c. 2,000 requests/lines but thereafter generally crashes. The request/line number, at which the crash occurs, thereby varies.
The debugger, which is appearing at that point, highlights that the code line leading to the crash is:
Cells(i, 7).Value = var1.innerText
I have already checked other threads and solutions on this topic, including a suggested change from 'Int' to 'Long' etc - none of these worked however. Can anyone help why the macro is working well for smaller requests but failing after a certain number of lines?
The code I am using is pasted below. Many thanks in advance.
Sub Gethits()
Dim url As String, lastRow As Long
Dim XMLHTTP As Object, html As Object, objResultDiv As Object, objH3 As Object, link As Object
Dim start_time As Date
Dim end_time As Date
Dim var As String
Dim var1 As Object
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Dim cookie As String
Dim result_cookie As String
start_time = Time
Debug.Print "start_time:" & start_time
For i = 1654 To lastRow
url = "https://www.google.com/search?q=" & Cells(i, 4) & "&source=lnt&tbs=cdr%3A1%2Ccd_min%3A" & Cells(i, 5) & "%2Ccd_max%3A" & Cells(i, 6) & "&tbm=nws"
Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
XMLHTTP.Open "GET", url, False
XMLHTTP.setRequestHeader "Content-Type", "text/xml"
XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
XMLHTTP.send
Set html = CreateObject("htmlfile")
html.body.innerHTML = XMLHTTP.responseText
Set objResultDiv = html.getElementById("rso")
Set var1 = html.getElementById("resultStats")
Cells(i, 7).Value = var1.innerText
DoEvents
Next
end_time = Time
Debug.Print "end_time:" & end_time
Debug.Print "done" & "Time taken : " & DateDiff("n", start_time, end_time)
MsgBox "done" & "Time taken : " & DateDiff("n", start_time, end_time)
End Sub
excel vba excel-vba crash
excel vba excel-vba crash
asked Nov 24 '18 at 17:35
M. S.M. S.
375
asked Nov 24 '18 at 17:35
M. S.M. S.
375
asked Nov 24 '18 at 17:35
M. S.M. S.
375
asked Nov 24 '18 at 17:35
M. S.M. S.
375
asked Nov 24 '18 at 17:35
M. S.M. S.
375
375
I wrote a class for [Retrieve data from eBird API and create multi-level hierarchy of locations ](codereview.stackexchange.com/a/196922/171419) that handles 50+ requests at a time. It will drastically speed up your code.
– TinMan
Nov 24 '18 at 17:49
For now I loading the information into an array and writing all the rows at once is your best bet. It is also possible that the server is limiting the number of requests in a given time period.
– TinMan
Nov 24 '18 at 17:51
What's the error, specifically? Your code is assumingvar1isn'tNothing, could it be error 91?
– Mathieu Guindon
Nov 24 '18 at 18:06
1
And have your XMLHTTP object created outside the loop. You can add code to introduce waits at specified step intervals in your loop to help mitigate hitting with lots of requests too quickly.
– QHarr
Nov 24 '18 at 18:22
2
Option Explicitevery time. Every time - at the top of every module.
– AJD
Nov 24 '18 at 19:34
add a comment |
I wrote a class for [Retrieve data from eBird API and create multi-level hierarchy of locations ](codereview.stackexchange.com/a/196922/171419) that handles 50+ requests at a time. It will drastically speed up your code.
– TinMan
Nov 24 '18 at 17:49
For now I loading the information into an array and writing all the rows at once is your best bet. It is also possible that the server is limiting the number of requests in a given time period.
– TinMan
Nov 24 '18 at 17:51
What's the error, specifically? Your code is assumingvar1isn'tNothing, could it be error 91?
– Mathieu Guindon
Nov 24 '18 at 18:06
1
And have your XMLHTTP object created outside the loop. You can add code to introduce waits at specified step intervals in your loop to help mitigate hitting with lots of requests too quickly.
– QHarr
Nov 24 '18 at 18:22
2
Option Explicitevery time. Every time - at the top of every module.
– AJD
Nov 24 '18 at 19:34
I wrote a class for [Retrieve data from eBird API and create multi-level hierarchy of locations ](codereview.stackexchange.com/a/196922/171419) that handles 50+ requests at a time. It will drastically speed up your code.
– TinMan
Nov 24 '18 at 17:49
I wrote a class for [Retrieve data from eBird API and create multi-level hierarchy of locations ](codereview.stackexchange.com/a/196922/171419) that handles 50+ requests at a time. It will drastically speed up your code.
– TinMan
Nov 24 '18 at 17:49
For now I loading the information into an array and writing all the rows at once is your best bet. It is also possible that the server is limiting the number of requests in a given time period.
– TinMan
Nov 24 '18 at 17:51
For now I loading the information into an array and writing all the rows at once is your best bet. It is also possible that the server is limiting the number of requests in a given time period.
– TinMan
Nov 24 '18 at 17:51
What's the error, specifically? Your code is assuming
var1 isn't Nothing, could it be error 91?– Mathieu Guindon
Nov 24 '18 at 18:06
What's the error, specifically? Your code is assuming
var1 isn't Nothing, could it be error 91?– Mathieu Guindon
Nov 24 '18 at 18:06
1
1
And have your XMLHTTP object created outside the loop. You can add code to introduce waits at specified step intervals in your loop to help mitigate hitting with lots of requests too quickly.
– QHarr
Nov 24 '18 at 18:22
And have your XMLHTTP object created outside the loop. You can add code to introduce waits at specified step intervals in your loop to help mitigate hitting with lots of requests too quickly.
– QHarr
Nov 24 '18 at 18:22
2
2
Option Explicit every time. Every time - at the top of every module.– AJD
Nov 24 '18 at 19:34
Option Explicit every time. Every time - at the top of every module.– AJD
Nov 24 '18 at 19:34
add a comment |
1 Answer
1
active
oldest
votes
Thanks for your feedback. The restriction indeed seems to be driven by Google limiting the number of requests in a given time period. When visiting the Google webpage after the error occurs, I am asked to run trough a captcha process due to unsually high request activity.
The idea of submitting multiple requests at a time seems very interesting in any case, as it would save a lot of time collecting the data. Is there, however, a simplified approach to the example by TinMan (codereview.stackexchange.com/a/196922/171419)? I have checked for other threads but have not found anything helpful thus far.
answered Jan 7 at 13:57
M. S.M. S.
375
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53460742%2fexcel-vba-crashes-after-a-larger-number-of-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for your feedback. The restriction indeed seems to be driven by Google limiting the number of requests in a given time period. When visiting the Google webpage after the error occurs, I am asked to run trough a captcha process due to unsually high request activity.
The idea of submitting multiple requests at a time seems very interesting in any case, as it would save a lot of time collecting the data. Is there, however, a simplified approach to the example by TinMan (codereview.stackexchange.com/a/196922/171419)? I have checked for other threads but have not found anything helpful thus far.
answered Jan 7 at 13:57
M. S.M. S.
375
add a comment |
Thanks for your feedback. The restriction indeed seems to be driven by Google limiting the number of requests in a given time period. When visiting the Google webpage after the error occurs, I am asked to run trough a captcha process due to unsually high request activity.
The idea of submitting multiple requests at a time seems very interesting in any case, as it would save a lot of time collecting the data. Is there, however, a simplified approach to the example by TinMan (codereview.stackexchange.com/a/196922/171419)? I have checked for other threads but have not found anything helpful thus far.
answered Jan 7 at 13:57
M. S.M. S.
375
add a comment |
Thanks for your feedback. The restriction indeed seems to be driven by Google limiting the number of requests in a given time period. When visiting the Google webpage after the error occurs, I am asked to run trough a captcha process due to unsually high request activity.
The idea of submitting multiple requests at a time seems very interesting in any case, as it would save a lot of time collecting the data. Is there, however, a simplified approach to the example by TinMan (codereview.stackexchange.com/a/196922/171419)? I have checked for other threads but have not found anything helpful thus far.
answered Jan 7 at 13:57
M. S.M. S.
375
Thanks for your feedback. The restriction indeed seems to be driven by Google limiting the number of requests in a given time period. When visiting the Google webpage after the error occurs, I am asked to run trough a captcha process due to unsually high request activity.
The idea of submitting multiple requests at a time seems very interesting in any case, as it would save a lot of time collecting the data. Is there, however, a simplified approach to the example by TinMan (codereview.stackexchange.com/a/196922/171419)? I have checked for other threads but have not found anything helpful thus far.
answered Jan 7 at 13:57
M. S.M. S.
375
answered Jan 7 at 13:57
M. S.M. S.
375
answered Jan 7 at 13:57
M. S.M. S.
375
answered Jan 7 at 13:57
M. S.M. S.
375
375
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53460742%2fexcel-vba-crashes-after-a-larger-number-of-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I wrote a class for [Retrieve data from eBird API and create multi-level hierarchy of locations ](codereview.stackexchange.com/a/196922/171419) that handles 50+ requests at a time. It will drastically speed up your code.
– TinMan
Nov 24 '18 at 17:49
For now I loading the information into an array and writing all the rows at once is your best bet. It is also possible that the server is limiting the number of requests in a given time period.
– TinMan
Nov 24 '18 at 17:51
What's the error, specifically? Your code is assuming
var1isn'tNothing, could it be error 91?– Mathieu Guindon
Nov 24 '18 at 18:06
1
And have your XMLHTTP object created outside the loop. You can add code to introduce waits at specified step intervals in your loop to help mitigate hitting with lots of requests too quickly.
– QHarr
Nov 24 '18 at 18:22
2
Option Explicitevery time. Every time - at the top of every module.– AJD
Nov 24 '18 at 19:34