logo标签列表

从函数设置单元格的值

vbaexcel
24

单元格 A1 的内容为 =test(2)，其中test是一个函数：

Function test(ByRef x As Double) As Double
  Range("A2") = x
  test = x * x
End Function

你能解释一下为什么这个公式在单元格A1中会返回#VALUE!，而在单元格A2中不显示任何内容吗？我原本期望的是A2应该包含2，而A1则应该包含4。如果没有Range("A2") = x这行代码，函数就能像预期的那样工作（对单元格的值进行平方）。
真正令人困惑的是，如果你将test用子程序calltest包裹起来，它就能正常工作：
Sub calltest()
  t = test(2)
  Range("A1") = t
End Sub

Function test(ByRef x As Double) As Double
  Range("A2") = x
  test = x * x
End Function

但这并不是...
Function test(ByRef x As Double) As Double
  Range("A2") = x
End Function
正如https://dev59.com/ll7Va4cB1Zd3GeqPIlTP中所述，UDF实际上可以更改另一个单元格，但这很丑陋。 - brettdj
4个回答
11

是的，你可以拥有自己定义的函数，它可以将值写入 任何单元格，而不仅仅是在公式中输入的那个单元格。

这里是一个简单的例子。UDF函数需要两个参数A和B，并返回它们的乘积A*B。但有趣的是，它将结果返回到紧挨着我们输入公式的那个单元格右侧的单元格。

将以下代码放入标准的VBA模块中：

Function UDF_RectangleArea(A As Integer, B As Integer)
    Dim MagicSpell As String
    MagicSpell = "Adjacent(" & Application.Caller.Offset(0, 1).Address(False, False) & "," & A & "," & B & ")"
    Evaluate MagicSpell
    UDF_RectangleArea = "Hello world"
End Function

Private Sub Adjacent(CellToChange As Range, A As Integer, B As Integer)
    CellToChange = A * B
End Sub

现在在B2中输入公式：=UDF_RectangleArea(3,4)

enter image description here

该函数将在两个单元格中返回结果：“Hello world”在B2中（这并不奇怪），而矩形面积则在C2中（这就像变魔术一样）。所有结果以及“兔子”出现的位置都可以轻松自定义。这项工作是由VBA的EVALUALTE命令完成的。在UDF返回结果之前，变量MagicSpell的值在此情况下变为Adjacent(C2,3,4)。玩得开心！

也许在其他评论中已经解释过了，但是每次我使用它时，它都可以返回正确的答案（在崩溃/恢复之后）。 - JB-007
啊 - 当然；只适用于迭代计算，因为B2中的值会更新，从而再次调用自身...（我猜） - JB-007
10

由于Function的基本原则规定您无法更改或设置工作表单元格。您需要通过Range("A2")=x删除该行。

编辑以下是一些附加链接（我认为这对于想要分析UDF主题的人始终是有用的）：Microsoft创建自定义功能

你能解释一下为什么如果你从子程序中调用该函数，它会起作用吗？ - Chris Seymour
2
还有一些规则，其结果是在Excel应用程序中使用的函数必须比在VBA中使用的函数更严格。因此编写和使用UDF有点棘手。 - Kazimierz Jawor
子程序允许操作工作表单元格，而函数则不行。函数只能将值返回给自身（或另一个子程序中的变量）。 - David Zemens
@DavidZemens 这并没有解释为什么一个函数在从子程序调用时可以修改工作表单元格，但是直接调用时却不能？ - Chris Seymour
2
@sudo_O 可以在这里查看回答，可能会有更好的解释。似乎Excel在从工作表单元格调用UDF时与从子程序调用UDF时处理方式不同。我一直只使用函数来返回值（即将计算步骤分隔和流线化）到子程序，然后子程序设置单元格值。 - David Zemens
7
当您从工作表单元格调用函数时，实际上是在使用该函数作为用户定义功能，其限制如此处所述：

http://support.microsoft.com/kb/170787

在文本中有一句话：

应该通过使用Visual Basic子程序来进行任何环境更改。

