VBA: 常用功能

## Application 級的功能
  ' 執行時停止螢幕異動
  Application.ScreenUpdating = False

## 目前工作資料取得
  ' 取得目前活頁簿的工作目錄及名稱
  ActiveWorkbook.Path
  ActiveWorkbook.Name
  ' 取得目前執行VBS的活頁簿其工作目錄及名稱
  ThisWorkbook.Path
  ThisWorkbook.Name

  ' 取得活頁簿的工作目錄(檔案路徑)
  strNowPath = Excel.ActiveWorkbook.Path
 
  ' 取得目前工作頁的名稱
  szName = ThisWorkbook.ActiveSheet.Name
  ' 取得目前資料格的 row 值
  endRow = ActiveCell.Row
  ' 取得 VBA 程式所在工作表內特定工作表的資料格內容
  A = ThisWorkbook.Worksheets(strNameSheet).Cells(1, 1)

## 指定目前的有效視窗
  Windows("D:\test\myfile.xls").Activate

## 新增指定名稱的工作表
  Sheets.Add().Name = "TestOK"
  Worksheets.Add(After:=Worksheets("ReadMe")).Name = "MySheet"

## 取得目前所有工作表的名稱
方式一
  For Each thisSheet In Sheets
    thisSheet.Name  的內容即為工作表名稱
  Next thisSheet
方式二
  For i = 1 To Worksheets.Count
    Worksheets(i).Name 的內容即為工作表名稱
  Next

## 刪除作用中的工作表
  '關閉警告視窗
  Application.DisplayAlerts = False
  '刪除作用中的工作表
  ActiveSheet.Delete
  '恢復警告視窗
  Application.DisplayAlerts = True
 

## 與工作表相關的指令
  ' 設定工作表是否可見
  Worksheets(toSheet).Visible = True
  ' 設定為作用中的工作表
  Worksheets(toSheet).Activate
  ' 選取工作表
  Worksheets(toSheet).Select
  ' 刪除工作表資料內容
  Worksheets(toSheet).Cells.Delete
  ' 選取目前活動工作表所有資料格並複製到剪貼簿
  ActiveSheet.Cells.Select
  Selection.Copy
  ' 將前項剪貼簿內容貼到目前活動工作表資料格內
  ' 請注意複製與貼上所選取的範圍宜相同
  ActiveSheet.Cells.Select
  ActiveSheet.Paste
  ' 設定欄寬
  Columns("A:A").Select
  Selection.ColumnWidth = 20
  或
  Columns("A:A").ColumnWidth = 20
  ' 隱藏工作表
  Worksheets("mySheet").Visible = False

## 讀取或設定資料格的方式
  m_year1 = Worksheets("Setting").Cells(5, 2)
  Worksheets("Setting").Cells(5, 2) = 1234
  ' 設定資料格內容為公式
  Sheets("總表_每日").Cells(myRow, 2).Formula = "=DSUM(DB_Open!$A:$E,5,$" & col(ii - 1) & "$2:$" & col(ii - 1) & "$3)"


## 找到有資料列的最後一列
  Range("A65530").Select
  '跳到該欄最後一列有資料的地方
  Selection.End(xlUp).Select
  'endRow 為最後一列有資料的列數
  endRow = ActiveCell.Row

VBA: 檔案處理

## 檔案處理
## 開啟檔案  
  Workbooks.Open Filename:= _
    "D:\20140325\test.xls"

## 關閉檔案  
Windows(szOriginalFileName).Activate
ActiveWindow.Close False

## 使用循序模式寫入檔案()
Open "MyFile.txt" For Output As #1
For inputData = 1 To 4
Print #1, inputData
Next inputData
Close #1


## 使用循序模式讀取檔案
EX1.
Open "MyFile.txt" For Input As #1
For retu = 1 To 4
Input #1, x
Cells(1, retu) = x
Next retu
Close #1

EX2.
' Open the file for Input.
Open "TEXTFILE.TXT" For Input As #1
' Read each line of the text file into a single string
' variable.
Do While Not EOF(1)
   Line Input #1, LineofText
   MsgBox LineofText
