I'm making some significant changes to some VBA code, and some variables are being deleted and/or renamed. It would be a lot easier to find all the locations I need to update if the compiler would complain to me that the variables don't exist instead of creating it on the fly.
我正在对某些VBA代码进行一些重大更改,并且正在删除和/或重命名某些变量。如果编译器会向我抱怨变量不存在而不是动态创建它,那么找到我需要更新的所有位置会容易得多。
How do I force VBA/Access to require variables to be declared?
如何强制VBA / Access要求声明变量?
3 个解决方案
#1
You need to use Option Explicit at the top of each VBA code module including forms and reports.
您需要在每个VBA代码模块的顶部使用Option Explicit,包括表单和报告。
You can set this for all future created modules and VBA code behind forms and reports by going into the VBA editor >> Tools >> Options >> Editor tab and ensuring Require Variable Declaration is checked.
您可以通过进入VBA编辑器>>工具>>选项>>编辑器选项卡并确保选中要求变量声明,为表单和报表后面的所有模块和VBA代码设置此项。
From Access 2003 help:
从Access 2003帮助:
Require Variable Declaration — Determines whether explicit variable declarations are required in modules. Selecting this adds the
Option Explicitstatement to general declarations in any new module.需要变量声明 - 确定模块中是否需要显式变量声明。选择此选项会将Option Explicit语句添加到任何新模块中的常规声明中。
I also use camel case when I Dim my variables. ThisIsAnExampleOfCamelCase. As soon as I exit the VBA code line if Access doesn't change the lower case variable to camel case then I know I've got a typo.
当我调暗我的变量时,我也使用驼峰的情况。 ThisIsAnExampleOfCamelCase。一旦我退出VBA代码行,如果Access没有将小写变量更改为camel case,那么我知道我有一个错字。
#2
Some History on OPTION EXPLICIT and Access VBA
To follow on from Tony's answer, here's some explanation of why there are issues with OPTION EXPLICIT not being on in some Access code modules.
接下来回答Tony的回答,这里有一些解释为什么在某些Access代码模块中没有OPTION EXPLICIT的问题。
In Access 95 and Access 97 (the first two Office versions with VBA), Access had a different code editor than the other office programs. In Access 2000, Microsoft implemented the VBE from the other Office apps in Access. At the same time, MS chose to make Access VBA modules behave like the modules in the other apps, which defaulted to not having OPTION EXPLICIT.
在Access 95和Access 97(带有VBA的前两个Office版本)中,Access具有与其他Office程序不同的代码编辑器。在Access 2000中,Microsoft从Access中的其他Office应用程序实现了VBE。与此同时,MS选择使Access VBA模块的行为与其他应用程序中的模块类似,后者默认为没有OPTION EXPLICIT。
Thus, in Access 2000, by default, modules were created without OPTION EXPLICIT.
因此,在Access 2000中,默认情况下,模块是在没有OPTION EXPLICIT的情况下创建的。
This was, of course, a really stupid design decision on MS's part, and they reversed it later (I can't remember if it was Access 2002 or 2003 that rectified the problem and defaulted to OPTION EXPLICIT in all new modules again). The reason it was dumb (and MS should have known this) is because Access is a DATABASE APPLICATION DEVELOPMENT tool, and thus is operating on data that is strongly typed. Thus, the code environment should be strongly typed by default so that it is in harmony with the data it is working with.
当然,对于MS而言,这是一个非常愚蠢的设计决策,他们后来又改变了它(我不记得是Access 2002还是2003纠正了这个问题,并且在所有新模块中再次默认为OPTION EXPLICIT)。它是愚蠢的(MS应该知道这个)的原因是因为Access是数据库应用程序开发工具,因此对强类型的数据进行操作。因此,默认情况下应强烈键入代码环境,以使其与正在使用的数据保持一致。
In Excel or Word, the data is not strongly typed, and it thus makes more sense to use variant data types for just about everything, simply to make it easier for everyone. The downside of implementing that by not using OPTION EXPLICIT is that you can end up with typos that automatically intrdoduce new variables [such as the variable "intrdoduce" -- if I was writing this post with OPTION EXPLICIT, that wouldn't have happened without producing a runtime error! :)]. This is a problem with all such languages that work this way (I pull my hair out working in PHP, where variable names can be distinct by case, i.e., $Var is not the same variable as $var; but I digress), but MS made the decision to implement it that way in Word and Excel on the theory that the people writing code there are going to have an easier time of it if they aren't forced to declare their variables.
在Excel或Word中,数据不是强类型的,因此将变量数据类型用于几乎所有内容更有意义,只是为了让每个人都更容易。通过不使用OPTION EXPLICIT来实现这一点的缺点是你最终可能会自动输入新变量的错别字[例如变量“intrdoduce” - 如果我用OPTION EXPLICIT写这篇文章的话,那就不会没有产生运行时错误! :)。所有这些语言都是以这种方式工作的问题(我把我的头发用在PHP中工作,其中变量名称可以根据情况不同,即,$ Var与$ var不是同一个变量;但我离题了),但是MS决定在Word和Excel中以这种方式实现它的理论是,如果人们不*声明他们的变量,那么编写代码的人将会有更轻松的时间。
So, MS made the mistake of making Access's version of the VBE like the other apps, even though there was no logic internal to Access's own purposes that supported that move. And MS then backed out that change and returned to the previous status quo (i.e., OPTION EXPLICIT by default in all modules).
因此,MS错误地将Access的VBE版本与其他应用程序一样,即使Access本身没有支持该移动的内部逻辑。然后MS退出该更改并返回到先前的状态(即默认情况下,所有模块中的OPTION EXPLICIT)。
Thus, you will often see apps that began life in Access 2000 that have modules all over the place without OPTION EXPLICIT. When I have to work on such an app, my first task is to implement OPTION EXPLICIT in all modules and then fix the damned thing so it will compile (which is often quite tough, given that the thing was programmed without it).
因此,您经常会看到在Access 2000中开始生活的应用程序,这些应用程序在没有OPTION EXPLICIT的情况下具有模块。当我必须在这样的应用程序上工作时,我的第一个任务是在所有模块中实现OPTION EXPLICIT,然后修复该死的东西,这样它就会编译(这通常很难,因为没有它就编程了)。
#3
This question was quite helpful for Excel, you might see if it'll work for Access:
这个问题对Excel很有帮助,你可能会看到它是否适用于Access:
Essentialy, MZ-Tools will search through your code and tell you what is not being used. The version for VBA can be found here. Use the Review Source Code feature.
实际上,MZ-Tools将搜索您的代码并告诉您未使用的内容。可以在此处找到VBA的版本。使用“查看源代码”功能。
#1
You need to use Option Explicit at the top of each VBA code module including forms and reports.
您需要在每个VBA代码模块的顶部使用Option Explicit,包括表单和报告。
You can set this for all future created modules and VBA code behind forms and reports by going into the VBA editor >> Tools >> Options >> Editor tab and ensuring Require Variable Declaration is checked.
您可以通过进入VBA编辑器>>工具>>选项>>编辑器选项卡并确保选中要求变量声明,为表单和报表后面的所有模块和VBA代码设置此项。
From Access 2003 help:
从Access 2003帮助:
Require Variable Declaration — Determines whether explicit variable declarations are required in modules. Selecting this adds the
Option Explicitstatement to general declarations in any new module.需要变量声明 - 确定模块中是否需要显式变量声明。选择此选项会将Option Explicit语句添加到任何新模块中的常规声明中。
I also use camel case when I Dim my variables. ThisIsAnExampleOfCamelCase. As soon as I exit the VBA code line if Access doesn't change the lower case variable to camel case then I know I've got a typo.
当我调暗我的变量时,我也使用驼峰的情况。 ThisIsAnExampleOfCamelCase。一旦我退出VBA代码行,如果Access没有将小写变量更改为camel case,那么我知道我有一个错字。
#2
Some History on OPTION EXPLICIT and Access VBA
To follow on from Tony's answer, here's some explanation of why there are issues with OPTION EXPLICIT not being on in some Access code modules.
接下来回答Tony的回答,这里有一些解释为什么在某些Access代码模块中没有OPTION EXPLICIT的问题。
In Access 95 and Access 97 (the first two Office versions with VBA), Access had a different code editor than the other office programs. In Access 2000, Microsoft implemented the VBE from the other Office apps in Access. At the same time, MS chose to make Access VBA modules behave like the modules in the other apps, which defaulted to not having OPTION EXPLICIT.
在Access 95和Access 97(带有VBA的前两个Office版本)中,Access具有与其他Office程序不同的代码编辑器。在Access 2000中,Microsoft从Access中的其他Office应用程序实现了VBE。与此同时,MS选择使Access VBA模块的行为与其他应用程序中的模块类似,后者默认为没有OPTION EXPLICIT。
Thus, in Access 2000, by default, modules were created without OPTION EXPLICIT.
因此,在Access 2000中,默认情况下,模块是在没有OPTION EXPLICIT的情况下创建的。
This was, of course, a really stupid design decision on MS's part, and they reversed it later (I can't remember if it was Access 2002 or 2003 that rectified the problem and defaulted to OPTION EXPLICIT in all new modules again). The reason it was dumb (and MS should have known this) is because Access is a DATABASE APPLICATION DEVELOPMENT tool, and thus is operating on data that is strongly typed. Thus, the code environment should be strongly typed by default so that it is in harmony with the data it is working with.
当然,对于MS而言,这是一个非常愚蠢的设计决策,他们后来又改变了它(我不记得是Access 2002还是2003纠正了这个问题,并且在所有新模块中再次默认为OPTION EXPLICIT)。它是愚蠢的(MS应该知道这个)的原因是因为Access是数据库应用程序开发工具,因此对强类型的数据进行操作。因此,默认情况下应强烈键入代码环境,以使其与正在使用的数据保持一致。
In Excel or Word, the data is not strongly typed, and it thus makes more sense to use variant data types for just about everything, simply to make it easier for everyone. The downside of implementing that by not using OPTION EXPLICIT is that you can end up with typos that automatically intrdoduce new variables [such as the variable "intrdoduce" -- if I was writing this post with OPTION EXPLICIT, that wouldn't have happened without producing a runtime error! :)]. This is a problem with all such languages that work this way (I pull my hair out working in PHP, where variable names can be distinct by case, i.e., $Var is not the same variable as $var; but I digress), but MS made the decision to implement it that way in Word and Excel on the theory that the people writing code there are going to have an easier time of it if they aren't forced to declare their variables.
在Excel或Word中,数据不是强类型的,因此将变量数据类型用于几乎所有内容更有意义,只是为了让每个人都更容易。通过不使用OPTION EXPLICIT来实现这一点的缺点是你最终可能会自动输入新变量的错别字[例如变量“intrdoduce” - 如果我用OPTION EXPLICIT写这篇文章的话,那就不会没有产生运行时错误! :)。所有这些语言都是以这种方式工作的问题(我把我的头发用在PHP中工作,其中变量名称可以根据情况不同,即,$ Var与$ var不是同一个变量;但我离题了),但是MS决定在Word和Excel中以这种方式实现它的理论是,如果人们不*声明他们的变量,那么编写代码的人将会有更轻松的时间。
So, MS made the mistake of making Access's version of the VBE like the other apps, even though there was no logic internal to Access's own purposes that supported that move. And MS then backed out that change and returned to the previous status quo (i.e., OPTION EXPLICIT by default in all modules).
因此,MS错误地将Access的VBE版本与其他应用程序一样,即使Access本身没有支持该移动的内部逻辑。然后MS退出该更改并返回到先前的状态(即默认情况下,所有模块中的OPTION EXPLICIT)。
Thus, you will often see apps that began life in Access 2000 that have modules all over the place without OPTION EXPLICIT. When I have to work on such an app, my first task is to implement OPTION EXPLICIT in all modules and then fix the damned thing so it will compile (which is often quite tough, given that the thing was programmed without it).
因此,您经常会看到在Access 2000中开始生活的应用程序,这些应用程序在没有OPTION EXPLICIT的情况下具有模块。当我必须在这样的应用程序上工作时,我的第一个任务是在所有模块中实现OPTION EXPLICIT,然后修复该死的东西,这样它就会编译(这通常很难,因为没有它就编程了)。
#3
This question was quite helpful for Excel, you might see if it'll work for Access:
这个问题对Excel很有帮助,你可能会看到它是否适用于Access:
Essentialy, MZ-Tools will search through your code and tell you what is not being used. The version for VBA can be found here. Use the Review Source Code feature.
实际上,MZ-Tools将搜索您的代码并告诉您未使用的内容。可以在此处找到VBA的版本。使用“查看源代码”功能。