Function Returning Excel COM Objects Unexpectedly

Question

function xlAssignRange
{
    Param($shtSheet, [string]$strRange)

    $rngRange = $shtSheet.Range($strRange)

    $rngRange
    return
}

When I use the above function to assign an Excel COM object for a range of cells, the behavior is a bit unexpected in that this returned object is handled similar to a ForEach-Object instead of a "whole" object.

As an example:

$rngA1E10 = xlAssignRange $shtSheetOne "A1:E10"

The above code puts range A1:E10 into $rngA1E10, but if I do the following:

$rngA1E10.Select()

Each of the cells is individually selected, one at a time, ending on E10. This is not what I expected or intended to happen.

What I actually expected to happen is the functionally working equivalent here, not using the function above:

$shtSheetOne.Range("A1:E10").Select()

The above selects/highlights the entire range of A1 through E10, which is my intended behavior.

How do I fix the way that this function returns the variable so that it is handled "as a whole object," as opposed to iterating through each component of said object?

EDIT: As strictly just another example, of the same unintended behavior:

function xlCopyRange
{
    Param($strRange)

    $strRange.Copy() | Out-Null
}

Passing xlCopyRange a range of "E1:E10" would only result in the clipboard having the value of E10 after it is completed (I can see the "True" for each value as it iterates through each of the individual cells in the Range by omitting Out-Null).

Please note that these are just examples of ranges being used in functions, but not being acted on as a whole, but rather iterating through each of the cells within said range passed to a function.

Answer 1

If you want to return an array as a single object, you have two solutions according to about_Return:

1. You can use the unary array expression:

   function xlAssignRange
   {
       Param($shtSheet, [string]$strRange)
   
       $rngRange = $shtSheet.Range($strRange)
   
       return (, $rngRange)
   }
   

2. Or you can use Write-Output with the -NoEnumerate parameter:

   function xlAssignRange
   {
       Param($shtSheet, [string]$strRange)
   
       $rngRange = $shtSheet.Range($strRange)
   
       return Write-Output -NoEnumerate $rngRange
   }
   

Casting $rngRange to an array will also work in your case:

function xlAssignRange
{
    Param($shtSheet, [string]$strRange)

    [array] $rngRange = $shtSheet.Range($strRange)
    
    return $rngRange
}