VBA MS-Access：在私有子函数中访问子变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8534303/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA MS-Access: Function in a Private Sub to access Sub variables
提问by Henrik Erlandsson
It's a simple question. I've googled around a bit, but found not much that's pertinent.
这是一个简单的问题。我用谷歌搜索了一下，但发现没有多少相关的。
I've finished a large-ish Sub and would like to replace duplicate chunks of code with anything that takes a couple of input params and returns a result.
我已经完成了一个大型的 Sub 并且想用需要几个输入参数并返回结果的任何东西替换重复的代码块。
So basically, I'd like to cut a snippet of code and go
所以基本上，我想剪下一段代码然后去
Private Sub Command1_Click()
Function Calc(input) as Integer
<insert snippet using Sub variables>
End Function
Dim base As Integer
base=1337
total = Calc(55)
if total <100 then total = Calc(56)
End Sub
...where the variable 'base' can be used in the Function. Optimally, to also access variables set by the function without having to put them in an array and return that.
...在函数中可以使用变量“base”。最佳情况下，还可以访问由函数设置的变量，而不必将它们放入数组并返回。
Actually, I'd be happy with a simple include or macro for this. It's just to avoid duplicate code.
实际上，我很乐意为此提供一个简单的包含或宏。这只是为了避免重复代码。
回答by Stephen Turner
You can't nest a function within a sub like this, it'll just show you an error when you compile.
你不能像这样在 sub 中嵌套一个函数，它只会在你编译时显示一个错误。
Put the function outside the sub and pass it all the variables it needs to do the calculation:
将函数放在 sub 之外，并将计算所需的所有变量传递给它：
Private Sub Command1_Click()
Dim base As Integer
base=1337
total = Calc(55, base)
if total <100 then total = Calc(56, base)
End Sub
Function Calc(input as integer, ByRef base as integer) as Integer
<insert snippet using Sub variables>
End Function
Using the ByRef keyword means the reference for the variable is passed rather than the value, this means if you update basein the function, it changes in the sub.
使用 ByRef 关键字意味着传递的是变量的引用而不是值，这意味着如果您base在函数中更新，它会在子中更改。
回答by Olivier Jacot-Descombes
In languages like Pascal or Modula-2, you can nest procedures and functions. This is not possible in VBA.
在 Pascal 或 Modula-2 等语言中，您可以嵌套过程和函数。这在 VBA 中是不可能的。
If you have many parameters to pass, then instead of having individual variables for the parameters, you can regroup the parameters in a user defined type like this:
如果您有许多参数要传递，那么您可以将参数重新组合为用户定义的类型，而不是为参数设置单独的变量，如下所示：
Private Type CalcParameters
number As Double
otherNumber As Double
percent As Long
End Type
Function Calc(params As CalcParameters) As Long
Calc = params.percent * (params.number + params.otherNumber) / 100
End Function
Private Sub Command1_Click()
Dim params As CalcParameters
Dim total As Long
params.number = 77.5
params.otherNumber = 2.5
params.percent = 30
total = Calc(params)
End Sub
A more elaborate solution is to use an object-oriented approach, by creating a class module. Let's call it "clsCalcData":
更精细的解决方案是使用面向对象的方法，通过创建类模块。我们称之为“clsCalcData”：
Option Compare Database
Option Explicit
Public Number As Double
Public OtherNumber As Double
Public Percent As Long
Public Function GetTotal() As Long
GetTotal = Percent * (Number + OtherNumber) / 100
End Function
You would use it like this:
你会像这样使用它：
Private Sub Command1_Click()
Dim calcData As clsCalcData
Dim total As Long
Set calcData = New clsCalcData
calcData.Number = 77.5
calcData.OtherNumber = 2.5
calcData.Percent = 30
total = calcData.GetTotal()
End Sub
Note that here you do not have to pass any parameters at all, since the GetTotal function can access the values directly.
请注意，此处您根本不必传递任何参数，因为 GetTotal 函数可以直接访问这些值。
You can think of a class module like being a module that you can make copies of with the newkeyword. (It copies only the variables, not the functions and sub procedures.)
您可以将类模块视为可以使用new关键字进行复制的模块。（它只复制变量，不复制函数和子过程。）
