Solved

Slowing down a "For Next"  Loop in Visual basic

Posted on 2013-11-20
4
338 Views
Last Modified: 2014-01-06
I have the following code in Excel.
It basically, moves back and forth from Excel to my DOS based system
and Copy's and Pastes some information.
I'd just like to slow it down, so I can see what lines are not being copied.

I'd just like a 2 second delay on each loop.
I know how to do that in other programming languages I've used.
How can I do that in Visual Basic?




Sub sendtocp()
AppActivate "My Program"
For i = 3 To 21



 SendKeys Sheet1.Cells(i, 3), True
 SendKeys "~", True
 SendKeys Sheet1.Cells(i, 5), True
 SendKeys "~", True
 SendKeys Sheet1.Cells(i, 5), True
 SendKeys "~", True
 SendKeys "~", True
 y = 1
 For j = 1 To 10000
 y = y + 1
 Next j
Next i
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
0
Comment
Question by:James Powell
4 Comments
 
LVL 9

Accepted Solution

by:
WebDevEM earned 300 total points
ID: 39662814
Hi,

I think what you're looking for would be
Sleep 2000 'Number of milliseconds to pause

Open in new window

There's a site called MrExcel.com that has some VERY handy tips, and I found this on it.  I hope it helps!

WebDevEM
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 39663503
Do note, as in the link provided by WebDevEM, you have to declare the Sleep() API before you can use it:

    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
0
 
LVL 40
ID: 39664658
The .NET topic has nothing to do with VBA in Excel because Visual Basic .NET and VBA are quite different.

The Microsoft Access topic does not help much either, because although both Excel and Access use VBA, they do it in different ways.

You are losing potential experts by defining useless topics in your questions. Aren't there VBA and Excel topics?
0
 
LVL 13

Expert Comment

by:Surone1
ID: 39674646
i reccomend not using sleep since it will "freeze" things up.
as an alternative you could try something like this :


starttime = Now 'i put this in for debugging only
endtime = DateAdd("s", 90, Now) 'determine endtime
Do While endtime > Now 'start 90 second loop
Label1.Caption = Now 'do sonmething OR NOT!
DoEvents 'prevent this from freezing
Loop
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now