有趣的是，他们使用"应该"而不是"必须"这个词。我不知道KB的作者是否知道从VBA函数中可以发生环境变化。
现在，当您从另一个VBA Sub / Function调用该函数时，它会被处理得不同。从帮助文档中（很抱歉我找不到网页参考 - 基本上，在VBE中，突出显示单词Function并按F1）：

像Sub过程一样，Function过程也是一个单独的过程，可以接受参数，执行一系列语句，并更改其参数的值。但与Sub过程不同的是，当您想要使用函数返回的值时，可以在表达式的右侧使用Function过程，就像使用任何内部函数（如Sqr、Cos或Chr）一样。

因此，听起来Sub和function在VBA中使用时可以做相同的事情，除了Function可以将值返回到调用function/sub。
这其实非常有趣，因为Excel可以调用带有某种“只读”限制的函数来访问Excel的环境。
我认为，最终，Excel可以通过不同于VBA的方式从工作表单元格中调用函数。当您从单元格中调用它时，它被视为用户定义函数，包括更改Excel环境的限制。而当从VBA内部调用它时（其中调用链的原始调用者来自VBA），它具有与Sub相同的所有功能，还可以返回值。
2
这里的“应该”是指建议，而不是必须。因为你可以合法地编写一个UDF来干扰Application.EnableEvents，例如这样。这将会是意料之外的错误和意外行为的美妙源泉。 - Mathieu Guindon
是的，这也是问题所在。真是让人恼火！ - SteveCinq
0

我已经将您的代码适应于字符串、函数和其他任何中间内容，希望可以正常工作（仍在测试中）。

由测试产生了很多问题，但首先想到的是两个：

您可以使用哪些其他方法来执行/处理参数并返回所需的结果；

a. 就像我所做的那样 b. 不像我所做的那样（即不同的方法）。

非常感谢Rick Rothstein MrExcel MVP和SO上的许多其他人为此提供支持和帮助。

Function MOVEME27(a As Variant, b As Variant, Optional CELLR As Variant, Optional cellq As Variant) '21/05/2018 works copied to ar4' 03/06/2019 23:30 was cellr as range , cellq as range - changed to variants
 Dim WTVR1 As Variant '' ''20/05/2019'' '09/06/2019 Code by S Tzortzis/David Wooley
 Dim WTVR2 As Variant
 Dim P As String
 Dim P1 As String
 Dim bb As String
 Dim bb1 As String
 Dim A1 As Long
 Dim A2 As Long

 Dim c As String

 'x' a = Evaluate(a)
P = Chr(34) & a & Chr(34)
P2 = Chr(34) & [P] & Chr(34)

bb = Chr(34) & b & Chr(34)
bb1 = Chr(34) & [bb] & Chr(34)

c = Chr(34) & CELLR & Chr(34)
f = Chr(34) & callq & Chr(34)


'P2 = Chr(34) & "'''" & [P] & "'''" & Chr(34)
'p1 = Chr(34) & p & Chr(34)

''WTVR1 = "MOVEUS1(" & Application.Caller.Offset(0, 2).Address(False, False) & "," & Chr(34) & P2 & Chr(34) & "," & b & ")"
   WTVR1 = "MOVEUS11h(" & Application.Caller.Offset(0, 2).Address(False, False) & "," & [P2] & "," & [bb1] & ")"
Evaluate WTVR1


WTVR2 = "MOVEUS22h(" & Application.Caller.Offset(0, 1).Address(False, False) & "," & [P2] & "," & [bb1] & ")" ' used or be adjacent - maybe redo rhat pr put a GO TO sub. '' ''20/05/2019''

Evaluate WTVR2

A1 = cellq.Row
A2 = cellq.Column

CELLRR = Chr(34) & CELLR & Chr(34)
CELLRR1 = Chr(34) & [CELLRR] & Chr(34)
cellqq = Chr(34) & cellq & Chr(34)
cellqq1 = Chr(34) & [cellqq] & Chr(34)


''wtvr3 = "CopyFrom.Parent.Evaluate CopyOver234h(" & c & "," & f & ")" ''''20190531 1929
wtvr31 = "MOVEUS33h(" & Application.Caller.Offset(A1 - ActiveCell.Row + 1, A2 - ActiveCell.Column).Address(False, False) & "," & [CELLRR] & "," & [cellqq] & ")"

    Evaluate wtvr31

