时间戳

首页 > 解决方案 > 将字符串值传递给函数并返回集合对象 VBA

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

问题描述

出于某种原因，如果字符串变量被硬编码到函数中，以下代码不会将字符串传递给函数，而是很好地返回集合。

我收到以下错误

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

指向 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

标签： excelvba

解决方案

您已将其定义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

推荐阅读