Compile error Wrong number of arguments or invalid property assignment

[Giordano Bruno]

I have recently found that a number of macros which used to work are now failing with the above message. It happens on my desktop computer, but not on my Surface Pro. The simplest test is to open a workbook, set the cursor in the top left corner and start to record a macro. I hold the shift key and press the down arrow and then stop recording. The recorded code is "Range("A1:A2").Select" Not brilliant code, but it works on my Surface Pro. When I try to run this code on my Desk top computer I get the Compile error message. It can be fixed by typing "ActiveSheet." before the word "Range". Neither the error message, not the Help option seem to refer to the situation. Can anyone tell me why this problem occurs on my desktop computer and what can be done to fix this issue?

 

[Keebellah]

Hi, IMHO it has nothing to do with the fact of a Surface Pro or Desktop, it's Excel period.
The only reason I can come up with is that the worksheet at that moment active is not of the type worksheet. Maybe a graph or something similar.
Is there a worksheet active the moment you start Excel on the desktop or is the macro recorded and stored in your Personal.xlsb?

 

[Giordano Bruno]

Hi Keebellah,
Thanks for the reply, but yes, there is an active workbook with one active worksheet and it's the same worksheet that I record the macro on. It's not recorded on my Personal.xlsb That's the reason that I think it relates to either the computer itself or to some setting in Excel in The Desk Top computer that is not the same as in my Surface Pro. They are both running Excel 365.

 

[Keebellah]

Maybe include Activeworkbook with the sheet

Activeworkbook.Sheets(Activesheet.name). etc

 

[Giordano Bruno]

Hi Keebellah,
I think you're missing the point. I know that I can change the code to make it work. As I explained initially, adding "ActiveSheet." is sufficient to make it run. The question is, "what is causing recorded code that works on one computer, to not work on another?

 

[MARK858]

@Giordano Bruno can you upload a copy of a workbook where this is occurring to a free file hosting site like www.dropbox.com or www.box.com, mark it for sharing and post the link it provides in the thread so that we can see if we can replicate the behaviour (I can't get it to replicate it on a regular sheet at the moment).

 

[Giordano Bruno]

Hi Mark,
Thanks for your reply, but I think the problem doesn't lie within the worksheet since I can transfer the sheet to my Surface Pro and another laptop and it runs happily. I've tried the slow and fast Excel repair tools and also removed and replaced office. My suspicion is that there is either some setting or some problem in my desktop computer that is causing this behavior. I'm not normally a believer in the occult, but I'm starting to think this may be a case of witchcraft.

 

[Keebellah]

Try the following: close excel, open windows explorer go to the folder where the workbook’s stored, select it, press the shift button and open the file with the shift button pressed, the file will open without running macros, then open VBA project and run the routine you have step by step to see what’s happening

 

[Giordano Bruno]

Thanks Keebellah, but this is not code that is triggered on opening the workbook, but code stored n a normal code module. As I noted in my query there is only one line of code recorded. "Range("A1:A2").Select" and whether I step through it or try to run it I get the same message.

 

[Alex Blakenburg]

Do you have any Add-ins turned on, on your desktop computer ?
If so try turning them all off and see if the issue goes away.