Excel vba 操作

来源：互联网发布：java 发送get请求编辑：程序博客网时间：2024/05/16 17:16

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 IntegerAge = ThisWorkbook.Sheets("Sheet1").Cells(5, 2).ValueIf Age > 18 ThenMsgBox ("old")ElseMsgBox ("young")End IfEnd Sub

9 for语句循环添加
code：

Sub Fill_SEries()For counter = 2 To 6ThisWorkbook.Sheets("Sheet1").Cells(1, counter).ValueNextEnd Sub

0 0