Sub assistance
in [Programming]
|
Prev: FIFO HELP!
Next: Application.GetOpenFilename
From: Max on 4 Jan 2010 09:13 Dave, I hit some problems trying it out live at the office .. a. The core Sub YourSubRoutineNameHere() The pasted new sheet (copied from Live) seems to be prematurely? copied as all of the formulated cells pasted are showing errors such as "#Name", or "#N/A requesting data". The live formulas involved are Bloomberg formulas, and the PC is a bloomberg terminal. When I tested it at home the other day, I used some volatile functions (Rand(),Now()) and it worked fine. What can be done to force the sub to wait awhile (say, 30 sec) before copying the sheet, codename: Live, and pasting it? That should suffice to allow all calcs to complete before the copy/paste proceeds. b. The timer subs On 2 testing occasions I was somehow caught in an interminable: "Rename failed!" loop where answering the msgbox failed to end the sub (it looped to return yet another "Rename failed!"), and I had to Ctrl-break to stop the sub. Grateful for further views, thanks
From: Dave Peterson on 4 Jan 2010 09:27 I've never used the bloomberg stuff, so this is just a guess. Try adding these two lines after the .copy line: doevents application.calculate (maybe the doevents will mean you won't need the .calculate--you'll find out soon!) If that doesn't work, then try: Set wks = ActiveSheet 'just copied version of live 'doevents 'application.calculate With wks .cells.Replace what:="=", replacement:="=", _ lookat:=xlPart, searchorder:=xlByRows, _ MatchCase:=False .... This replaces the equal sign with equal sign. Hoping that it forces excel to recalculate all the formulas in the new worksheet. Since the name is changed to the month and day, there's a good chance that it'll fail when you're testing. I'd use something that would make it much harder to fail: ..Name = Format(Now, "yyyymmdd hhmmss") It would be pretty weird to have the program run at the same second! Max wrote: > > Dave, I hit some problems trying it out live at the office .. > > a. The core Sub YourSubRoutineNameHere() > The pasted new sheet (copied from Live) seems to be prematurely? copied as > all of the formulated cells pasted are showing errors such as "#Name", or > "#N/A requesting data". The live formulas involved are Bloomberg formulas, > and the PC is a bloomberg terminal. When I tested it at home the other day, > I used some volatile functions (Rand(),Now()) and it worked fine. What can > be done to force the sub to wait awhile (say, 30 sec) before copying the > sheet, codename: Live, and pasting it? That should suffice to allow all > calcs to complete before the copy/paste proceeds. > > b. The timer subs > On 2 testing occasions I was somehow caught in an interminable: "Rename > failed!" loop where answering the msgbox failed to end the sub (it looped to > return yet another "Rename failed!"), and I had to Ctrl-break to stop the > sub. > > Grateful for further views, thanks -- Dave Peterson
From: Max on 4 Jan 2010 09:37 Thanks Dave. Will tinker as guided tomorrow, and feedback further in this thread (in ~ 24 hours time). cheers
From: Max on 5 Jan 2010 08:15 Dave, think it seems to work, but I'll like to monitor it over the next few days What does > doevents accomplish? To enable accelerated testing, how could your Sub StartTimer() below be changed to say, fire it at 3 min intervals between 8 am - 9 am everyday? ------ Sub StartTimer() If Time < TimeSerial(8, 0, 0) Then RunWhen = Date + TimeSerial(8, 0, 0) Else RunWhen = Date + 1 + TimeSerial(8, 0, 0) End If Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _ Schedule:=True End Sub
From: Dave Peterson on 5 Jan 2010 09:19
DoEvents lets the pc handle some other stuff--sometimes, the VBA loops can essentially take over the pc. The DoEvents says to let other processes run. Go back to Chip's page. His start timer routine would be perfect for testing. Max wrote: > > Dave, think it seems to work, but I'll like to monitor it over the next few > days > > What does > doevents accomplish? > > To enable accelerated testing, how could your Sub StartTimer() below be > changed > to say, fire it at 3 min intervals between 8 am - 9 am everyday? > > ------ > Sub StartTimer() > If Time < TimeSerial(8, 0, 0) Then > RunWhen = Date + TimeSerial(8, 0, 0) > Else > RunWhen = Date + 1 + TimeSerial(8, 0, 0) > End If > Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _ > Schedule:=True > End Sub -- Dave Peterson |