Excel 宏基础知识-4

50 Excel VBA - Types of Errors

在写VBA的时候，主要有三种错误：

1. 语法错误(syntax errors)
   syntax errors will work for only one statement one single line at a time whereas compilation errors it will work for more than one lines
2. 编译错误 (compilation errors)
3. 运行时错误(runtime errors)
   只有在运行时才会报的错

51 Excel VBA - Types of Errors

'one method
Sub using_resume_next()

On Error Resume Next
    MsgBox 10
    MsgBox 10 / 0
    MsgBox 20

End Sub

'second method
Sub using_resume_next()

On Error Resume Next
    MsgBox 10
    MsgBox 10 / 0
    MsgBox 20

End Sub

52 Excel VBA - Debugging

52-1.png

53 Excel VBA - Immediate Window

52-1.png
52-2.png

54 Excel VBA - Number to Text Function

55 Excel VBA - If Statement

Sub IF_test1()
If Range("a2") >= 35 Then Range("c2") = "yes"
End Sub

56 Excel VBA - If Else Statement

Sub IF_test2()

If Range("a2") >= 35 Then
    Range("c2") = "yes"
Else
    Range("c2") = "no"
End If
End Sub

57 Excel VBA - If Elseif Else Statement

Sub IF_test3()
If Range("a2").Value <= 35 Then
    Range("c2").Value = "Fail"
ElseIf Range("a2").Value <= 60 Then
    Range("c2").Value = "C Grade"
ElseIf Range("a2").Value <= 80 Then
    Range("c2").Value = "B Grade"
Else
    Range("c2").Value = "A Grade"
End If
End Sub

58 Excel VBA - If Elseif Else with AND Operator

Sub IF_test4()
If Range("a2").Value > 0 And Range("a2").Value <= 35 Then
    Range("c2").Value = "Fail"
ElseIf Range("a2").Value >= 35 And Range("a2").Value <= 60 Then
    Range("c2").Value = "C Grade"
ElseIf Range("a2").Value >= 61 And Range("a2").Value <= 80 Then
    Range("c2").Value = "B Grade"
ElseIf Range("a2").Value >= 81 And Range("a2").Value <= 100 Then
    Range("c2").Value = "A Grade"
Else
    Range("c2").Value = "Invalid"
End If
End Sub

59 Excel VBA - If Else Using For Loop

Sub IF_else_using_For()
Dim x As Integer

For x = 2 To 20
    If Cells(x, 2).Value >= 35 Then
        Cells(x, 3).Value = "Pass"
    Else
        Cells(x, 3).Value = "Fail"
    End If
Next x
End Sub

60 Excel VBA - Select Case Statement

Sub select_Case_Statement()
var1 = InputBox("Enter Month Number")

Select Case var1
    Case 1: MsgBox "Month is Jan"
    Case 2: MsgBox "Month is Feb"
    Case 3: MsgBox "Month is Mar"
    Case 4: MsgBox "Month is Apr"
    Case 5: MsgBox "Month is May"
    Case 6: MsgBox "Month is June"
    Case 7: MsgBox "Month is July"
    Case 8: MsgBox "Month is Aug"
    Case 9: MsgBox "Month is Sept"
    Case 10: MsgBox "Month is Oct"
    Case 11: MsgBox "Month is Nov"
    Case 12: MsgBox "Month is Dec"
    
    Case Else: MsgBox "Invalid Month"

End Select
End Sub

61 Excel VBA - Message Box 1

Sub messagebox1()
MsgBox "welcome to Tutorials Point China"
MsgBox 10000
MsgBox #10/12/2020#
End Sub

62 Excel VBA - Message Box 2

Sub messagebox2()
MsgBox "welcome", 1
MsgBox "welcome", 2
MsgBox "welcome", 3
MsgBox "welcome", 4
MsgBox "welcome", 5
MsgBox "welcome", 6

MsgBox "Welcome", vbAbortRetryIgnore
MsgBox "Welcome", vbOKCancel
End Sub

1.png
2.png
3.png
4.png
5.png
6.png

63 Excel VBA - Message Box 3

Sub messagebox3()
MsgBox "welcome", 16
MsgBox "welcome", 32
MsgBox "welcome", 48
MsgBox "welcome", 64

