VBA Excel宏操作示例

1、 首先在excel中任意添加一个列状图

2、 编辑Excel—》》宏

 

3、 在对应的sheet上进行处理

//定义几个变量
Dim rowCount As Integer                                                                                                                                                                                                                     
    Dim endRowIndex As Integer                                                                                                                                                                                                              
    Dim currentYear As String                                                                                                                                                                                                               
    Dim lastYear As String                                                                                                                                                                                                                  
  //获取指定单元格的信息，这里Me就是当前的sheet                                                                                                                                                                                                            
    currentYear = Me.Range("C5").Value                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                Dim rowCount As Integer                                                                                                                                                                                                                                                                                                                                                                                                        
    lastYear = Me.Range("D5").Value                                                                                                                                                                                                             Dim endRowIndex As Integer                                                                                                                                                                                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                Dim currentYear As String                                                                                                                                                                                                                                                                                                                                                                                                      
    startRowIndex = 5                                                                                                                                                                                                                           Dim lastYear As String                                                                                                                                                                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
    endRowIndex = Me.Range("AO1").Value                                                                                                                                                                                                         currentYear = Me.Range("C5").Value                                                                                                                                                                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
    endRowIndex = 7                                                                                                                                                                                                                             lastYear = Me.Range("D5").Value                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
    Dim columnChart1 As Chart                                                                                                                                                                                                                   startRowIndex = 5                                                                                                                                                                                                                                                                                                                                                                                                              
//拿到第一个图吧对象                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    With ActiveSheet.ChartObjects(1).Chart                                                                                                                                                                                                      endRowIndex = Me.Range("AO1").Value                                                                                                                                                                                                                                                                                                                                                                                            
//对标题进行处理                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
    .ChartTitle.Characters.Text = currentYear + "年及" + lastYear + "年投资完成率对比"                                                                                                                                                                    endRowIndex = 7                                                                                                                                                                                                                                                                                                                                                                                                                
//绑定数据源，name，x，y                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
    .SetSourceData Union(Sheets("test").Range("B" & startRowIndex, "B" & endRowIndex), Sheets("test").Range("C" & startRowIndex, "C" & endRowIndex), Sheets("test").Range("D" & startRowIndex, "D" & endRowIndex)), Excel.XlRowCol.xlColumns    Dim columnChart1 As Chart                                                                                                                                                                                                                                                                                                                                                                                                      
        With .SeriesCollection(1)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
           .HasDataLabels = True                                                                                                                                                                                                                With ActiveSheet.ChartObjects(1).Chart                                                                                                                                                                                                                                                                                                                                                                                         
           .Name = Me.Range("C5").Value                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        End With                                                                                                                                                                                                                                .ChartTitle.Characters.Text = currentYear + "年及" + lastYear + "年投资完成率对比"                                                                                                                                                                + lastYear + "年投资完成率对比"                                                                                                                                                                
        With .SeriesCollection(2)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
           .HasDataLabels = True                                                                                                                                                                                                                .SetSourceData Union(Sheets("test").Range("B" & startRowIndex, "B" & endRowIndex), Sheets("test").Range("C" & startRowIndex, "C" & endRowIndex), Sheets("test").Range("D" & startRowIndex, "D" & endRowIndex)), Excel.XlRowCol.xlColumnstartRowIndex, "B" & endRowIndex), Sheets("test").Range("C" & startRowIndex, "C" & endRowIndex), Sheets("test").Range("D" & startRowIndex, "D" & endRowIndex)), Excel.XlRowCol.xlColumns
           .Name = Me.Range("D5").Value                                                                                                                                                                                                             With .SeriesCollection(1)                                                                                                                                                                                                                                                                                                                                                                                                  
        End With                                                                                                                                                                                                                                       .HasDataLabels = True                                                                                                                                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                       .Name = Me.Range("C5").Value                                                                                                                                                                                                                                                                                                                                                                                            
    End With                                                                                                                                                                                                                                        End With                                                                                                                                                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                    With .SeriesCollection(2)                                                                                                                                                                                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                                       .HasDataLabels = True                                                                                                                                                                                                                                                                                                                                                                                                   
    With ActiveSheet.ChartObjects(2).Chart                                                                                                                                                                                                             .Name = Me.Range("D5").Value                                                                                                                                                                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                    End With                                                                                                                                                                                                                                                                                                                                                                                                                   
    .ChartTitle.Characters.Text = currentYear + "年及" + lastYear + "年资金完成率对比"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                End With                                                                                                                                                                                                                                                                                                                                                                                                                       
    .SetSourceData Union(Sheets("test").Range("B" & startRowIndex, "B" & endRowIndex), Sheets("test").Range("F" & startRowIndex, "F" & endRowIndex), Sheets("test").Range("G" & startRowIndex, "G" & endRowIndex)), Excel.XlRowCol.xlColumns                                                                                                                                                                                                                                                                                                                                                                                                                                   
        With .SeriesCollection(1)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
           .HasDataLabels = True                                                                                                                                                                                                                With ActiveSheet.ChartObjects(2).Chart                                                                                                                                                                                                                                                                                                                                                                                         
           .Name = Me.Range("C5").Value                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        End With                                                                                                                                                                                                                                .ChartTitle.Characters.Text = currentYear + "年及" + lastYear + "年资金完成率对比"                                                                                                                                                                + lastYear + "年资金完成率对比"                                                                                                                                                                
        With .SeriesCollection(2)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
           .HasDataLabels = True                                                                                                                                                                                                                .SetSourceData Union(Sheets("test").Range("B" & startRowIndex, "B" & endRowIndex), Sheets("test").Range("F" & startRowIndex, "F" & endRowIndex), Sheets("test").Range("G" & startRowIndex, "G" & endRowIndex)), Excel.XlRowCol.xlColumnstartRowIndex, "B" & endRowIndex), Sheets("test").Range("F" & startRowIndex, "F" & endRowIndex), Sheets("test").Range("G" & startRowIndex, "G" & endRowIndex)), Excel.XlRowCol.xlColumns
           .Name = Me.Range("D5").Value                                                                                                                                                                                                             With .SeriesCollection(1)                                                                                                                                                                                                                                                                                                                                                                                                  
        End With                                                                                                                                                                                                                                       .HasDataLabels = True                                                                                                                                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                       .Name = Me.Range("C5").Value                                                                                                                                                                                                                                                                                                                                                                                            
    End With                                                                                                                                                                                                                                        End With                                                                                                                                                                                                                                                                                                                                                                                                                   
        With .SeriesCollection(2)                                                                                                                                                                                                                                                                                                                                                                                                  
           .HasDataLabels = True                                                                                                                                                                                                                                                                                                                                                                                                   
           .Name = Me.Range("D5").Value                                                                                                                                                                                                                                                                                                                                                                                            
        End With                                                                                                                                                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                                                                                                                                                   
    End With                                                                                                                                                                                                                                                                                                                                                                                                                       
                                                                                                                                                                                                                                                                                                                                                                                                                                   
End Sub