Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #110573

Re: Pivot Table run-time error 1004 (PivotField class)

Show all headers | View raw

Since I do table data analysis in a userform I never did check to see what 
effect my EnableFastCode routine has on VBA processing of tables. Give that 
tables do a lot of automatic recalcs, this will suspend all background 
processes until your process is finished. You could give it a shot to see if it 
speeds things up!

'--------------------------------------------------------------------------------------
' **Note: EnableFastCode requires the following declarations be in a standard 
module.
'--------------------------------------------------------------------------------------
'Type udtAppModes
'  'Default types
'  Events As Boolean: CalcMode As XlCalculation: Display As Boolean: CallerID 
As String
'  'Project-specific types
'End Type
'Public AppMode As udtAppModes
'--------------------------------------------------------------------------------------
Sub EnableFastCode(Caller$, Optional SetFast As Boolean = True)
' **Note: Requires 'Type udtAppModes' and 'Public AppMode As udtAppModes' 
declarations

  'The following will make sure only the Caller has control,
  'and allows any Caller to take control when not in use.
  If AppMode.CallerID <> Caller Then _
    If AppMode.CallerID <> "" Then Exit Sub

  With Application
    If SetFast Then
      AppMode.Display = .ScreenUpdating: .ScreenUpdating = False
      AppMode.CalcMode = .Calculation: .Calculation = xlCalculationManual
      AppMode.Events = .EnableEvents: .EnableEvents = False
      AppMode.CallerID = Caller
    Else
      .ScreenUpdating = AppMode.Display
      .Calculation = AppMode.CalcMode
      .EnableEvents = AppMode.Events
      AppMode.CallerID = ""
    End If
  End With
End Sub

You use it like this:
  Sub DoThis()
  Const sSrc$ = "DoThis" '//name of procedure

  EnableFastCode sSrc
  'Do some overhead intensive stuff
  EnableFastCode sSrc, False
  End Sub

How it Works:
This is a concept-based methodology where the control belongs to only 1 Caller 
until that caller is done its task. That means other Callers using the settings 
can't inadvertently toggle them while another operation is using them.

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-05 20:14 -0400
  Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-06 06:55 -0400
  Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-06 07:23 -0400
    Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-06 07:35 -0400
      Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-06 20:13 -0400
        Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-06 20:42 -0400
          Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-07 13:09 -0400
            Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-07 15:33 -0400
              Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-07 15:52 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-08 14:02 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-08 16:08 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-09 06:34 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-09 16:29 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-09 22:01 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-09 23:10 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-10 14:05 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-10 22:10 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-11 00:03 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-11 05:30 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-11 10:46 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-14 09:09 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-14 13:58 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) Phrank <pbeal@hotmail.com> - 2018-04-17 18:15 -0400
                Re: Pivot Table run-time error 1004 (PivotField class) GS <gs@v.invalid> - 2018-04-17 19:48 -0400

csiph-web