VBA - 通过长数组公式Application.Evaluate [英] VBA - Long Array Formula via Application.Evaluate

问题描述

假设我们有储存在细胞一些长期公式的 A1

Say we have some long formula saved in cell A1:

=SomeArrayFunction(
IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X 01",
"part_one"),
IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X 02",
IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X 03",
"part_two"))
)

它使用以下VBA函数

which uses the following VBA function

Public Function SomeArrayFunction(sOne As String, sTwo As String) As Variant
    Dim V() As Variant
    ReDim V(1 To 2, 1 To 1)
    V(1, 1) = sOne
    V(2, 1) = sTwo
    SomeArrayFunction = V
End Function

返回一个2×1阵列。

returning a 2×1 array.

现在，当我把这个VBA函数

Now when I call this VBA function

Public Sub EvaluateFormula()
    Dim vOutput As Variant

    vOutput = Application.Evaluate(Selection.Formula)

    If VarType(vOutput) >= vbArray Then
        MsgBox "Array:" & vbCrLf & vOutput(1, 1) & vbCrLf & vOutput(2, 1)
    Else
        MsgBox "Single Value: " & vbCrLf & vOutput
    End If
End Sub

而在选择单元格的 A1 我得到一个错误，因为Application.Evaluate不能超过255个字符处理公式（例如参见<一个href=\"http://stackoverflow.com/questions/30267826/vba-error-when-using-application-evaluate-on-long-formula\">VBA - 长公式使用Application.Evaluate时出错）。在另一方面，如果我写

while having selected cell A1 I get an error, because Application.Evaluate cannot handle formulas with more than 255 characters (e.g. see VBA - Error when using Application.Evaluate on Long Formula). On the other hand, if I write

vOutput = Application.Evaluate(Selection.Address)

代替（如上面的链接提出的），那么它工作得很好。除了事实阵列没有被recgonised了，即 MSGBOX单值：的被称为取代的 MSGBOX阵：的

所以我的问题是：如何评估使用VBA长公式（返回数组）

So my question is: How can I evaluate long formulas (which return arrays) using VBA?

编辑：我要强调，我需要这个工作的时候，我只选中一个单元格的conains公式（而不是一个区域或几个细胞）。我还没有进入它作为一个数组公式（即没有大括号）：

Let me stress that I need this to work when I only select the one cell that conains the formula (not a region or several cells). And I have not entered it as an array formula (i.e. no curly brackets):

EDIT2：我来回答的原因：我目前的工作需要我在A S preadsheet如此大的公式有一长串。而且，由于它们在列表中的每个组织这样的公式只能占用一个单元格。在几乎所有情况下，公式返回单个值（因此一个单元是足以存储/显示输出）。然而，当在评价式的内部错误，该公式将返回一个错误信息。这些错误消息通常是相当长的，因此，返回为不同大小的阵列（取决于该错误消息是如何长）。所以我的目标是写一个VBA函数，将首先获得，然后输出完整的错误消息从列表中选择特定的条目。

Let me answer the why: my current work requires me to have a long list of such large formulas in a spreadsheet. And since they are organised in a list every such formula can only take up one cell. In almost all cases the formulas return single values (and hence one cell is sufficient to store/display the output). However, when there is an internal error in evaluating the formula, the formula returns an error message. These error messages are usually quite long and are therefore returned as an array of varying size (depending on how long the error message is). So my goal was to write a VBA function that would first obtain and then output the full error message for a given selected entry from the list.

推荐答案

我相信 Application.Evaluate 将返回输入地址的大小相匹配的结果。我怀疑你的选择是一个单细胞因此它返回一个值。

I believe that Application.Evaluate will return a result that matches the size of the input address. I suspect that your Selection is a single cell so it is returning a single value.

如果不是你把它与 Selection.CurrentArray.Address ，你会得到一个答案，它是大小正确的数组一样的。

If instead you call it with Selection.CurrentArray.Address you will get an answer that is the same size as the correct array.

VBA和Excel的图片

code与测试

Public Function Test() As Variant

    Test = Array(1, 2)


End Function

Sub t()

    Dim a As Variant

    a = Application.Evaluate(Selection.CurrentArray.Address)

End Sub

修改，根据意见在这里是一种通过创建一个新的工作表评估这一关表。我使用的是剪切/粘贴的方式，以确保所有的公式工作相同。这很可能效果会更好，如果细胞不引用切之一。因为我使用剪切/粘贴它将技术上没有打破任何其他细胞虽然。

Edit, based on comments here is a way evaluate this off sheet by creating a new sheet. I am using a cut/paste approach to ensure the formulas all work the same. This probably works better if cells don't reference the cut one. It will technically not break any other cells though since I am using cut/paste.

在下面的code，我在单元格数组公式 J2 它所引用其他几个细胞。它扩大到有3行，然后在评估调用。返回像你想的数组。然后，它缩小它归结为一个单元格并移动了回去。

In the code below, I had an array formula in cell J2 it referenced several other cells. It is expanded to have 3 rows and then the Evaluate call is made. That returns an array like you want. It then shrinks it down to one cell and moves it back.

我有一个简单的例子测试了这个。我不知道它是否适合你心目中的应用程序。

I have tested this for a simple example. I have no idea if it works for the application you have in mind.

Sub EvaluateArrayFormulaOnNewSheet()

    'cut cell with formula
    Dim str_address As String
    Dim rng_start As Range
    Set rng_start = Sheet1.Range("J2")
    str_address = rng_start.Address

    rng_start.Cut

    'create new sheet
    Dim sht As Worksheet
    Set sht = Worksheets.Add

    'paste cell onto sheet
    Dim rng_arr As Range
    Set rng_arr = sht.Range("A1")
    sht.Paste rng_arr

    'expand array formula size.. resize to whatever size is needed
    rng_arr.Resize(3).FormulaArray = rng_arr.FormulaArray

    'get your result
    Dim v_arr As Variant
    v_arr = Application.Evaluate(rng_arr.CurrentArray.Address)

    ''''do something with your result here... it is an array


    'shrink the formula back to one cell
    Dim str_formula As String
    str_formula = rng_arr.FormulaArray

    rng_arr.CurrentArray.ClearContents
    rng_arr.FormulaArray = str_formula

    'cut and paste back to original spot
    rng_arr.Cut

    Sheet1.Paste Sheet1.Range(str_address)

    Application.DisplayAlerts = False
    sht.Delete
    Application.DisplayAlerts = True

End Sub

这篇关于VBA - 通过长数组公式Application.Evaluate的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！