Error 400 Solution Required

[Maroota]

Hi All

Hope someone can help with a problem running a macro.

I am using Excel version 2010 and have been running an excel macro without any problems.

I then transfered the spreadsheet and ran it in Excel version 2007 without any problems.

1 week later the version 2007 is now throwing an error 400 when it is run. I transfered a copy back to 2010 and it works perfectly.

Had a quick look on the internet to see if it could be easily sorted, but I am unsure how to proceed. The article is saying that this problem is related to an add-in.

Many thanks



Diane

[Izandol]

It may help us to help you if we may see the macro and know where it fails.

[Maroota]

Many thanks for your reply Izandol

It was a code which MJMetzer kindly wrote for me last week. It has run many times without issue, and has just started to be a problem today.

It is on a laptop which is being used by a true novice when it comes to excel, so I am thinking the settings have been changed in some way.

The error message - Microsoft Visual Basic X 400 but does not point to any line within the code.

Macro For Dividing a Named Range by 10 was the original thread.

Just tried to run a different macro and the same error appears.

Thanks


Diane

[Izandol]

If you load Excel on this machine in Safe Mode (hold the Ctrl key and start Excel), will the macro then run without error? If it will, then it is a problem with an add-in.

[Maroota]

Hi Izandol

I have loaded Excel in "Safe Mode", and yes the macro runs perfectly.

Diane

[Izandol]

Then I think you must run Excel in regular mode and disable each add-in until the macro will run. Then you will know which is the problem.

[Maroota]

Hi Izandol

Thanks for your reply.

I have checked the Excel Add-ins (Excel Options, Manage Excel Add-ins, Go) and I have no add-ins sellected.

Could it be an add-in which needs to be added.

Thanks


Diane

[Izandol]

That would not explain Safe Mode working, I do not think. Have you checked COM add-ins?

Also, there may be files in the XLSTART folders - there are 2 of these folders - that cause the problem. If there are add-in files there, they will not show as installed add-ins even if they are open.

[Maroota]

Thanks Izandol

I will check the COM add-ins. But as for the 2 XLSTART folders.....where will I find them?

Kindest regards

Diane

[Izandol]

Exact location will depend on your version of Office and Windows but you may search for them in Windows Explorer - be sure to search in hidden files and folders as well.

[Maroota]

Thanks

Will do.

[LJMetzger]

Hi Diane,

I hope you have the problem fixed. If not, you might want to try the following modified version of the code that causes your problem on the offending computer. It uses 'Conditional Compilation' in an attempt to isolate the problem if it is in fact my Macro that causes the problem.

Instructions are inside the Macro. The aim is to start with everything disabled, and run the Macro. One by One by setting values to 'True', small parts of the code are enabled one by one. The Macro won't do it's intended function, but the aim is not to run the Macro, but to attempt to isolate the 400 error.

Lewis

Please Login or Register  to view this content.

[Maroota]

Hi Lewis

Very sorry for the late reply......not enough hours in the day sometimes.

I have not been able to rectify the problem at the moment, and will certainly run the code and let you know the results.

Once again Lewis your time and expertise is very much appreciated.

Diane