Ошибка 400 эксель

When you have a set of actions that you do constantly, creating a Macro in Excel is crucial.

Once you create it, it runs on its own and delivers great reports. That is if you don’t get any error.

Some users seem to be getting a VBA error 400 when trying to run their Macro in Excel. There are no further indications only a box with an OK button.

How can I solve VBA error 400 in Excel?

Either way, don’t worry we have some simple solutions for you.

We suggest that you follow the steps mentioned in this article in the presented order. Hopefully, they will help you solve your problem.

1. Fix VBA error 400 by moving your Macros to another Module

If you are getting the VBA error 400 in Excel while trying to run your Macros, moving them to a new Module can fix it.

There could be a problem with the old Module, therefore switching to a new one might fix the error.

1. Open Microsoft Excel.

2. We need to create the new Module to transfer your Macros to. Click on the Developer tab from the Ribbon menu.

3. Select Visual Basic from the options.

4. In the VBA menu, click Module on the Insert menu.

5. Paste the VBA code that you want to use.

6. Navigate to the File menu and click Save Global. Now your Macros are successfully moved to the new Module.

7. Delete the old module.

2. Fix VBA error 400 by turning on the trusted access

1. Go to the Developer tab (If this is not available click File > Options > Customise Ribbon > choose the Developer checkbox).
2. In the  Code group – go to the Developer tab.
3. Click Macro Security.
4. Select Developer Macro Settings – choose Trusted access to the VBA project.

3. Check your code for mistakes to fix VBA error 400

Make sure to always check your code for any mistakes. Additionally, checking for any macros corruption is necessary as well.

That’s about it. We hope that doing these confirmed solutions will fix your VBA 400 Error. In case you have other confirmed suggestions please let us know in a comment below.

This article will demonstrate how to fix Excel VBA Error 400.

Error 400 occurs while a VBA macro is running, usually causing the macro to crash and stop running. It is a very annoying error that can be quite difficult to resolve but by following the methods below you can hopefully solve your problem!

Ensure that VBA Projects are Trusted

First, that your VBA Projects are trusted.   To ensure that they are, follow these steps:

In the Ribbon, select File > Options and then (1) select Trust Center and (2) Trust Center Settings.

OR

In the Ribbon, select Developer > Macro Security.

Note: If you don’t see the Developer Ribbon, you’ll need to enable it.

Then,  select (1) Macro Settings. Ensure that (2) Trust access to the VBA project object model is checked and then (3) click OK.

Transfer Your Code to a New Module

If you still encounter the error, the issue might be a corrupt code module.

To ensure that the code module is not corrupt, transfer your code to a new module and then delete the old module.

1) To insert a new module, we need to open the Visual Basic Editor (VBE).

In the Ribbon, select Developer > Visual Basic.

Or Press Alt+F11 to switch to the VBE.

2) Make sure you have the correct VBA Project selected and then, in the Menu, select Insert > Module.

3) Click in the original module and press CTRL + A on they keyboard to select all the code. Then copy the code (CTRL + C or right-click and select COPY)

4) Click in your new module and Paste the copied code into this module (CTRL + V).

5) Then right-click on your original module (in this case Module1), and select Remove Module1 to remove it from your VBA project.

Or in the Menu, select File > Remove Module1.

6) Save your workbook to keep your changes.

Debug and Review the VBA Code

An Error 400 could also occur due to incorrect VBA code. The VBA Editor contains a variety of debugging tools. A powerful one is the ability to step through your code.

Click in the procedure that you wish to debug and then, in the Menu, select Debug > Step Into  OR press F8 on the keyboard.

The procedure name will then be highlighted in yellow indicating that you are now in Debug mode.

You can then go through your code line by line by pressing F8 each time to move down to the next line.

By debugging your code in this manner, you can check that it all runs correctly and does not raise the error 400

Repair or Reinstall Microsoft Office

If all of the above suggestions do not help, you may unfortunately have to repair or reinstall Microsoft Office as you may have a corrupt installation or corrupt registry entry.

First, try repairing the installed version of Office.

In the Settings App in your PC, select Apps > Apps and Features and then scroll down the list until your find your installation of Microsoft Office.

Click on the Modify button, select Online Repair or Quick Repair and then click Repair.

Re-boot your PC and try your macro again.

If this does not help, you may have to remove Microsoft Office from your machine by uninstalling it and then re-install it.

Check Your System for Malware or Viruses

Finally, do make sure that your system is checked for both Malware and Viruses as these can also lead to this Error 400.

