Fix Excel VBA Run time Error 1004 In Microsoft Excel

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’.


ADVERTISEMENT

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.

 

Ms Excel File Options

 

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.

 

Excel Options Trust Center 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.

 

Macro Settings Disable All Macros With Notification Trust Access To The Vba Project Object Mode Ok


ADVERTISEMENT

 

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.

 

Ms Excel Developer Tab Insert Activex Controls Command Button

 

Step 2: Now, anywhere in the Excel sheet, drag and draw a Command Button. This will be CommandButton1.

 

Empty Area Drag And Draw A Command Button

 

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.

 

Microsoft Visual Basic For Applications Window Write Command Enter

 

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.

 

Excel Sheet Design Mode Commandbutton1 Total Auto Populates

 

Step 5: If you enter the VBA code incorrectly, it will give you the VBA runtime error 1004.

 

If You Enter The Vba Code Wrong Vba Run Time 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.


ADVERTISEMENT


 

Excel Sheet File Tab

 

Step 2: Now, on the right-side of the workbook, under the Recent section, delete all the excel sheets except one.

 

File Tab Right Side Recent Delete All Files 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.

 

File Tab Save As Select Location Save As Type Excel Template File Name .xltx Or .xlt Format Save

 

*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

 


ADVERTISEMENT

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.

 

This Pc C Drive

 

Step 2: Now, follow the path as shown below to reach the Excel folder:

 

C:\Users\"username"\AppData\Local\Microsoft\Excel

This Pc C Drive Follow The Path Excel Xlstart

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.

ADVERTISEMENT