Excel VBA中的公共vs私人/昏暗 [英] Public vs Private/Dim in Excel VBA

问题描述

我可以在理解Excel 2013 VBA的模块中使用Public vs Dim方面提供一些帮助.

I could use some help in understanding using Public vs Dim in a module in Excel 2013 VBA.

首先，我想说的是，我确实找到了具有出色定义的出色文章(请参见下面的链接)，但是没有示例，我可以使用一些示例来说明如何将公共变量应用于我的项目.另外，我对何时需要使用Option Private Module感到困惑.我需要在拥有的每个模块上还是仅在包含以下代码的模块上使用它?

First I want to say I did find this great post with excellent definitions (see link below), but no examples and I could use some examples of how I could apply the Public Variables to my project. Also I am a little confused on when I would need to use the Option Private Module; would I need to use that on each module I have or just the module that holds the below code?

stackoverflow描述差异在公共/私人之间

我想做的是在Standard Mod中进行设置，因此我不必继续通过我的所有UserForm来为工作表设置变量，这些用户窗体对它们引用的工作表使用相同的命名约定.

What I would like to do is set this up in a Standard Mod so I dont have to continue setting variables for worksheets through all of my UserForms that use the same naming convention for Worksheets they reference.

Sub PubVar()
Public wb As Workbook
Public wsSI As Worksheet
Public wsRR As Worksheet
Public wsCalcs As Worksheet
Public wsNarr As Worksheet
Public wsEval As Worksheet
Public wsUW As Worksheet
Public wsLVBA As Worksheet

Set wb = Application.ThisWorkbook
Set wsSI = wb.Sheets("SavedInfo")
Set wsCalcs = wb.Sheets("Calcs")
Set wsNarr = wb.Sheets("Narrative")
Set wsEval = wb.Sheets("EvalCL")
Set wsUW = wb.Sheets("UWCL")
Set wsLVBA = wb.Sheets("ListsForVBA")
End Sub

谢谢您的帮助.

推荐答案

如果您要制作自己的Excel加载项(* .XLAM文件)，则实际上只需要Option Private Module.它允许模块的Public变量对加载项自己的项目中的其他模块可见，但可以防止其他使用您的AddIn的工作表看到/调用它们.

You really should only need Option Private Module if you are making your own Excel Add-In (*.XLAM file). It allows a module's Public variables to be visible to other modules within the Add-In's own project but keeps them from being visible/callable by other worksheets that use your AddIn.

至于其余的，不清楚你在问什么.您链接的问题很好地解释了Public vs Dim/Private. VBA代码无法在同一VBA项目中的不同模块，窗体或类中看到私有/昏暗变量.如果希望所有的VBA代码都能够看到/调用它，则可以使用public.如果希望只在其自己的模块中可见/可调用它，则可以使用private/dim.

As for the rest, it's not clear what you are asking. The question you linked has a good explanation of Public vs Dim/Private. Private/Dim variables cannot be seen by the VBA code in different Modules, Forms or Classes in the same VBA project. You use public if you want all of your VBA code to be able to see/call it. You use private/dim if you want it to only be visible/callable from within it's own module.

但是通常您要对公开的内容保持谨慎，这都是因为它可能给您提供过多的全局名称(令人困惑)，可能导致名称重复的问题(有问题，可以通过以下方式解决)命名标准)，最重要的是因为它可能导致令人困惑/晦涩的错误和可怕的调试问题(因为任何公共变量更改都可能导致任何其他代码可以看到其行为不同).有些事情比其他事情更糟糕:

But generally you want to be judicious with what you make public, both because it can give you too many global names (confusing), can cause problems with duplicate names (problematic, can be addressed with naming standard) and most of all because it can lead to confusing/obscure bugs and horrendous debugging problems (because any public variable change could cause any other code that can see it to behave differently). And some things are worse than others:

Public Subs        OK in a module, but better in a Class
Public Function    Usually fine, especially if its a true function
Public Const       No problem, belongs in modules
Public Variables   Very Bad.  *UNLESS ...*

公共变量是优秀程序员担心的变量.应避免使用它们，除非它们是我们所谓的只读变量".这些变量只需设置一次，就不会再更改.只要遵循该规则，它们实际上就是常量并且可以(尽管只读静态属性会更好，但是VBA在这方面有太多限制).

Public variables are the ones that good coders worry about. They are to be avoided, UNLESS they are what we call "Read-Only Variables". These are variables that you set only once, and never change again. As long as you follow that rule, they are effectively constants and are OK (though read-only static properties would be better, but VBA has too many limitations in this regard).

最后，如果要在所有代码中可见/可用的所有工作表中使用命名变量，但只需设置一次即可，这就是Public的用途，但需要声明("Public")必须位于"模块级别"，这意味着在模块中，但是在任何子例程或函数之外.像这样:

Finally If you want to use named variables for all of your worksheets that are visible/useable from all code, but you only have to setup once, that is what Public is for, but the declaration ("Public ") needs to be at "the Module Level" which means, in a module, but outside of any subroutine or function. Like this:

Public wb As Workbook
Public wsSI As Worksheet
Public wsRR As Worksheet
Public wsCalcs As Worksheet
Public wsNarr As Worksheet
Public wsEval As Worksheet
Public wsUW As Worksheet
Public wsLVBA As Worksheet

' sets all of the Worksheet variables
Sub PubVar()

    Set wb = Application.ThisWorkbook
    Set wsSI = wb.Sheets("SavedInfo")
    Set wsCalcs = wb.Sheets("Calcs")
    Set wsNarr = wb.Sheets("Narrative")
    Set wsEval = wb.Sheets("EvalCL")
    Set wsUW = wb.Sheets("UWCL")
    Set wsLVBA = wb.Sheets("ListsForVBA")

End Sub

这篇关于Excel VBA中的公共vs私人/昏暗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！