Excel VBA 学习笔记（一）

1. 获取有数据的单元格
   Set rng = ws.UsedRange Get all the cells with data.

2. range更改

Set rng = ws.Range(ws.Cells(rng.Row, rng.Column), _
rng.SpecialCells(xlCellTypeLastCell).Offset(-1, 0))

ws.Cells 获得Range的第一个单元格的坐标
rng.SpecialCells(xlCellTypeLastCell).Offset(-1,0)获得最后一个单元格的坐标，且行数减1

3. 简化格式后的完整程序

Sub FormatAndChart( )   
' AutoFormats and Charts all of the worksheets in a workbook.   
' Designed to work with Sales Data tables.    
' 5/28/04 by Jeff Webb    '        
Dim rng As Range, ws As Worksheet        
' Repeats actions for all Worksheets in the workbook.        
For Each ws In Worksheets            
  ' Get the cells with data in them.            
  Set rng = ws.UsedRange            
  ' Apply AutoFormat            
rng.AutoFormat Format:=xlRangeAutoFormatSimple            
' Omit the Total row from the range.            
Set rng = ws.Range(ws.Cells(rng.Row, rng.Column), _ 
              rng.SpecialCells(xlCellTypeLastCell).Offset(-1, 0))            
' Create a chart.            
Charts.Add           
' Set chart properties.            
ActiveChart.ChartType = xlColumnClustered            ActiveChart.SetSourceData Source:=rng, PlotBy:=xlColumns           
' Insert the chart on the worksheet.            
ActiveChart.Location Where:=xlLocationAsObject, Name:=ws.Name  Next
End Sub

4. 显示message box

Sub ShowMessage( )        
Dim x As Integer        
x = Sheets.Count        
MsgBox "This workbook contains " & x & " sheets."
End Sub

5. 写入单元格

Sub ChangeRange( )        
Dim x As Double        
x = InputBox("Enter a number.")        
Range("J5") = x ^ (1 / 3)    
End Sub

6. 公共函数 module

Public Function CubeRoot(x As Double) As Double        
CubeRoot = x ^ (1 / 3)
End Function

7. 自动填充

Range("A2").AutoFill Destination:=Range("A2:A26"), Type:=xlFillDefault

8. 对于大于平均值的数用绿色背景标记

 Selection.FormatConditions.AddAboveAverage
 Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
 Selection.FormatConditions(1).AboveBelow = xlAboveAverage
 With Selection.FormatConditions(1).Font
 .Color = -16752384
 .TintAndShade = 0
 End With
 With Selection.FormatConditions(1).Interior
 .PatternColorIndex = xlAutomatic
 .Color = 13561798
 .TintAndShade = 0
 End With
MsgBox "Added an Above Average Conditional Format to Melanie's data. Press F9 to update values.", vbInformation
End Sub