VBA Excel: wrong number of arguments or invalid property assignment on call function

Question

I get a compile error on that code below. Maybe someone can help me.

"Wrong number of arguments or invalid property assignment" for
Call Alternative(folderspec, f1.Name)

It used to work a day ago for some reason.

Sub WM()
Const folderspec = "C:\Users\xxx\Desktop\xxx\wm"
Dim fs, f, f1, fc

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.Files
    For Each f1 In fc
        If UCase(Right(f1, 3)) = "XLS" Then 'Excel File gefunden
        Call Alternative(folderspec, f1.Name)

        End If
    Next

End Sub

Sub Alternative()

Active.Workbook.UsedRange.Replace "Anteilklasse ", "", xlPart

End Sub

Vityata · Accepted Answer · 2018-09-14 09:14:07Z

1

The error "Wrong number of arguments or invalid property assignment" means that you need 2 parameters in Sub Alternative, as you are passing them with the Call:

Sub Alternative(folderspec As String, f1Name As String)    
    ActiveWorkbook.UsedRange.Replace "Anteilklasse ", "", xlPart    
End Sub

Now the compile error should be ok.
There should not be a dot between Active and Workbook - ActiveWorkbook is the correct;

edited Sep 14, 2018 at 9:14

answered Sep 14, 2018 at 9:12

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

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

2 Comments

Vityata Over a year ago

@FunThomas - It could be that the user is writing minimal reproducible example and has decided to delete about 20 lines of irrelevant code, where the parameters are used :)

Dave Over a year ago

Thanks for the quick response. I try to open several .xls files in a specific folder and then "sub alternative" should edit those files. I thought i need those parameters to open the files.

Collectives™ on Stack Overflow

VBA Excel: wrong number of arguments or invalid property assignment on call function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related