MOVEME27 = "Hello world       " & " / " & WTVR1 & " / " & WTVR2 & "\\\\\/////" & wtvr31 & "\\\\\/////---" & ActiveCell.Row - A1 & "//////---" & ActiveCell.Column - A2

' DO AS WHATVER = "MOVEUS3(" APPLICATION.CALLER.OFFSET(THE ROW & COLUMN IE CELL         YOU REFERENCES IN a as variant (copy from)'
    'with ="" in sub 30052019 19:28

    'CopyFrom.Parent.Evaluate "CopyOver2(" & CELLR.Address(False, 1) & "," &              CELLR.Address(False, False) & ")"  ''''2019050 1929



    End Function

    Private Sub MOVEUS11h(CELL1 As Range, G1 As Variant, G2 As Variant)

        '[ak333] = a


        CELL1 = Chr(34) & G1 & Chr(34) & "B" & "//" & G2


    End Sub


    Private Sub MOVEUS22h(CELL2 As Range, G3 As Variant, G4 As Variant)

        CELL2 = Chr(34) & G3 & Chr(34) & "<>" & G4

    End Sub

    '' with chr(34) arond the p's and a's in sub or fucntion changes behavior. thinking of doing if a is string, then a=x , x as string, if not kep as variant
    ''27/05/2019 :(

    '''''30/05/2019 .....'''''''


    '------------------------------------------------------------------------------- ADD THIS 30052019 -------------------------------
    'private sub Movus3(cellfrom as range, cellto as range)
    'End Sub

    Private Sub moveus33h(cell3 As Range, CopyFrom As Variant, copyTo As Variant) ''''2019050 1929 ''' 03062019 change ema back to as Range here. :)
       '' copyTo.Value = CopyFrom.Value ''''2019050 1929

       ''CopyFrom.Value = ""

       cell3 =  CopyFrom  'Chr(34) & CopyFrom & Chr(34)

    End Sub ''''2019050 1929
除了它是使其工作的原因之外（在我的版本中），使用评估变量参数对Excel执行、Excel处理和的影响是什么？这就是我为什么提出问题的原因，也是为什么问这个问题非常困难/令人困惑的原因（这个答案）。并且将参数包装-视为字符串处理（我为此做了两次），如果我不将其视为字符串，会发生什么，我也会测试-在以前的版本中已经测试过。太多了，再见/太多了。 - David Wooley - AST
Excel的处理能力和令人难以置信的影响以及进一步的操作，你可以在各个方面进行测试——甚至接受自我引用的函数！？！这就像是打开了潘多拉魔盒，基本上允许（或者看起来允许）疯狂的循环逻辑。这简直颠覆了一切！我很享受这个过程，它变成了实时的……Excel允许这样做……在我的看法中非常出色。如果被用作工具并且负责任地使用，它可以帮助我们完成其他事情，比如自动化（也许可以涉及到机器学习？）我不知道。 - David Wooley - AST
我开始手动输入char(34)&A1&char(34)来查看会发生/返回什么；这起作用了（将这些结果传递到某个地方并对它们进行操作/添加），在A1中调用函数的文本也是如此，将“=”符号替换为A1中的函数也是如此。然后我看到任务要求我在代码中输入char(34)或在参数中细化输入变量。 - David Wooley - AST
有一个有趣的情况，你可以使用它来获得正确的结果，将其打印/写在公式上，并使用于计算结果的公式消失，从而使该单元格中的值看起来没有使用任何公式或计算。 - David Wooley - AST
有一个有趣的情况，你可以使用它来获得正确的结果，将其打印/写在公式上，并使用于计算结果的公式消失，从而使该单元格中的值看起来没有任何公式或计算。用公式覆盖自己的公式。不知道这有什么用！但是这很令人印象深刻！（同时在其他地方或相邻处显示使用的公式。）/感觉任何事情都有可能！ - David Wooley - AST
显示剩余2条评论
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接