Mac的最新Excel不再编译 [英] Latest Excel for Mac no longer compiles

问题描述

以下代码在最新的Excel Windows以及Mac的Excel 16.28上均能正常运行.但是在最新的Mac版Excel(16.29和16.30)上，它会在代码行 MyShape.Select 上生成以下错误:编译错误:未找到方法或数据成员".

The following code works fine on latest Excel Windows and also on Excel 16.28 on Mac. But on the latest Excel for Mac (16.29 and 16.30) it generates this error: "Compile error: Method or data member not found" on the code line MyShape.Select.

我假设有一种替代方法可以执行编译器将批准的操作，但是我不知道它会是什么.作为一种替代方法，我尝试不选择形状而是仅引用形状，但是随后在 With MyShape.ShapeRange.Fill 行上遇到了相同的错误.

I assume that there is an alternative way to do what I want that the compiler will approve of, but I don't know what it would be. As an alternative, I tried not selecting the shape and just referring to it, but then I get the same error but on the With MyShape.ShapeRange.Fill line.

  Dim MyShape As Shape
  'Other stuff
  Set MyShape = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 400, 400, DistanceBetweenCells, LineWidth)
  MyShape.Select
  With Selection.ShapeRange.Fill
    'stuff here
  End With

我希望发布新版本的Mac Excel时，如果允许上述操作，将还原为旧版本，但是假设不是这种情况，有什么解决方法吗?

I'm hoping that a newer version of Mac Excel, when released, will revert to the older version in allowing the above, but assuming that's not the case, any workarounds?

推荐答案

我喜欢你明确地指的是 ActiveSheet ，恭喜！

I like that you're explicitly referring to ActiveSheet, kudos!

问题是 ActiveSheet 是 Object ，这意味着编译器无能为力: ActiveSheet.Shapes 会编译，但是> ActiveSheet.Shapess -即使指定了 Option Explicit .整个表达式在运行时求值.

The problem is that ActiveSheet is an Object, wich means the compiler is helpless: ActiveSheet.Shapes compiles, but so will ActiveSheet.Shapess - even with Option Explicit specified. The entire expression is evaluated at run-time.

首先解决问题:

Dim sheet As Worksheet
Set sheet = ActiveSheet

现在 sheet.Shapes 获得智能感知和编译时验证，以及随后的 .AddShape 成员调用.您甚至可以在输入参数列表时获得参数工具提示！

Now sheet.Shapes gets intellisense and compile-time validation, along with subsequent the .AddShape member call. You even get parameter tooltips as you type up the argument list!

接下来发生的事情很有趣:您将 MyShape 声明为 Shape ，但是它不是您要查看的 Shape -code> Shape 类没有 ShapeRange 属性，因此... MyShape.ShapeRange 从何而来?

What happens next is interesting: you declared MyShape as a Shape, but it's not a Shape you're looking at - the Shape class doesn't have a ShapeRange property, so... where does MyShape.ShapeRange come from then?

如果在 MyShape.Select 调用后中断执行(使用F9设置断点)，然后调出立即窗格(Ctrl + G)，则答案为出现:

If you break execution (F9 to set a breakpoint) after the MyShape.Select call, and then bring up the immediate pane (Ctrl+G), the answer appears:

?typename(selection)
Rectangle

如果您在单词矩形 ...

If you press Shift+F2 on the word Rectangle...

Dim myRectangle As Excel.Rectangle '<~ here

