函数参数 VBA

Function arguments VBA

提问by Nilzone-

I have these three functions:

我有这三个功能：

When I run the first 2 functions, There's no problem, but when I run the last function (LMTD), It says 'Division by zero' yet when I debug some of the arguments have values, some don't. I know what I have to do, but I want to know why I have to do it, because it makes no sense to me.

当我运行前 2 个函数时，没有问题，但是当我运行最后一个函数 (LMTD) 时，当我调试某些参数时，它显示“除以零”，但有些参数没有。我知道我必须做什么，但我想知道为什么我必须这样做，因为这对我来说毫无意义。

Tinn-function doesn't have Tut's arguments, so I have to add them to Tinn-function's arguments. Same goes for Tut, that doesn't know all of Tinn's arguments, and LMTD has to have both of Tinn and Tut's arguments. If I do that, it all runs smoothly. Why do I have to do this?

Tinn-function 没有 Tut 的参数，所以我必须将它们添加到 Tinn-function 的参数中。Tut 也是如此，它不知道 Tinn 的所有论点，而 LMTD 必须同时拥有 Tinn 和 Tut 的论点。如果我这样做，一切都会顺利进行。为什么我必须这样做？

Public Function Tinn(Tw, Qw, Qp, Q, deltaT)

Tinn = (((Tw * Qw) + (Tut(Q, fd, mix) * Q)) / Qp) + deltaT

End Function

---

---

Public Function Tut(Q, fd, mix)

Tut = Tinn(Tw, Qw, Qp, Q, deltaT) _
    - (avgittEffektAiUiLMTD() / ((Q * fd * mix) / 3600))

End Function

---

---

Public Function LMTD(Tsjo)

LMTD = ((Tinn(Tw, Qw, Qp, Q, deltaT) - Tsjo) - (Tut(Q, fd, mix) - Tsjo)) _
    / (WorksheetFunction.Ln((Tinn(Tw, Qw, Qp, Q, deltaT) - Tsjo) _
       / (Tut(Q, fd, mix) - Tsjo)))

End Function

回答by Trace

I will try to give a useful and complete explanation on how arguments are being passed:

我将尝试对如何传递参数进行有用且完整的解释：

As far as I can tell, LMTD is the main function calling the other function. Each time a new function is called, it is placed on top of what they call the "stack";
The principle of Stack involves that memory is allocated and deallocated at one end of the memory (top of the stack): memory is allocated to those local variables declared and used in the function on top of the stack (function that is called gets in scope and forms a new layer on top of the stack) while these local variables are being released as soon as the function goes out of scope (when the value is returned). Something generally referred to as "Last In First Out" (LIFO).
So if you consider LMTD the base (which is probably not the ultimate base, since it is must be called by another sub routine or function), Tinn and Tut are placed on top of the stack whenever these functions are being called.

据我所知，LMTD 是调用另一个函数的主函数。每次调用一个新函数时，它都会被放置在他们所谓的“堆栈”之上；
Stack的原理是在内存的一端（栈顶）分配和释放内存：内存分配给那些在栈顶的函数中声明和使用的局部变量（被调用的函数gets in scope并在堆栈顶部形成一个新层），而一旦函数超出范围（返回值时），这些局部变量就会被释放。通常称为“后进先出”(LIFO) 的东西。
因此，如果您将 LMTD 视为基础（这可能不是最终基础，因为它必须由另一个子例程或函数调用），每当调用这些函数时，Tinn 和 Tut 都会被放置在堆栈顶部。

However (and here is the point),
Variables not locally declared in functions and passed as argument are standard passed by Reference, they are pointer variables containing the memory address of the arguments sent by the function (or sub) on the lower layer of the stack. When a function takes parameters by reference (default), it can change the values contained by the memory addresses that are passed and thus the original variable value can be changed when the called function is returned.

但是（这就是重点），
未在函数中本地声明并作为参数传递的变量是通过引用标准传递的，它们是指针变量，包含函数（或子）在底层的函数（或子）发送的参数的内存地址堆。当函数通过引用（默认）获取参数时，它可以更改传递的内存地址所包含的值，因此在返回被调用函数时可以更改原始变量值。

This example illustrates it:

这个例子说明了它：

Sub Base_Sub()

Dim i as single
Dim c as single
Dim d as single

c = 5
d = 6

i = Function_1(c, d)

End Sub

Function Function_1(c, d)

c = 7 'Notice that the variables c and d are also changed in the Base_sub
d = 5 

Function_1 = c + d

End Function

On the contrary, if you would send variable by value (byVal keyword), this would mean that a copy of the original variable (that is passed as argument) is made and the original variable remains untouched while the copy is being manipulated in the function. In other words, this copy would become a local variable on top of the stack and released as soon as the function goes out of scope.

相反，如果您按值发送变量（byVal 关键字），这将意味着制作了原始变量（作为参数传递）的副本，并且在函数中操作副本时，原始变量保持不变. 换句话说，这个副本将成为堆栈顶部的局部变量，并在函数超出范围时立即释放。

So without looking into dept into your code to deep, when you call many functions in one routine, it may help you to keep this general concept of the different layers in mind. In order to keep an eye on your local variables, use the "locals" window in VBA for follow-up or use debug.print to follow up in the immediate window. What could help you gain more transparency regarding the error is by performing a check. For example for Tinn function:

因此，无需深入研究代码，当您在一个例程中调用多个函数时，记住不同层的一般概念可能会有所帮助。为了密切关注您的局部变量，请使用 VBA 中的“locals”窗口进行跟进或使用 debug.print 在即时窗口中进行跟进。执行检查可以帮助您提高有关错误的透明度。例如对于 Tinn 函数：

If QP = 0 then 
    'Notify problem at QP. 
end if

I'm sorry if my explanation was more than you expected, but I tried to be as complete as possible on this one.

如果我的解释超出您的预期，我很抱歉，但我试图在这一方面尽可能完整。