CODEWITHSUNDEEP
excelvba
nightcrawler
nightcrawler

Reputation: 327

Increment Cell Value in Excel but LOOP doesn't behave accordingly

I want to loop a cell value with defined UpperLimit, LowerLimit & Increment with a 3sec interval. But Do While Loop doesn't stop ...it keeps on incrementing

Sub IntervalIncrement()
varValueL = InputBox("Input LowerBound")
varValueU = InputBox("Input UpperBound")
varValueI = InputBox("Input increment")
ActiveCell.Value = varValueL
Application.Wait (Now + TimeValue("0:00:03"))
    Do While (ActiveCell.Value < varValueU)
    ActiveCell.Value = ActiveCell.Value + varValueI
    Application.Wait (Now + TimeValue("0:00:03"))
    Loop
End Sub

Upvotes: 0

Views: 28

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12665

It's because your variable varValueU is set as a String and not as an Integer (or a numerical value, more generically).

You should solve the issue by changing this: 

Do While (ActiveCell.Value < varValueU)

... with this: 

Do While (ActiveCell.Value < CInt(varValueU))

Question 1:

  • Why is varValueU defined as a String?
  • It's actually a Variant, since you never declare it, but since you get it via an InputBox which has as default type String (since you can input whatever) it gets interpreted as a String

Question 2:

  • Why it understands ActiveCell.Value = ActiveCell.Value + varValueI (where varValueI is also a String) then?
  • Because your variables are not declared. It's a very very good habit (actually, in most languages is compulsory) to declare the type of your variables to avoid this kind of issues. If you don't declare, your type will be Variant. In your specific case, a Variant which is found in an operation (like the sum ActiveCell.Value + varValueI) is tried to be evaluated as numeric with success, but it's not the same treatment that the interpreter gives in a comparison (like the condition ActiveCell.Value < varValueU)

Question 3:

  • How do I make sure to declare all my variables and avoid this kind of incidents?
  • Add Option Explicit on top of your module, the compiler will force you to declare each variable in your code explicitly.

Upvotes: 1

Related Questions