... VBE似乎无法弄清楚(光标下的标识符未被识别").因此，我们按F2键，然后右键单击某个位置并勾选显示隐藏的成员"选项-可以肯定的是:

...the VBE doesn't seem to figure it out ("identifier under cursor is not recognized"). So we press F2, then right-click somewhere and tick the "Show hidden members" option - and sure enough, there it is:

因此您的代码说让我们使用 Shape 界面"，但可以与 Rectangle 对象一起使用.既然可以了，那就意味着 Rectangle 是一个 Shape :这两个接口只是通过不同的镜头描述了相同的对象，所以两者都可以起作用...但是然后 Shape.ShapeRange 看起来不太正确，因为 Shape 类未定义该成员，而这正是我们明确表示要使用的接口.

So your code says "let's use the Shape interface", but works with a Rectangle object. And since that works, it means a Rectangle "is a" Shape: the two interfaces simply describe the same object through different lens, so either works... but then Shape.ShapeRange doesn't look quite right, since the Shape class doesn't define that member and that's the interface we explicitly said we were going to be working with.

如果我们要调用 Rectangle 的成员，则可以-并且由于我们现在在对象浏览器中显示隐藏的成员，因此intellisense也可以显示隐藏的类型和成员.如果整个 With 块是早期绑定的，那么一切就更有意义了:

If we want to invoke the members of Rectangle, we can - and since we're now showing hidden members in the object browser, intellisense displays the hidden types and members too. If the entire With block is early-bound, everything makes much more sense:

With myRectangle.ShapeRange.Fill

...并说明 ActiveSheet 上的后期绑定代码如何在运行时工作以解决成员调用，现在编译器需要一种完全其他的策略来编译VBA代码:也许可以使事情彻底动摇，使其正常工作，也许不会.至少类型歧义和编译器忽略的语句都消失了:)

...and explains how the late-bound code off ActiveSheet would work at run-time to resolve the member call, and now the compiler needs a completely other strategy to compile the VBA code: maybe that could shake things up enough to get it to work, maybe it won't. At least the type ambiguities and ignored-by-compiler statements are all gone :)

令人惊讶的是，您不能使用VBA用户代码来做到这一点.如果使用 DoSomething 方法创建了 MyShape 类:

The thing that's surprising here, is that you can't do that with VBA user code. If you made a MyShape class with a DoSomething method:

'@ModuleDescription "A metaphorical Shape"
Option Explicit

Public Sub DoSomething()
    MsgBox TypeName(Me)
End Sub

然后是实现 MyShape 的 MyRectangle 类，并在其自己的公共接口上公开成员，从而产生 MyShape 对象引用:

And then a MyRectangle class that implements MyShape and exposes a member on its own public interface, that yields a MyShape object reference:

'@ModuleDescription "A metaphorical Rectangle"
Option Explicit
Private sh As MyShape
Implements MyShape

Public Property Get Thing() As Object
    Set Thing = sh
End Property

Private Sub Class_Initialize()
    Set sh = New MyShape
End Sub

Private Sub MyShape_DoSomething()
    MsgBox TypeName(Me)
End Sub

现在在任何标准模块中，我们都可以对其进行测试-首先，在所有早期绑定中，我们将拥有一个工厂方法，该方法返回 MyShape ，以模仿 Shapes.CreateShape:

And now in any standard module, we can test this - first, all early-bound, and we'll have a factory method that returns a MyShape, to mimick Shapes.CreateShape:

Public Sub WorksMaybe()
    Dim r As MyShape
    Set r = CreateRect
    r.Thing.DoSomething
End Sub

Private Function CreateRect() As MyShape
    Set CreateRect = New MyRectangle
End Function

因此，我们在Windows上运行此代码，并且我希望代码无法编译:

So we run this (on Windows), and I expected, the code doesn't compile:

但是后期绑定...

Public Sub WorksMaybe()
    Dim r As Object
    Set r = CreateRect
    r.Thing.DoSomething
End Sub

Private Function CreateRect() As MyShape
    Set CreateRect = New MyRectangle
End Function

...行得通吗?不:

...works? Nope:

我们不是在查看 MyRectangle 对象吗?否:我们正在研究VBA中后期绑定多态性的限制-我们创建了 New MyRectangle ，但对于编译器 CreateRect 返回 MyShape 对象引用.如果我们在 End Function 上放置一个断点，运行它，然后在击中该断点时在立即窗格(Ctrl + G)中键入?TypeName(CreateRect)尽管声明的类型是 MyShape ，但运行时类型显然是 MyRectangle .

