如何将Variant数组转换为Range? [英] How to convert a Variant array to a Range?

问题描述

我有一个Variant类型的2D数组.填充数组的大小和值是基于工作表中的数据生成的.此数组需要进一步处理，主要是几个值的插值.我正在使用插值函数(我知道excel等效函数，但做出了不使用它们的设计选择.我遇到的问题是插值函数需要一个Range对象.

I have a 2D array of type Variant. The size and values that populate the array are generated based on data within a worksheet. Further processing is required on this array, the primary being the interpolation of several values. I am using this interpolation function (I know about excel equivalent functions but a design choice was made not to use them) . The problem I am having is the that the Interpolation function requires a Range object.

我已经尝试过修改该函数以使用Variant(r as Variant)参数.以下行nR = r.Rows.Count可以替换为nR = Ubound(r).在此可行的同时，我也想在任何工作表中正常使用此功能，而不以任何方式更改该功能.

I have already tried modifying the function to use a Variant (r as Variant) argument. The following line nR = r.Rows.Count can be replaced with nR = Ubound(r). While this works, I would also like to use this function normally within any worksheet and not change the function in any way.

Sub DTOP()
    Dim term_ref() As Variant
    ' snip '
    ReDim term_ref(1 To zeroRange.count, 1 To 2)

    ' values added to term_ref '

    ' need to interpolate x1 for calculated y1 '
    x1 = Common.Linterp(term_ref, y1) 
End Sub

插值函数

Function Linterp(r As Range, x As Double) As Double
    Dim lR As Long, l1 As Long, l2 As Long
    Dim nR As Long

    nR = r.Rows.Count
    ' snipped for brevity ' 
End Function

如何将内存中的变量数组转换为Range，以便可以将其用于插值函数? (不输出到WorkSheet)

How do I convert my in-memory variant array to a Range so that it can be used for the interpolate function? (without outputting to a WorkSheet)

答案

简而言之，答案是你做不到. Range对象必须引用工作表.

In short, the answer is you can't. A Range object must reference a worksheet.

已更改的插值函数将检查参数的TypeName，并相应地设置nR的值.不是最漂亮的解决方案.

The changed interpolation function checks the TypeName of the argument and sets the value of nR accordingly. Not the prettiest solution.

请注意，在这种情况下VarType函数被证明是无用的，因为VarType(Variant())和VarType(Range)都返回了相同的值(即vbArray)，并且不能用于区分范围内的数组

As a note, the VarType function proved useless in this situation since both VarType(Variant()) and VarType(Range) returned the same value (i.e vbArray) and could not be used to disambiguate an array from a range

Function Linterp(r As Variant, x As Variant) As Double
    Dim lR As Long, l1 As Long, l2 As Long
    Dim nR As Long

    Dim inputType As String
    inputType = TypeName(r)

    ' Update based on comment from jtolle      
    If TypeOf r Is Range Then
        nR = r.Rows.Count
    Else
        nR = UBound(r) - LBound(r) 'r.Rows.Count
    End If
    ' ....
 End Function 

推荐答案

AFAIK，您无法创建不以某种方式引用工作簿位置的Range对象.它可以是动态的，例如，名为Named = OFFSET()的函数，但是它必须在某个地方绑定到工作表.

AFAIK, you can't create a Range object that doesn't in some way reference a worksheet location your workbook. It can be something dynamic, liked a Named =OFFSET() function, for example, but it has to tie back to a worksheet somewhere.

为什么不更改插值功能?保持您的Linterp签名不变，但将其放入包装在数组中的函数的包装器中.

Why not change the interpolation function? Keep your Linterp signature as is, but make it into a wrapper for a function that interpolates on an array.

类似这样的东西:

Function Linterp(rng As Range, x As Double) As Double
' R is a two-column range containing known x, known y
' This is now just a wrapper function, extracting the range values into a variant
    Linterp = ArrayInterp(rng.Value, x)

End Function

Function ArrayInterp(r As Variant, x As Double) As Double

Dim lR As Long
Dim l1 As Long, l2 As Long
Dim nR As Long

    nR = UBound(r) ' assumes arrays are all 1-based

    If nR = 1 Then
        ' code as given would return 0, better would be to either return
        ' the only y-value we have (assuming it applies for all x values)
        ' or perhaps to raise an error.
        ArrayInterp = r(1, 2)
        Exit Function
    End If

    If x < r(1, 1) Then ' x < xmin, extrapolate'
        l1 = 1
        l2 = 2
    ElseIf x > r(nR, 2) Then ' x > xmax, extrapolate'
        l2 = nR
        l1 = l2 - 1
    Else
        ' a binary search might be better here if the arrays are large'
        For lR = 1 To nR
            If r(lR, 1) = x Then ' no need to interpolate if x is a point in the array'
                ArrayInterp = r(lR, 2)
                Exit Function
            ElseIf r(lR, 2) > x Then ' x is between tabulated values, interpolate'
                l2 = lR
                l1 = lR - 1
                Exit For
            End If
        Next
    End If

    ArrayInterp = r(l1, 2) _
           + (r(l2, 2) - r(l1, 2)) _
           * (x - r(l1, 1)) _
           / (r(l2, 1) - r(l1, 1))

End Function

这篇关于如何将Variant数组转换为Range?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！