The Excel macro does not update properly

advertisements

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