Are we not looking at a MyRectangle object? No: we're looking at the limits of late-binding polymorphism in VBA - we created a New MyRectangle, but to the compiler CreateRect returns a MyShape object reference. If we place a breakpoint on End Function, run it, and then type ?TypeName(CreateRect) in the immediate pane (Ctrl+G) when the breakpoint is hit, then despite the declared type being MyShape, the runtime type is clearly MyRectangle.

它应该有效-但事实并非如此.错误438，找不到成员:与未找到方法或数据成员"的后期绑定/运行时等效的编译错误.

And it should work - but it doesn't. Error 438, member not found: the late-bound/run-time equivalent of the "method or data member not found" compile error.

如果我们使用该界面，那真的意味着可以使用...

And if we use the interface we really mean to work with...

Public Sub WorksMaybe()
    Dim r As MyRectangle
    Set r = CreateRect
    r.Thing.DoSomething
End Sub

Private Function CreateRect() As MyShape
    Set CreateRect = New MyRectangle
End Function

...然后一切正常":

...then everything "just works":

现在，我没有在Mac上运行它，但是这段代码可以为我编译...

Now, I'm not running this on a Mac, but this code compiles for me...

Option Explicit
Const DistanceBetweenCells As Long = 50
Const LineWidth As Long = 2

Public Sub WorksMaybe()
    Dim r As Excel.Rectangle
    Set r = CreateRect
    r.ShapeRange.Fill.BackColor.RGB = vbRed
End Sub

Private Function CreateRect() As Excel.Shape
    Set CreateRect = Shapes.AddShape(msoShapeRectangle, 40, 40, DistanceBetweenCells, LineWidth)
End Function

...并在 CreateRect 返回并且将 Shape 引用分配给 Rectangle 之后，系统地引发运行时错误13-错误13是类型不匹配".换句话说， Rectangle 是 not 是 Shape (!!?！??).证明，如果我们使 CreateRect 返回一个 Excel.Rectangle ，则现在我们在尝试分配该函数的返回值时就得到了类型不匹配错误，并且不再有意义.:发生了一件奇怪的事情，而且，我没主意-尽管 TypeName(选择)声明类型为(毕竟出于某种原因该类是隐藏/未记录的！)，这...几乎摧毁了所有希望，尤其是如果 With Selection.Fill 和使用MyShape.Fill 可以正常工作(尽管在我的Windows机器上确实可以正常工作).

...and systematically raises run-time error 13 as soon as CreateRect returns and the Shape reference gets assigned to a Rectangle - error 13 being "type mismatch". In other words, a Rectangle is not a Shape (!!?!??). Proof, if we make CreateRect return a Excel.Rectangle, we now get the type mismatch error as soon as we try to assign the function's return value, and nothing makes sense anymore: there's something weird going on, and, well, I'm out of ideas - there doesn't appear to be any way to work early-bound with a Rectangle, despite what TypeName(Selection) claims the type is (the class is hidden/undocumented for a reason after all!), which... pretty much destroys all hope, especially if neither With Selection.Fill nor With MyShape.Fill work (it does work perfectly fine here on my Windows box though).

通过用户反馈功能发送带有一些repro代码的皱眉应该会让您从Microsoft产品团队那里听到.我怀疑他们从任何地方都删除了任何内容-但是在某些看似无关的内部API的深处某个地方，有什么问题打破了接口的解析方式不是不可能的:)

Sending a frown with some repro code through the user feedback feature should get you heard from the product team at Microsoft. I doubt they removed anything from anywhere - but it's not impossible something broke how interfaces are resolved, somewhere deep down in some seemingly unrelated piece of internal API :)

这篇关于Mac的最新Excel不再编译的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！