Excel VBA-For/Do While迴圈

Posted on August 17, 2021 by Tingyu Cho

計算判斷式可以使用的迴圈有兩種，若有確定的範圍可以使用for迴圈，若資料量較大或隨時會更新則可使用while迴圈。

For迴圈/If 判斷式

r = 0 '預設值

For r = 2 To 8 '計算範圍

    w = Cells(r, 4) '體重
    l = Cells(r, 3) '身高
    bmi = Math.Round(w / (l * 0.01) ^ 2) '計算BMI
    Cells(r, 5) = bmi '填入
    
    '判斷BMI

    If bmi >= 35 Then
        Cells(r, 7) = "重度肥胖"
    ElseIf bmi >= 30 Then
        Cells(r, 7) = "中度肥胖"
    ElseIf bmi >= 27 Then
        Cells(r, 7) = "輕度肥胖"
    ElseIf bmi >= 24 Then
        Cells(r, 7) = "過重"
    ElseIf bmi >= 18.5 Then
        Cells(r, 7) = "正常"
    Else
        Cells(r, 7) = "過瘦"
    End If
Next r 'For迴圈結尾

Do While迴圈/ Select判斷式

On Error Resume Next '

r = 2

Do While Not IsEmpty(Cells(r, 1))

    w = Cells(r, 4) '體重
    l = Cells(r, 3) '身高
    bmi = Math.Round(w / (l * 0.01) ^ 2) '計算BMI
    Cells(r, 6) = bmi '填入

    Select Case bmi
        Case Is < 18.5
            Cells(r, 8) = "過瘦"
        Case Is < 24
            Cells(r, 8) = "正常"
        Case Is < 27
            Cells(r, 8) = "過重"
        Case Is < 30
            Cells(r, 8) = "輕度肥胖"
        Case Is < 35
            Cells(r, 8) = "中度肥胖"
        Case Else
            Cells(r, 8) = "重度肥胖"
    End Select

    r = r + 1
Loop

Excel VBA-打卡系統

Posted on August 9, 2021 by Tingyu Cho

用VBA寫了一個簡單的打卡系統，可以判斷上班遲到早退以及加班時數

Private Sub CommandButton1_Click()
'r=>要準備寫資料的那一行 (ROW)
Dim r As Long
Dim d As Date
Dim t As Dated = Range("C3")
t = Range("C4")


'如果現在沒有資料，那麼r就從第二行開始寫 (r=2)
If Cells(10, 2) = "" Then
    r = 10
Else
    '如果有資料，找到最後一筆資料，並取得他的ROW
    Range("B9").Select
    Selection.End(xlDown).Select
    r = Selection.Row + 1

End If


'資料是從 r 開始寫入
Cells(r, 2) = Range("C2")
Cells(r, 4) = Range("C3")
Range("D" & r).Select
Selection.NumberFormatLocal = "yyyy/m/d"
Cells(r, 5) = Range("C4")
Range("E" & r).Select
Selection.NumberFormatLocal = "[$-x-systime]h:mm:ss AM/PM"

'顯示目前多少資料
Range("C7") = r - 10 + 1


'判斷上下班

If (DateDiff("h", t, "12:00:00")) > 0 Then
    Cells(r, 3) = "上班"
Else
    Cells(r, 3) = "下班"
End If

If (DateDiff("h", t, "9:30:00")) < 0 And (DateDiff("h", t, "9:00:00")) > 0 Then Cells(r, 6) = "遲到"
If (DateDiff("h", t, "18:00:00")) > 0 And (DateDiff("h", t, "12:00:00")) < 0 Then Cells(r, 6) = "早退"
If (DateDiff("h", t, "18:00:00")) < -1 And (DateDiff("h", t, "9:00:00")) < 0 Then
    out = -DateDiff("h", t, "19:00:00")
    Cells(r, 6) = "加班" & out & "小時"
End If


If r <> 10 And Cells(r - 1, 3) = "上班" And Cells(r, 3) = "上班" And DateDiff("d", d, Cells(r - 1, 4)) < 0 Then
    If Cells(r, 6) <> "" Then
        Text = Cells(r, 6)
        Cells(r, 6) = Text & " , 未登記下班"
    Else
        Cells(r, 6) = "未登記下班"
    End If

