Excel VBA 五 String、日期、Function、Sub

※String

Dim str As String
str = "abcdefg"
Cells(1, "A").Value = Now
Cells(1, "B").Value = Left(str, 4) 'abcd
Cells(2, "B").Value = Right(str, 4) 'defg
Cells(3, "B").Value = Mid(str, 4, 2) 'de
Cells(4, "B").Value = Len(str) '7
Cells(5, "B").Value = InStr(str, "cd") '3

※VBA 的 index 是從 1 開始的

※日期

Dim d1 As Date
Dim d2 As Date

d1 = DateValue("Jan 21, 1999")
Cells(1, "A").Value = d1 '1999/1/21
Cells(2, "A").Value = Year(d1) '1999
Cells(3, "A").Value = Month(d1) '1
Cells(4, "A").Value = Day(d1) '21

d2 = TimeValue("17:25:37")  '等同 TimeValue("5:25:37 pm")
Cells(5, "A").Value = Hour(d2) '17
Cells(6, "A").Value = Minute(d2) '25
Cells(7, "A").Value = Second(d2) '37


．增加
Dim d As Date
d = DateValue("Jan 21, 1999")
d = DateAdd("m", -1, d)
MsgBox (d)

※第一個參數如下：
yyyy - 年
q - 季度
m - 月
y - 当年的第几天
d - 日
w - 当周的第几天
ww - 周
h - 小时
n - 分钟
s - 秒

 ※Function 和 Sub

Sub hello() '我是註解
  'MsgBox 3 * fun1()
  'MsgBox fun2(3, 2)
  'Call s1
  's1
  Call s2(3, 2)
  s2 3, 2
End Sub

Function fun1() As Integer
  fun1 = 2
End Function

Function fun2(a As Integer, b As Integer) As Integer
  fun2 = a * b
End Function

Sub s1()
  MsgBox fun2(3, 2)
End Sub

Sub s2(a As Integer, b As Integer)
  MsgBox fun2(a, b)
End Sub

※Function 有沒有回傳值都可以，Sub 不能有回傳值

※呼叫時，Sub 可用 Call，如果不想用 Call，後面一定不能有圓括號

Excel VBA 四 宣告變數、陣列、If、迴圈、Select Case

※宣告變數

Dim x As Integer
'x = "d"
x = 1 + 2
MsgBox (7 + x)
MsgBox ("x=" & x)

※雖然不用宣告也可以執行，但如果不宣告，註解那行是可以執行的

※類型
Byte (1)：無符號，0-255
Integer (2)
Long (4)
Double (8)
Boolean (2)
Decimal (14)
String

Date (8)
Currency (8)
Single (4)
Object (4)
Variant （根据分配确定）

數字預設是 Integer

※陣列

Dim a(6 To 10) As String
a(6) = "a"
a(8) = "b"
a(10) = "c"
MsgBox a(6) 'a
MsgBox a(7) '

※如果不在範圍內 (6-10)，如 a(5) 會報 Subscript out of range

．二維陣列

Dim b(1 To 3, 2 To 4) As String
b(1, 2) = "kkk"
MsgBox b(1, 2) 'kkk

※If

Dim i As Byte
i = 7
If i = 1 Then
  MsgBox (1)
ElseIf i = 2 Then
  MsgBox (2)
ElseIf i = 7 Then
  MsgBox (7)
Else
  MsgBox ("hahaha")
End If

※不等於用 <>


Dim i As Byte
i = 7
If i = 0 Then
  MsgBox (0)
ElseIf i >= 1 & i <= 10 Or i = 100 Then
  MsgBox ("1~10 or 100")
Else
  MsgBox ("hahaha")
End If

※& 和 Or 可以更進一步的判斷

※迴圈

※Do ~ Loop

Dim i As Integer
'i = 10
Do While i < 10
  i = i + 1
Loop
MsgBox (i)

※至少會跑一次

※For ~ Next

Dim i As Integer
Dim sum As Long

For i = 1 To 10 'Step 1
  sum = sum + i
Next i
MsgBox (sum)

※Step 預設就是 1 了


For i = 1 To 9
  For j = 1 To 9
    Cells(i, j).Value = i & "x" & j & "=" & i * j
  Next j
Next i

※可以嵌套

※For Each ~ Next

For i = 1 To 5 Step 1
    Cells(i, "a").Value = i * 9
Next i

For Each j In Range("A1: A5")
  If j = 36 Then
    GoTo xx
    'Exit For
  End If
  MsgBox (j)
  xx:
Next j

※Exit For 就是 break

※GoTo 到一個標籤，模擬 continue

※Select Case

n = -9
Dim str As String

Select Case n
  Case 1
    n = "a"
  Case 2, 3
    n = "b"
  Case 4 To 6
    n = "c"
  Case Is > 6
    n = "d"
  Case Else
    n = "other"
  End Select
MsgBox (n)

Excel VBA 三 選取、複製、清除內容、總數、常用方法

※選取

