Excel VBA-工作表属性未分配应有的字段 [英] Excel VBA - Sheet Property does not assign a field as it should

问题描述

VBA的新功能-尝试像我在OOP语言中使用Getters和Setters一样使用Sheet属性.我在Sheet1中有以下代码(为清晰起见，已简化):

new to VBA--Trying to use Sheet properties like I would use Getters and Setters in OOP languages. I have the following code in Sheet1(simplified for clarity):

Option Explicit

Private bAllowChange As Boolean

Public Property Let AllowChange(bLetAllowChange As Boolean)
    bAllowChange = bLetAllowChange
End Property

我希望当AllowChange属性由另一个模块中的子程序调用时，bAllowChange字段将相应地更新.通过使用VBE的调试功能，我可以看到正在传递AllowChange正确的值(bLetAllowChange接受正确的值)，但是行

I would expect that when the AllowChange property is called by a sub in another module, the bAllowChange field will be updated accordingly. By using the debugging feature of VBE I can see that AllowChange is being passed the correct value (bLetAllowChange takes on the correct value) but the line

bAllowChange = bLetAllowChange

不将值分配给bAllowChange.如果我将bAllowChange设置为Public，那么它将按预期工作，但是这违背了首先使用Properties的目的.这可能是一些简单的事情，我对VBA中的范围不了解.有什么建议?提前致谢.

does not assign the value to bAllowChange. If I set bAllowChange to Public then it works as expected, however this defeats the purpose of using Properties in the first place. This is probably something simple that I'm not understanding about scope in VBA. Any suggestions? Thanks in advance.

推荐答案

以下对我有用:

在Module1中:

Option Explicit

Sub SetItToFalse()
    Sheet1.AllowChange = False
End Sub
Sub SetItToTrue()
    Sheet1.AllowChange = True
End Sub

在代码名称为Sheet1的工作表中:

In the sheet with a code name of Sheet1:

Option Explicit

Private bAllowChange As Boolean

Public Property Let AllowChange(bLetAllowChange As Boolean)
    bAllowChange = bLetAllowChange
End Property
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox bAllowChange
End Sub

设置该代码后，我可以调用SetItToFalse子例程，然后每次选择更改都会显示它是False.调用SetItToTrue后，每次选择更改都会显示为True.

With that code set up, I can invoke the SetItToFalse subroutine and then every selection change shows me it is False. After invoking SetItToTrue every selection change shows me it is True.

这篇关于Excel VBA-工作表属性未分配应有的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！