在VBA功能中从范围切换到阵列并返回 [英] Switching from Range to Array and Back in a VBA Function

问题描述

关于VBA中的范围/数组转换，有很多问题和很多答案。我一直找不到合适的答案，所以我会很感激您。

There are a lot of questions, and a lot of responses dealing with Range/Array conversion in VBA. I haven't been able to find an answer that works, so I would really apreciate some help.

以下是我要尝试做的事情：

Below is what I'm trying to do:

    Function RangeToArrayToRange(inputRange As Range) As Range
        Dim inputArray As Variant
        inputArray = inputRange
        'operations on inputArray'
        '...'
        Dim outputRange As Range
        outputRange = inputArray
        Set RangeToArrayToRange = outputRange
    End Function

提前谢谢您的帮助！

推荐答案

如果outputRange是要填充的范围的左上角单元格：

If outputRange is the top-left cell of the range to be populated:

outputRange.Resize(Ubound(inputArray,1), _
                   Ubound(inputArray,2)).Value = inputArray

编辑：我想这就是你想要做的

EDIT: I think this is what you want to do

Function RangeToArray(inputRange As Range) As Variant
   Dim inputArray As Variant
   inputArray = inputRange.Value

   'operations on inputArray
   '...'

   RangeToArray = inputArray
End Function

您可以在工作表上将其用作用户定义函数（UDF）

You can use this on a worksheet as a user-defined function (UDF)

1. 选择与 inputRange（相同的行数/列数）相同尺寸的范围


2. 在公式栏中输入 = RangeToArray（[yourinputrange]），然后按Ctrl + Shift + Enter将公式作为数组公式输入

这假定您没有在函数中更改 inputArray 的尺寸（上下边界）。

This assumes you're not altering the dimensions (upper/lower bounds) of inputArray in your function.

这篇关于在VBA功能中从范围切换到阵列并返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！