Passing string values to function and returning a collection object VBA

Question

For some reason the following code is not passing a string to the function but returning the collection very well if string variables are hardcoded to the function.

I get following error

Compile error: Argument not optional

pointing to the findlastrow() in

Set Dimensions = findlastrow()  --line 8

Here is the code.

The main sub:

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

The function:

   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

Why do you expect the code to pass a string if you didn't pass anything here? Set Dimensions = findlastrow() — Simo
– Simo, Commented Aug 22, 2018 at 16:57
the solution could be: Function findlastrow(Optional ByVal Tabname as String) As Collection By the way, to know the last row or last column you can use: xlWorksheet.UsedRange.Rows.Count or xlWorksheet.UsedRange.Columns.Count — Simo
– Simo, Commented Aug 22, 2018 at 16:59
Couldnt really find how to do that, tried putting in the tabname variable in but did not work — tengee
– tengee, Commented Aug 22, 2018 at 17:00

Ibo · Accepted Answer · 2018-08-22 17:27:21Z

4

you have defined Tabname as the argument for your findlastrow function and you have tried to define it again inside the function, you cannot do that, so

remove this line: Tabname As String

and amend the sub as explained below:

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

edited Aug 22, 2018 at 17:27

answered Aug 22, 2018 at 17:04

Ibo

4,3197 gold badges51 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Ibo Over a year ago

@BigBen just noticed it and fixed it

Mathieu Guindon Over a year ago

That duplicate declaration was going to be OP's next compile error =)

Ibo Over a year ago

@MathieuGuindon the sub does not have end sub too, LOL

tengee Over a year ago

@MathieuGuindon if you mean the "tabname as string" in the function? fixed, if not can you please elaborate?

Mathieu Guindon Over a year ago

@tengee I mean the compiler starts at the top and chokes on the first compile error it encounters - in your case a parameterless procedure call against a procedure requiring a parameter. The next compile error was going to be the duplicate declaration of Tabname in the scope of findLastrow, which this answer fixes.

|

Collectives™ on Stack Overflow

Passing string values to function and returning a collection object VBA

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related