MsgBox "welcome", vbCritical
MsgBox "welcome", vbInformation
MsgBox "welcome", vbExclamation
MsgBox "welcome", vbQuestion
End Sub

16.png
32.png
48.png
64.png

64 Excel VBA - Message Box 4

Sub messagebox4()
a = MsgBox("welcome", 1)
MsgBox a
If a = 1 Then
MsgBox "OK Button was pressed"
Else
MsgBox "Cancel button was pressed"
End If
End Sub

65 Excel VBA - Message Box 5

Sub messagebox5()
MsgBox "Welcome", vbOKCancel, "Title"
End Sub

66 Excel VBA - Input Box

'1000, 2000分别是input box在屏幕上的位置，x, y
Sub inputbox1()
a = InputBox("Enter the data", "Title", "default value", 1000, 2000)
MsgBox a
End Sub

67 Excel VBA - Rows & Columns Count

image.png

Sub Row_Column_Count()
Dim x As Long
Dim y As Long
'1048576
x = Rows.Count
MsgBox x
'16384
y = Columns.Count
MsgBox y
End Sub

68 Excel VBA - String Functions Left-Right

Sub string_functions()
Dim x As Integer
For x = 2 To 8
    Cells(x, 2).Value = Left(Cells(x, 1).Value, 2)
Next x
End Sub

69 Excel VBA - String Functions UCase-Lcase

Sub str_function_ucase_lcase()
Dim x As Integer
For x = 2 To 8
    Cells(x, 2).Value = UCase(Cells(x, 1).Value)
    Cells(x, 3).Value = LCase(Cells(x, 1).Value)
End Sub

70 Excel VBA - String Reverse

Sub str_function_String_Reverse()
Dim x As Integer
For x = 2 To 8
    Cells(x, 2).Value = StrReverse(Cells(x, 1).Value)
Next x
End Sub

71 Excel VBA - Create Function

Function add2Numbers(x As Integer, y As interger) As Integer
add2Numbers = x + y
End Function

72 Excel VBA - Date Add

给年月日或者时分秒加1

Sub Date3_dateadd()
mydate = #11/20/2016#
MsgBox mydate
'adding days, months and year
MsgBox DateAdd("yyyy", 1, mydate)
MsgBox DateAdd("m", 1, mydate)
MsgBox DateAdd("d", 1, mydate)
'adding hours minutes and seconds
MsgBox DateAdd("h", 1, "31 dec 2020 12:00:00")
MsgBox DateAdd("n", 1, "31 dec 2020 12:00:00")
MsgBox DateAdd("s", 1, "31 dec 2020 12:00:00")
End Sub

73 Excel VBA - Date Part

Sub Date4_Datepart()
Dim mydate As Variant
mydate = #12/20/2016#
MsgBox mydate

MsgBox DatePart("yyyy", mydate) 'display year
MsgBox DatePart("d", mydate) 'display day
MsgBox DatePart("m", mydate) 'display month
MsgBox DatePart("q", mydate) 'display quater 季度
End Sub

74 Excel VBA - DD/MM/YYYY

Sub Date5_day_month_year()
Dim mydate As Variant
mydate = #12/20/2016#
MsgBox mydate

MsgBox Day(mydate)
MsgBox Month(mydate)
MsgBox Year(mydate)
End Sub

75 Excel VBA - Dates

Sub Date1_date()
Dim mydate As Variant
mydate = Date '今天
MsgBox mydate
End Sub

Sub Date2_date()
Dim mydate As Variant
mydate = CDate("20 Dec 2020")
MsgBox mydate
End Sub

76 Excel VBA - Time Function Part 1

Sub Time1_now_time()
MsgBox Now() 'display date and time
MsgBox Time() 'display oly time
MsgBox Hour(Time) 'display hout
MsgBox Minute(Time) 'display minute
MsgBox Second(Time) 'display second

MsgBox Hour("3:10:13") 'display 3
MsgBox Minute("3:10:13") 'display 10
MsgBox Second("3:10:13") 'display 13
End Sub

77 Excel VBA - Time Function Part 2

Sub Time2_timeserial_timevalue()
MsgBox TimeSerial(3, 4, 5) '03:04:05
MsgBox TimeSerial(12, 59, 59)

MsgBox TimeValue("20:19")
MsgBox TimeValue("3:10:10")
MsgBox TimeValue("2:10:8")
End Sub