Loop
' Close the file.
Close #1



## 讀取特定目錄內的檔名
Private Sub readFileName()
    Dim szWorkPath As String   ' 目前工作檔案目錄
    Dim szFileName As String   ' 讀到的檔案名稱
    Dim szDirString As String  ' DIR 搜尋檔案的字串
    szWorkPath = ThisWorkbook.Path
    szDirString = szWorkPath & "\data\*.pdf"
    n = 0
    'Sheet3.Cells.Delete  '將之前的結果清除
    szFileName = Dir(szDirString, vbDirectory)
    szData = ""
    While szFileName <> ""
      n = n + 1
        szData = szData + "##" + szFileName
      szFileName = Dir() '讀取下一個檔案
    Wend
    MsgBox (n & szData)
End Sub

## 執行功能前的確認視窗
Dim szYesNo
szYesNo = MsgBox("確定執行發送功能 ?", vbYesNo, "發送視窗")
If szYesNo = vbYes Then
  MsgBox "Hello"
Else
  MsgBox "Naff Off"
End If

VBA: 程式(控制流程與回圈)

VBA 控制流程與回圈
分享: 7Headlines facebook PLURK twitter
1. If-Then

If 條件 Then
    敘述
End If

2. If-Then-Else

If 條件 Then
    敘述
Else
    敘述
End If

3. If-Then-ElseIf-Else

If 條件 Then
    敘述
ElseIf 條件
    敘述
ElseIf 條件
    敘述
Else
    敘述
End If

4. Select Case

Select Case 變數
Case 變數值 [to 變數值]
    程式敘述一
    …….
Case 變數值 [to 變數值]
    程式敘述二
    …….
[Case 變數值 [to 變數值]
    程式敘述n-1
    …….]
[Case Else
    程式敘述n
    …….]
End Select

5. Do While ...Loop

Do While 條件
    敘述
Loop

6. Do Until… Loop

Do Until 條件
     敘述
Loop

7. Do … Loop While

Do
      敘述
Loop While 條件

8. Do … Loop Until

Do
      敘述
Loop Until 條件

9. Do … Loop

Do
    敘述
Loop

10. For … Next

For 數值變數 = 初始值 To 終止值 [Step 增量]
    敘述
Next [數值變數]

11. For Each … Next

For Each 元素 In 群組
    敘述
Next [元素]

12. While … Wend

While 條件
    敘述
Wend

13. 跳離指令

*Exit Do：強制離開Do Loop迴圈。
*Exit For：強制離開For Next迴圈。
*Exit Sub：強制跳離Sub程序。
*Exit Function：強制跳離Function程序。
*Goto：需在程式碼前加上行號。
*End：無條件結束應用程式。

VBA: function