Уважаемые формучане, появилась ошибка 400 при работе макроса. Макрос делит один большой файл с кучей информации на много(14) файлов по необходимым параметрам и сохраняет в указанную папку. Прдеположительно, на втором шаге происходит ошибка. Но вроде все там в порядке. С чем может быть свзяана ошибка? Гугл фактически не дал ответа((

П.С Второй шаг прилагается
Step2:

Set wb3 = Workbooks.Add
For i = 2 To k
Branch(i) = wb1.Sheets(1).Cells(i, 3).Value
Channel(i) = wb1.Sheets(1).Cells(i, 4).Value
wb2.Sheets(1).Activate
wb2.Sheets(1).Range(«A1:BR50000» .Select
Selection.AutoFilter Field:=bran_fil, Criteria1:=(«*» & Branch(i) & «*»
Selection.AutoFilter Field:=chan_fil, Criteria1:=(«*» & Channel(i) & «*»

If i = 2 Then
Range(«A1:BR50000» .Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
wb3.Sheets(1).Activate
Range(«a1» .Select
Selection.PasteSpecial (-4104)
Selection.AutoFilter
Selection.EntireColumn.AutoFit
ActiveWindow.Zoom = 85
With Range(«1:1» .Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark2
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Else
Range(«A2:BR50000» .Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
wb3.Sheets(1).Activate
For m = 2 To 50000
If Cells(m, 1) = «» Then
f = m
GoTo Step3
End If
Next m

In Excel, Visual Basic for Applications (VBA) is used to automate tasks. Sometimes, while working with VBA codes, you may encounter several errors. One such error is the runtime error 400. It usually occurs when your Excel fails to find the VBA code or you try to use an object in VBA code that has been deleted or inaccessible. It can also occur if you mistakenly put the code in the wrong module in your VBA project or the macro you are running is corrupt.

Causes of VBA Runtime Error 400 in Excel

There are a variety of reasons that can lead to the VBA error 400. Some of them are cited below:

• Incorrect registry entries.
• There is an issue with the module in which you are running the macro.
• The macros you are trying to run contain an incorrect parameter or argument.
• Macros in the Excel file are corrupt.
• Accessing an object in workbook that does not exist.
• Incorrect installation of Microsoft Excel.
• Important Excel-related files are corrupted.
• Outdated Microsoft Excel version.

Methods to Fix the VBA Error 400 in MS Excel

Follow the below-mentioned solutions and workarounds to fix the runtime error 400 in Excel.

Method 1: Move the Macro to Another Module

Problematic modules can trigger the runtime error 400 in Excel. For example, you have inserted a code in the wrong module in the VBA project. If you receive the error, then there is an issue with the module. In such a case, you can move your macros to another module. Here are the steps:

• Open your Microsoft Excel application.
• Create a new module using the below steps:
  • Go to the Developer tab from the Ribbon menu.

• The Microsoft Visual Basic for Applications wizard is displayed.

• In the VBA wizard, click Insert > Module.

• Now, copy the code from the old module and paste it into the newly created module.
• Go to the File and click Save to save the code in the new module.

• Go to the old module, right-click on it, and click Remove Module to delete it.

• Now try to run the code to check whether the error is fixed or not.

Method 2: Check Invalid Input in Macro

The Excel error 400 may occur when you try to run a macro that contains an incorrect or invalid input, such as an argument or parameter. You can check your VBA code for any incorrect arguments and their specifications using Error handling. In Excel VBA, error handling is used to handle runtime errors that appear while running the program. Also, it is used to check for invalid arguments in VBA code.

Method 3: Update your Excel Application

Runtime errors, including error 400, can occur if your Microsoft Excel is not updated. So, consider updating and upgrading your Excel to the latest version. Following are the steps to update your Excel:

• Open your Excel application.
• Go to the File menu on the top-left corner of the window.
• Select Account and click on Update Options under Product information.
• Click Update Now.

Excel will check for the latest updates and download them if available.

Method 4: Check for Deleted Objects in the Code

Sometimes, the runtime error 400 can occur if an object you are calling in a code is not available, deleted, or inaccessible. You can check your VBA code for any deleted or missing objects in the macro. You can use the debug command to check for errors in the code. Debugging in Excel can help you quickly identify and fix the errors in the VBA code.

Method 5: Enable Trusted Access to VBA

You can enable the trusted access to VBA option to provide complete access to the code to run. Here are the steps to enable trusted access to VBA:

• Go to the Developer tab.
• Click Macro Security.

• In Trust Center window, select the option “Trust Access to the VBA Project object model” under the Developer Macro Settings section.
• Click OK.

Method 6: Fix the Corrupted Excel File

The runtime errors in Excel may occur if your file gets corrupted or damaged. To fix the error, you can try repairing the file using the built-in “Open and Repair” tool. Follow these steps:

• Open Excel and click on the File menu.
• Choose Open and select the corrupted Excel file.
• Click on the arrow next to the Open button and click on “Open and Repair”.
• Select the repair option that you want to use.
• Click OK.

The “Open and Repair” tool may not be able to repair the Excel file if it is severely corrupted or the extent of the damage is too high. In such a case, you can try a third-party Excel repair tool, such as Stellar Repair for Excel. It is a powerful tool that uses advanced algorithms to repair the corrupted Excel file, even if the damage is too extensive. It recovers all the objects from the Excel file with complete integrity. 

Conclusion

The VBA error 400 in Excel can occur due to various reasons, such as invalid parameters in macros, deleted objects, or corruption in Excel file. By following the solutions outlined in this post, you can resolve the VBA runtime error 400 in MS Excel. If the error persists, then you can try a reliable third-party Excel repair software, such as Stellar Repair for Excel. It can effectively repair corrupted macros in Excel by scanning the Excel file and help you resolve the issue. The tool is compatible with all Excel versions.