在 VBA 中,我用于使用 Evaluate() 计算一列的值,这是另一列的函数,以便不使用循环。例如,为了评估第 2 列,其值是第 1 列值的指数,我会写(这里仅限于前 10 行):
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Worksheets("Sheet1").Range(Cells(1,1),Cells(10,1))
Set rng2 = Worksheets("Sheet1").Range(Cells(1,2),Cells(10,2))
rng2 = Evaluate("EXP(" & rng1.Address & ")")
我现在想以同样的方式处理我自己的职能。为了进行测试,我创建了一个返回输入指数值的函数:
Function TestExpo(alpha) As Double
TestExpo = EXP(alpha)
End Function
我按照之前使用 Evaluate() 所述的相同方式进行:
rng2 = Evaluate("TestExpo(" & rng1.Address & ")")
我没有收到错误,但 Excel 工作表上没有任何反应。我试图通过在函数名称中输入错误来触发错误,例如:
rng2 = Evaluate("TestEx(" & rng1.Address & ")")
我实际上在工作表上 rng2 的每个单元格上都收到一个"名称"错误。因此,调用函数"TestExpo"有效。我还尝试使用实数而不是 rng1。地址:
rng2 = Evaluate("TestExpo(" & 2 & ")")
我在工作表上 rng2 的所有单元格中都获得了 exp(2) 的正确值,因此调用函数并获取单元格中的值就是这样工作的。
我试图以不同的方式指定输入和输出的类型。例如:
Function TestExpo(alpha) ' No error, but no values on the sheet
Function TestExpo(alpha) As Range ' No error, but no values on the sheet
Function TestExpo(alpha) As Variant ' No error, but no values on the sheet
Function TestExpo(alpha As Range) ' No error, but no values on the sheet
Function TestExpo(alpha As Variant) ' No error, but no values on the sheet
Function TestExpo(alpha As Double) ' Value error for each cell of the range rng2
除了最后一次使用 alpha 作为双精度进行测试外,我没有收到任何错误,但 Excel 工作表上也没有值。
我的想法快用完了...我试图找到 VBA 的 EXP() 函数的代码,看看输入和输出是如何指定的,认为如果我在函数"TestExpo"中使用相同的类型和参数,它可能会起作用,但我找不到 EXP() 函数的代码,它可能不是公开的。
有人会有类似的情况并解决它吗?
亲切问候 泽维尔
更新:
感谢您的贡献!我继续前进!
当我使用时,正如@FaneDuru所建议的那样
rng2 = Evaluate("""=TestExpo(" & rng1.Address & ")""")
我在每个单元格中都收到一个值错误。检查工作表中的单元格,我可以看到每个单元格都归因于公式:
"=@TestExpo($A$1:$A$35)"
我与写作相比
rng2 = Evaluate("""=EXP(" & rng1.Address & ")""")
哪个有效并归因于每个单元格的公式
"=EXP(@$A$1:$A$35)"
所以@出现在不同的位置。我手动将公式"=@TestExpo($A$1:$A$35)"更正为"=TestExpo(@$A$1:$A$35)",它有效!
所以问题出在@的位置,我不明白为什么在使用函数EXP时,@默认放在括号内的范围前面,而在使用我自己的函数时放在函数名称的前面。
但是,手动更正单元格中的公式并不方便。受到@FaneDuru提议的启发,我通过添加 .细胞(1,1)。地址 (0,0) 按以下方式在范围名称后:
rng2 = Evaluate("""=TestExpo(" & rng1.Cells(1,1).Address(0,0) & ")""")
这奏效了!
现在,第一个单元格中的公式如下所示:
"=@TestExpo(A1)"
并且单元格地址适应每行(第 2 行公式为"=@TestExpo(A2)",第 3 行"=@TestExpo(A3)"等)
@ 仍然显示在函数名称的前面,但括号内有一个单元格名称而不是范围,这使得公式有效。
现在我不得不承认,我并不真正了解代码语法是如何工作的。事实上,添加.细胞(1,1)。地址(0,0),我预计 rng2 的所有单元格都会引用范围 rng1 的 Cell(1,1),但公式以正确的方式适应了每个单元格。
因此,我最初的问题已通过以下方式解决:
rng2 = Evaluate("""=TestExpo(" & rng1.Cells(1,1).Address(0,0) & ")""")
甚至更简单:
rng2 = "=TestExpo(" & rng1.Cells(1,1).Address(0,0) & ")"
尽管如此,我还是热衷于理解代码语法的工作原理 - 即如果代码明确引用 rng1 中的 cell(1,1)。细胞(1,1)。地址(0,0),为什么 rng2 的所有单元格都不引用单元格 (1,1),而是引用正确的单元格(单元格 (2,1)、单元格 (3,1) 等)?
我还想了解为什么在使用函数 EXP 时,@ 默认放在范围前面,但在使用我自己的函数时放在函数名称的前面。有人有想法吗?
不幸的是,我认为你不能以这种方式做你想做的事。 我认为你的例子没有达到你的期望。
rng2 = Evaluate("EXP(" & rng1.Address & ")")
将返回一个值数组,但只会在rng2
中的每个单元格中输入第一个值。 因此,即使您可以模仿 EXP 正在做的事情,它仍然不会做您想要的。
但是,如果您仍然想模仿EXP
函数获得的(可能不正确的)功能:
EXP 和 UDF之间的区别在于 EXP 函数可以处理和返回数组,而您的 UDF 则不能。
这是一个非常快速的功能,可以(并且应该)显着改进,但它可能会给你一个起点。
Public Function test(a As Variant) As Variant
Dim arr() As Double
If IsNumeric(a) Then
ReDim arr(1 To 1)
arr(1) = Exp(a)
Else
ReDim arr(1 To a.Count)
Dim i As Long
For i = 1 To a.Count
arr(i) = Exp(a(i))
Next i
End If
test = arr
End Function
更新:
显然,工作表上的公式中使用的EXP
函数与VBA版本之间似乎也存在差异。
VBA版本仅接受单元格或值,而工作表版本能够接受值的范围/数组。 通常,您可以使用Application.WorksheetFunction
访问工作表版本,但不幸的是,这似乎不适用于EXP
。
要计算的函数必须是字符串(在双引号之间)...
请尝试
rng2 = Evaluate("""=TestEx(" & rng1.Address & ")""")
已编辑:
请尝试下一个(经过测试的)示例:
Function myFuncX(x As Long) As Long
myFuncX = x + x * 2
End Function
和测试子评估它。当然,你必须感受到"J2:J4"与适当的长值......
Sub testEvaluateCustFunc()
Dim rng As Range, rng2 As Range
Set rng = Range("I2:I4"): Set rng2 = Range("J2:J4")
rng.Value = Application.Evaluate("""=myFuncX(" & rng2.cells(1, 1).Address(0, 0) & ")""")
End Sub
已编辑:
请尝试了解下一个测试Sub
会发生什么。该方法能够计算公式、名称、范围、对象及其属性,返回对象、值或数组:
Sub testEvaluate()
Dim rng As Range, rng2 As Range, testArr As Variant
Set rng = Range("I2:I4"): Set rng2 = Range("J2:J4")
'Application.Evaluate can simple be replaced by using []:
testArr = [J2:J4]: Debug.Print Join(Application.Transpose(testArr), "||") 'Transpose is necessary to convert in 1D array
'able to be shown in Immediate Window (IW)...
testArr = Application.Evaluate("Index(" & rng2.Address(0, 0) & ",)") 'in this way an array of the range values is built
Debug.Print Join(Application.Transpose(testArr), ",") 'here the array can be visualized in IW
testArr = Evaluate("Row(1:5)") 'A nice way of making an array of numbers using the Row property
Debug.Print Join(Application.Transpose(testArr), "|") 'See the resulted array in IW
'the next line evaluates the function for all rng range, incrementing the function argument addresses:
'I did not see your function and I used this way ONLY TO USE THE FIRST CELL IN THE RANGE TO BE INCREMENTED
rng.Value = Application.Evaluate("""=myFuncX(" & rng2.cells(1, 1).Address(0, 0) & ")""")
Debug.Print rng2.Address, rng2.Address(0, 0) ' See here the difference between absolute and relative address
'Only a relative address/reference can be incremented!
'trying the absolute reference, will produce a wrong result, the formula using only the first cell of the range (absolute address):
rng.Value = Application.Evaluate("""=myFuncX(" & rng2.cells(1, 1).Address & ")""")
testArr = rng.Value
Debug.Print Join(Application.Transpose(testArr), " ") 'the function is using only the first range cell
'Now, the same thing using the first cell addres. Because I know what parametes the function uses...
rng.Value = Application.Evaluate("""=myFuncX(" & Range("J2").Address(0, 0) & ")""")
testArr = rng.Value
Debug.Print Join(Application.Transpose(testArr), "|") 'if using the absolute address, the result will allways refer only
'the first range cell, not being able to increment an absolute ref...
End Sub
如果您的函数应该接受一系列许多单元格,它可以按照我最初建议的方式工作。例如,使用下一个函数:
Function myFunc2(rng As Range) As Long
myFunc2 = rng.cells.count * 2 + Len(rng.cells(1, 1).Value)
End Function
您可以在测试Sub
中使用下一个构造:
Sub testmyFunc2()
Dim rng3 As Range: Set rng3 = Range("F2:F4")
Dim rng2 As Range: Set rng2 = Range("J2:J4")
Debug.Print myFunc2(Range("J2:J4"))
rng3.Value = Evaluate("""=myFunc2(" & rng2.Address & ")""")
End Sub
我希望现在更清楚一点...如果仍然有雾,请随时要求澄清。但准时,在特定方面。Evaluate
方法在使用方式方面非常复杂......