Excel RibbonX动态下拉框VBA计数报错：.End(xlUp)替代常量值问题

在Excel customUI中设置了两个下拉框：

• 第一个下拉框通过.End(xlUp)计算项目数量（对应GetItemCount1过程）
• 第二个直接将项目数设为常量5（对应GetItemCount2过程）

第二个过程正常运行，但第一个使用.End(xlUp)的过程出现报错，相关代码如下：

Sub GetItemCount1(control1 As IRibbonControl, temp As Long, ByRef returnedVal1)
 Dim varItems As Long
varItems = Sheet1.Cells(Rows.Count, "A").End(xlUp) - 3
returnedVal1 = varItems
End Sub

Sub GetItemLabel1(control1 As IRibbonControl, index1 As Integer, ByRef returnedVal1)
returnedVal1 = Sheet1.Cells(index1 + 4, "A").Value
End Sub

Sub GetItemCount2(control2 As IRibbonControl, ByRef returnedVal2)
returnedVal2 = 5
End Sub

Sub GetItemLabel2(control2 As IRibbonControl, index2 As Integer, ByRef returnedVal2)
returnedVal2 = Sheet1.Cells(index2 + 4, "B").Value
End Sub

1. 回调参数签名错误：customUI中getItemCount回调的标准参数仅包含IRibbonControl对象和用于返回值的ByRef参数，你在GetItemCount1中多定义了一个temp As Long参数，导致Ribbon调用该过程时参数不匹配，触发运行错误。
2. 单元格对象未取行号：Sheet1.Cells(Rows.Count, "A").End(xlUp)返回的是单元格对象，不是行号数值。直接对其做减法运算会引发类型不匹配错误，必须通过.Row属性提取行号后再计算。

Sub GetItemCount1(control1 As IRibbonControl, ByRef returnedVal1)
 Dim varItems As Long
' 获取A列最后一行的行号，再计算项目数量
varItems = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row - 3
returnedVal1 = varItems
End Sub

Sub GetItemLabel1(control1 As IRibbonControl, index1 As Integer, ByRef returnedVal1)
returnedVal1 = Sheet1.Cells(index1 + 4, "A").Value
End Sub

Sub GetItemCount2(control2 As IRibbonControl, ByRef returnedVal2)
returnedVal2 = 5
End Sub

Sub GetItemLabel2(control2 As IRibbonControl, index2 As Integer, ByRef returnedVal2)
returnedVal2 = Sheet1.Cells(index2 + 4, "B").Value
End Sub

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