'/////////////////////////////////////////////////////////////
' -ワークシート内のデータの初期化を行う-
'/////////////////////////////////////////////////////////////
Function CmdClear()
Dim sakiSH As Worksheet
Beep
Set sakiSH = ThisWorkbook.Sheets(2) '転記先のシート
sakiSH.Range("A4:O1000").ClearContents
sakiSH.Range("A4:O1000").Clear
Set sakiSH = Nothing
End Function
'/////////////////////////////////////////////////////////////
'paintCellsLine
'使い方：
' Dim startCell As Range
' Dim endCell As Range
' Set startCell = Workbooks(1).Sheets(2).Cells(4, 1)
' Set endCell = Workbooks(1).Sheets(2).Cells(48, 9)
' Call paintCellsLine(startCell, endCell)
'@param startCell
'@param endCell
'/////////////////////////////////////////////////////////////
Function paintCellsLine(startCell As Range, endCell As Range)
With Range(startCell, endCell)
For i = 7 To 12
.Borders(i).LineStyle = xlContinuous
Next
End With
End Function
VBA连DB
Dim sqlStr As String
sqlStr = getSqlStr()
Dim connection As New ADODB.connection
Dim resultSet As ADODB.recordSet
Dim connStr As String
connStr = "Provider=SQLOLEDB;Server=127.0.0.1;Database=BMS;Trusted_Connection=yes;Uid=sa;Pwd="
connection.Open connStr
Set resultSet = New ADODB.recordSet
resultSet.Open sqlStr, connection
Dictionary的使用（add Microsoft Scripting Runtime.）
Dim dict
' 创建Dictionary
Set dict = CreateObject("Scripting.Dictionary")
' 增加项目
dict.Add "A", 300
dict.Add "B", 400
dict.Add "C", 500
' 统计项目数
n = dict.Count
' 删除项目
dict.Remove ("A")
' 判断字典中是否包含关键字
dict.exists ("B")
' 取关键字对应的值，注意在使用前需要判断是否存在key，否则dict中会多出一条记录
Value = dict.Item("B")
' 修改关键字对应的值,如不存在则创建新的项目
dict.Item("B") = 1000
dict.Item("D") = 800
' 对字典进行循环
k = dict.keys
v = dict.Items
For i = 0 To dict.Count - 1
key = k(i)
Value = v(i)
MsgBox key & Value
Next
' 删除所有项目
dict.Removeall
VBA编程黄金法则：
录制宏，修改录制的宏为己所用
posted on 2010-09-08 18:15
Ying-er 阅读(293)
评论(0) 编辑 收藏 所属分类:
VBA