将字符串值传递给函数并返回集合对象 VBA

Question

出于某种原因，下面的代码没有将字符串传递给函数，但如果字符串变量被硬编码到函数，则很好地返回集合。

我收到以下错误

编译错误：参数不是可选的

指向 中的 findlastrow((

Set Dimensions = findlastrow()  --line 8

这是代码。

主子：

Sub SheetCopier()
Dim TargetlRow As Long
Dim TargetlCol As Long
Dim Tabname As String
Dim Dimensions As Collection
Set Dimensions = findlastrow()
TargetControlTab = "tab1"
Tabname = TargetControlTab

Call findlastrow(Tabname)
MsgBox "Last Row: " & Dimensions.Item(1) & vbNewLine & "Last Column: " & Dimensions.Item(2)
End Sub

该函数：

Function findlastrow(Tabname As String) As Collection
'Finds the last non-blank cell in a single row or column
Dim FilledDimensions As Collection
Set FilledDimensions = New Collection

Sheets(Tabname).Select
'Find the last non-blank cell in column A(1)
TargetlRow = Cells(Rows.Count, 1).End(xlUp).Row
'Find the last non-blank cell in row 1
TargetlCol = Cells(1, Columns.Count).End(xlToLeft).Column
FilledDimensions.Add TargetlRow
FilledDimensions.Add TargetlCol
Set findlastrow = FilledDimensions
End Function

Answer 1

您已经将Tabname定义为findlastrow函数的参数，并且您尝试在函数中再次定义它，因此无法执行此操作，因此

删除此行：Tabname As String

并修改子项，解释如下：

Sub SheetCopier()
Dim TargetlRow As Long
Dim TargetlCol As Long
Dim Tabname As String
Dim Dimensions As Collection
'this line is the problem since you are not passing a worksheet to the function
'Set Dimensions = findlastrow()
TargetControlTab = "tab1"
Tabname = TargetControlTab

set Dimensions=findlastrow(Tabname) 
MsgBox "Last Row: " & Dimensions.Item(1) & vbNewLine & "Last Column: " & Dimensions.Item(2)
end sub