End If

If r <> 10 And Cells(r - 1, 3) = "上班" And Cells(r, 3) = "上班" And DateDiff("d", d, Cells(r - 1, 4)) = 0 Then
    Cells(r, 2) = ""
    Cells(r, 3) = ""
    Cells(r, 4) = ""
    Cells(r, 5) = ""
    Cells(r, 6) = ""
    MsgBox ("重複打卡")

End If

If r <> 10 And Cells(r - 1, 3) = "下班" And Cells(r, 3) = "下班" And DateDiff("d", d, Cells(r - 1, 4)) < 0 Then

    If Cells(r, 6) <> "" Then
        Text = Cells(r, 6)
        Cells(r, 6) = Text & " , 未登記上班"
    Else

        Cells(r - 1, 6) = "未登記上班"
    End If

End If

If r <> 10 And Cells(r - 1, 3) = "下班" And Cells(r, 3) = "下班" And DateDiff("d", d, Cells(r - 1, 4)) = 0 Then
    Cells(r, 2) = ""
    Cells(r, 3) = ""
    Cells(r, 4) = ""
    Cells(r, 5) = ""
    Cells(r, 6) = ""
    MsgBox ("重複打卡")

End If
End Sub

Excel VBA-抽獎/抽籤

Posted on August 8, 2021 by Tingyu Cho

用Excel VBA Rnd亂數以及Select Case語法，可以寫一個簡單的抽籤跟抽獎程式

Public Sub button1()
Dim r As Long

r = Int(Rnd * 10) + 1 ' 1~10

Select Case r

    Case 1: 'r=1
        Range("C4") = "大凶"
    Case Is <= 3
        Range("C4") = "凶"
    Case Is <= 7
        Range("C4") = "平"
    Case Is <= 9
        Range("C4") = "吉"
    Case Else
        Range("C4") = "大吉"

End Select
End Sub

Public Sub button2()
Dim r As Long

r = Int(Rnd * 101)

Select Case r
    Case Is <= 1 ' r=0, r=1 ===> case 0 to 1  ===> case 0,1  => case 0: case 1:
        Range("C4") = "GOGORO"
    Case Is <= 10
        Range("C4") = "機票"
    Case Is <= 30
        Range("C4") = "$6000"
    Case Is <= 60
        Range("C4") = "$3000"
    Case Else
        Range("C4") = "$1000"
End Select
End Sub

Excel VBA-亂數與判斷式

Posted on July 21, 2021 by Tingyu Cho

運用VBA撰寫簡單的判斷式與亂數

點選插入 > 自訂表單，並用工具列建立標籤與按鈕

2. 雙擊按鈕兩下，開始編寫程式

公式為： If 判斷式 Then 符合判斷式結果 Else 不符合判斷式結果 End If

Rnd為亂數的語法，數值介於0到1的浮點小數，故必須使用Int語法將Rnd*10換算為整數

Label1.Caption為物件Lebel中Caption屬性，Caption為欄位顯示內容

Excel VBA-基本運算公式

Posted on July 20, 2021 by Tingyu Cho

使用VBA寫出基礎的運算程式

打開巨集 > 插入 > 自訂表單

2. 使用工具列建立文字方格與按鈕

3. 點選按鈕，於測欄屬性的部分可以看到按鈕資訊，這裡可以更換按鈕顏色及字型

Caption為按鈕顯示文字

4. 雙擊欲設定公式按鈕，會出現框格如下，這時就可以開始編寫程式了

5. 首先先定義按鈕的意義，按下下拉選單會出現各個工具的名稱

6. 下公式

有關於VBA資料類型型態可以參考：微軟資料類型摘要

運算符號代表可以參考：微軟運算子摘要

其他運用：BMI計算

Sub CommandButton1_Click()
Dim height as long
Dim weight as long
Dim BMI as long

height = TextBox1.text
weight = TextBox1.text

BMI= weitht / ((height * 0.01) ^ 2)
MsgBox (“BMI= ” &BMI)
End Sub