Excel 宏基础知识-3

31 Excel VBA - Sheet Protection

image.png

image.png

Sub Protect_Unprotect_sheet()
Sheets("Details").Protect Password:=123
Sheets("Details").Unprotect Password:=123
End Sub

32 Excel VBA - Activate Sheet

Sub Activate_Sheet()
Sheets("Properties").Activate
Sheets("Details").Select
Sheets("Properties").Activate
End Sub

33 Excel VBA - Create Workbook

Sub Workbook_Create()
'Workbooks.Add
Workbooks.Add.SaveAs Filename:="C:\Demobook.xlsx"
End Sub

34 Excel VBA - Get Workbook Name

Sub Get_Workbook_Name()
MsgBox (ActiveWorkbook.Name)
MsgBox (ThisWorkbook.Name)

Workbooks("Book1.xlsx").Activate
MsgBox (ActiveWorkbook.Name)
MsgBox (ThisWorkbook.Name)
End Sub

35 Excel VBA - Save & Close Workbook

Sub Workbook_Save_Close()
Workbooks("Book1.xlsx").Sheets(1).Range("a1:a10") = "Excel"
Workbooks("Book1.xlsx").Save
Workbooks("Book1.xlsx").Close
End Sub

36 Excel VBA - Open and Close Workbook

Sub Workbook_Open_Close()
Workbooks.Open Filename = "C:\Demobook.xlsx"
Workbooks("Demobook.xlsx").Sheets(1).Range("a1:a20") = "Excel"
Workbooks("Demobook.xlsx").Save
Workbooks("Demobook.xlsx").Close
End Sub

37 Excel VBA - Delete Workbook

Sub Delete_Workbook()
Kill ("C:\Demobook.xlsx")
End Sub

38 Excel VBA - Create Folder

Sub create_folder()
MkDir ("C:\Folder1")
MkDir ("C:\Folde2")
MkDir ("C:\Folder3")
End Sub

39 Excel VBA - Variable Usage

Sub Variables_Usage()
Range("a1").Value = "Tutorials"
Range("a4").Value = "Tutorials"
Range("a9").Value = "Tutorials"
Range("a8").Value = "Tutorials"

var1 = "Tutorials"

Range("c1").Value = var1
Range("c5").Value = var1 & var1
Range("c7").Value = var1
Range("c8").Value = var1
End Sub

40 Excel VBA - Comment

将右侧的"comment block"和"Uncomment block"拖到左边Edit框中，就可以直接用这两个指令来comment.

image.png

image.png

'code with variable
Rem code with variable

41 Excel VBA For Loop Example 1

Sub For_Loop1()
Dim x As Integer
For x = 1 To 10
    MsgBox 25
Next
End Sub

image.png

'步进为2，x=1直接跳到x=3
Sub For_Loop1()
Dim x As Integer
For x = 1 To 10 Step 2
    MsgBox x
Next
End Sub

42 Excel VBA - For Loop Example 2

Sub For_Loop2()
Dim x As Integer
For x = 1 To 10 Step 2
   Cells(x, 1) = 10
Next
End Sub

43 Excel VBA - For Loop Example 3

image.png

Sub For_Loop3()
Dim x As Integer
For x = 1 To 56
   Cells(x, 1) = x
   Cells(x, 1).Interior.ColorIndex = x
Next
End Sub

44 Excel VBA - For Loop Example 4

Sub For_Loop4()
Dim x As Integer
For x = 20 To 1 Step -1
   Cells(x, 1) = x
Next
End Sub

image.png

45 Excel VBA - For Loop Example 5

Sub For_Loop5()
Dim x As Integer
For x = 1 To 10
   Cells(x, x) = x
Next
End Sub

46 Excel VBA - For Loop Sheet Name

Sub For_Loop6()
Dim x As Integer
For x = 1 To ThisWorkbook.Sheets.Count
    MsgBox ThisWorkbook.Sheets(x).Name
Next
End Sub

47 Excel VBA - For Each Next Loop

Sub For_Each_Next_1()
Dim sht As Worksheet

For Each sht In ThisWorkbook.Sheets
    MsgBox sht.Name
Next
End Sub

48 Excel VBA - Do While

Sub loop_do_while()
Dim i As Integer
i = 1
Do While Cells(i, 1).Value <> ""
    Cells(i, 2).Value = Cells(i, 1).Value + 10
    i = i + 1
Loop
End Sub

49 Excel VBA - Do Until -->感觉这个和do while也没啥区别

Sub loop_do_until()
Dim i As Integer
i = 1
Do Until i > 4
    Cells(i, 1).Value = 20
    i = i + 1
Loop
End Sub