链接单元格与Excel形状 [英] Link cell with Excel Shape

问题描述

在excel中，我们插入一个形状，我们可以通过选择形状并在公式栏中键入
单元格引用来将其链接到单元格值。我想知道：我们如何以编程方式执行。像

 对于每个形状作为Excel.Shape在工作。形状
'有像shape.Formula或任何方法，我可以从中做上述
任务。 
下一个
  

这里是我想以编程方式执行的

现在已经几天了，我正在搜索它。真的需要帮助，谢谢Alot。

解决方案

您不需要使用选择将公式应用于形状。应该尽可能避免选择，因为它会膨胀代码，并且可能会产生意想不到的后果 - 例如触发事件

相反，您可以使用DrawingOBject直接使用公式， code> FormApp 这也将让您操纵现有的公式（即将6个单元格添加到A2，使其成为A8，A12到A18等）。第二个代码例程 FormAdd 进行此调整，它适用于单元格地址和范围名称

 code> Sub FormApp（）
 Dim Shp As Shape 
对于每个Shp在ActiveSheet.Shapes 
'公式
 Shp.DrawingObject.Formula == A1
'范围名称
 Shp.DrawingObject.Formula == RangeName
下一个
 End Sub 
 
 Sub FormAdd（）
 Dim Shp As Shape 
 Dim rng1 As Range 
对于每个Shp在ActiveSheet.Shapes 
设置rng1 = Nothing 
如果Len（Shp.DrawingObject.Formula）> 0然后
 On Error Resume Next 
设置rng1 =范围（Shp.DrawingObject.Formula）
错误GoTo 0 
如果不是rng1是没有，那么Shp.DrawingObject.Formula = =&如果
 
 End Sub 
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
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
Here is what I want to do programmatically

It's been days now , I am searching it. Really Need help, Thanks Alot.
解决方案
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

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 FormAdd makes this adjustment, it works on both cell addresses and range names
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


                        
这篇关于链接单元格与Excel形状的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！