在 Excel VBA 中获取当前单元格周围的相邻单元格值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44174434/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting adjacent cell values around the current cell in Excel VBA
提问by Ashram Kane
I want to get the adjacent cell values for calculation in VBA.
我想在 VBA 中获取用于计算的相邻单元格值。
It is easy to get the left cell of the current cell(e.g.H19) usually like this:
很容易得到当前单元格（egH19）的左单元格，通常是这样的：
=H18
If I copy this cell to other cells, each of them is changed to an appropriate cell number.
如果我将此单元格复制到其他单元格，则每个单元格都会更改为适当的单元格编号。
But in VBA code, I am not sure if I am right to get the value of the left cell.
但是在 VBA 代码中，我不确定我是否正确获取左侧单元格的值。
Public Function LeftCell()
LeftCell = ActiveCell.Offset(0, -1).Value
End Function
I am not sure this is correct, I tested copying this cell to other cells but each result is not changed automatically.
我不确定这是否正确，我测试了将此单元格复制到其他单元格但不会自动更改每个结果。
I clicked all kinds of Calcuation buttons on the Menu, changed Calculation as Automatic, but there is no calculation occur.
我点击了菜单上的各种计算按钮，将计算更改为自动，但没有计算发生。
The only way I can do is to manually select each cell and press enter.
我能做的唯一方法是手动选择每个单元格并按回车键。
Is there any way to calculate all cell values? Otherwise, "The Active Cell" means "The Selected Cell by Cursor"?
有没有办法计算所有单元格值？否则，“活动单元格”是指“光标选定的单元格”？
Thanks for your help in advance.
提前感谢您的帮助。
回答by Darren Bartrup-Cook
Adding a formula as @Chris Harper suggests would work, but then you may as well just write the formula in the cell.
按照@Chris Harper 的建议添加公式会起作用，但是您也可以在单元格中编写公式。
Rather than the ActiveCell you want the cell that called the formula.
您想要调用公式的单元格而不是 ActiveCell。
Public Function LeftCell()
LeftCell = Application.Caller.Offset(, -1).Value
End Function
Edit: If you want the cell to update whenever you change the value add Application.Volatile Trueas your first line in the function.
编辑：如果您希望单元格在您更改值时更新Application.Volatile True为函数中的第一行。
https://msdn.microsoft.com/en-us/library/office/ff193687.aspx
https://msdn.microsoft.com/en-us/library/office/ff193687.aspx
回答by Chris Harper
Calculate method in Excel VBA do all kind of calculations. You can even define a range to calculate only a range of specific cells like Worksheets("Sheet1").Calculate.
Excel VBA 中的计算方法进行各种计算。您甚至可以定义一个范围来仅计算特定单元格的范围，例如 Worksheets("Sheet1").Calculate。
Yes, ActiveCell is always the Selected Cell.
是的，ActiveCell 始终是选定的单元格。
As an alternative to setting value by Offset, you can use ActiveCell.FormulaR1C1 = "=RC[-1]"
作为通过偏移设置值的替代方法，您可以使用 ActiveCell.FormulaR1C1 = "=RC[-1]"
