Getting a compile error with a very simple VBA code

[borski88]

I have this simple code open a hidden sheet:

Sub Test()
    
    Sheets("sheet1").Visible = True
    Sheets("sheet1").Select

End Sub

I get the following Compile Error:

Compile Error:
Wrong number of arguments or invalid property assignments

 

[borski88]

Actually I have a question... are you simply trying to run this code by itself, or are you calling it from somewhere else? If you step through your code does it work?

I was running the code with a button that had the macro binded to it.

 

[Domenic]

It seems to me that both Sheets("Sheet1") and Application.Sheets("Sheet1") both refer to Sheet1 of the active workbook. So I don't see how it would make a difference. Maybe I'm missing something?

 

[borski88]

It seems to me that both Sheets("Sheet1") and Application.Sheets("Sheet1") both refer to Sheet1 of the active workbook. So I don't see how it would make a difference. Maybe I'm missing something?

I'm not sure either, but it seemed to work

 

[Domenic]

I'm not sure either, but it seemed to work

Yeah, I'm glad it's working for you.

Cheers!

 

[svendiamond]

It seems to me that both Sheets("Sheet1") and Application.Sheets("Sheet1") both refer to Sheet1 of the active workbook. So I don't see how it would make a difference. Maybe I'm missing something?

Yes, they both refer to the same thing. I found the Application.Sheets solution from a similar thread from Googling. I would encourage OP to ensure that they are using the proper references in VBA. Tools>References, make sure you have the correct Object Libraries chosen. I have not found exactly why this works or why it is needed for this issue so my best theory is that he did not have the Microsoft Office 16.0 Object Library selected... not sure, though.

 

[Rick Rothstein]

It seems to me that both Sheets("Sheet1") and Application.Sheets("Sheet1") both refer to Sheet1 of the active workbook. So I don't see how it would make a difference. Maybe I'm missing something?

If the OP has a macro or function (maybe class, but I'm not sure about that one) named Sheets, then not using Application will generate the error he reported as VB will attempt to use the macro or function directly... using Application makes the Sheets property reference the workbook's Sheet and not the macro or function with that name. One could use ThisWorkbook instead of Application and it would have the same effect. My advice to the OP is to not use reserved keywords as macro or function names.

 

[Domenic]

@Rick

That explains it. Thanks, I really appreciate it.

@Svendiamond

Thanks for your help as well.