Excel评估公式错误 [英] Excel Evaluate formula error

问题描述

我的VBA代码是

Function yEval(entry As String)
yEval = Evaluate(entry)
Application.Volatile
End Function

在单元格f4的Sheet1中的

我具有公式=yEval(Sheet2!E19) sheet1上的单元格d4包含数字12 Sheet2！I19包含字符串$ Q

in Sheet1 in cell f4 i have formula =yEval(Sheet2!E19) cell d4 on sheet1 contains number 12 Sheet2!I19 contains string $Q

如果Sheet2！e19具有字符串INDIRECT("pries！"& Sheet2！I19& 12)或 INDIRECT("pries！"& Sheet2！I19& Sheet1！d4)或 INDIRECT("pries！"& Sheet2！I19&"Sheet1！D"& row()+ 12)公式返回 表撬的结果！$ Q12

if Sheet2!e19 has string INDIRECT("pries!"&Sheet2!I19&12) or INDIRECT("pries!"&Sheet2!I19&Sheet1!d4) or INDIRECT("pries!"&Sheet2!I19&"Sheet1!D"&row()+12) the formula return result from sheet pries!$Q12

如果Sheet2！e19有字符串 INDIRECT("pries！"& Sheet2！I19&address(row()，4))或 INDIRECT("pries！"& Sheet2！I19&"Sheet1！D"& row())公式返回 结果0似乎忽略了row()函数

if Sheet2!e19 has string INDIRECT("pries!"&Sheet2!I19&address(row(),4)) or INDIRECT("pries!"&Sheet2!I19&"Sheet1!D"&row()) the formula return result 0 it seems like ignoring the row() function

如何使此公式根据行数更改d4部分，如果行为5，则d5

How to make work this formula to change d4 part depending on row number, if row is 5 then d5

INDIRECT("pries!"&Sheet2!I19&Sheet1!d4)

推荐答案

有趣的问题...有关该问题的更多见解，请在即时窗口或监视窗口中尝试以下操作:

Interesting problem... For further insight into the issue try these from the immediate or watch window:

?[indirect("rc",0)]
?[index(a:a,row())]
?[offset(a1,row()-1,column()-1)]

奇怪的是，无论哪个单元处于活动状态，它们都将评估为A1. Evaluate或[]的怪癖是，当返回值是范围引用时，相对于A1而不是活动(调用)单元格处理引用.您可以认为row()等同于相对于A1评估的RC表示法中的row(RC).

Strangely they all evaluate to A1 no matter which cell is active. It's a quirk of Evaluate or [] that references are treated relative to A1 instead of the active (calling) cell when the return value is a range reference. You can think of row() as equivalent to row(RC) in RC-notation evaluated relative to A1.

但是，请注意，在上述三个公式中将]更改为&""]会导致前一个错误，但在后两个错误中导致预期结果，因此现在相对于活动单元格对row()进行评估.因此，当返回值不是范围引用时，Evaluate的功能似乎有所不同.

However, note that changing ] to &""] in the three formulas above results in an error in the first but the expected result in the last two so that row() is now evaluated relative to the active cell. So it seems that Evaluate is functioning differently when the return value is not a range reference.

更新

基于这些观察，您可以将INDEX用作行引用，并将INDIRECT移到外部:

Based on these observations you can use INDEX for the row reference and move INDIRECT outside:

Sheet1!F4:=INDIRECT(yEval(Sheet2!E19))
Sheet2!E19:"pries!"&Sheet2!I19&INDEX(Sheet1!D:D,Row())

...或者只使用RC样式的引用而不需要UDF:

...or just use RC-style references without the need for the UDF:

=INDIRECT("pries!"&Sheet2!I19&INDIRECT("RC4",0))

这篇关于Excel评估公式错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！