# Excel VBA 物控基础教程 📊

> 从零开始学习 Excel VBA 在物控中的应用

---

## 目录

1. [VBA 基础语法](#vba-基础语法)
2. [常用对象](#常用对象)
3. [物控常用函数](#物控常用函数)
4. [实战案例](#实战案例)
5. [调试技巧](#调试技巧)

---

## VBA 基础语法

### 1. 变量声明

```vba
' 基本变量类型
Dim item_code As String      ' 物料编码
Dim quantity As Double       ' 数量
Dim unit_price As Currency   ' 单价
Dim order_date As Date       ' 订单日期
Dim is_critical As Boolean   ' 是否关键物料

' 数组
Dim part_list(1 To 100) As String
Dim inventory_data(1 To 1000, 1 To 5) As Variant

' 集合和字典
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
```

### 2. 条件语句

```vba
' If-Then-Else
If quantity < safety_stock Then
    MsgBox "库存不足！"
    status = "需补货"
ElseIf quantity > max_stock Then
    status = "库存过高"
Else
    status = "正常"
End If

' Select Case（适合多条件判断）
Select Case abc_class
    Case "A"
        priority = "高"
        review_freq = "每周"
    Case "B"
        priority = "中"
        review_freq = "每月"
    Case "C"
        priority = "低"
        review_freq = "每季度"
End Select
```

### 3. 循环结构

```vba
' For 循环（已知次数）
For i = 2 To last_row
    part_no = Cells(i, "A").Value
    qty = Cells(i, "B").Value
    
    ' 处理数据
    If qty > 0 Then
        Cells(i, "C").Value = qty * 1.1  ' 建议采购量
    End If
Next i

' For Each 循环（遍历集合）
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    Debug.Print ws.Name
Next ws

' Do While 循环（条件满足时继续）
Do While Cells(row, "A").Value <> ""
    ' 处理数据
    row = row + 1
Loop
```

### 4. 函数定义

```vba
' 自定义函数（Function）
Function Calculate_Safety_Stock(avg_demand As Double, lead_time As Double) As Double
    Dim safety_factor As Double
    safety_factor = 1.5  ' 安全系数
    
    Calculate_Safety_Stock = avg_demand * lead_time * safety_factor
End Function

' 子程序（Sub）
Sub Generate_Purchase_Order()
    ' 执行采购订单生成逻辑
    MsgBox "采购订单生成完成！"
End Sub
```

---

## 常用对象

### 1. Worksheet 对象

```vba
' 引用工作表
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("库存")
Set ws = ThisWorkbook.Sheets(1)  ' 按索引

' 激活工作表
ws.Activate

' 工作表属性
Debug.Print ws.Name          ' 工作表名称
Debug.Print ws.Cells.Count   ' 单元格数量
Debug.Print ws.UsedRange.Rows.Count  ' 已使用行数

' 清空工作表
ws.Cells.Clear  ' 清除所有内容和格式
ws.Cells.ClearContents  ' 仅清除内容
```

### 2. Range 对象

```vba
' 单元格引用
Cells(1, 1).Value = "物料编码"  ' 行列索引
Range("A1").Value = "物料编码"   ' A1 表示法
Range("A1:C10").Value = "数据"   ' 区域

' 动态范围
last_row = Cells(Rows.Count, "A").End(xlUp).Row
last_col = Cells(1, Columns.Count).End(xlToLeft).Column

' 批量操作
Range("A2:A" & last_row).Value = "默认值"

' 格式设置
With Range("A1:H1")
    .Font.Bold = True
    .Interior.Color = RGB(200, 200, 200)
    .HorizontalAlignment = xlCenter
End With
```

### 3. Workbook 对象

```vba
' 当前工作簿
Set wb = ThisWorkbook

' 新建工作簿
Set new_wb = Workbooks.Add

' 打开工作簿
Set wb = Workbooks.Open("C:\路径\文件.xlsx")

' 保存
wb.Save
wb.SaveAs "C:\路径\新文件.xlsx"

' 关闭
wb.Close SaveChanges:=True
```

### 4. 文件对话框

```vba
' 选择文件
Dim file_path As String
With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "选择库存数据文件"
    .Filters.Clear
    .Filters.Add "Excel 文件", "*.xlsx;*.xls"
    
    If .Show = -1 Then
        file_path = .SelectedItems(1)
    End If
End With
```

---

## 物控常用函数

### 1. 库存计算函数

```vba
' 计算安全库存
Function Safety_Stock(avg_demand As Double, lead_time As Double, _
                      Optional service_level As Double = 0.95) As Double
    
    Dim z_score As Double
    Select Case service_level
        Case 0.90: z_score = 1.28
        Case 0.95: z_score = 1.65
        Case 0.98: z_score = 2.05
        Case 0.99: z_score = 2.33
        Case Else: z_score = 1.65
    End Select
    
    Safety_Stock = avg_demand * lead_time * z_score
End Function

' 计算订货点
Function Reorder_Point(avg_demand As Double, lead_time As Double, _
                       safety_stock As Double) As Double
    
    Reorder_Point = avg_demand * lead_time + safety_stock
End Function

' 计算经济订货量（EOQ）
Function EOQ(annual_demand As Double, order_cost As Double, _
             holding_cost As Double) As Double
    
    EOQ = Sqr(2 * annual_demand * order_cost / holding_cost)
End Function

' 计算库存周转率
Function Inventory_Turnover(sales_cost As Double, avg_inventory As Double) As Double
    If avg_inventory = 0 Then
        Inventory_Turnover = 0
    Else
        Inventory_Turnover = sales_cost / avg_inventory
    End If
End Function

' 计算库存天数
Function Inventory_Days(turnover As Double) As Double
    If turnover = 0 Then
        Inventory_Days = 0
    Else
        Inventory_Days = 365 / turnover
    End If
End Function
```

### 2. 数据查找函数

```vba
' 查找物料信息
Function Get_Part_Info(part_no As String, info_type As String) As Variant
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("物料主数据")
    
    Dim last_row As Long
    last_row = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim i As Long
    For i = 2 To last_row
        If ws.Cells(i, "A").Value = part_no Then
            Select Case info_type
                Case "名称": Get_Part_Info = ws.Cells(i, "B").Value
                Case "规格": Get_Part_Info = ws.Cells(i, "C").Value
                Case "单位": Get_Part_Info = ws.Cells(i, "D").Value
                Case "单价": Get_Part_Info = ws.Cells(i, "E").Value
                Case "供应商": Get_Part_Info = ws.Cells(i, "F").Value
                Case Else: Get_Part_Info = ""
            End Select
            Exit Function
        End If
    Next i
    
    Get_Part_Info = "未找到"
End Function

' 查找库存数量
Function Get_Stock_Qty(part_no As String) As Double
    On Error Resume Next
    Get_Stock_Qty = Application.WorksheetFunction.VLookup( _
        part_no, ThisWorkbook.Sheets("库存").Range("A:D"), 4, False)
    
    If Err.Number <> 0 Then
        Get_Stock_Qty = 0
        Err.Clear
    End If
    On Error GoTo 0
End Function
```

### 3. 数据验证函数

```vba
' 检查物料编码格式
Function Validate_Part_No(part_no As String) As Boolean
    ' 假设物料编码格式：3位字母 + 6位数字（如：ABC123456）
    If Len(part_no) = 9 Then
        If Left(part_no, 3) Like "[A-Z][A-Z][A-Z]" And _
           Right(part_no, 6) Like "######" Then
            Validate_Part_No = True
            Exit Function
        End If
    End If
    
    Validate_Part_No = False
End Function

' 检查库存合理性
Function Validate_Stock_Qty(qty As Double, min_qty As Double, max_qty As Double) As Boolean
    If qty >= min_qty And qty <= max_qty Then
        Validate_Stock_Qty = True
    Else
        Validate_Stock_Qty = False
    End If
End Function
```

---

## 实战案例

### 案例 1：库存报表自动生成

```vba
Sub Generate_Inventory_Report()
    Dim ws_stock As Worksheet, ws_report As Worksheet
    Dim last_row As Long, i As Long
    Dim report_row As Long
    
    ' 设置工作表
    Set ws_stock = ThisWorkbook.Sheets("库存")
    
    ' 删除旧报表
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets("库存报表").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    ' 创建新报表
    Set ws_report = ThisWorkbook.Sheets.Add
    ws_report.Name = "库存报表_" & Format(Date, "yyyy-mm-dd")
    
    ' 设置表头
    ws_report.Range("A1:J1").Value = Array( _
        "序号", "物料编码", "物料名称", "规格", "单位", _
        "当前库存", "安全库存", "最高库存", "状态", "建议操作")
    ws_report.Range("A1:J1").Font.Bold = True
    
    ' 获取数据
    last_row = ws_stock.Cells(ws_stock.Rows.Count, "A").End(xlUp).Row
    report_row = 2
    
    For i = 2 To last_row
        Dim part_no As String
        Dim part_name As String
        Dim spec As String
        Dim unit As String
        Dim current_qty As Double
        Dim safety_qty As Double
        Dim max_qty As Double
        
        part_no = ws_stock.Cells(i, "A").Value
        part_name = ws_stock.Cells(i, "B").Value
        spec = ws_stock.Cells(i, "C").Value
        unit = ws_stock.Cells(i, "G").Value
        current_qty = ws_stock.Cells(i, "D").Value
        safety_qty = ws_stock.Cells(i, "E").Value
        max_qty = ws_stock.Cells(i, "F").Value
        
        ' 写入报表
        ws_report.Cells(report_row, 1).Value = report_row - 1
        ws_report.Cells(report_row, 2).Value = part_no
        ws_report.Cells(report_row, 3).Value = part_name
        ws_report.Cells(report_row, 4).Value = spec
        ws_report.Cells(report_row, 5).Value = unit
        ws_report.Cells(report_row, 6).Value = current_qty
        ws_report.Cells(report_row, 7).Value = safety_qty
        ws_report.Cells(report_row, 8).Value = max_qty
        
        ' 判断状态
        If current_qty < safety_qty Then
            ws_report.Cells(report_row, 9).Value = "⚠️ 库存不足"
            ws_report.Cells(report_row, 9).Interior.Color = RGB(255, 200, 200)
            ws_report.Cells(report_row, 10).Value = "立即补货"
        ElseIf current_qty > max_qty Then
            ws_report.Cells(report_row, 9).Value = "📦 库存过高"
            ws_report.Cells(report_row, 9).Interior.Color = RGB(255, 255, 200)
            ws_report.Cells(report_row, 10).Value = "暂停采购"
        Else
            ws_report.Cells(report_row, 9).Value = "✅ 正常"
            ws_report.Cells(report_row, 9).Interior.Color = RGB(200, 255, 200)
            ws_report.Cells(report_row, 10).Value = "维持现状"
        End If
        
        report_row = report_row + 1
    Next i
    
    ' 格式化
    ws_report.Columns("A:J").AutoFit
    
    ' 添加统计
    ws_report.Range("L1").Value = "统计"
    ws_report.Range("L2").Value = "总物料数："
    ws_report.Range("M2").Value = report_row - 2
    ws_report.Range("L3").Value = "缺料数："
    ws_report.Range("M3").Value = Application.WorksheetFunction.CountIf(ws_report.Range("I:I"), "*不足*")
    ws_report.Range("L4").Value = "库存过高数："
    ws_report.Range("M4").Value = Application.WorksheetFunction.CountIf(ws_report.Range("I:I"), "*过高*")
    
    MsgBox "库存报表生成完成！" & vbCrLf & _
           "报表名称：" & ws_report.Name
End Sub
```

### 案例 2：MRP 计算

```vba
Sub Calculate_MRP()
    Dim ws_bom As Worksheet, ws_inventory As Worksheet, ws_mrp As Worksheet
    Dim last_row As Long, i As Long, mrp_row As Long
    
    ' 设置工作表
    Set ws_bom = ThisWorkbook.Sheets("BOM")
    Set ws_inventory = ThisWorkbook.Sheets("库存")
    
    ' 删除旧 MRP 表
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets("MRP").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    ' 创建 MRP 表
    Set ws_mrp = ThisWorkbook.Sheets.Add
    ws_mrp.Name = "MRP"
    
    ' 设置表头
    ws_mrp.Range("A1:H1").Value = Array( _
        "物料编码", "物料名称", "需求量", "库存量", _
        "净需求", "建议采购", "供应商", "到货日期")
    ws_mrp.Range("A1:H1").Font.Bold = True
    
    ' 获取 BOM 数据
    last_row = ws_bom.Cells(ws_bom.Rows.Count, "A").End(xlUp).Row
    mrp_row = 2
    
    For i = 2 To last_row
        Dim part_no As String
        Dim part_name As String
        Dim qty_needed As Double
        Dim qty_in_stock As Double
        Dim net_qty As Double
        Dim lead_time As Double
        
        part_no = ws_bom.Cells(i, "A").Value
        part_name = ws_bom.Cells(i, "B").Value
        qty_needed = ws_bom.Cells(i, "C").Value
        
        ' 查找库存
        On Error Resume Next
        qty_in_stock = Application.WorksheetFunction.VLookup( _
            part_no, ws_inventory.Range("A:D"), 4, False)
        If Err.Number <> 0 Then
            qty_in_stock = 0
            Err.Clear
        End If
        On Error GoTo 0
        
        ' 计算净需求
        net_qty = qty_needed - qty_in_stock
        
        ' 如果需要采购
        If net_qty > 0 Then
            ws_mrp.Cells(mrp_row, 1).Value = part_no
            ws_mrp.Cells(mrp_row, 2).Value = part_name
            ws_mrp.Cells(mrp_row, 3).Value = qty_needed
            ws_mrp.Cells(mrp_row, 4).Value = qty_in_stock
            ws_mrp.Cells(mrp_row, 5).Value = net_qty
            
            ' 建议采购量（考虑 MOQ 和安全系数）
            Dim suggested_qty As Double
            suggested_qty = net_qty * 1.1  ' 10% 安全系数
            
            ' 查找最小订货量
            Dim moq As Double
            On Error Resume Next
            moq = Application.WorksheetFunction.VLookup( _
                part_no, ws_inventory.Range("A:H"), 8, False)
            If Err.Number <> 0 Then
                moq = 1
                Err.Clear
            End If
            On Error GoTo 0
            
            If suggested_qty < moq Then
                suggested_qty = moq
            End If
            
            ws_mrp.Cells(mrp_row, 6).Value = suggested_qty
            
            ' 查找供应商和提前期
            On Error Resume Next
            ws_mrp.Cells(mrp_row, 7).Value = Application.WorksheetFunction.VLookup( _
                part_no, ws_inventory.Range("A:H"), 7, False)
            lead_time = Application.WorksheetFunction.VLookup( _
                part_no, ws_inventory.Range("A:H"), 6, False)
            If Err.Number <> 0 Then
                lead_time = 7  ' 默认 7 天
                Err.Clear
            End If
            On Error GoTo 0
            
            ' 计算到货日期
            ws_mrp.Cells(mrp_row, 8).Value = Date + lead_time
            
            mrp_row = mrp_row + 1
        End If
    Next i
    
    ' 格式化
    ws_mrp.Columns("A:H").AutoFit
    
    ' 添加汇总
    ws_mrp.Range("J1").Value = "MRP 汇总"
    ws_mrp.Range("J2").Value = "需采购物料数："
    ws_mrp.Range("K2").Value = mrp_row - 2
    ws_mrp.Range("J3").Value = "总采购金额："
    ws_mrp.Range("K3").Formula = "=SUM(F2:F" & mrp_row - 1 & ")*VLOOKUP(A2,库存!A:E,5,FALSE)"
    
    MsgBox "MRP 计算完成！" & vbCrLf & _
           "需采购物料数：" & mrp_row - 2
End Sub
```

### 案例 3：呆滞料分析

```vba
Sub Analyze_Stagnant_Inventory()
    Dim ws As Worksheet
    Dim last_row As Long, i As Long
    Dim stagnant_count As Long
    Dim stagnant_value As Double
    
    Set ws = ThisWorkbook.Sheets("库存")
    last_row = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 添加分析列
    ws.Cells(1, "I").Value = "呆滞状态"
    ws.Cells(1, "J").Value = "呆滞天数"
    ws.Cells(1, "K").Value = "呆滞金额"
    
    ' 清空旧数据
    ws.Range("I2:K" & last_row).ClearContents
    
    ' 分析每行
    For i = 2 To last_row
        Dim last_move_date As Date
        Dim days_stagnant As Long
        Dim stock_qty As Double
        Dim unit_price As Double
        
        ' 获取最后移动日期（假设在 H 列）
        On Error Resume Next
        last_move_date = ws.Cells(i, "H").Value
        If Err.Number <> 0 Or last_move_date = 0 Then
            last_move_date = Date - 365  ' 默认一年前
            Err.Clear
        End If
        On Error GoTo 0
        
        ' 计算呆滞天数
        days_stagnant = Date - last_move_date
        ws.Cells(i, "J").Value = days_stagnant
        
        ' 计算呆滞金额
        stock_qty = ws.Cells(i, "D").Value
        unit_price = ws.Cells(i, "E").Value
        ws.Cells(i, "K").Value = stock_qty * unit_price
        
        ' 判断呆滞状态
        Select Case days_stagnant
            Case Is > 365
                ws.Cells(i, "I").Value = "🔴 严重呆滞（>1年）"
                ws.Cells(i, "I").Interior.Color = RGB(255, 150, 150)
                stagnant_count = stagnant_count + 1
                stagnant_value = stagnant_value + stock_qty * unit_price
            Case Is > 180
                ws.Cells(i, "I").Value = "🟡 中度呆滞（6-12月）"
                ws.Cells(i, "I").Interior.Color = RGB(255, 220, 150)
            Case Is > 90
                ws.Cells(i, "I").Value = "⚠️ 轻度呆滞（3-6月）"
                ws.Cells(i, "I").Interior.Color = RGB(255, 255, 150)
            Case Else
                ws.Cells(i, "I").Value = "✅ 正常"
                ws.Cells(i, "I").Interior.Color = RGB(200, 255, 200)
        End Select
    Next i
    
    ' 格式化
    ws.Columns("I:K").AutoFit
    
    ' 统计结果
    Dim total_value As Double
    total_value = Application.WorksheetFunction.Sum(ws.Range("K2:K" & last_row))
    
    MsgBox "呆滞料分析完成！" & vbCrLf & _
           "总物料数：" & last_row - 1 & vbCrLf & _
           "严重呆滞数：" & stagnant_count & vbCrLf & _
           "呆滞料金额：$" & Format(stagnant_value, "#,##0.00") & vbCrLf & _
           "呆滞比例：" & Format(stagnant_value / total_value * 100, "0.00") & "%"
End Sub
```

### 案例 4：盘点表生成

```vba
Sub Generate_Inventory_Count_Sheet()
    Dim ws_stock As Worksheet, ws_count As Worksheet
    Dim last_row As Long, i As Long
    
    ' 设置工作表
    Set ws_stock = ThisWorkbook.Sheets("库存")
    
    ' 删除旧盘点表
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets("盘点表").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    ' 创建新盘点表
    Set ws_count = ThisWorkbook.Sheets.Add
    ws_count.Name = "盘点表_" & Format(Date, "yyyy-mm-dd")
    
    ' 设置表头
    ws_count.Range("A1:H1").Value = Array( _
        "序号", "物料编码", "物料名称", "规格", _
        "单位", "账面数量", "实盘数量", "差异")
    ws_count.Range("A1:H1").Font.Bold = True
    
    ' 获取库存数据
    last_row = ws_stock.Cells(ws_stock.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To last_row
        ws_count.Cells(i, 1).Value = i - 1
        ws_count.Cells(i, 2).Value = ws_stock.Cells(i, "A").Value
        ws_count.Cells(i, 3).Value = ws_stock.Cells(i, "B").Value
        ws_count.Cells(i, 4).Value = ws_stock.Cells(i, "C").Value
        ws_count.Cells(i, 5).Value = ws_stock.Cells(i, "G").Value
        ws_count.Cells(i, 6).Value = ws_stock.Cells(i, "D").Value
    Next i
    
    ' 设置差异公式
    ws_count.Range("H2:H" & last_row).Formula = "=F2-G2"
    
    ' 格式化
    ws_count.Columns("A:H").AutoFit
    
    ' 添加说明
    ws_count.Range("J1").Value = "盘点说明"
    ws_count.Range("J2").Value = "1. 在'实盘数量'列填写实际盘点数量"
    ws_count.Range("J3").Value = "2. '差异'列会自动计算"
    ws_count.Range("J4").Value = "3. 差异为负表示盘亏，为正表示盘盈"
    
    ' 添加统计
    ws_count.Range("J6").Value = "盘点统计"
    ws_count.Range("J7").Value = "总物料数："
    ws_count.Range("K7").Value = last_row - 1
    
    MsgBox "盘点表生成完成！" & vbCrLf & _
           "盘点表名称：" & ws_count.Name
End Sub
```

---

## 调试技巧

### 1. 基本调试方法

```vba
' 使用 Debug.Print 输出信息
Debug.Print "当前处理行：" & i
Debug.Print "物料编码：" & part_no
Debug.Print "库存数量：" & qty

' 使用断点
' 在代码左侧点击设置断点（F9）
' 程序会在断点处暂停，可以检查变量值

' 使用 Watch 窗口
' 视图 → 立即窗口 (Ctrl+G)
' 视图 → 监视窗口
```

### 2. 错误处理

```vba
' 基本错误处理
On Error Resume Next  ' 忽略错误，继续执行
' 你的代码...
If Err.Number <> 0 Then
    Debug.Print "错误：" & Err.Description
    Err.Clear
End If
On Error GoTo 0  ' 恢复正常错误处理

' 详细错误处理
Sub Safe_Execution()
    On Error GoTo ErrorHandler
    
    ' 主要代码
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("库存")
    
    ' 可能出错的操作
    Dim qty As Double
    qty = ws.Range("D100000").Value  ' 可能超出范围
    
    Exit Sub
    
ErrorHandler:
    MsgBox "错误发生：" & vbCrLf & _
           "错误号：" & Err.Number & vbCrLf & _
           "错误描述：" & Err.Description & vbCrLf & _
           "错误位置：" & Erl, vbCritical
    Err.Clear
End Sub
```

### 3. 性能优化

```vba
' 优化前（慢）
Sub Slow_Code()
    For i = 1 To 10000
        Cells(i, "A").Value = Cells(i, "A").Value * 2  ' 每次都访问单元格
    Next i
End Sub

' 优化后（快）
Sub Fast_Code()
    Dim data_range As Range
    Dim data_array As Variant
    Dim i As Long
    
    ' 一次性读取数据到数组
    Set data_range = Range("A1:A10000")
    data_array = data_range.Value
    
    ' 在数组中处理
    For i = 1 To 10000
        data_array(i, 1) = data_array(i, 1) * 2
    Next i
    
    ' 一次性写回
    data_range.Value = data_array
End Sub

' 其他优化技巧
Sub Optimization_Tips()
    Application.ScreenUpdating = False      ' 关闭屏幕刷新
    Application.Calculation = xlCalculationManual  ' 关闭自动计算
    Application.EnableEvents = False        ' 关闭事件
    
    ' 执行耗时操作...
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub
```

### 4. 调试工具

```vba
' 立即窗口（Immediate Window）
' Ctrl+G 打开
' ? 变量名  ' 查看变量值
' ? 10 * 2  ' 计算表达式

' 本地窗口（Locals Window）
' 视图 → 本地窗口
' 显示所有局部变量的值

' 调试工具栏
' 视图 → 工具栏 → 调试
' 包含：继续、中断、逐语句、逐过程、跳出
```

---

## 快速参考

### 常用快捷键

| 快捷键 | 功能 |
|--------|------|
| Alt + F11 | 打开 VBA 编辑器 |
| F5 | 运行宏 |
| F8 | 逐语句调试 |
| F9 | 设置/取消断点 |
| Ctrl + G | 打开立即窗口 |
| Ctrl + R | 打开工程资源管理器 |
| Ctrl + F | 查找 |
| Ctrl + H | 替换 |

### 常用对象模型

```vba
' 工作簿集合
Workbooks("文件名.xlsx")

' 工作表集合
ThisWorkbook.Sheets("表名")
ThisWorkbook.Sheets(1)  ' 索引从 1 开始

' 单元格
Cells(行, 列)  ' 如：Cells(1, 1) = A1
Range("A1")    ' A1 单元格
Range("A1:C10") ' A1 到 C10 区域

' 行和列
Rows(1)        ' 第 1 行
Columns("A")   ' A 列
```

### 常用常量

```vba
' 对齐方式
xlLeft      ' 左对齐
xlCenter    ' 居中
xlRight     ' 右对齐

' 边框样式
xlThin      ' 细线
xlMedium    ' 中等
xlThick     ' 粗线

' 颜色
RGB(255, 0, 0)      ' 红色
RGB(0, 255, 0)      ' 绿色
RGB(255, 255, 0)    ' 黄色
```

---

## 下一步

1. **练习基础语法**：尝试编写简单的宏
2. **修改现有工具**：根据你的需求调整代码
3. **创建新工具**：解决你的特定问题
4. **学习高级技巧**：数组、字典、正则表达式
5. **分享你的代码**：贡献到物控学习笔记

---

**最后更新**: 2026-02-03  
**版本**: v1.0.0