VBA Function What It Does
Abs Returns the absolute value of a number
Array Returns a variant that contains an array
Asc Converts the first character of string to its ASCII value
Atn Returns the arctangent of a number
CBool Converts an expression to Boolean data type
CByte Converts an expression to byte data type
CCur Converts an expression to currency data type
CDate Converts an expression to date data type
CDbl Converts an expression to double data type
CDec Converts an expression to decimal data type
Choose Selects and returns a value from a list of arguments
Chr Converts an ANSI value to a character
CInt Converts an expression to integer data type
CLng Converts an expression to long data type
Cos Returns the cosine of a number
CreateObject Creates an OLE Automation object
CSng Converts an expression to single data type
CStr Converts an expression to string data type
CurDir Returns the current path
CVar Converts an expression to variant data type
CVDate Converts an expression to date data type
CVErr Returns a user-defined error type
Date Returns the current system date
DateAdd Returns a date with a specific date interval added to it
DateDiff Returns the difference between two dates as a time interval
DatePart Returns an integer containing a specific part of a date
DateSerial Returns a date for a specified year, month, and day
DateValue Converts a string to date
Day Returns the day of the month of a date
Dir Returns the name of a file or directory that matches a pattern
DoEvents Yields execution so the operating system can process other events
EOF Returns True if the end of a text file has been reached
Error Returns the error message that corresponds to an error number
Exp Returns the base of the natural logarithms (e) raised to a power
FileAttr Returns the file mode for a text file
FileDateTime Returns the date and time when a file was last modified
FileLen Returns the number of bytes in a file
Fix Returns the integer portion of a number
Format Returns an expression in a particular format
FormatCurrency Returns a number as a string, formatted as currency
FormatDateTime Returns a number as a string, formatted as a date and/or time
FormatNumber Returns a number as a formatted string
FormatPercent Returns a number as a string, formatted as a percentage
FreeFile Returns the next file number available for use by the Open statement
GetAll Returns a list of key settings and their values (originally created with SaveSetting) from an application’s entry in the Windows registry
GetAttr Returns a code representing a file attribute
GetObject Retrieves an OLE Automation object from a file
GetSetting Returns a key setting value from an application’s entry in the Windows registry
Hex Converts from decimal to hexadecimal
Hour Returns the hour of a time
IIf Returns one of two parts, depending on the evaluation of an expression
Input Returns a specific number of characters from an open text file
InputBox Displays a box to prompt a user for input, and returns the value entered
InStr Returns the position of a string within another string
InStrRev Returns the position of a string within another string, beginning at the back end of the string
Int Returns the integer portion of a number
IsArray Returns True if a variable is an array
IsDate Returns True if a variable is a date
IsEmpty Returns True if a variable has not been initialized
IsError Returns True if an expression is an error value
IsMissing Returns True if an optional argument was not passed to a Procedure
IsNull Returns True if an expression contains no valid data
IsNumeric Returns True if an expression can be evaluated as a number
IsObject Returns True if an expression references an OLE Automation object
Join Returns a string created by joining a number of substrings contained in an array
LBound Returns the lower bound of an array
LCase Returns a string converted to lowercase
Left Returns a specified number of characters from the left of a string
Len Returns the length of a string, in characters
Loc Returns the current read or write position of a text file
LOF Returns the number of bytes in an open text file
Log Returns the natural logarithm of a number
LTrim Returns a copy of a string with no leading spaces
Mid Returns a specified number of characters from a string
MidB Returns a specified number of bytes from a specified position in a string string
Minute Returns the minute of a time
Month Returns the month of a date
MonthName Returns a string indicating the specified month
MsgBox Displays a modal message box and returns the ID of the button clicked
Now Returns the current system date and time
Oct Converts from decimal to octal
Replace Returns a string in which one substring is replaced with another
RGB Returns a number representing an RGB color value
Right Returns a specified number of characters from the right of a string
Rnd Returns a random number between 0 and 1
Round Rounds a number to a specific number of decimal places
RTrim Returns a copy of a string with no trailing spaces
Second Returns the second of a time
Seek Returns the current position in a text file
Sgn Returns an integer that indicates the sign of a number
Shell Runs an executable program
Sin Returns the sine of a number
Space Returns a string with a specified number of spaces
Split Returns an array consisting of a number of substrings
Sqr Returns the square root of a number
Str Returns a string representation of a number
StrComp Returns a value indicating the result of a string comparison
StrConv Returns a string variant converted as specified
String Returns a repeating character or string
StrReverse Returns the characters of a string in reverse order
Switch Evaluates a list of expressions and returns a value associated with the first expression in the list that is True
Tab Positions output in an output stream
Tan Returns the tangent of a number
Time Returns the current system time
Timer Returns the number of seconds since midnight
TimeSerial Returns the time for a specified hour, minute, and second
TimeValue Converts a string to a time serial number
Trim Returns a string without leading and spaces and replaces multiple spaces with a single space
TypeName Returns a string that describes the data type of a variable
UBound Returns the upper bound of an array
UCase Converts a string to uppercase
Val Returns the numbers contained in a string
VarType Returns a value indicating the subtype of a variable
Weekday Returns a number representing a day of the week
Weekday Name Returns a string indicating the specified weekday
Year Returns the year of a date