switch macro VBA running slow

Question

I just finished my first macro but it appears that it running extremely slow and after few loops it's freezes.Before that I have issues with data presentation but I solved it by putting true value in first row.

Point of macro is to show different data on dashboard in each 30 seconds.

Please find my code below:

Public Sub Switch()
    Do
    With ActiveWorkbook.SlicerCaches("Slicer_country1")
        .SlicerItems("NL").Selected = True
        .SlicerItems("SP").Selected = False
        .SlicerItems("GB").Selected = False
    End With
    With ActiveWorkbook.SlicerCaches("Slicer_Project1")
        .SlicerItems("XX").Selected = True
        .SlicerItems("YY").Selected = False
        .SlicerItems("ZZ").Selected = False
        Application.Wait Now + TimeValue("00:00:30")
    End With
    With ActiveWorkbook.SlicerCaches("Slicer_Project1")
        .SlicerItems("XX").Selected = True
        .SlicerItems("YY").Selected = False
        .SlicerItems("ZZ").Selected = False
         Application.Wait Now + TimeValue("00:00:30")
    End With
    With ActiveWorkbook.SlicerCaches("Slicer_Project1")
        .SlicerItems("XX").Selected = True
        .SlicerItems("YY").Selected = False
        .SlicerItems("ZZ").Selected = False
         Application.Wait Now + TimeValue("00:00:30")
    End With
    Loop
End Sub

