Microsoft Excel 中 的 編 程

Question

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

Answer 1

方法(函數)
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

Answer 2

连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等。

Answer 3

1、 分类统计&#61664;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)
● 在