如何创建实现地址州信息校验功能的Excel加载项？

一、编写自定义VBA函数（内置州列表） #

把州列表直接嵌入代码，不用依赖工作表区域，彻底省去每次添加列表的步骤：

1. 打开任意Excel文件，按Alt + F11打开VBA编辑器
2. 右键左侧「VBAProject」→ 插入→ 模块
3. 粘贴以下代码：

Function IsTargetState(cell As Range) As String
    ' 定义目标州列表：排除CT、NE，包含带前后空格的缩写和完整州名
    Dim targetStates As Variant
    targetStates = Array( _
        " AL ", " Alaska ", " AK ", " Arizona ", " AZ ", " Arkansas ", " AR ", _
        " California ", " CA ", " Colorado ", " CO ", " Connecticut ", _
        " Delaware ", " DE ", " Florida ", " FL ", " Georgia ", " GA ", " Hawaii ", " HI ", _
        " Idaho ", " ID ", " Illinois ", " IL ", " Indiana ", " IN ", " Iowa ", " IA ", _
        " Kansas ", " KS ", " Kentucky ", " KY ", " Louisiana ", " LA ", " Maine ", " ME ", _
        " Maryland ", " MD ", " Massachusetts ", " MA ", " Michigan ", " MI ", " Minnesota ", " MN ", _
        " Mississippi ", " MS ", " Missouri ", " MO ", " Montana ", " MT ", " Nebraska ", _
        " Nevada ", " NV ", " New Hampshire ", " NH ", " New Jersey ", " NJ ", " New Mexico ", " NM ", _
        " New York ", " NY ", " North Carolina ", " NC ", " North Dakota ", " ND ", " Ohio ", " OH ", _
        " Oklahoma ", " OK ", " Oregon ", " OR ", " Pennsylvania ", " PA ", " Rhode Island ", " RI ", _
        " South Carolina ", " SC ", " South Dakota ", " SD ", " Tennessee ", " TN ", " Texas ", " TX ", _
        " Utah ", " UT ", " Vermont ", " VT ", " Virginia ", " VA ", " Washington ", " WA ", _
        " West Virginia ", " WV ", " Wisconsin ", " WI ", " Wyoming ", " WY " _
    )
    
    Dim state As Variant
    Dim cellText As String
    cellText = " " & cell.Value & " " ' 给单元格内容前后加空格，确保匹配带空格的缩写
    
    For Each state In targetStates
        If InStr(1, cellText, state, vbTextCompare) > 0 Then
            IsTargetState = "Yes"
            Exit Function
        End If
    Next state
    
    IsTargetState = ""
End Function

• 代码说明：刻意排除了" CT "和" NE "；用vbTextCompare实现不区分大小写匹配；给单元格内容前后加空格，确保能精准匹配带空格的州缩写。

二、保存为Excel加载项文件 #

1. 在VBA编辑器里，点击「文件」→「保存」
2. 保存类型选择「Excel加载项（*.xlam）」，文件名设为StateChecker.xlam（默认保存位置是Excel加载项专用目录，不用修改）
3. 关闭当前Excel文件

三、安装加载项到Excel #

1. 打开任意Excel文件，点击「文件」→「选项」→「加载项」
2. 右下角「管理」选择「Excel加载项」，点击「转到」
3. 在弹出的对话框里，点击「浏览」，找到刚才保存的StateChecker.xlam文件，选中后确定
4. 勾选列表里的StateChecker，点击确定

四、使用方法 #

安装完成后，所有Excel工作簿都能直接用这个函数：
在任意单元格输入=IsTargetState(A1)（把A1换成你要检查的地址单元格），匹配到目标州就返回"Yes"，否则返回空。误报直接手动筛选即可，和你原来的逻辑一致。

补充更新说明 #

如果需要调整州列表，找到StateChecker.xlam文件，右键选择「打开」，修改VBA模块里的targetStates数组，保存后重启Excel即可生效。

内容的提问来源于stack exchange，提问作者Anthony