i have created macro for excel but it seems somewhere i have done something wrong,
i want to fetch an image from a URL and then update it up to 1 second (more or less)
Sub GetPicture() PictureURL = "This is where i put the URLi want" Set MyPict = ActiveSheet.Pictures.Insert(PictureURL) Cells(1).Value = Now nextTime = Now + TimeValue("00:00:01") End Sub
when i run the macro doesn't do anything,only when i press f5 the it updates as fast as i press f5,also what is the value to update less than 1 second ("00:00:01"),when i try ("00:00:0.5") it comes up with "run time error 13" "type mismatch" Any help is very much apreciated.
In Excel, you can use VBA to trigger code that updates a Worksheet on specific intervals. The code below shows how you would activate a Timer each time the Worksheet is activated by a user. Whenever the Timer fires (on 1 second intervals here) this code updates Cell A1 in the ActiveSheet with the current Time.
To further customize, you would add code to the OnTimerMacro in order to update a Picture or whatever else you recurring task might be. (Props to Hartmut Gierke for his post on the topic.)
Option Explicit Dim Execute_TimerDrivenMacro As Boolean Sub Start_OnTimerMacro() Execute_TimerDrivenMacro = True Application.OnTime Time + TimeValue("00:00:01"), ActiveSheet.Name & ".OnTimerMacro" End Sub Sub Stop_OnTimerMacro() Execute_TimerDrivenMacro = False End Sub Public Sub OnTimerMacro() If Execute_TimerDrivenMacro Then ' Do something e.g. put the actual time into cell A1 of the active sheet ActiveSheet.Cells(1, 1).Value = Time ' At the end restart timer Application.OnTime Time + TimeValue("00:00:01"), ActiveSheet.Name & ".OnTimerMacro" End If End Sub Private Sub Worksheet_Activate() 'Start the timer driven method when opening the sheet Start_OnTimerMacro End Sub Private Sub Worksheet_Deactivate() 'Stop the timer driven method when opening the sheet Stop_OnTimerMacro End Sub