The worst Code Ever

J

Jacob_F_Roecker

OK I think that I've got the record for the WORST code EVER.

I wanted to build a spreadsheet that would automatically become a webpage.

I built the macro and I was good. COOL BEANS

But I wanted excel to do that every minute...because it referenced a
external data that updated every minute.

I got it to work the WORST possible way you could imagine for code and speed.

I told it to calculate the difference between two cells--each of which were
times/dates.

Basically my Macro is continuously running the =now() on one cell until it's
been a minute and then it runs the other code.... starts all over and does it
again.

I KNOW there are more efficient ways of doing this but google didn't point
me to anything I could understand.

Someone mind writing me and letting me know the BEST way to do this? I'm
game for some reading.
 
N

Nick Hodge

Jacob

You can use the Ontime method of the application object to run code at a
specified time and then loop from that procedure back to the timer one, like
this

Sub WaitOneMinute()
Application.OnTime EarliestTime:=Now() + TimeValue("00:01:00"), _
Procedure:="RunNow"
End Sub

Sub runNow()
'Do your refresh here
WaitOneMinute
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]
 
D

David McRitchie

Hi Jacob,
Excel makes for a very inefficient and expensive clock. regardless of whether
you are looking at the clock or not, it going to severely interfere with Excel. Perhaps
with lots of RAM and a fast machine it would look better. I have more RAM than before but
I'm not going to experiment to see if it still makes Excel really bad, I would suggest
that you install something on the Status Bar to give you the seconds that you don't
see otherwise.
Dale Nurden's TClockEx, Taskbar Clock Enhancer v1.4.2
http://www.rcis.co.za/dale/tclockex/index.htm
and if you need something to sync your time
http://tf.nist.gov/service/its.htm look for Windows 32bit

More information on my datetime.htm page, but you have the links to actual sites above.

If you want something more customized you can use a Java Applet on a web page
of your own. example http://www.mvps.org/dmcritchie/ie/javaclock.htm
I don't think the links are good anymore you may have to a Google search
Java Applet time countdown countup timer.
actually the link is good may be different that the original --
http://timeanddate.com/counters/newyear.html
(and the time shown matches my Atomic clock -- receives radio signal from WWV)

I doubt that you would be able to convert you Excel page with ontime to a web page anyway
by saving it as HTML..
 
Top