[Excel VBA] Common Errors and How to Fix Them

2025年12月30日

Overview

When developing programs using Excel VBA, you may encounter various errors. In this article, I will introduce errors I have actually experienced, along with their causes, explanations, and how to fix them.

Runtime Error 1004: Method ‘Copy’ of object failed

Cause and Explanation

This error occurs when the Copy method of an Excel object is not executed correctly. Specifically, it happens when trying to refer to an object that does not exist or when the selection range is invalid. For example, it occurs if the specified chart object cannot be found or if the target range is not specified correctly.

Solution

Check if the object is referenced correctly. Check if the selection range is appropriate.

' Correction example: Check if chartObj1 is set correctly
Set chartObj1 = ws.ChartObjects("Mori")
chartObj1.Chart.ChartArea.Copy

Runtime Error -2147024809 (80070057): The specified value is out of bounds

Cause and Explanation

This error occurs when an invalid parameter is specified. This happens when referring to an invalid constant value or a non-existent property. For example, using an invalid constant or trying to set a property that does not exist will trigger this.

Solution

Check if constants and properties are set correctly. Check if the value used is within the appropriate range.

' Correction example: Set text alignment correctly
.TextFrame.TextRange.ParagraphFormat.Alignment = 1 ' 1 is ppAlignLeft
.TextFrame.VerticalAnchor = 1 ' 1 is msoAnchorTop

Errors with GetObject or CreateObject for PowerPoint Application

Cause and Explanation

This error occurs when trying to get or create a new instance of the PowerPoint application. It happens if PowerPoint is not installed or cannot be launched properly.

Solution

Confirm that PowerPoint is installed. Check the usage of GetObject and CreateObject.

' Correction example: Get PowerPoint application
On Error Resume Next
Set pptApp = GetObject(Class:="PowerPoint.Application")
If pptApp Is Nothing Then
    Set pptApp = CreateObject(Class:="PowerPoint.Application")
End If
On Error GoTo 0

Error with PasteSpecial Method

Cause and Explanation

This occurs when the PasteSpecial method is not executed correctly. Possible causes include no data existing in the clipboard or an invalid data type being specified.

Solution

Check if appropriate data exists in the clipboard. Specify a valid data type.

' Correction example: Specify data type correctly
pptSlide.Shapes.PasteSpecial DataType:=2 ' 2 is ppPasteEnhancedMetafile

Summary

I have introduced common errors encountered when creating programs in Excel VBA and how to deal with them. When an error occurs, it is important to identify the cause and take appropriate measures. By keeping these points in mind, you will be able to resolve errors quickly and create programs efficiently.