'/////////////////////////////////////////////////////////////
' -ワークシート內のデータの初期化を行う-
'/////////////////////////////////////////////////////////////
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 閱讀(301)
評論(0) 編輯 收藏 所屬分類:
VBA