Excel vba 操作

excel2003

vba操作应用快捷键

Alt+F11

Alt+F8

 

1hello_world

code：

Sub Hello_World()
    MsgBox ("hello vba")
End Sub

2光标定位

code：

Sub Select_Cells()
    'ThisWorkbook.Sheets("Sheet1").Range("A1").Select '定位单个光标
    'ThisWorkbook.Sheets("Sheet1").Range("A1", "D3").Select '定位光标范围
    'ThisWorkbook.Sheets("Sheet1").Range("C:C").Select '定位光标列
    ThisWorkbook.Sheets("Sheet1").Range("3:10").Select '定位光标列
End Sub

3固定位置的取值和赋值

code：

Sub Select_Cells()
    'ThisWorkbook.Sheets("Sheet1").Cells(1, 1).Value = 22 '添加字
    'ThisWorkbook.Sheets("Sheet1").Cells(1, 1).Value = "" '清空
    
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1).Value = ThisWorkbook.Sheets("Sheet1").Cells(2, 1).Value
    
End Sub

4固定位置的取值计算

code：

Sub Add_Cells()

    With ThisWorkbook.Sheets("Sheet1")
    
        Dim Cell1, Cell2, Cell3 As Integer
        Cell1 = .Cells(8, 1).Value
        Cell2 = .Cells(8, 2).Value
        Cell3 = Cell1 + Cell2
        
        .Cells(8, 3).Value = Cell3
    
    End With
    
End Sub

5固定位置的设置字符串

code：

Sub Add_Cells()

    With ThisWorkbook.Sheets("Sheet1")
    
    Dim Text, Text2 As String
    Text = "Hello ZXL"
    Text2 = "my name is matt"
    
    .Cells(1, 1).Value = Text
    
    End With
    
End Sub

6判断语句（if）+inputbox输入框
code：

Sub Password_Protected()
    
    Dim Password, UserPassword As String
    Password = "Password"
    UserPassword = InputBox("please enther yout password")
    If Password = UserPassword Then
    MsgBox ("access granted")
    Else
    MsgBox ("password incorrect")
    End If
    
End Sub

7 界面交互

1.在excel中加入-自选图形-圆形矩形-右键 设置自选图形格式 设置对齐 颜色...

2.点右键制定宏

code：

Sub Password_Protected()
    
    Dim Password, UserPassword As String
    Password = "Password"
    UserPassword = InputBox("please enther yout password")
    If Password = UserPassword Then
    MsgBox ("access granted")
    Else
    MsgBox ("password incorrect")
    End If
    
End Sub

单击后

8 界面交互应用（年龄判断）

code：

Sub SubmitAge()

Dim Age As Integer
Age = ThisWorkbook.Sheets("Sheet1").Cells(5, 2).Value
If Age > 18 Then
MsgBox ("old")
Else
MsgBox ("young")
End If
End Sub

9 for语句循环添加

code：

Sub Fill_SEries()

For counter = 2 To 6
ThisWorkbook.Sheets("Sheet1").Cells(1, counter).Value
Next

End Sub