CODEWITHSUNDEEP
excelvbaexcel-2010optional-parameters
Fin
Fin

Reputation: 1

VBA Macro - Argument not optional

I'm new to the whole VBA thing, currently practicing making macros for Excel worksheets. I wanted to make a button that, when pressed, updates query from the table to an archive table and then updates all the pivot tables linked to it. I managed to do both parts, but only as separate actions, meaning I can either update the query or update the pivot tables. I figured pivot tables need a slight delay after query update to actually have new data to load, but when I tried to put a timer macro in-between the 2 functions, it showed me a "Argument not optional" error. What am I doing wrong? Code in question below. I also tried to put the macro code directly into the button code, but it showed a different error. I figured it'd be easier to just write it as a separate macro for later uses as well.

Private Sub CommandButton1_Click()
    ActiveWorkbook.Connections("Query — Archive").Refresh

    Call Macro1
     'Code for macro is as follows:
        'Sub Macro1(ByVal seconds As Integer)
        '   For i As Integer = 0 To seconds * 100
        '       System.Threading.Thread.Sleep (10)
        '        Application.DoEvents()
        '    Next
        'End Sub
    
    Dim pc As PivotCache
  For Each pc In ThisWorkbook.PivotCaches
    pc.Refresh
  Next pc

End Sub

Upvotes: -2

Views: 112

Answers (2)

mailoof
mailoof

Reputation: 1

To wait for the request update to complete, disable background update (False), then perform a PivotTable update cycle and return the background update to its original value (True).

Private Sub CommandButton1_Click()

    Dim pc As PivotCache

    With ActiveWorkbook.Connections("Query — Archive").OLEDBConnection
        .BackgroundQuery = False
        .Refresh
    
      For Each pc In ThisWorkbook.PivotCaches
         pc.Refresh
      Next pc

        .BackgroundQuery = True
    End With
End Sub

Upvotes: 0

mailoof
mailoof

Reputation: 1

To delay code execution, you can use the following:

Public Sub Pause(myTime As Single)
    Dim timeEnd As Single
    timeEnd = Timer + myTime * 10 'Multiplying by 10, we increase the interval by 10 times
    While Timer < timeEnd
        DoEvents
    Wend
End Sub

Public Sub mainCode()
    startTime = Timer
    Pause 1   'The number of pauses can be changed: Pause 1/Pause 2/..../Pause n

    'You code....

    Debug.Print Timer - startTime
End Sub
'Measurements (Pause 1): ~ 10.01 sec.
'Measurements(Pause 2): ~ 20.01 sec.

Upvotes: -3

Related Questions