VBA Function Arguments

Question

I have three functions:

When I run the first 2 functions, there is no problem, but when I run the last function (LMTD), it says "Division by zero", but when I debug some arguments, the values ​​matter and some don't. I know what I need to do, but I want to know why I should do this, because for me it does not make sense.

Tinn-function has no Tut arguments, so I have to add them to the Tinn function arguments. The same goes for Tut, which does not know all Tinn arguments, and LMTD must have both Tinn and Tut arguments. If I do, everything will go smoothly. Why should I 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 

Answer 1

I will try to give a useful and complete explanation of how the arguments are passed:

As far as I can tell, LMTD is the main function calling another function. Each time a new function is called, it is placed on top of what they call a "stack",
The Stack principle assumes that memory is allocated and freed at one end of the memory (top of the stack): the memory is allocated to those local variables that were declared and used in the function on top of the stack (the function that is called gets into the region and forms a new layer on top of the stack ), while these local variables are freed as soon as the function goes out of scope (when the value is returned). Something is usually called "Last In First Out" (LIFO).
Therefore, if you are considering an LMTD base (which is probably not the final base, since it should be called by another routine or function), Tinn and Tut are placed on top of the stack when these functions are called.

However (and here's the point)
Variables that are not declared locally in the function and passed as an argument are standardly passed by reference; these are pointer variables containing the memory address of the arguments sent by the function (or auxiliary) at the lower level of the stack. When a function takes parameters by reference (the default), it can change the values ​​contained in the memory addresses that are transferred, and thus, the original value of the variable can be changed when the called function is returned.

This example illustrates this:

 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 

Conversely, if you send a variable by value (the byVal keyword), this will mean that a copy of the original variable (which is passed as an argument) will be executed, and the original variable will remain untouched when the copy is processed in the function. In other words, this copy will become a local variable on top of the stack and will be released as soon as the function goes out of scope.

Thus, without looking into default in your code to the depth, when you call many functions in one routine, this can help you keep this general concept in relation to different layers. To monitor your local variables, use the "locals" window in VBA for later monitoring, or use debug.print to monitor in the nearest window. What can help you achieve more transparency about the error is to check. For example, for the Tinn function:

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

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