Excel vba
快捷键:
ALT+F11打开程序编辑器
F2 对象浏览器
F4 属性窗口
F5 运行子过程/用户窗体
F7 代码窗口
F8 逐语句执行代码
F9 设置/取消断点
Ctrl+Shift+F9 清除所有断点
Ctrl+G 立即窗口
Ctrl+R 工程窗口
Shift+F8 逐过程执行代码
Shift+F10 显示右键菜单
Alt+F11 返回Excel界面
Ctrl+F 查找
Ctrl+H 替换
F3 查找下一个
Shift+F3 查找上一个
Ctrl+上箭头 前一个过程
Ctrl+下箭头 下一个过程
Shift+F2 查看定义
Tab 缩进
Shift+Tab 凸出
Ctrl+Break 中断
快捷运行F5 逐条运行F8
MsgBox 弹框
Debug.Print 在立即窗口打印
Application用Excel函数时前加这个
WorksheetFunction用Excel函数时前加这个
RandBetween随机取_到_数
弹出 msgbox 1+1
打印 debug.print 1+1
语法:
Sub 名称()
对象.属性 = 属性值
End sub
名称:第一个位置不能是数字
对象:工作簿,工作表,单元格,行,列
工作表:sheets(n) sheetn
单元格:
range(“a1”) range(“a1:d20”) range(“a1:d20,e1:f20”) cells(行号,列号)
属性:
Font:
Font.color = RGB(255,255,0) VBRED
Font.colorindex = 1~56
Font.size
Font.italic
Font.underline
Font.bold
Interior 背景
Name
Mod
Rnd
Application.randbetween
For 变量 = 初始 to 结束值 (step 步长值)
Next
if 语句
If 条件 then
语句
Elseif 条件 then
语句
Else
语句
End if
DO While i>1 “条件为True时执行”
Sub 练习1()
For i = 1 To 20
Range("a" & i) = Application.RandBetween(5, 25)
If i Mod 2 = 1 Then
Range("c" & i) = 100
Range("c" & i).Font.ColorIndex = Range("c" & i).Row
Range("d" & i) = "小明"
Range("d" & i).Font.ColorIndex = 3
Range("d" & i).Font.Italic = 1
Else
Range("c" & i) = 200
Range("c" & i).Font.ColorIndex = Range("c" & i).Row
Range("d" & i) = "小红"
Range("d" & i).Font.ColorIndex = 6
Range("d" & i).Font.Italic = 1
End If
If i Mod 3 = 1 Then
Range("e" & i) = "小明"
Range("e" & i).Interior.ColorIndex = 1
Range("e" & i).Font.ColorIndex = 2
ElseIf i Mod 3 = 2 Then
Range("e" & i) = "小红"
Range("e" & i).Interior.ColorIndex = 7
Range("e" & i).Font.ColorIndex = 2
Else
Range("e" & i) = "小李"
Range("e" & i).Interior.ColorIndex = 9
Range("e" & i).Font.ColorIndex = 2
End If
Next
Range("b1") = Rnd
Range("b2") = Rnd
End Sub
Sub 重复()
q = 1
For u = 1 To 4
For i = 1 To 4
For o = 1 To 4
For p = 1 To 4
Range("h" & q) = u & i & o & p
q = q + 1
Next
Next
Next
Next
End Sub
Sub 不重复()
q = 1
For u = 1 To 4
For i = 1 To 4
For o = 1 To 4
For p = 1 To 4
If u <> i And u <> o And u <> p And i <> o And i <> p And o <> p Then
Range("j" & q) = u & i & o & p
q = q + 1
End If
Next
Next
Next
Next
End Sub
Sub 九九乘法表()
For i = 1 To 9
For j = 1 To i
Cells(i, j) = i & "*" & j & "=" & i * j
Next
Next
End Sub
Sub 转置()
For i = 3 To WorksheetFunction.CountA([a:a])
Cells(Int(i / 3), (i Mod 3) + 4) = Range("a" & i)
Cells(Int(i / 3), (i Mod 3) + 4).Font.ColorIndex = Range("b" & i)
Next
End Sub
Sub 标签云()
Application.CalculateFull ‘更新随机数’
For i = 2 To WorksheetFunction.CountA(Range("a:a"))
Range("b" & i).Font.ColorIndex = Range("e" & i)
Range("b" & i).Font.Size = Range("d" & i)
Next
End Sub
指定宏 标签云
Sub 标签云()
Application.CalculateFull
'7-Point Star 1 图片名
ActiveSheet.Shapes.Range(Array("7-Point Star 1")).Select
Dim str As String
str = ""
For i = 2 To 21
str = str + Range("b" & i) & " "
Next
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = str
L = 1
For j = 2 To 21
Selection.Characters(L,