Whether you're creating an Excel report for personal or business use, it comes in two formats, XLS and XLSX. When these formats get corrupted, you will see the file VBA runtime error "1004".
This error can also occur when you try to create a macro on an Excel sheet, preventing you from working with Excel. While this may stop you from working in VBA, it can also cause VBA and even your system to crash sometimes.
The good news is that this can be fixed using a couple of methods. Let's see how.
Method 1: use the Developer tab in MS Excel
Step 1: Open the Stand out sheet with which you are dealing with the problem and click on the button Developer language.
Now, click on the file To insert option below and below ActiveX controls select the section Command button.
Step 2: Now, anywhere in the file Stand out sheet, drag and draw a file Command button. This will be CommandButton1.
Step 3: Double click on the file CommandButton1 in order to write the file VBA code a Microsoft Visual Basic for Applications box that opens.
Now write the following VBA code as shown:
Dim a As Integer Dim b As Integer a = Worksheets("Sheet Name").Cells(Row number, Column number).Value b = Worksheets("Sheet Name").Cells(Row number, Column number)).Value Worksheets("Sheet Name").Cells(Row number, Column number)).Value = a + b
* Note - Make sure to replace the highlights with the actual sheet name, row number, and column number.
Step 4: Now go back to Excel sheet (Sheet1 in this case) and click Layout mode option in toolbar to cancel it.
Now click on the file CommandButton1 on the sheet to generate the result.
Step 5: If you insert the VBA code incorrectly, it will give you the VBA runtime error 1004.
So this should help you understand why you get the extension Runtime error 1004 and help you avoid it by using the VBA code and model. But if the problem persists, try the second method.
Method 2: creating a new Excel template
Step 1: Open Microsoft Excel and press the button Ctrl + N keys together on the keyboard to open a new workbook.
Now go to archive top left tab.
Step 2: Now, on the right side of the workbook, under the file Recent section, deletes all but one Excel sheet.
Please format this remaining sheet according to your needs.
Step 3: Once completed, go to archive tab again and click Save eat.
Select the location where you want to save the file. Now, set up the file Save as type similar field Excel template. Make sure you save the file in .xltx o .xlt Format.
* Note - The format used above is for Excel 2017 and earlier versions.
Step 4: After saving the template, you can add the template using the following code:
Make sure you replace the file name with the name of the saved document.
This should fix your VBA runtime error 1004 problem, but if not, then try the third method.
Method 3: via Trust Center settings
Step 1: Open MS Excel and go to archive language. Now click options on the menu.
Step 2: A Excel options window, click Trust Center on the left side of the panel. On the right side, click Trust Center Settings.
Step 3: A Trust Center window, click Macro settings options on the left. Now, on the right side of the panel, select the radio button next to deactivate all macros with notification.
Then select the radio button next to Trust access to VBA project access mode.
print ok to save the changes. Then press ok back into Excel options window to exit.
This method should fix your VBA runtime error problem. A little bit, if the problem persists, try the fourth method.
Method 4: delete the GWXL97.XLA file
Step 1: press the Windows key + E together on the keyboard to open the file Browser in files. Now click What PC left and then a Unit C On the right side.
Step 2: Now follow the path as shown below to reach the Stand out binder:
Now open the XLStart folder.
* Note - replace the highlighted part with your specific username without quotes.
Step 3: You will now see a file called - GWXL97.XLA. Select this file and press To remove.
That's all. Following these methods can help you fix VBA runtime error 1004 on your Windows 10 PC.