vba 将单元格与 Excel 形状链接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8189317/
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
Link cell with Excel Shape
提问by Waqas
In excel, we insert a shape, and we can link it to a cells value, by selecting shape and typing the cell reference in formula bar. I want to know: How can we do that programmatically. Like
在excel中，我们插入一个形状，我们可以通过选择形状并在公式栏中键入单元格引用将其链接到单元格值。我想知道：我们如何以编程方式做到这一点。喜欢
For Each shape As Excel.Shape In workshet.Shapes
'is there something like shape.Formula or any method from which I can do the above
task.
Next
Hereis what I want to do programmatically
这是我想以编程方式做的事情
It's been days now , I am searching it. Really Need help, Thanks Alot.
这几天过去了，我正在寻找它。真的需要帮助，非常感谢。
采纳答案by brettdj
You don't need to use Select to apply formulas to Shapes. Select should be avoided wherever possible as it bloats code, and can have unintended consequences - for example triggering Events
您不需要使用 Select 将公式应用于形状。Select 应该尽可能避免，因为它会使代码膨胀，并且可能会产生意想不到的后果 - 例如触发事件
Instead you can work with the Formula directly by using DrawingOBject, seeFormAppwhich also will let you manipulate existing formula (ie add 6 cell to A2 to make it A8, A12 to A18 etc). The second code routine FormAddmakes this adjustment, it works on both cell addresses and range names
相反，您可以通过使用 DrawingOBject 直接使用公式，查看FormApp哪个还可以让您操作现有公式（即，将 6 个单元格添加到 A2 使其成为 A8、A12 到 A18 等）。第二个代码例程FormAdd进行此调整，它适用于单元格地址和范围名称
Sub FormApp()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
'formula
Shp.DrawingObject.Formula = "=A1"
'range name
Shp.DrawingObject.Formula = "=RangeName"
Next
End Sub
Sub FormAdd()
Dim Shp As Shape
Dim rng1 As Range
For Each Shp In ActiveSheet.Shapes
Set rng1 = Nothing
If Len(Shp.DrawingObject.Formula) > 0 Then
On Error Resume Next
Set rng1 = Range(Shp.DrawingObject.Formula)
On Error GoTo 0
If Not rng1 Is Nothing Then Shp.DrawingObject.Formula = "=" & rng1.Offset(6, 0).Address
End If
Next
End Sub
回答by chris neilsen
The simplest option is to programatically select the shape first, then apply the formula
最简单的选择是先以编程方式选择形状，然后应用公式
Dim Shp as Shape
For each Shp in ActiveSheet.Shapes
Shp.Select
Selection.Formula = "=A1"
Next
Or course this will error if a shape on the sheet does not support the .Formulaproperty
或者，如果工作表上的形状不支持该.Formula属性，这当然会出错
