函数的参数VBA [英] Function arguments VBA

问题描述

我有这三个功能：

当我运行第2功能，有没有问题，但是当我运行最后一个函数（LMTD），它说被零除然而，当我调试的某些观点有价值观，有的没有。我知道我必须做的，但我想知道为什么我必须这样做，因为这是没有意义的我。

蒂恩功能没有图坦卡蒙的参数，所以我必须把它们添加到蒂恩 - 函数的参数。同去的啧，不知道在座的蒂恩的论点，并LMTD必须有两个蒂恩和图坦卡蒙的论点。如果我这样做，一切顺利进行。为什么我必须这样做？

 公共功能蒂恩（TW，QW，的Qp，Q，DeltaT来）蒂恩=（（（TW * QW）+（啧（Q，FD，混合）* Q））/ QP）+的DeltaT结束功能
 

---

 公共功能啧（Q，FD，混合）啧=蒂恩（TW，QW，的Qp，Q，DeltaT来）_
     - （avgittEffektAiUiLMTD（）/（（Q * FD *混合）/ 3600））结束功能
 

---

 公共功能LMTD（Tsjo）LMTD =（（蒂恩（TW，QW，的Qp，Q的DeltaT） -  Tsjo） - （啧（Q，FD，混合） -  Tsjo））_
    /（WorksheetFunction.Ln（（蒂恩（TW，QW，的Qp，Q的DeltaT） -  Tsjo）_
       /（啧（Q，FD，混合） -  Tsjo）））结束功能
 

解决方案

我会尽量给有关参数如何传递一个有用的和完整的解释：

据我所知，LMTD是调用其它功能的主要功能。
每当一个新的函数被调用时，它被放置在他们所谓的栈榜首;结果
协议栈的原则涉及到内存分配，并在内存（堆栈的顶部）的一端释放：内存分配给声明，在函数中使用堆栈的顶部（函数被调用获取范围的局部变量并在堆栈的顶部形成一个新层），而正在尽快功能超出范围（返回值时）发布这些局部变量。一些通常被称为后进先出（LIFO）。结果
所以，如果你考虑LMTD基础（这可能不是最终的基地，因为它必须由另一个子例程或函数被调用），蒂恩和图坦卡蒙被放置在每当这些函数被调用堆栈的顶部。

不过（这里是点），结果
在功能没有局部声明和传递的参数变量是标准由参考通过，它们是含有由栈的较低层上的功能（或子）发送的参数的存储器地址指针的变量。
当一个函数被引用（默认）采用参数，它可以改变由被传递，从而被返回被调用的函数，当原来的变量值可以改变的存储器地址包含的值。

这个例子说明吧：

 子Base_Sub（）我朦胧单
尺寸C单
昏暗Ð单C = 5
D = 6I = Function_1（C，D）结束小组功能Function_1（C，D）C = 7'。请注意，变量c和d在Base_sub也被更改
D = 5Function_1 = C + D结束功能
 

相反，如果你想通过值（ByVal关键字）发送变量，这将意味着原始变量的副本（即作为参数传递）制成，虽然副本正在处理的原始变量保持不变在功能。换句话说，作为函数超出范围该副本将成为在堆栈的顶部的局部变量，并尽快释放。

因此​​，没有寻找到部门到code到深，当你调用多种功能于一身程序，它可以帮助你记住不同层的这一笼统的概念。
为了保持眼睛的局部变量，使用VBA后续或使用debug.print的本地人窗口即时窗口跟进。
有什么能帮助您获得关于此错误的透明度是执行检查。例如
为蒂恩功能：

 如果QP = 0，则
    在QP'通知的问题。
万一
 

我很抱歉，如果我的解释是比你预期的多，但我试图尽可能完整的就这一个。

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.

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?

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

解决方案

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.

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.

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:

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.

这篇关于函数的参数VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！