vba 代码

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,