having a very hard time with Excel addpicture

by

Summary

A developer encountered a Run-time error ‘450’ while attempting to insert an image into an Excel worksheet using Shapes.AddPicture. The root cause was a mismatch between the code syntax and the object model for Excel 2019, specifically regarding the property names passed to the method and the handling of the Left and Top parameters. The fix requires using the correct named arguments (FileName, LinkToFile, SaveWithDocument, Left, Top) and ensuring the target range objects are evaluated to scalar values, not object references.

Root Cause

The error “Wrong number of arguments or invalid property assignment” occurs because the AddPicture method invocation does not match the VBA object model signature. There are three specific failures in the provided code:

  1. Incorrect Named Arguments: The code uses Filename (with a lowercase ‘n’) and LinkToFile. While LinkToFile is partially correct (depending on the context of the Office.MsoTriState enumeration), the argument name for the file path must be FileName. VBA is case-insensitive for arguments, but it relies on exact matching of the defined parameter names to bind values.
  2. Object vs. Value Assignment: The block With sh .Left = xlapp.Cells(rowNum, 2) .Top = xlapp.Cells(rowNum, 2) End With is problematic. ActiveSheet.Cells(rowNum, 2) returns a Range Object. The .Left and .Top properties of a Shape expect a numeric value (Single, Double, or Currency). While VBA often attempts to coerce the Value of the object automatically, explicitly passing the Object wrapper where a primitive is expected can trigger “Invalid Property Assignment.”
  3. Ambiguity in Method Overloads: The AddPicture method has two signatures (one with Left and Top, and another starting with FileName). If the named arguments are not recognized due to typos or casing, VBA may attempt to bind to the wrong overload, resulting in an argument count mismatch.

Why This Happens in Real Systems

Excel VBA relies heavily on the COM IDispatch interface for late binding. When code runs, VBA attempts to resolve the method name and arguments at runtime.

  • Documentation Drift: Developers often copy code from forums or older versions (e.g., Excel 2003 vs. 2019). While the method logic is stable, the parameter naming conventions or specific enumerations (like msoFalse vs. False) can cause binding failures.
  • Lazy Evaluation: Passing an Object (Range) where a Primitive (Double) is expected works in some Excel contexts due to default properties. However, in strict method arguments, this often breaks because the VBA compiler cannot decide which default member of the Range object to use (.Value, .Name, etc.).
  • Silent Failures: The Dir() loop logic and array handling are fragile. If the loop passes a Null or empty string to AddPicture, the error might manifest here, though the specific “Wrong number of arguments” points to syntax.

Real-World Impact

  • Development Stagnation: The developer spends hours debugging syntax rather than logic.
  • Runtime Crashes: If the code is part of an automated batch process, this error halts execution immediately, potentially leaving the workbook in an inconsistent state.
  • Performance Bottlenecks: Even if fixed, the use of ActiveSheet and Dir() loops is inefficient for inserting hundreds of images, causing UI freezing (Excel “Not Responding”).
  • Maintainability: The code contains unused variables (readyToBreak, checkFolder logic duplication) and non-idiomatic VBA (e.g., ptrArray vs. UBound), making future maintenance difficult.

Example or Code

The following code demonstrates the correct implementation. Note the use of msoFalse and msoTrue (standard for Office.MsoTriState) and the direct extraction of .Value for positioning.

Sub InsertPicturesCorrectly()
    Dim targetFolder As String
    Dim filesName As String
    Dim fullFileName As String
    Dim rowNum As Long
    Dim sh As Shape
    Dim targetCell As Range

    ' Ensure reference to "Microsoft Office Object Library" is set
    ' or use the numeric equivalents for msoFalse/msoTrue (0, -1)

    targetFolder = "E:\0-stuff\0-visio\catalogs\"
    rowNum = 1

    filesName = Dir(targetFolder & "*.png") ' Filter for images

    Do While filesName  ""
        fullFileName = targetFolder & filesName

        ' 1. Use Named Arguments Correctly (FileName, not Filename)
        ' 2. Cast Range values to Double explicitly for safety
        Set targetCell = ActiveSheet.Cells(rowNum, 2)

        Set sh = ActiveSheet.Shapes.AddPicture( _
            FileName:=fullFileName, _
            LinkToFile:=msoFalse, _
            SaveWithDocument:=msoTrue, _
            Left:=CDbl(targetCell.Left), _
            Top:=CDbl(targetCell.Top), _
            Width:=-1, _
            Height:=-1)

        rowNum = rowNum + 1
        filesName = Dir()
    Loop
End Sub

How Senior Engineers Fix It

  • Explicit Typing: Senior engineers avoid passing Objects (Range) directly to properties expecting primitives. They explicitly use CDbl(ActiveSheet.Cells(rowNum, 2).Value) or ActiveSheet.Cells(rowNum, 2).Value to ensure the correct data type is passed.
  • Use of “With” Blocks: They wrap the worksheet operations in With ActiveSheet to reduce verbosity and improve read performance.
  • Error Handling: They implement On Error Resume Next with checks or On Error GoTo to handle cases where files are locked or missing, rather than letting the runtime crash on the first error.
  • Variable Initialization: They declare variables with specific types (e.g., Dim sh As Excel.Shape) rather than generic Shape to avoid ambiguity if other libraries are referenced.
  • Code Review of Logic: They would refactor the Dir() loop logic. The nested loops and ptrArray increment logic are fragile. They would likely use FileSystemObject (FSO) to enumerate files, which is more robust than Dir().

Why Juniors Miss It

  • IntelliSense Confusion: Juniors rely heavily on IntelliSense. However, IntelliSense often shows the method signature, but if the developer types Filename (typo), IntelliSense won’t always correct it for the argument list immediately. They assume if the text looks close, it’s correct.
  • Copy-Paste Coding: This specific error often arises from copying code from the Macro Recorder. The Macro Recorder often uses .AddPicture but hardcodes values or uses specific object references that don’t match the current context. Juniors often fail to adapt the Recorder’s output to dynamic variables.
  • Lack of Parameter Knowledge: Juniors often don’t memorize the specific order or name of arguments for complex methods like AddPicture. They try to guess (e.g., LinkToFile:=False instead of msoFalse) and get confused when standard Boolean values don’t work with specific COM enumerations.
  • Debugging Blindness: They often look at the line of code but not the values being passed. They don’t realize that Cells(...) returns an Object, not a number, until they explicitly hover over the variable or use the Immediate Window to check ?TypeName(Cells(1,2)).