Please edit this macro for me...thanks everyone!

pedie · Jun 30, 2010

The below code works very well but it is design for both ANALOG CLOCK & DIGITAL CLOCK: ANALOG CLOCK is prepared with the help of MS PPT. I just want this macro to be edited for DIGITAL CLOCK only.

Please help...any help will be appriciated:

Sub Updateclok()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
' Updates the clock that's visible<o

></o

>
Dim Clock As Chart<o

></o

>
Set Clock = ThisWorkbook.Sheets("Sheet1").ChartObjects("ClockChart").Chart<o

></o

>
<o

></o

>
If Clock.Parent.Visible Then<o

></o

>
' ANALOG CLOCK<o

></o

>
Const PI As Double = 3.14159265358979<o

></o

>
Dim CurrentSeries As Series<o

></o

>
Dim s As Series<o

></o

>
Dim x(1 To 2) As Variant<o

></o

>
Dim v(1 To 2) As Variant<o

></o

>
<o

></o

>
' Hour hand<o

></o

>
Set CurrentSeries = Clock.SeriesCollection("HourHand")<o

></o

>
x(1) = 0<o

></o

>
x(2) = 0.5 * Sin((Hour(Time) + (Minute(Time) / 60)) * (2 * PI / 12))<o

></o

>
v(1) = 0<o

></o

>
v(2) = 0.5 * Cos((Hour(Time) + (Minute(Time) / 60)) * (2 * PI / 12))<o

></o

>
CurrentSeries.XValues = x<o

></o

>
CurrentSeries.Values = v<o

></o

>
<o

></o

>
' Minute hand<o

></o

>
Set CurrentSeries = Clock.SeriesCollection("MinuteHand")<o

></o

>
x(1) = 0<o

></o

>
x(2) = 0.8 * Sin((Minute(Time) + (Second(Time) / 60)) * (2 * PI / 60))<o

></o

>
v(1) = 0<o

></o

>
v(2) = 0.8 * Cos((Minute(Time) + (Second(Time) / 60)) * (2 * PI / 60))<o

></o

>
CurrentSeries.XValues = x<o

></o

>
CurrentSeries.Values = v<o

></o

>
<o

></o

>
' Second hand<o

></o

>
Set CurrentSeries = Clock.SeriesCollection("SecondHand")<o

></o

>
x(1) = 0<o

></o

>
x(2) = 0.85 * Sin(Second(Time) * (2 * PI / 60))<o

></o

>
v(1) = 0<o

></o

>
v(2) = 0.85 * Cos(Second(Time) * (2 * PI / 60))<o

></o

>
CurrentSeries.XValues = x<o

></o

>
CurrentSeries.Values = v<o

></o

>
Else<o

></o

>
' DIGITAL CLOCK<o

></o

>
ThisWorkbook.Sheets("Sheet1").Range("DigitalClock").Value = CDbl(Time)<o

></o

>
End If<o

></o

>
<o

></o

>
' Set up the next event one second from now<o

></o

>
NextTick = Now + TimeValue("00:00:01")<o

></o

>
Application.OnTime NextTick, "Updateclok"<o

></o

>
<o

></o

>

Greg Truby · Jun 30, 2010

pedie said:
...ANALOG CLOCK is prepared with the help of MS PPT...

...I just want this macro to be edited for DIGITAL CLOCK only...

Given that the "digital clock" appears to be naught be a simple cell this particular code would not be applicatable to PowerPoint. Furthermore at least up through PowerPoint 2007, the PowerPoint application's object model does not support the OnTime method.

If I have misunderstood and this will indeed be running in Excel then all you need to do is delete the lines pertaining to the analog clock, i.e. you end up with:

Rich (BB code):

Sub Updateclok()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
' DIGITAL CLOCK<o:p></o:p>
ThisWorkbook.Sheets("Sheet1").Range("DigitalClock").Value = CDbl(Time)<o:p></o:p>

' Set up the next event one second from now<o:p></o:p>
NextTick = Now + TimeValue("00:00:01")<o:p></o:p>
Application.OnTime NextTick, "Updateclok"
 
End Sub

Also, please use code tags when posting code.

pedie · Jun 30, 2010

This does not work....giving error message.

Please help guys!!

pedie · Jun 30, 2010

pedie said:
The below code works very well but it is design for both ANALOG CLOCK & DIGITAL CLOCK: ANALOG CLOCK is prepared with the help of MS PPT. I just want this macro to be edited for DIGITAL CLOCK only.

Please help...any help will be appriciated:

