Microsoft Excel 中 的 編 程

[ipqxiang]

提綱
語法
請參考 Microsoft Visual Basic 6.0.
方法(函數)
系統格式(sub),自定義格式(Function).
控件
采用VB6.0控件.
事件
單元格事件,控件事件.
結構
Project,object,modules,class modules,forms 等．
操作數據庫
數據庫對象(ADO),建立連接,添加,刪除,更新等.
擴展
不僅用在Excel,在 Word,PowerPoint 等中都可用.

[ipqxiang]

方法(函數)
Sub
格式代碼:
Sub Sub_Name(ByVal params as type)
‘add code in here
End Sub
Function(函數)
格式代碼:
Function Fun_Name(ByRef params as type)
‘add code in here，可以有返回值
End Function
事件
單元格事件
格式代碼:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As range)
Dim r(2) As range
Set r(1) = Target
Set r(2) = range(“A1")
If r(1).row = r(2).row And r(1).Column = r(2).Column Then
‘deal event
End if
End Sub
單元格賦值方法(讀取數據類同)：
1、[A1] = 10
2、[A1,A2] = 10 或 [A1:A2] = 10
3、Range(“A1”) = 10
4、Range(“A1,A2”) = 10 或 Range(“A1:A2”) = 10
5、Cells(1,1) = 10 或 Cells(1,”A”) = 10
控件事件
雙擊控件就可以對應事件代碼。
格式代碼:
Private Sub CommandButton1_Click()
‘deal event
End Sub
Validation(List)
加入到list:
set r1 = range(“A1:A5”)
Dim formula as string
formula = “=$A$1:$A$5”
r1.Validation.Delete
r1.Activate
With r1.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Formula1:=formula
.InCellDropdown = True
.IgnoreBlank = True
.ShowError = False
End With
操作數據庫
數據對象（ADO）
這里數據對象采用ADO技術，可以通過工具添加對應的引用






建立連接
代碼：ConnectString = “Driver={SQLSERVER};Server=10.162.130.85;Database=CAPEX;UID=Capex_User;pwd=capex;"
Set Connector = New ADODB.Connection
Connector.ConnectionString = ConnectString
Connector.Open

[ipqxiang]

连Access
Function Getconnection() As ADODB.Connection
Dim adoConnection As ADODB.Connection
Set adoConnection = New ADODB.Connection
Dim sqlConnection As String
'sqlConnection = "Driver={SQL SERVER};Server=;DataBase=;Uid=;Pwd=;"
sqlConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.mdb;Persist Security Info=False"
adoConnection.ConnectionString = sqlConnection
On Error GoTo err1
adoConnection.Open
Set Getconnection = adoConnection
err1:
If Err.Number <> 0 Then
Set Getconnection = Nothing
End If
End Function
操作數據庫
-- 新增数据
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim sql As String
Set conn = Me.Getconnection()
Set cmd = New ADODB.Command
sql = "INSERT INTO Table1(id,name) VALUES('" & [L5] & "','" & [L6] & "')"
With cmd
.CommandText = sql
.CommandType = adCmdText
.ActiveConnection = conn
.Execute
End With
数据更新、删除同理
数据绑定datagrid
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
sql = "SELECT * FROM Table1"
Set conn = Me.Getconnection()
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sql, conn, adOpenStatic, adLockOptimistic
Set DataGrid1.DataSource = rs
DataGrid1.Refresh
conn.Close
擴展
VBA 不僅僅用在 Excel 還用在對應 Office 系列的軟件中 Word,PowerPoint等。

[ipqxiang]

1、 分类统计PivotTable
Function RunSubTotal(sourceType As XlPivotTableSourceType, SourceData As String, _
DestinationRange As Range, tableName As String, rowFieldName As String, colFieldName As String, dataFieldName() As String)
Dim pCache As PivotCache
If ActiveSheet.PivotTables.Count < 1 Then
Set pCache = ActiveWorkbook.PivotCaches.Add(sourceType, SourceData:=SourceData)
pCache.CreatePivotTable TableDestination:=DestinationRange, tableName:=tableName
With ActiveSheet.PivotTables(tableName)
.SmallGrid = False
With .PivotFields(rowFieldName)
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields(colFieldName)
.Orientation = xlColumnField
.Position = 1
End With
Dim i As Integer
For i = 1 To UBound(dataFieldName)
With .PivotFields(dataFieldName(i))
.Orientation = xlDataField
.Position = 1
End With
Next i
End With
Else
ActiveSheet.PivotTables(tableName).RefreshTable
End If
End Function
2、 对工作簿Workbook进行编程
● Workbook是什么？
● Workbook 属性，方法，事件
● Workbook之间数据交互
Workbooks("book1.xls").Sheets(1).Cells(1, 1)
● 在