Recent solution with application on time, but it works properly only with last call (I get only wanted field, in first 2 other buttons won't switch off), is there any solution to show only wanted values on each call ?

Dim CallNumber As Integer
Sub ScheduleChange()
Change
Application.OnTime Now + TimeValue("00:00:05"), "ScheduleChange"
End Sub

Sub Change()
CallNumber = CallNumber + 1
With ActiveWorkbook.SlicerCaches("Slicer_Project1")
.SlicerItems("XX").Selected = (CallNumber = 1)
.SlicerItems("YY").Selected = False
.SlicerItems("ZZ").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Project1")
.SlicerItems("YY").Selected = (CallNumber = 2)
.SlicerItems("XX").Selected = False
.SlicerItems("ZZ").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Project1")
.SlicerItems("ZZ").Selected = (CallNumber = 3)
.SlicerItems("XX").Selected = False
.SlicerItems("YY").Selected = False
End With
If CallNumber = 3 Then
CallNumber = 0
End If
End Sub

Hi again, I use below code but still macro won't show one button at time, it's going from first button to second without deselecting previous one, is there any command to force it to show only one value at time ? Debug shows that Object variable or With block variable not set.

Dim CallNumber As Integer
Sub ScheduleChange()
Change
Application.OnTime Now + TimeValue("00:00:05"), "ScheduleChange"
End Sub

Sub Change()
CallNumber = CallNumber + 1
With ActiveWorkbook.SlicerCaches("Slicer_Project1")
.SlicerItems("XX").Selected = (CallNumber = 1)
.SlicerItems("YY").Selected = (CallNumber = 2)
.SlicerItems("ZZ").Selected = (CallNumber = 3)
End With
If CallNumber = 3 Then
CallNumber = 0
End If
End Sub

itChi · Answer 1 · 2017-10-19T13:41:33.443

1

From here

Application wait will freeze your application, and is not a very effective way to manage delays.

Use this delay function instead of Application.Wait

Private Sub delay(seconds As Long)
    Dim endTime As Date
    endTime = DateAdd("s", seconds, Now())
    Do While Now() < endTime
        DoEvents
    Loop
End Sub

edited Oct 19 '17 at 13:41

answered Oct 19 '17 at 13:34

itChi

642
6
19

Or you can simply use the Windows `Sleep` API. – K.Dᴀᴠɪs Oct 19 '17 at 13:35
@K.Davis or that :D I rather enjoy complex solutions. – itChi Oct 19 '17 at 13:36
1

@K.Davis Sleep will also freeze Excel. – LS_ᴅᴇᴠ Oct 19 '17 at 13:39
1

Better ti use Application.Ontime for this, a lot more efficient. – jkpieterse Oct 19 '17 at 13:47

Ste Griffiths · Accepted Answer · 2017-10-24T12:51:59.660

As itChi says, using a Application.Wait will block Excel. But I think there's an even better way of doing this than a Do-Loop and DoEvents - you could try Application.OnTime to schedule when you want your event to happen:

Your current code is like this:

Sub BlockingChange()
    Dim i As Integer
    i = 0
    Do
        Range("A1").Value2 = i
        Application.Wait Now + TimeValue("0:00:01")
        i = i + 1
    Loop
End Sub

...which blocks Excel altogether.

The alternative with OnTime would be:

Sub NonBlockingChange()
    Update
    Application.OnTime Now + TimeValue("0:00:01"), "NonBlockingChange"
End Sub

Sub Update()
    Range("A1").Value2 = CInt(Range("A1").Value2) + 1
End Sub

Now be warned that every time you run this, it will schedule another instance of the running sub, so if you run the macro twice, it will run the sub twice per second, etc.

To cancel these queued events, you just pass False as the fourth parameter:

Sub StopChanges()

On Error GoTo Catch
    Application.OnTime Now + TimeValue("0:00:01"), "NonBlockingChange", , False
    Exit Sub
Catch:
    MsgBox ("Nothing to stop")

End Sub

This should work great for you. Just replace the body of Update with what you want to change, and change the 1 second timeout to your value.

Edit

Lukas, in your edited code, you have just swapped out Application.Wait for Application.OnTime. They are not the same and you need to use them in different ways, read the examples again to see how.

From your edited code, here are the things you need to change:

No more Do-Loop. Read the examples above to see that we don't need a loop any more, the OnTime call will set a schedule to call your sub
OnTime needs an argument which is the name of the sub to call
So this means you need to refactor (change) your code a little to use the new structure

Something LIKE this (you need to tailor it and finish it):

Dim CallNumber As Integer
Sub ScheduleChange()
    Change
    Application.OnTime Now + TimeValue("0:00:10"), "ScheduleChange"
End Sub

Sub Change()
    'Select a different slicer on each call to this function
    CallNumber = CallNumber + 1
    With ActiveWorkbook.SlicerCaches("Slicer_Project1")
        .SlicerItems("XX").Selected = (CallNumber = 1)
        .SlicerItems("YY").Selected = (CallNumber = 2)
        .SlicerItems("ZZ").Selected = (CallNumber = 3)
    End With

    ' When it gets to 3, roll over to the first one again
    If CallNumber = 3 Then
        CallNumber = 0
    End If
End Sub

And then to start it, you would call ScheduleChange. Put these functions in their own Module or you will get a "Cannot run the macro" error.

Well, sound great, but once I try it, I get compile error:"argument not optional". — Lukas_88, Oct 23 '17 at 13:33
Is that in the Set or the Stop sub? Please can you edit the question with the exact code you're using? — Ste Griffiths, Oct 24 '17 at 09:46
Ok, see my edit of the answer. You need to put some work into this and really read the answer. — Ste Griffiths, Oct 24 '17 at 12:52
Hi Ste, It works almost perfect, but once I use this method slicers are switching but without deselecting previous call, I tried to fix it by adding false values, but it won't work. — Lukas_88, Nov 07 '17 at 13:36
Hi @Lukas_88, if you step through your latest code, you should see that it is running all the changes in one go instead of them happening on sequential calls to Change(). I recommend using the Change function I wrote above and stepping through your code with the debugger to see why the slicers are not being set back to false. — Ste Griffiths, Nov 08 '17 at 13:51
Hi Ste, once I highlight line with slicer item, I have "object variable or with variable not set" I think that is the issue, but I have no idea to fix it — Lukas_88, Nov 08 '17 at 14:45

switch macro VBA running slow

2 Answers2

Edit