excelpowershelloutput

Why does Range.BorderAround emit "True" to the console?


Using BorderAround emits "True" to the console.

$range = $sum_wksht.Range('B{0}:G{0}' -f ($crow))
$range.BorderAround(1, -4138)

This can be overcome by using one of the following.

$wasted = $range.BorderAround(1, -4138)
[void]$range.BorderAround(1, -4138)

Why is this needed? Am I not creating the range correctly? Is there a better workaround?


Solution

  • Why is this needed?

    It is needed, because the BorderAround method has a return value and, in PowerShell, any command or expression that outputs (returns) data is implicitly output to the (success) output stream, which by default goes to the host, which is typically the console window (terminal) in which a PowerShell session runs.

    That is, the data shows in the console/terminal, unless it is:

    or any combination thereof.

    That is:

    $range.BorderAround(1, -4138)
    

    is (more efficient) shorthand for:

    Write-Output $range.BorderAround(1, -4138)
    

    (Explicit use of Write-Output is rarely needed.)

    Since you don't want that output, you must suppress it, for which you have several options:

    $null = ... may be the best overall choice, because:

    Conversely, avoid ... | Out-Null, because it is generally much slower (except in the edge case of a side effect-free expression's output in PowerShell (Core) 6+)[1].

    However, if you need to silence all output streams - not just the success output, but also errors, verbose output, ... - you must use *> $null


    Why does PowerShell produce output implicitly?

    Example:

    # Define a function that takes an array of integers and
    # outputs their hex representation (e.g., '0xa' for decimal 10)
    function Get-HexNumber {
      param([int[]] $numbers)      
      foreach ($i in $numbers) {
        # Format the integer at hand 
        # *and implicitly output it*.
        '0x{0}' -f $i.ToString('x')
      }
    }
    
    # Call the function with integers 0 to 16 and loop over the
    # results, sleeping 1 second between numbers.
    Get-HexNumber (0..16) | ForEach-Object { "[$_]"; Start-Sleep 1 }
    

    The above yields the following:

    [0x0]
    # 1-second pause
    [0x1]
    # 1-second pause
    [0x2]
    ...
    [0x10]
    

    This demonstrates the streaming aspect of the behavior: Get-HexNumber's output is available to the ForEach-Object cmdlet call as it is being produced, and not only until after Get-HexNumber has exited.


    [1] In PowerShell (Core) 6+, Out-Null has an optimization if the only preceding pipeline segment is a side effect-free expression rather than a method or command call; e.g., 1..1e6 | Out-Null executes in almost no time, because the expression is seemingly not even executed. However, such a scenario is atypical, and the functionally equivalent Write-Output (1..1e6) | Out-Null takes a long time to run, much longer than $null = Write-Output (1..1e6).