Sub Updateclok()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
' Updates the clock that's visible<o></o>
Dim Clock As Chart<o></o>
Set Clock = ThisWorkbook.Sheets("Sheet1").ChartObjects("ClockChart").Chart<o></o>
<o></o>
If Clock.Parent.Visible Then<o></o>
' ANALOG CLOCK<o></o>
Const PI As Double = 3.14159265358979<o></o>
Dim CurrentSeries As Series<o></o>
Dim s As Series<o></o>
Dim x(1 To 2) As Variant<o></o>
Dim v(1 To 2) As Variant<o></o>
<o></o>
' Hour hand<o></o>
Set CurrentSeries = Clock.SeriesCollection("HourHand")<o></o>
x(1) = 0<o></o>
x(2) = 0.5 * Sin((Hour(Time) + (Minute(Time) / 60)) * (2 * PI / 12))<o></o>
v(1) = 0<o></o>
v(2) = 0.5 * Cos((Hour(Time) + (Minute(Time) / 60)) * (2 * PI / 12))<o></o>
CurrentSeries.XValues = x<o></o>
CurrentSeries.Values = v<o></o>
<o></o>
' Minute hand<o></o>
Set CurrentSeries = Clock.SeriesCollection("MinuteHand")<o></o>
x(1) = 0<o></o>
x(2) = 0.8 * Sin((Minute(Time) + (Second(Time) / 60)) * (2 * PI / 60))<o></o>
v(1) = 0<o></o>
v(2) = 0.8 * Cos((Minute(Time) + (Second(Time) / 60)) * (2 * PI / 60))<o></o>
CurrentSeries.XValues = x<o></o>
CurrentSeries.Values = v<o></o>
<o></o>
' Second hand<o></o>
Set CurrentSeries = Clock.SeriesCollection("SecondHand")<o></o>
x(1) = 0<o></o>
x(2) = 0.85 * Sin(Second(Time) * (2 * PI / 60))<o></o>
v(1) = 0<o></o>
v(2) = 0.85 * Cos(Second(Time) * (2 * PI / 60))<o></o>
CurrentSeries.XValues = x<o></o>
CurrentSeries.Values = v<o></o>
Else<o></o>
' DIGITAL CLOCK<o></o>
ThisWorkbook.Sheets("Sheet1").Range("DigitalClock").Value = CDbl(Time)<o></o>
End If<o></o>
<o></o>
' Set up the next event one second from now<o></o>
NextTick = Now + TimeValue("00:00:01")<o></o>
Application.OnTime NextTick, "Updateclok"<o></o>
<o></o>

Please help!!!

Greg Truby · Jun 30, 2010

pedie said:
This does not work....giving error message.

Please help guys!!

(a) Yes, it works and (b) no it does not give an error message.

Greg Truby · Jun 30, 2010

Now - does my above response help at all? I have provided the same level of detail you are providing. If you want more helpful responses then you might consider providing more information - like the error message being returned and the line being highlighted. You would also want to confirm that you are trying to run this in Excel and not PowerPoint.

pedie · Jun 30, 2010

it does not work....please dont say it works...

Greg Truby · Jun 30, 2010

Not to belabor the point, but I tested the code I posted and it works just fine. Perhaps you didn't see part 2 of my response?

pedie · Jun 30, 2010

Greg Truby,

The error message is as given below:

Error: Run time error '1004':

Application-defined or object-defined error

When i click on debug

Sub Updateclok()
' DIGITAL CLOCK
ThisWorkbook.Sheets("Sheet1").Range("DigitalClock").Value = CDbl(Time)
' Set up the next event one second from now
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "Updateclok"

End Sub

Greg Truby · Jun 30, 2010

Again, I'm assuming you are indeed running this in Excel and not trying to run this in PowerPoint...

Does your workbook have a sheet named Sheet1?

Does Sheet1 have a named range with the name DigitalClock?

Edit - also, it's not a bad idea to put code in the workbook's BeforeClose event handler that will kill the OnTime process. Otherwise you may find Excel reopening the workbook every second.

Please edit this macro for me...thanks everyone!

pedie

Well-known Member

Excel Facts

Greg Truby

MrExcel MVP

pedie

Well-known Member

pedie

Well-known Member

Greg Truby

MrExcel MVP

Greg Truby

MrExcel MVP

pedie

Well-known Member

Greg Truby

MrExcel MVP

pedie

Well-known Member

Greg Truby

MrExcel MVP

Similar threads

Forum statistics

Share this page

Please edit this macro for me...thanks everyone!

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock