excel - Having problems with Macro running every 15 minutes correctly -


i have code (see below) suppose run code every 15 minutes data refreshed yahoo finance , recorded in appropriate cells. macro suppose run on own once run first time. run first time after 15 min start doing every 2 min 15 min. isn't consistent. below code picture of producing. 

sub timestamp() ' ' timestamp macro ' ' keyboard shortcut: ctrl+shift+a ' ' following refreshes data     application.calculatefullrebuild  ' following inputs exchange date (l1) , time (n1) next available cell in column activesheet     .cells(rows.count, "a").end(xlup).offset(1, 0)         .value = application.evaluate("concatenate(l1,n1)")         .wraptext = false     end end  ' following inputs current price of stock (g3) next available cell in column d activesheet     .cells(rows.count, "d").end(xlup).offset(1, 0) = .range("g3").value2 end  ' following inputs date of exchange (l1) next available cell in column b activesheet.cells(rows.count, "b").end(xlup).offset(1, 0)      'this sets value of b2 value of l1     .value = activesheet.range("l1").value2     .wraptext = false end  ' following inputs time of exchange (n1) next available cell in column c activesheet.cells(rows.count, "c").end(xlup).offset(1, 0)      'this sets value of b2 value of l1     .value = activesheet.range("n1").value2     .wraptext = false end  ' following runs timestamp macro every 15 minutes     application.ontime + timevalue("00:15:00"), "timestamp"  end sub

enter image description here

a global variable means accessible routine or function in macro. declare variable global doing outside (above) rest of code.

chip has excellent tutorial should address issue.

declare public variables in standard code module, outside of , before procedure (sub or function) declaration:

public runwhen double public const crunintervalseconds = 120 ' 2 minutes public const crunwhat = "thesub"  ' name of procedure run

to start repeatable timer, create procedure named starttimer shown below:

sub starttimer()     runwhen = + timeserial(0,0,crunintervalseconds)     application.ontime earliesttime:=runwhen, procedure:=crunwhat, _         schedule:=true end sub

this stores time run procedure in variable runwhen, 2 minutes after current time.

next, need write procedure called ontime. example,

sub thesub()     ''''''''''''''''''''''''     ' code here     ''''''''''''''''''''''''     starttimer  ' reschedule procedure end sub

this procedure executes whatever code include in it, , @ end calls starttimer procedure schedule ontime event. how periodic calls implemented. note if close workbook while ontime event pending, excel re-open workbook execute procedure , not close workbook after ontime event finished.


Comments

Post a Comment

Popular posts from this blog

searchKeyword not working in AngularJS filter -

i writing app using angularjs on front end. want search through table word/field; 1 search box everything. according article here: http://hello-angularjs.appspot.com/searchtable can use filter: searchkeyword this. this code, , not working expected. <label>search: <input ng-model="searchkeyword"></label> <table ng-repeat="post in posts | orderby: sort | filter: searchkeyword"> <tr> <td> {{index(post)}} </td> <td> {{post.title}} </td> <td> {{post.author}} </td> <td> {{post.content}} </td> <td> {{post.date | date: "d/m/yyyy"}} </td> </tr> </table> what should do? thank you try this: controller $scope.posts = [ {title:'title one', author:'author one', content: 'content one'}, {title:'title two', author:'author two', content: 'cont...
Read more

sequelize.js - Sequelize: sort by enum cases -

i have model ( article ) field (the name type ) of type enum('source', 'translated') , return 1 article ordered type field. source articles should returned before translated articles. like: article.findone(order: [ { type: [ 'source', 'translated' ] } ]); unfortunately there appears no built-in solution sorting enums. instead have sort by case , couldn't find documentation on how sequelize. what conventional approach here? sorting case hard in sequelize, sort field (the second answer in linked post) should possible: order: sequelize.fn('field', sequelize.col('type'), 'source', 'translated') it's supported mysql far know though
Read more

user interface - how to replace an ongoing process of image capture from another process call over the same ImageLabel in python's GUI TKinter -

i trying use following code create interactive gui changes color-space(hsv, rbg, grayscale etc) on event of button click. display opencv video in tkinter using multiprocessing being new python having problems multiprocessing , attempts on making gui change it's color space on button clicks hangs entire system. on it's implementation highly appreciated. thankyou. below code: import cv2 pil import image,imagetk import tkinter tk import numpy np multiprocessing import process , queue def quit_it(root,process): root.destroy() process.terminate() def black_andwhite(root,process): process.terminate p=process(target=capture_image, args=(5,queue, )) p.start() root.after(0, func=lambda: update_all(root, image_label, queue)) def update_image(image_label, queue): frame = queue.get() = image.fromarray(frame) b = imagetk.photoimage(image=a) image_label.configure(image=b) image_label._image_cache = b root.update() def update_all(root...
Read more