VBA中Collection转Array报类型不匹配及对象变量未设置错误如何解决

核心报错原因梳理 #

• 变量命名冲突：你在主代码里声明了一个名为CollectionToArray的Variant类型变量，和你自定义的转换函数完全重名。VBA运行时会优先调用未赋值的局部变量，而非同名函数，传入集合对象时自然触发对象未设置错误，同时导致类型不匹配。
• Range对象赋值错误：Set关键字仅用于给对象类型赋值，你直接给FindCol、FindCol2两个Range对象赋值整数1、5，属于非法赋值，对象未正确初始化，后续调用.Column属性时就会报错。
• 变量声明不规范：VBA单行声明多个变量时，每个变量都需要单独指定类型，你写的Dim lastc, lastc2, lastr, FindColNumber, FindColNumber2 as Long里，只有FindColNumber2是Long类型，其余均为Variant类型，容易引发隐式类型转换错误。
• 工作表对象未初始化：代码里用到的ws工作表变量没有赋值，调用ws.Rows.Count时也会触发对象未设置错误。
• 变量赋值笔误：原代码中FindColNumber = FindCol2.Column重复给FindColNumber赋值，覆盖了之前的取值，属于逻辑错误。
• 循环逻辑冗余：循环内重复给lastc2 = R赋值完全没有必要，不影响功能但属于冗余代码。

修正后的完整代码 #

' 变量声明每个单独指定类型，删除和函数重名的冗余变量
Dim lastc As Long, lastc2 As Long, lastr As Long, FindColNumber As Long, FindColNumber2 As Long
Dim FindCol As Range
Dim col As New Collection
Dim R As Long
Dim ws As Worksheet

' 先给工作表对象赋值，这里用当前活动工作表，可按需修改为指定工作表
Set ws = ActiveSheet

' Range对象正确赋值，1代表第一列，5代表第五列
Set FindCol = ws.Columns(1) 'example
FindColNumber = FindCol.Column
lastc = FindColNumber + 1

Set FindCol2 = ws.Columns(5) 'example
FindColNumber2 = FindCol2.Column
lastc2 = FindColNumber2 - 1

For R = lastc2 To lastc Step -1
    col.Add ws.Cells(ws.Rows.Count, R).End(xlUp).Row
Next R

' 命名冲突解决后可正常调用自定义转换函数
Debug.Print WorksheetFunction.Min(CollectionToArray(col))

' 放在模块通用位置的自定义转换函数
Public Function CollectionToArray(myCol As Collection) As Variant
    Dim result  As Variant
    Dim cnt     As Long
    ReDim result(myCol.Count - 1)
    For cnt = 0 To myCol.Count - 1
        result(cnt) = myCol(cnt + 1)
    Next cnt
    CollectionToArray = result
End Function

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