lastr=ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
'Select the last record
'Select the last row in the worksheet
'End + Up
Rows(Rows.Count).Select
Selection.End(xlUp).Select
'last row #
Rows(Rows.Count).End(xlUp).Row
'last column #
Columns(Columns.Count).End(xlToLeft).Column
2. 為 Cells 加入背景色
'Color a cell
With Selection.Interior
.ColorIndex = 6 'Yellow
.Pattern = xlSolid
End With
colorindex = 0 '透明顏色
3. 文字設定
' Bold font
Cells(1,1).Font.Bold=True
' font size
Cells(1,1).Font.Size=12
4. 清理整頁
Sheets("Sheets").Activate
Cells.Clear
5. 使用 Excel function
Application.VLookup(cells(2,1), Range("A1:B99"), 2, False)
Application.Index(....)
6. 開啟 Excel 檔, 檢查檔案是否存在
' Call function to test file lock.
If Not IsFileOpen(strFileName) Then
' If the function returns False, open the document.
Workbooks.Open strFileName
End If
Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long
On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0
Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select
End Function
7. 檢查 Worksheet 是否存在
If Not SheetExists(shtName) Then
.......
Else
Sheets(shtName).Activate
End If
Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
SheetExists = False
On Error GoTo NoSuchSheet
If Len(Sheets(SheetName).Name) > 0 Then
SheetExists = True
Exit Function
End If
NoSuchSheet:
End Function
8. 加入 new sheet
Sheets.Add.Name = shtName
Sheets(shtName).Move After:=Sheets(Sheets.Count)
9. 複製 Range
Range("A1").Copy Destination:=Range("B1")
startRow = Worksheets(1).UsedRange.Row 'ws 使用範圍的開始列
startCol = Worksheets(1).UsedRange.Column 'ws 使用範圍的開始欄
endRow = Worksheets(1).UsedRange.Rows.Count + startRow - 1 'ws 使用範圍的終點列
endCol = Worksheets(1).UsedRange.Columns.Count + startCol - 1 'ws 使用範圍的終點欄
10. 複製 format
Cells(1,1).Copy
Range("B1").PasteSpecial Paste:=xlPasteFormats
11. Find Method + Exact Match
Dim rng as range
Set rng = range(...).Find("abc",LookIn:=xlValue, LookAt:=xlWhole)
if rng Is Nothing then
msgbox("not found")
else
msgbox("found")
end if
12. InStr 字母最先出現位置 (findB 不能使用)
MyPos = Instr(startPosition, SearchString, SearchChar, 1) '0 = 二進位比對方式, case sensitive ; 1=文字對比方式, 大小楷沒有分別MyPos = Instr(1, SearchString, SearchChar, 0) 13. Data Conversion
CBool(expression)
CByte(expression)
CCur(expression)
CDate(expression)
CDbl(expression)
CDec(expression)
CInt(expression)
CLng(expression)
CSng(expression)
CStr(expression)
CVar(expression)
CStr(expression)
14. 日期格式
Format(#17/04/2004#, "Short Date") 'would return '17/04/2004'
Format(#17/04/2004#, "Long Date") 'would return 'April 17, 2004'
Format(#17/04/2004#, "yyyy/mm/dd") 'would return '2004/04/17'
Format(Date, "yyyy/mm/dd")
Format(Date, "d mmmm yyyy") 'would return 12 January 2008
Format(Date, "d mmm yyyy") 'would return 12 Jan 2008
15. Return Relative Address
Range("A1:A10").Address(0, 0)
16. 設定快速鍵
Application.OnKey "%b", "runIt" '按 Alt + b 執行 runIt procedure
% = Alt
^ = Ctrl
+ = Shift
解除快速鍵
Application.OnKey "%b", ""
Excel 寬度、闊度
Application.Width
Application.Height
VBA 資源網
沒有留言:
張貼留言