PowerShell Excel Interop - InvalidCastException, but only from VS Code

Question

I have a large PowerShell script that automates the creation of an Excel workbook from scratch. For months now, I have been unable to resolve one issue - but only when run from Visual Studio Code. To simplify this to a minimal test case:

    $ErrorActionPreference = 'Stop'
    
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $true
    $wb = $excel.Workbooks.Add()
    
    $wb.Worksheets('Sheet1').Range('C2:C5').Value2 = 'abc'
    $arr1 = $wb.Worksheets('Sheet1').Range('C2:C5').Value2
    
    # Try a reflexive test first.  This seems to always fail whenever the below does.
    $wb.Worksheets('Sheet1').Range('C2:C5').Value2 = $arr1
    
    # Assuming we made it that far, try to overwrite with "x0, x1, x2, x3"...
    
    $arr2 = [object[,]]::new(4, 1)
    for($i = 0; $i -lt 4; $i++){
        $arr2[$i, 0] = ('x' + $i)
    }
    
    $wb.Worksheets('Sheet1').Range('C2:C5').Value2 = $arr2

The typical failure presents as:

    Specified cast is not valid.
    At line:11 char:1
    + $wb.Worksheets('Sheet1').Range('C2:C5').Value2 = $arr1
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : OperationStopped: (:) [], InvalidCastException
        + FullyQualifiedErrorId : System.InvalidCastException

I'm more than familiar with a class cast exception, and this is not one - at least not from the code written and shown. I could see that this could be an exception from some interop or system code itself, but am then struggling to see the details of what or where that is.

I can work-around this issue by assigning the values one cell at a time, but this is terribly less efficient - especially for assigning hundreds or even thousands of cells at once. The code certainly works in some environments, just not others - and it is representative of code samples over 10 years old (though typically without the PowerShell or interop components), so it isn't really anything "new" or bleeding-edge.

This issue sometimes seems to come and go. However, it is seen by multiple users across multiple devices in multiple environments, separately maintained. All that is effectively required here is PowerShell + Excel on a currently-supported Windows OS.

Following are some details of where I'm currently seeing this tonight:

• Windows 11 Professional, Version 22H2, OS Build 22598.100 (Insider)
  • Also reproducible under Windows 10.
• Excel from Office 365, Version 2203 Build 15028.20204 / 16.0.15028.20178 64-bit.
• $PSVersionTable

    Name                           Value
    ----                           -----
    PSVersion                      5.1.22598.1
    PSEdition                      Desktop
    PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
    BuildVersion                   10.0.22598.1
    CLRVersion                     4.0.30319.42000
    WSManStackVersion              3.0
    PSRemotingProtocolVersion      2.3
    SerializationVersion           1.1.0.1

• Visual Studio Code:

    Version: 1.66.2 (user setup)
    Commit: dfd34e8260c270da74b5c2d86d61aee4b6d56977
    Date: 2022-04-11T07:46:01.075Z
    Electron: 17.2.0
    Chromium: 98.0.4758.109
    Node.js: 16.13.0
    V8: 9.8.177.11-electron.0
    OS: Windows_NT x64 10.0.22598

Tests:

Environment	PowerShell Version	Result
Visual Studio Code	5.1	Fails
Visual Studio Code	7.2	Success
Windows PowerShell ISE	5.1	Success
Windows PowerShell / Windows Terminal	5.1	Success

I repeated the same 3+ times each with consistent results.

I recalled in the past killing the terminal within VS Code (clicking the trash can icon to kill the "PowerShell Integrated Console") and then re-launching it when prompted. Sure enough, that has again - but temporarily - resolved the issue, now allowing for a successful run of both the minimal test case and the entire script from VS Code under PS 5.1, even though I started from a clean session for the above tests. Restarting VS Code now, I'm no longer able to reproduce the issue - but am sure the problem will reappear after restarting Windows or another set of circumstances I've yet to identify.

In short, what is causing this array assignment to sporadically fail? What else can I check that may be altering how PowerShell runs from within the VS Code terminal than elsewhere?