“未找到方法或数据成员"；当将工作表调为工作表而不是变体时 [英] "Method or Data Member Not Found" when Dim'ing Worksheet as Worksheet but not Variant

问题描述

考虑这个简单的例子.在新工作表中，创建一个名为Checkbox1

Consider this simple example. In a new sheet create a ActiveX Checkbox called Checkbox1

尝试以下两个子例程.第一个没有编译时出现找不到方法或数据成员"错误，第二个可以正常工作.

Try the following two subroutines. The first does not compile with a "Method or Data Member Not Found" error, the second one works fine.

第一个示例为什么不起作用?

Why doesn't the first example work?

Option Explicit

Sub DoesntWork()
Dim ws As Worksheet
Set ws = Worksheets(1)

MsgBox "Checkbox state is: " + CStr(ws.CheckBox1.Value)

End Sub

Sub Works()
Dim ws As Variant
Set ws = Worksheets(1)

MsgBox "Checkbox state is: " + CStr(ws.CheckBox1.Value)

End Sub

推荐答案

问题出在ws.CheckBox1.Value行.您不能像这样使用它，因此会出现该错误.试试这个

The problem is with the line ws.CheckBox1.Value. You can't use it like this and hence you are getting that error. Try this

Sub Sample()
    Dim ws As Worksheet
    Dim objole As OLEObject

    Set ws = Worksheets(1)

    Set objole = ws.OLEObjects("CheckBox1")

    MsgBox "Checkbox state is: " & objole.Object.Value
End Sub

如果您想直接使用对象，那么您也可以使用它

If you want to use the Object directly then you can also use this

Sub Sample()
    Dim ws As Worksheet

    Set ws = Worksheets(1)

    MsgBox "Checkbox state is: " & Worksheets(ws.Name).CheckBox1.Value
End Sub

这篇关于“未找到方法或数据成员"；当将工作表调为工作表而不是变体时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！