调用单元格“看到”时的公式被调用单元格中的公式而不是值 - 为什么？
这看起来不像 Excel 的常见行为。我以前从未见过这种情况发生。我会尽力解释它。
我有两个电子表格，SS-A 和 SS-B。 SS-A 有一个工作表 WS-source ，SS-B 有一个工作表 WS-dest ，其中 WS-dest 通过几个公式从 WS-source 中提取数据。
SS-B 还有另一个工作表 WS-final，它再次使用公式从 WS-dest 提取数据。由于某种原因，WS-final 中的公式结果失败，因为 WS-final 中的公式“看到”了 WS-dest 中的公式而不是公式的值。
这些公式适用于 WS-dest。当我查看 WS-dest 中的电子表格时，我没有看到单元格中的公式，而是看到公式结果的值。但是，当尝试使用这些值时，WS-final 的公式会失败。
我是如何得出这个结论的：WS-dest 过去不使用公式。我手动将值放入字段中。 WS决赛效果很好。几天前，我开始使用公式从 WS-source 中提取数据，而不是手动将其插入到 WS-dest 中。在此更改之前，WS-final 运行良好。此外，出于测试目的，我将 WS-dest 中的几个测试字段从公式替换为手工插入的值，WS-final 中的公式再次起作用。我从 WS-final 中复制了公式，并将它们放置在 WS-dest 中未使用的单元格中，调整单元格引用以使公式能够工作。结果，我观察到了同样的行为。
宏：
Public Function sbTextToColumn(srcRng As Range) As Variant
Dim vData As Variant
vData = Split(srcTang.Value2, "/")
ReDim Preserve vData(0 To Application.Caller.Columns.Count - 1)
sbTextToColumn = vData
End Function
这是另一个SO成员协助我实现的功能，效果很好！我个人是一名 Linux/Unix 爱好者，擅长 Perl 和 PHP 等语言。我很感谢你的帮助。
This doesn't seem like usual behavior for Excel. I've never seen this happen before. I'll try and explain it as best I can.
I have two spreadsheets, SS-A and SS-B. SS-A has a worksheet, WS-source ,and SS-B has a worksheet, WS-dest in which WS-dest pulls data via a couple of formulas from WS-source.
SS-B has another worksheet, WS-final, which pulls data from WS-dest, again, using formulas. For some reason, the result of the formulas in WS-final fail because the formulas in WS-final "see" the formulas in WS-dest instead of the values of the formulas.
The formulas work in WS-dest. When I look at the spreadsheet in WS-dest, I do not see the formulas in the cells, I see the values of the result of the formulas. However, WS-final's formulas, when trying to use those values fail.
How I have come to this conclusion: WS-dest did not used to use formulas. I hand placed the values in the fields. WS-final worked just fine. A few days ago, I started using formulas to extract the data from WS-source instead of hand-jamming it into WS-dest. Prior to this change, WS-final worked fine. Furthermore, for testing purposes, I replaced a couple of test fields in WS-dest from the formulas to hand-jammed values, which the formulas in WS-final worked again. I took copies of the formulas from WS-final and placed them in unused cells of WS-dest, tweaking the cell references to that the formula would work. As a result, I observed the same behavior.
The macro:
Public Function sbTextToColumn(srcRng As Range) As Variant
Dim vData As Variant
vData = Split(srcTang.Value2, "/")
ReDim Preserve vData(0 To Application.Caller.Columns.Count - 1)
sbTextToColumn = vData
End Function
This is a function that another SO member assisted me with and it works great! I personally am a Linux/Unix guy strong in languages like Perl and PHP. I appreciate your assistance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
问题可能是您的自定义函数使用 Value2 而不是 Value 属性。请参阅http://support.microsoft.com/kb/182812/en-us 差异。
Problem could be your custom function using the Value2 instead of the Value property. See http://support.microsoft.com/kb/182812/en-us for differences.