• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to stop a VBA program running into an infinite loop

Hrishikesh Sandilya

Hrishikesh Sandilya

New Member
I am very new to VBA and so please don't mind if my question is very simple.

I put my program into an infinite loop while writing a DO WHILE loop.

I tried to stop it by Ctrl+Pause/Break but it did not work and finally I was compelled to do Alt+Ctrl+Del.

Can someone tell me if there is a better way to stop a program running a infinite loop.
 
Hi Hrishikesh, welcome to the Forum !

Could you please upload a sample file with your code.
If the code is going to infinite loop then we have to change the code.

Pausing will work as a temp (incorrect) solution only.
 
Well here is the code. A very simple one.

Code: 
Option Explicit
Sub UpdateAverageProfitLossPerCustomer()
'This will calculate the average profit or loss per customer
Sheets("Sheet1").Select
Range("H2").Select

Do While Condition

Loop
 
Hi ThrottleWorks

Here is the code with the condition. The intend of this code is to add all values in column G until in encounters a blank value in column G and show that value in H2.

I can achieve this by simply using this formula. =sum(G2:G1080)
But I want to do it by using VBA.

The problem is every time I execute the program, it runs in an infinite loop.

I am struggling to get out of that loop. Please help.


Code: 
Option Explicit
Sub UpdateAverageProfitLossPerCustomer()
'This will calculate the average profit or loss per customer
Sheets("Sheet1").Select

Do While Range("G2") <> ""

Range("H1").Value = Range("G2").Value + Range("G3").Value

Loop


End Sub
 
Hi, Hrishikesh Sandilya!
Please tell us in detail what do you want to do with that code, and post the whole code, or better indeed upload a sample file.
Regards!
 
Hi, use below code:
Code: 
Sub AddG()


lr = 2
Range("H1").Value = 0


Do While Range("g" & lr) <> ""
Range("H1").Value = Range("H1").Value + Range("G" & lr).Value
lr = lr + 1
Loop

End Sub


Ignore the earlier one.
Regards,
 
@Hrishikesh Sandilya
Try this..
You dont have go for any Loop..

Code: 
Sub UpdateAverageProfitLossPerCustomerButIAmUsingSumOnly_WhereIsMyAverageFunction()
'This will calculate the average profit or loss per customer
Sheets("Sheet1").Select
  Range("H1").Value = Evaluate("=sum(G2:G1080)")
End Sub
 
@Debraj , I tried your code.

I have 2 doubts, could you please help if you get time.

1) what is the difference between "Evaluate("=sum(G2:G1080)")" and ""=sum(G2:G1080)""
Both are giving me same results.

2) The code is populating the sum for entire range, does not consider blank cell.
I think OP want the sum till the first blank cell in the range.

I tried Mr. Google for Excel VBA Evaluate Method but could not understand.
 

Attachments

  • Book1.xls
    29 KB · Views: 6
Hi Sachin A Sonawane!

1) what is the difference between "Evaluate("=sum(G2:G1080)")" and ""=sum(G2:G1080)""
Both are giving me same results.

* evaluate will convert the formula to values and will give only the result in Vlaue.

2) The code is populating the sum for entire range, does not consider blank cell.
I think OP want the sum till the first blank cell in the range.

* I know.. you have already deleted your comment for SM :).. its upto you.. to choose one Line from below both OP's quote.. :)

add all values in column G until in encounters a blank value in column G and show that value in H2
Click to expand...

and

I can achieve this by simply using this formula. =sum(G2:G1080)
Click to expand...
 
There seems to be some confusion regarding what I actually wanted from the forum.

Well, my query was, "If a VBA program runs into an infinite loop, because of some condition in the code, is there a way to forcibly stop the program and edit the code?"

I was basically looking for alternative of using Alt+Ctrl+Del to stop a program.

I did not get an answer to my query, but instead learnt many other things from the forum members. Thanks you everyone for your help.

Will get back to it in the future, to learn more.
 
SirJB7 said:
And regarding your issue, it's always useful to place a DoEvents function statement usually just before the Loop instruction, so as to be able to manually stop the code with Ctrl-Break. Despite of this, check for your logic to avoid endless loops.
Click to expand...
Hi, Hrishikesh Sandilya!

Have you tried this? It's the answer to your question, so I believe you yes did get the answer to your query. Am I wrong?

Now if you didn't understand it or you need a fully working code, you should have helped the community to help you and you should have explained the whole logic or posted a sample file, as was asked here:
ThrottleWorks said:
Could you please upload a sample file with your code.
If the code is going to infinite loop then we have to change the code.
Click to expand...
ThrottleWorks said:
Hrishikesh, please paste the condition part also, that is very important to find the cause.
Click to expand...

Or you could have tried this suggested code. Have you done so? If so which was the result, did it worked fine?
Somendra Misra said:
Hi, use below code:
Code: 
Sub AddG()


lr = 2
Range("H1").Value = 0


Do While Range("g" & lr) <> ""
Range("H1").Value = Range("H1").Value + Range("G" & lr).Value
lr = lr + 1
Loop

End Sub
Click to expand...

Briefly. Don't write:
Hrishikesh Sandilya said:
There seems to be some confusion regarding what I actually wanted from the forum.
...
I did not get an answer to my query
Click to expand...
when you've been answered, you've been asked twice for posting the required information to give you a working code if needed, and even you've been provided by a suggested code. People who tried to help you deserve more respect for their efforts to aid you.

Regards!
 
Hi SirJB7,

Well, I did put up the code and the file, so that other's can have an idea of what I am looking for.

Also, I am very aware of the efforts others are putting in to contribute here. I thank every one of them for it. There can be no two ways about it. In fact the prompt and relevant answers from the members has impressed me a lot. My apologies if this point was not clear at any point.

This is my 4th day into learning VBA, so the concepts of using DO event just before a loop is still a bit above my level.

Having said that, I hope, I have the right to express my honest opinions about this forum. I expect people to accept it similarly too.

Regards
Hrishikesh
 
Last edited:
Hrishikesh Sandilya said:
Hi SirJB7,

Well, I did put up the code and the file, so that other's can have an idea of what I am looking for.

Also, I am very aware of the efforts others are putting in to contribute here. I thank every one of them for it. There can be no two ways about it. In fact the prompt and relevant answers from the members has impressed me a lot. My apologies if this point was not clear at any point.

This is my 4th day into learning VBA, so the concepts of using DO event just before a loop is still a bit above my level.

Having said that, I hope, I have the right to express my honest opinions about this forum. I expect people to accept it similarly too.

Regards
Hrishikesh
Click to expand...

Dear @Hrishikesh Sandilya, & other respected users.


In addition, if Personal.xls have many modules, any one module can cause for halted/hanged excel or infinite running of any code. You can stop infinitely running macro & overcome from hanged/halted excel ...trick is here

Mainly please concentrate on post no 4 ...90% success ratio. Try it..

https://stackoverflow.com/questions...macro-executution-when-pressing-esc-wont-work

STOP MACRO.png

Hope this helps users to outcome from Halted/ Hanged Excel or macro goes to infinite loop.

Respected Experts requested here to spread little light on "Do Events".
what Do event actually do? & how its is important in code? Where it should be use in code?

Hope there are something useful- forever found here.

Regards,
Chirag Raval
 
Last edited:
You must log in or register to reply here.
Back
Top