NO.74 GPT+Excel宏拓展“Excel自定义指令”边界

一、问题背景

为了方便用户去执行Excel相关的操作影刀中提供了丰富的自定义指令，但是某些操作还是无法满足，这时候我们可以借助Excel宏去完成某些特定的需求，目前影刀只提供了“运行Excle宏”指令，意味着你需要先手动打开Excle，把相关宏代码添加进去才能完成自动化。今天我们介绍一站式方案解决这个问题。

二、解决思路

案例说明：现有一张测试表格如下，想要筛选出杭州，北京，上海，借助现有的Excel筛选指令只能同时设置两个筛选条件，无法满足需求，我们来看看怎么通过Excel宏实现该需求。

1.先让GPT帮我们写出相关Excle宏代码

Sub FilterCities()
    Dim ws As Worksheet
    Dim rng As Range
    Dim filterCriteria As Variant

    ' 定义工作表
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 请确保工作表名称正确

    ' 定义筛选范围
    Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)

    ' 定义筛选条件
    filterCriteria = Array("杭州", "上海", "北京")

    ' 清除任何现有的筛选
    If ws.AutoFilterMode Then
        ws.AutoFilterMode = False
    End If

    ' 应用筛选
    rng.AutoFilter Field:=1, Criteria1:=filterCriteria, Operator:=xlFilterValues
End Sub
2.将宏添加进Excel

#excel_instance为Excel对象，micro_str为宏代码
def append_宏(excel_instance,micro_str):
    excel_app_module = excel_instance.workbook.VBProject.VBComponents.Add(1)
    excel_app_module.CodeModule.AddFromString(micro_str)
    3.运行宏
    
4.删除宏

#excel_instance为Excel对象，macro_name为宏名称
def delete_宏(excel_instance, macro_name):
    # 获取VBProject对象
    vb_project = excel_instance.workbook.VBProject
    # 遍历所有的VBComponents
    for vb_component in vb_project.VBComponents:
        # 获取CodeModule对象
        code_module = vb_component.CodeModule
        # 获取CodeModule中的所有代码行
        code_lines = code_module.CountOfLines
        # 遍历每一行代码，查找宏的定义
        for line_num in range(1, code_lines + 1):
            line_text = code_module.Lines(line_num, 1)
            # 如果找到宏的定义，删除整个宏
            if line_text.strip().startswith("Sub " + macro_name):
                # 找到宏的结束行
                end_line_num = line_num
                while end_line_num <= code_lines:
                    end_line_text = code_module.Lines(end_line_num, 1)
                    if end_line_text.strip().startswith("End Sub"):
                        break
                    end_line_num += 1
                # 删除宏的所有行
                code_module.DeleteLines(line_num, end_line_num - line_num + 1)
                return
    

三、实现效果