Set r = Range("C4: E5")
r.Value = 999
r.Select
'r.Rows(1).Select
'r.Columns(1).Select

分別為 Select、Rows(1).Select、Columns(1).Select
R 橫 C 直

※複製

Set r = Range("C4: E5")
r.Value = 888

'方法一
'r.Select
'Selection.Copy

'Range("A7").Select
'ActiveSheet.Paste

'方法二
Range("A7:C8").Value = r.Value

推薦用方法二比較簡潔有力

※清除內容

Range("A7:C8").ClearContents
'Range("A7:C8").Value = ""

※總數

Set r = Range("C4: E5")
r.Value = 888
Cells(1, "A").Value = r.Count '6
Cells(2, "A").Value = r.Rows.Count '2
Cells(3, "A").Value = r.Columns.Count '3

※常用方法

Cells(4, "A").Value = "abc"
Cells(4, "A").Interior.Color = vbYellow '儲存格背景顏色
Cells(4, "A").Font.Color = RGB(100, 200, 255) '字體顏色
Cells(4, "A").Font.Bold = True '粗體
Cells(4, "A").Font.Italic = True '斜體
Cells(4, "A").Font.Underline = True '底線
Cells(4, "A").Font.Size = 20 '字體大小
Cells(4, "A").ColumnWidth = 20 '儲存格寬度
Cells(4, "A").EntireColumn.AutoFit '自動調整適合的儲存格寬度
'Cells(4, "A").ClearContents '清除儲存格內容
'Cells(4, "A").ClearFormats '清除儲存格非內容的狀態 (背景色、字體色、字體大小…等)

Excel VBA 二 插入按鈕、Cell、Range

1.Insert 有很多的圖形可以控制，這裡以左上角的按鈕為例

2.選完第一張圖的按鈕後，在 Excel 工作區隨便畫一個長方形，如此圖的上半部分
滑鼠放開後，就會看到下半部分
然後名稱預設是 CommandButton1，想改名可按上圖的下半部分操作

3.第一和第二張圖都有個 View Code 可到這個畫面來，然後打程式碼
Private Sub CommandButton1_Click()
  Cells(2, "B").Value = "haha"
  MsgBox ("insert" & vbNewLine & "success")
End Sub
打完按三角按鈕即可執行

4.此時按鈕就可以按了，但發現按右鍵沒反應，此時可以發現 Properties 是灰色的
按下橘框的 Design Mode，會發現 Properties 又可以用了且按右鍵也可以了

※Cell、Range

Cells(1, "A").Value = 123  '第二個參數用數字也可以；1就是A，2就是B，依此類推
Cells(2, "B").Value = "haha"

Range("C3").Value = "I'm C3"
Range("C4: E5").Value = "c4-e5"
Range("C4: E5, A7: B13, G1").Value = "yeah"

以上是針對當前工作表，如果想控制其他的工作表可用如下三個方法：

Worksheets("Sheet1").Range("A2").Value = "A7"
Worksheets(1).Range("A3").Value = "A3" 'Worksheets.Count
Sheet1.Range("A4").Value = "A4"

如上圖右邊，Sheet1 和 Sheet2 都是 Excel 幫我們定好的，後面的括號是別名，會對應到上圖左邊下面的名稱
注意 Sheet2 的別名是 Sheet1

第一種是用別名的方式

第二種是以上圖左邊下面去算的，最左邊的 xxx 是1，Sheet1 是 2，但滑鼠按著其中一個去換，是可以換的，這時順序會改變，所以這種方法不推薦

第三種是用 Excel 幫我們定好的名稱

使用 Worksheets.Count 可以知道有幾張工作表

※Range 的另外一種用法

Range("xxx")，裡面的 xxx 可以自己定義，方法如下：

1.這次使用的不是 Developer 了，是 Formulas

2.Scope 是工作表的別名，橘框按了以後視窗會變小，這時在 Excel 拉需要的範圍即可，如果要多個範圍，就按 Ctrl

3.在左上角打上自己取得名稱，就會反白範圍

4.這個畫面可以編輯和刪除，但 Scope 不能改

Excel VBA 一 環境

1.在 File/Options (檔案/選項) 裡會出現如上的畫面

2.勾了第一張圖的 Developer 後，就有活頁標籤可以使用，選 Visual Basic 寫程式

3.大小寫要正確才行，打完後按橘框的按鈕可測試

Sub hello() '我是註解
  MsgBox ("hello, VBA")
End Sub

註解的按鈕和「"」是同一個

如果整行都要註解，可用 Rem xxx

MsgBox 的圖括號可省略

4.選左邊的程式，然後按 Run 即可
Macro Name 是搜尋，有可能有很多巨集
一個Sub ~ End Sub 就是一個巨集，可以在同一個地方寫很多巨集

5.存檔要存成 xlsm 才行，下次再進來選 Macros (巨集)，會出現上一張圖的畫面，選 Edit (編輯) 就會出現打程式的地方了

官方文檔連結
可參考的 API