Whether you are creating an Excel report for your own use or for a business, it comes in two formats, XLS and XLSX. When these formats get corrupted, you see the VBA Run-time error ‘1004’.
This error can also occur when you are trying to create a Macro in an Excel sheet, thus, preventing you to work with the Excel. While this can prevent you to work on VBA, it can also lead the VBA to crash, and even your system at times.
The good news is, this can be fixed using a couple of methods. Let’s see how.
Method 1: Through Trust Center Settings
Step 1: Open MS Excel and go to the File tab. Now, Click on Options in the menu.
Step 2: In the Excel Options window, click on Trust Center on the left side of the pane. On the right side, click on the Trust Center Settings.
Step 3: In the Trust Center window, click on the Macro Settings options on the left. Now, on the right side of the pane, select the radio button next to Disable all macros with notification.
Then, select the radio button next to Trust access to the VBA project access mode.
Press OK to save the changes. Then press OK again in the Excel Options window to exit.
This method should fix your VBA runtime error issue.
Method 1: Using the Developer Tab in MS Excel
Step 1: Open the Excel sheet with which you are facing the issue, and click on the Developer tab.
Now, click on the Insert option below and under the ActiveX Controls section, select the Command Button.
Step 2: Now, anywhere in the Excel sheet, drag and draw a Command Button. This will be CommandButton1.
Step 3: Double-click on the CommandButton1 to be able to write the VBA code in the Microsoft Visual Basic for Applications box that opens.
Now, type the below 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 highlighted parts with your actual sheet name, row number and the column number.
Step 4: Now, go back to the Excel sheet (Sheet1 in this case) and click on the Design Mode option in the Toolbar to undo it.
Now, click on the CommandButton1 in the sheet to generate the result.
Step 5: If you enter the VBA code incorrectly, it will give you the VBA runtime error 1004.
So, this should help you understand why you get the Run-time error 1004 and help you avoid it using the correct VBA code and pattern. But, if problem persists, try the 2nd method.
Method 2: By Creating A New Excel Template
Step 1: Open Microsoft Excel and press the Ctrl+N keys together on your keyboard to open a new workbook.
Now, go to the File tab on the top left.
Step 2: Now, on the right-side of the workbook, under the Recent section, delete all the excel sheets except one.
Format this sheet that you have left as per your requirement.
Step 3: Once completed, go to the File tab again and click on Save As.
Select the location where you want o save the file. Now, set the Save as type field as Excel Template. Make sure you save the file either in .xltx or .xlt format.
*Note – The format used above is for Excel 2017 and above versions.
Step 4: Once you have saved the template, you can add the template using the below code:
Add Type:=pathfilename
Make sure you replace the filename with your saved document name.
This should resolve your VBA runtime error 1004 issue, but if not, then try the 3rd method.
Method 4: By Deleting the GWXL97.XLA File
Step 1: Press the Windows key + E together on your keyboard to open the File Explorer. Now, click on This PC on the left and then on the C drive on the right side.
Step 2: Now, follow the path as shown below to reach the Excel folder:
C:\Users\"username"\AppData\Local\Microsoft\Excel
Now, open the XLStart folder.
*Note – replace the highlighted part with your specific user name without the quotation marks.
Step 3: You will now see a file named – GWXL97.XLA. Select this file and hit Delete.
That’s all. Following these methods can help you fix the VBA run-time error 1004 in your Windows 10 PC.