VBA - 通过Application.Evaluate的长阵列公式 [英] VBA - Long Array Formula via Application.Evaluate

问题描述

说我们有一些长的公式保存在单元格 A1 中：

  = SomeArrayFunction（
 IF（SUM（D3：D6）> 1，ABCDEFGHIJKLMNOPQRSTU-VWX 01，
part_one），
 IF（SUM（D3：D6）> 1，ABCDEFGHIJKLMNOPQRSTU-VWX 02，
 IF（SUM（D3：D6）> 1，ABCDEFGHIJKLMNOPQRSTU-VWX 03，
part_two））
）
  

其中使用以下VBA功能

 公开函数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 
结束函数
  

返回一个2×1数组。

---

现在，当我调用这个VBA函数

  Public Sub EvaluateFormula（）
 Dim vOutput As Variant 
 
 vOutput = Application.Evaluate（Selection.Form ula）
 
如果VarType（vOutput）> = vbArray Then 
 MsgBoxArray：& vbCrLf& vOutput（1，1）& vbCrLf& vOutput（2，1）
 Else 
 MsgBoxSingle Value：& vbCrLf& vOutput 
如果
 End Sub 
  

同时选择单元格 A1 我收到错误，因为Application.Evaluate无法处理超过255个字符的公式（例如，请参阅 VBA - 使用Application.Evaluate在长公式时出错）。另一方面，如果我写了

  vOutput = Application.Evaluate（Selection.Address）
  

（如上面的链接中提出的），那么它的工作原理很好。除了数组没有被重新标记的事实，即 MsgBoxSingle Value：被调用，而不是MsgBoxArray：。

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

---

$ b $编辑：让我强调，当我只选择一个单元格时，我需要这个工作，它符合公式（不是一个区域或几个单元格）。而且我没有将它作为数组公式输入（即没有大括号）：

---

Edit2：让我回答一下为什么：我目前的工作要求我电子表格中这样大的公式的长列表。而且由于它们被列在一个列表中，每个这样的公式只能占用一个单元格。在几乎所有情况下，公式返回单个值（因此一个单元格足以存储/显示输出）。但是，如果在评估公式时出现内部错误，该公式会返回错误消息。这些错误消息通常相当长，因此返回为不同大小的数组（取决于错误消息的长度）。所以我的目标是编写一个VBA函数，它将首先获得列表中给定的选定条目的完整错误消息。

解决方案

我相信 Application.Evaluate 将返回与输入地址大小相匹配的结果。我怀疑你的选择是一个单元格，所以它返回一个单一的值。

如果相反，你称之为使用 Selection.CurrentArray.Address ，您将得到一个与正确数组大小相同的答案。

VBA和Excel的图片

使用测试的代码

 公共功能测试（）作为变量
 
测试=数组（1，2）
 
 
结束函数
 
 Sub t（）
 
 Dim a As Variant 
 
a = Application.Evaluate（Selection.CurrentArray.Address）
 
 End Sub 
  

编辑，基于这里的评论是通过创建新的表格来评估此表。我使用剪切/粘贴方法来确保公式都是一样的。如果细胞不参照切片，这可能会更好。因为我使用剪切/粘贴，它在技术上不会破坏任何其他单元格。

在下面的代码中，我在单元格中有一个数组公式J2 它引用了其他几个单元格。它被扩展为有3行，然后调用评估调用。这将返回一个你想要的数组。然后将其缩小到一个单元格并将其移回。

我已经测试了一个简单的例子。我不知道它是否适用于您所考虑的应用程序。

  Sub EvaluateArrayFormulaOnNewSheet（）
 
'切割单元格公式
 Dim str_address As String 
 Dim rng_start As Range 
设置rng_start = Sheet1.Range（J2）
 str_address = rng_start.Address 
 
 rng_start.Cut 
 
'创建新工作表
 Dim sht As Worksheet 
设置sht = Worksheets.Add 
 
'将单元格粘贴到工作表上
 Dim rng_arr As Range 
设置rng_arr = sht.Range（A1）
 sht.Paste rng_arr 
 
'展开数组公式大小..调整大小到任意大小需要
 rng_arr.Resize（3）.FormulaArray = rng_arr.FormulaArray 
 
'得到你的结果
 Dim v_arr As Variant 
 v_arr = Application.Evaluate（rng_arr。 CurrentArray.Address）
 
''''做某事与你的结果在这里...它是一个数组
 
 
'缩小公式回到一个单元格
 Dim str_formula As String 
 str_formula = rng_arr.FormulaArray 
 
 rng_arr.CurrentArray.ClearContents 
 rng_arr.FormulaArray = str_formula 
 
'cut and粘贴到原始点
 rng_arr.Cut 
 
 Sheet1.Paste Sheet1.Range（str_address）
 
 Application.DisplayAlerts = False 
 sht.Delete 
 Application.DisplayAlerts = True 
 
 End Sub 
  

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"))
)

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

returning a 2×1 array.

---

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

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)

instead (as proposed in the link above), then it works just fine. Except for the fact that the array is not being recgonised anymore, i.e. MsgBox "Single Value: " is called instead of MsgBox "Array:".

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

---

Edit: 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: 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.

解决方案

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.

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

Picture of VBA and Excel

Code to test with

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.

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屋！