excel – this and that @ work

Excel VBA – VBA performance Optimization

Optimizing VBA code

Sample file download – https://github.com/dannyyun/pivot-vba.git

Add ‘OptimizeCode_Begin’ to the first line inside sub, and add ‘OptimizeCode_End’ to the last line inside sub.


Sub OptimizeCode_Begin()
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

End Sub

Sub OptimizeCode_End()
    
    ActiveSheet.DisplayPageBreaks = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub

Example of usage

Sub GroupBy()

    OptimizeCode_Begin

    'Enter your code between optimize code begin/end

    OptimizeCode_End

End Sub

Excel VBA – Pivot table/chart 5 – Chart Data label control

Turn on/off chart data label using VBA code

Sample file download – https://github.com/dannyyun/pivot-vba.git

Turn on/off chart data label

Sub DataLabel()

    Dim cbValue As Object
    Set cbValue = ActiveSheet.CheckBoxes("Check Box 1")
    
    Dim chtObj As ChartObject
    Dim sr As Series
    
    With cbValue
        If .Value = 1 Then
            For Each chtObj In ActiveSheet.ChartObjects
                For Each sr In chtObj.Chart.SeriesCollection
                    sr.ApplyDataLabels
                        With sr.DataLabels
          '             .ShowSeriesName = True
                        .ShowValue = True
           '            .Position = xlLabelPositionInsideBase
          '             .Orientation = -90
          '             .Font.Size = 8
                        End With
                Next sr
            Next chtObj
            
        Else
            For Each chtObj In ActiveSheet.ChartObjects
                For Each sr In chtObj.Chart.SeriesCollection
                    sr.ApplyDataLabels
                        With sr.DataLabels
          '             .ShowSeriesName = True
                        .ShowValue = False
           '            .Position = xlLabelPositionInsideBase
          '             .Orientation = -90
          '             .Font.Size = 8
                        End With
                Next sr
            Next chtObj
        
        End If
    End With
    
End Sub

Excel VBA – Pivot table/chart 4 – Chart type control

Changing Chart Type using VBA code

Sample file download – https://github.com/dannyyun/pivot-vba.git

Changing Chart type (line chart, stacked column, 100% stacked column)

Sub ChtType()

    With ActiveSheet.Shapes("Drop Down 4").ControlFormat

        Select Case .ListIndex
            Case Is = 1
                ActiveSheet.ChartObjects("Chart 1").Activate
                ActiveChart.ChartType = xlLine
                ActiveSheet.Range("A1").Select
                
            Case Is = 2
                ActiveSheet.ChartObjects("Chart 1").Activate
                ActiveChart.ChartType = xlColumnStacked
                ActiveSheet.Range("A1").Select
                
            Case Is = 3
                ActiveSheet.ChartObjects("Chart 1").Activate
                ActiveChart.ChartType = xlColumnStacked100
                ActiveSheet.Range("A1").Select
                
        End Select
    End With

End Sub

Excel VBA – Pivot table/chart 3 – Data fields control

Switching Data Fields using VBA code

Sample file download – https://github.com/dannyyun/pivot-vba.git

Switching Data fields (Quantity, Amount, Average Selling Price, Average Order Value)

Sub DisplayValue()
    
    OptimizeCode_Begin
    
    With ActiveSheet.Shapes("Drop Down 3").ControlFormat
    
        Select Case .ListIndex
            Case Is = 1
                Qty
            Case Is = 2
                Amt
            Case Is = 3
                ASP
            Case Is = 4
                AOV
        End Select
        
    End With
    
    DataLabel
    
    OptimizeCode_End

End Sub

2. Add ‘column’ to Data field.

Sub Amt()

    Dim pt As PivotTable
    Dim pi As PivotItem
    Set pt = ActiveSheet.PivotTables(1)
    
    For Each pi In pt.DataPivotField.PivotItems
        If pi.Name = "Amt" Then
            Exit Sub
        Else
            pi.Visible = False
        End If
    Next pi
    
    With pt.PivotFields("Amt")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "$#,##0"
        
    End With
 
End Sub

Sub Qty()
   
    Dim pt As PivotTable
    Dim pi As PivotItem
    Set pt = ActiveSheet.PivotTables(1)
    
    For Each pi In pt.DataPivotField.PivotItems
        If pi.Name = "Qty" Then
            Exit Sub
        Else
            pi.Visible = False
        End If
    Next pi

    With pt.PivotFields("Qty")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "#,##0"
    End With
    
End Sub

Sub ASP()
  
    Dim pt As PivotTable
    Dim pi As PivotItem
    Set pt = ActiveSheet.PivotTables(1)
    
    For Each pi In pt.DataPivotField.PivotItems
        If pi.Name = "ASP" Then
            Exit Sub
        Else
            pi.Visible = False
        End If
    Next pi
    
    With pt.PivotFields("ASP")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "$#,##0.00"
    End With
    
End Sub

Sub AOV()
   
    Dim pt As PivotTable
    Dim pi As PivotItem
    Set pt = ActiveSheet.PivotTables(1)
    
    For Each pi In pt.DataPivotField.PivotItems
        If pi.Name = "AOV" Then
            Exit Sub
        Else
            pi.Visible = False
        End If
    Next pi
        
    With pt.PivotFields("AOV")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "$#,##0.00"
    End With

End Sub

Excel VBA – Pivot table/chart 2 – Column fields control

Switching Column Fields using VBA code

Sample file download – https://github.com/dannyyun/pivot-vba.git

Switching column fields

Sub GroupBy()
   
    
    With ActiveSheet.Shapes("Drop Down 2").ControlFormat
    
        Select Case .ListIndex
            Case Is = 1
                PrTypeView
            Case Is = 2
                ProductView
            Case Is = 3
                ChTypeView
            Case Is = 4
                ChannelView
        End Select
        
    End With


End Sub

2. Add ‘column’ to Column field. Note that ‘ClearColumn’ sub is at the first of the line before adding a ‘column’ to Column field.

Sub PrTypeView()
    
    ClearColumn
    
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("Product_Type")
        .Orientation = xlColumnField
        .Position = 1
    End With
    

End Sub

Sub ProductView()
    
    ClearColumn
    
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("Product_Name")
        .Orientation = xlColumnField
        .Position = 1
    End With

End Sub
Sub ChTypeView()
    
    ClearColumn
    
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("Channel_Type")
        .Orientation = xlColumnField
        .Position = 1
    End With

End Sub


Sub ChannelView()
    
    ClearColumn
    
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("Channel")
        .Orientation = xlColumnField
        .Position = 1
    End With

End Sub

3. Clear Column field

Sub ClearColumn()
    
    Dim pt As PivotTable
    Dim fld As Object
    Set pt = ActiveSheet.PivotTables(1)
    For Each fld In pt.ColumnFields
        Debug.Print fld.Name
        fld.Orientation = xlHidden
    Next fld

End Sub