Make sure you double-check your code as errors can easily creep in.
by Vlad Turiceanu
Passionate about technology, Windows, and everything that has a power button, he spent most of his time developing new skills and learning more about the tech world. Coming… read more
Updated on August 18, 2022
Reviewed by
Radu Tyrsina
Radu Tyrsina has been a Windows fan ever since he got his first PC, a Pentium III (a monster at that time). For most of the kids of… read more
- Excel Macro represents a set of commands and actions that are triggered by a VBA code.
- As handy as it has proven to be, VBA is no stranger to errors especially if you write the code yourself.
- The VBA error 400 is quite common and usually appears if your Macro fails or crashes whilst it’s running.
- Don’t worry, we have prepared 3 easy fixes for you to try in order to remove this pesky error for good.
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
- Go to the Developer tab (If this is not available click File > Options > Customise Ribbon > choose the Developer checkbox).
- In the Code group – go to the Developer tab.
- Click Macro Security.
- Select Developer Macro Settings – choose Trusted access to the VBA project.
- MegaStat for Excel: How to Download & Install
- Runtime Error 13: Type Mismatch [Fix]
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.
Still having issues? Fix them with this tool:
SPONSORED
If the advices above haven’t solved your issue, your PC may experience deeper Windows problems. We recommend downloading this PC Repair tool (rated Great on TrustPilot.com) to easily address them. After installation, simply click the Start Scan button and then press on Repair All.
we are getting an 400/bad Request when we send a request. Please find our code below.
Public Sub test123()
URL = "http://dev1.xxxxx.employer/employer/v02/Employer.svc"
Dim requestDoc
Set requestDoc = CreateObject("MSXML2.DOMDocument.3.0")
Dim root
Set root = requestDoc.createNode(1, "Envelope", "http://schemas.xmlsoap.org/soap/envelope/")
requestDoc.appendChild root
Dim nodeBody
Set nodeBody = requestDoc.createNode(1, "Body", "http://schemas.xmlsoap.org/soap/envelope/")
root.appendChild nodeBody
Dim nodeOp
Set nodeOp = requestDoc.createNode(1, "Register", "http://xxxxx.com/Employer/Contracts/v2")
nodeBody.appendChild nodeOp
Dim nodeRequest
Set nodeRequest = requestDoc.createNode(1, "request", "http://xxxxx.com/Employer/Contracts/v2")
'content of the request will vary depending on the WCF Service.'
' This one takes just a plain string. '
Dim sv_strTempxml As Variant
sv_strTempxml1 = "<CreateProspectRequest xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">" _
& "<Employer><PartyDisplayName>0707 NS2 DEV1 KRISH 001</PartyDisplayName><PreferredLanguageIdentifier xsi:nil=""true"" />" _
& "<PartyIdentifier xsi:nil=""true"" /><PartyAddresses><PartyAddressStructure><BeginTimeStamp xsi:nil=""true"" /><CityName>Alexander City</CityName>" _
& "<CountryCode>US</CountryCode><EndTimeStamp xsi:nil=""true"" /><FirstLineAddress>111 Main Street</FirstLineAddress><PostalCode>35010</PostalCode>" _
& "<SecondJurisdictionCode>AL</SecondJurisdictionCode><SecondJurisdictionTypeCode>ST</SecondJurisdictionTypeCode><SecondLineAddress>PL</SecondLineAddress><AddressIdentifier xsi:nil=""true"" />" _
& "<PartyIdentifier xsi:nil=""true"" /><AddressUsageIdentifier>100000</AddressUsageIdentifier><SecondJurisdiction>1</SecondJurisdiction><ChangeTypeCode xsi:nil=""true"" />" _
& "</PartyAddressStructure></PartyAddresses><PreferredLanguage>100</PreferredLanguage><ChangeTypeCode xsi:nil=""true"" /><PartyTypeIdentifier xsi:nil=""true"" />" _
& "<EstablishedDate xsi:nil=""true"" /><OrganizationTypeIdentifier>100018</OrganizationTypeIdentifier><OrganizationNames><OrganizationNameStructure>" _
& "<NameEndTimestamp xsi:nil=""true"" /><NameStartTimestamp xsi:nil=""true"" /><OrganizationPartyName>0707 NS2 DEV1 KRISH 001</OrganizationPartyName><NameTypeIdentifier>1</NameTypeIdentifier>" _
& "<PartyIdentifier xsi:nil=""true"" /><ChangeTypeCode xsi:nil=""true"" /></OrganizationNameStructure></OrganizationNames><ProspectReceivedDate xsi:nil=""true"" />" _
& "<RatingGroupIdentifier xsi:nil=""true"" /></Employer><AffiliationCode>UUS</AffiliationCode></CreateProspectRequest>"
nodeRequest.Text = sv_strTempxml1
nodeOp.appendChild nodeRequest
Set nodeRequest = Nothing
Set nodeOp = Nothing
Set nodeBody = Nothing
Set root = Nothing
'MsgBox "sending request " & vbCrLf & requestDoc.XML
Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP.3.0")
' set the proxy as necessary and desired '
'xmlhttp.setProxy 2, "http://localhost:8888"
xmlhttp.Open "POST", URL, False
' set SOAPAction as appropriate for the operation '
xmlhttp.setRequestHeader "SOAPAction", "http://xxxxx.com/Employer/Contracts/v2/Employer/CreateProspect"
xmlhttp.setRequestHeader "Content-Type", "application/soap+xml; charset=utf-8"
xmlhttp.send requestDoc.XML
' vbCrLf & "Raw XML response:" & vbCrLf
MsgBox xmlhttp.responseXML.XML
End Sub
is there any item we have missed out
Santhosh
Graham Clark
12.9k8 gold badges50 silver badges81 bronze badges
asked Sep 2, 2010 at 10:24
What binding(s) is your WCF service exposed over? If you’re using the WsHttpBinding, you’re probably going to have to use https.
If the service is using a BasicHttpBinding, then http should be fine, but you’ll need to change the Content-Type to text/xml.
Update
The reason I say this is that I’ve used Visual Studio WebTests to send SOAP XML to WCF services. When accessing Basic HTTP endpoints, it only works if I use text/xml. Maybe you need to put the SOAP Header stuff in the XML. Here’s what I’ve used successfully in the past:
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:a="http://www.w3.org/2005/08/addressing">
<s:Header>
<a:Action s:mustUnderstand="1">[ACTION]</a:Action>
<a:To s:mustUnderstand="1">[ADDRESS]</a:To>
</s:Header>
<s:Body>
...
</s:Body>
</s:Envelope>
answered Sep 2, 2010 at 11:07
Graham ClarkGraham Clark
12.9k8 gold badges50 silver badges81 bronze badges
2
Последнее обновление Ноя 12, 2021
Некоторые пользователи, похоже, получают ошибку VBA 400 при попытке запустить свой макрос в Excel. Дальнейших указаний нет, только окошко с кнопкой ОК.
В любом случае, не волнуйтесь, у нас есть для вас несколько простых решений. Мы предлагаем вам выполнить шаги, упомянутые в этой статье, в указанном порядке. Надеюсь, они помогут вам решить вашу проблему.
Как исправить ошибку 400 в Excel?
1 Переместите ваши макросы в новый модуль
- Чтобы создать новый модуль, перейдите в меню «Инструменты».
- Щелкните Макрос – редактор Visual Basic.
- В VBA щелкните ” Модуль” в меню “Вставка”.
- Вставьте код VBA, который хотите использовать.
- В меню «Файл» выберите «Сохранить глобально».
- Обязательно удалите старый модуль.
2 Включите доверенный доступ к VBA
- Перейдите на вкладку «Разработчик» (если она недоступна, нажмите «Файл» > «Параметры» > «Настроить ленту»> установите флажок «Разработчик» ).
- В группе Код – перейдите на вкладку Разработчик.
- Щелкните Безопасность макросов.
- Выберите «Параметры макроса разработчика» – выберите «Надежный доступ к проекту VBA».
3 Проверьте свой код
Обязательно всегда проверяйте свой код на наличие ошибок. Кроме того, также необходима проверка на наличие повреждений макросов.
Вот об этом. Мы надеемся, что это подтвержденное решение исправит вашу ошибку VBA 400. Если у вас есть другие подтвержденные предложения, сообщите нам об этом в комментариях ниже.
Источник записи: windowsreport.com
Introduction
It is often said that debugging is part of programming, and through error messages, the programmer can easily identify where there are problems in his/her code. But as you can see from the above screenshot the VBA Error 400 is one of those annoying types of error that does not give you any information on the origin of the bug of your code.
You only know that there is an error because you see the red cross on the message box with the number 400 referring to the error number. If as a VBA coder you have come across the VBA error 400 and do not know what to do, then this article is for you.
In the following article, we shall see the possible causes of this error (there are many of them) and how to fix them.
What Are the Causes of VBA Error 400?
Excel error 400 occurs when Microsoft Excel fails or crashes while running. This can be caused by many factors. The following are the most common:
- Infected download or deficient installation of Microsoft Excel software
- Invalid registry entries
- Windows system files or Microsoft Excel-related program files infected by malware or a virus
- Microsoft Excel-connected files maliciously or wrongly deleted by another program
- Error in a macro; or the macro that you are trying to run is corrupt
- Failure to read or write from a file or trying to get access to an object that does not exist
- Attack on the device or disk storage system including the MS Excel related data files and folders by malware or virus
Note: VBA 400 error messages can arise in any of the following Microsoft Windows operating systems: Windows 10, Windows 8, Windows 7, Windows Vista, Windows XP, Windows ME, Windows 2000.
How to Fix Microsoft Excel Error 400
The VBA 400 error message does not give any indication on the origin of the error. Consequently, you might have to try one or a combination of the methods below to fix the problem. However, I strongly advise you to check your macro very well to be certain that the error is not coming from the code itself before attempting any fix.
Transfer macros to a new module
Step 1: Select the developer tab on the Excel menu and click on the Visual Basic option or Click on Alt + F11 simultaneously.
Step 2: Select the Insert tab of the VBA Editor and click on the Module option or click on Tools > Macro then name the macro and press Enter
Step 3: Paste the VBA code that you want to use.
Step 4: Navigate to the File menu and click on Save Global.
Note: Make sure to delete the old module.
Switch on Trusted Access to VBA
Step 1: Select the Developer tab on the Excel menu, go to the Code group and click on Macro Security.
Step 2: Under “Developer Macro Settings” check “Trusted Access to the VBA project.”
Other More Advanced Methods
If the solutions above have not solved your problem, then you should resolve to try the ones below.
Warning: If you are not versed in registry manipulation and related activities, I suggest you work with an expert on these solutions.
- Scan your device to remove harmful malware and viruses from the system.
- Remove system junk files and corrupted cache data.
- Next, update the PC with the latest system drivers.
- Try to “undo” all the recently made changes to the device through Windows System Restore files.
- Uninstall each suspicious application, including the corrupted MS Excel app. Then, reinstall a fresh version.
- Next, run the Windows System File Checker (SFC) scan to detect and delete corrupted system files.
- Now, install the latest Windows OS update.
- Finally, run a clean installation of Windows.
Automatic Solution
Beside the manual solutions explained above, there is also an automatic solution called the MS Excel Repair Tool. It is a highly recommended and one of the most appropriate tools to repair all sort of issues, corruption, errors in Excel workbooks. Just by scanning once, the tool detects and fixes various errors in Excel file and repair and recover damaged, corrupted or even inaccessible Excel file as well.
It allows you to easily restore all corrupt Excel file including the charts, worksheet properties, cell comments, and other important data. This is a unique tool to repair multiple Excel files in one repair cycle and recovers the entire data in a preferred location. It is easy to use and compatible with both Windows (http://www.repairmsexcel.com/blog/free-download-for-win) as well as Mac operating system (http://www.repairmsexcel.com/blog/free-download-for-mac ).
In this Article
- Ensure that VBA Projects are Trusted
- Transfer Your Code to a New Module
- Debug and Review the VBA Code
- Repair or Reinstall Microsoft Office
- Check Your System for Malware or Viruses
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.
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Learn More!