首先看你的代码核心问题：你想让CountWorksheets返回工作表名称的数组，但当前函数声明为As String（单个字符串），和返回数组的需求不匹配，导致无法正确传递数组到主代码的ar变量里。另外数组下标处理也有小瑕疵，我们一步步来修正：

1. 修正CountWorksheets函数 #

把函数返回类型改成字符串数组As String()，同时调整数组下标范围，避免出现空索引位：

Public Function CountWorksheets() As String()
    Dim i As Integer, size As Integer ' 原代码i未声明类型，默认是变体型，这里明确类型更规范
    Dim Arr() As String
    
    size = Worksheets.Count
    ' 直接让数组下标从1开始，和工作表索引对应，避免空的下标0
    ReDim Arr(1 To size)
    
    For i = 1 To size
        Arr(i) = Worksheets(i).Name
    Next i
    
    CountWorksheets = Arr ' 现在可以直接返回数组了
End Function

关键修改点： #

• 将函数返回类型从As String改为As String()，明确要返回字符串数组
• 给i声明Integer类型，避免变体类型带来的潜在问题
• 使用ReDim Arr(1 To size)让数组下标和工作表索引对齐，循环赋值更直观

2. 修正主代码RunLoc_Click过程 #

主代码里不需要提前ReDim ar(Worksheets.Count)，函数返回的数组会自动匹配变量，直接赋值即可：

Private Sub RunLoc_Click()
    Dim ar() As String ' 仅声明，无需提前ReDim
    Dim i As Integer, j As Integer ' 明确所有变量类型
    
    If Not IsEmpty(Location.Value) Then
        RunLoc.Enabled = True
        ' 查找并定位指定工作表
        For i = 1 To Worksheets.Count
            If Worksheets(i).Name = Location.Value Then
                Worksheets(i).Activate ' 用Activate替代Select，减少对选中状态的依赖
                Range("A1").Activate
                Exit For ' 找到目标后直接退出循环，提升效率
            End If
        Next i
        
        ' 直接接收函数返回的完整数组
        ar = CountWorksheets()
        
        ' 可选：添加测试代码，在立即窗口输出数组内容验证结果
        Dim sheetName As String
        For Each sheetName In ar
            Debug.Print sheetName
        Next sheetName
    End If
    
    TextBox1.Value = Location.Value
End Sub

关键优化点： #

• 删除多余的ReDim ar(Worksheets.Count)，函数返回的数组会自动赋值给ar
• 把Select替换为Activate，VBA里尽量避免依赖选中状态，让代码更稳定
• 找到目标工作表后加入Exit For，避免不必要的遍历
• 新增测试代码，通过Debug.Print在立即窗口输出数组内容，方便你验证是否正确获取了所有工作表名称

这样修改后，CountWorksheets函数就能正确返回包含所有工作表名称的数组，主代码里的ar也能完整接收这个数组，后续你就可以基于ar来追踪新增工作表（比如对比前后两次获取的数组差异），完成相应的处理逻辑。

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