software

Is there any problem with using one UDF within another because I am
having problems:

I am trying to input times as 4 digit integers then use the function
quot;TimeDiffquot; to work out the difference between times. Timediff works
fine if used in a spreadsheet cell. I then want to use this function
to work out a relationship between sets of different times. This
function is called quot;Findcasequot;. Both are listed below. It seems that
the problem lies with the multiple use of TimeDiff within FindCase
because the first calculation WsRs = TimeDiff(Ws, Rs) seems to work but
the others do not. Perhaps it's just a bug in my code that I just
cannot see.

Any ideas?Function TimeDiff(BeginTime As Integer, EndTime As Integer) As Single

Dim BeginHour As Integer, BeginMinute As Integer
Dim EndHour As Integer, EndMinute As Integer

If EndTime lt; BeginTime Then EndTime = EndTime 2400

BeginHour = Int(BeginTime / 100)
BeginMinute = BeginTime - (BeginHour * 100)
BeginTime = (BeginHour * 60) BeginMinute

EndHour = Int(EndTime / 100)
EndMinute = EndTime - (EndHour * 100)
EndTime = (EndHour * 60) EndMinute

TimeDiff = (EndTime - BeginTime) / 60

End Function

Function FindCase(Ws As Integer, We As Integer, Rs As Integer, Re As
Integer) As Single
Dim WsRs As Single, RsWe As Single, WsWe As Single, WsRe As Single,
RsWs As Single, WeRe As Single, RsRe As Single

WsRs = TimeDiff(Ws, Rs)
RsWe = TimeDiff(Rs, We)
WsWe = TimeDiff(Ws, We)
WsRe = TimeDiff(Ws, Re)
RsWs = TimeDiff(Rs, Ws)
RsRe = TimeDiff(Rs, Re)

FindCase=0

If WsRs RsWe = WsWe Then FindCase = FindCase 1
If RsWs WsWe WeRe = RsRe Then FindCase = FindCase 10
If WsRe ReWe = WsWe Then FindCase = FindCase 100
If WsRs RsRe ReWe = WsWe Then FindCase = FindCase 1000

End Function--
millsy
------------------------------------------------------------------------
millsy's Profile: www.excelforum.com/member.php...foamp;userid=5122
View this thread: www.excelforum.com/showthread...hreadid=494261lt;the first calculation WsRs = TimeDiff(Ws, Rs) seems to work but the others
do not. gt;

What makes you think so? What does quot;do not workquot; mean?

What are the inputs? What results did you expect and what did you get
instead?--
Kind regards,

Niek Otten

quot;millsyquot; gt; wrote in
message ...
gt;
gt; Is there any problem with using one UDF within another because I am
gt; having problems:
gt;
gt; I am trying to input times as 4 digit integers then use the function
gt; quot;TimeDiffquot; to work out the difference between times. Timediff works
gt; fine if used in a spreadsheet cell. I then want to use this function
gt; to work out a relationship between sets of different times. This
gt; function is called quot;Findcasequot;. Both are listed below. It seems that
gt; the problem lies with the multiple use of TimeDiff within FindCase
gt; because the first calculation WsRs = TimeDiff(Ws, Rs) seems to work but
gt; the others do not. Perhaps it's just a bug in my code that I just
gt; cannot see.
gt;
gt; Any ideas?
gt;
gt;
gt; Function TimeDiff(BeginTime As Integer, EndTime As Integer) As Single
gt;
gt; Dim BeginHour As Integer, BeginMinute As Integer
gt; Dim EndHour As Integer, EndMinute As Integer
gt;
gt; If EndTime lt; BeginTime Then EndTime = EndTime 2400
gt;
gt; BeginHour = Int(BeginTime / 100)
gt; BeginMinute = BeginTime - (BeginHour * 100)
gt; BeginTime = (BeginHour * 60) BeginMinute
gt;
gt; EndHour = Int(EndTime / 100)
gt; EndMinute = EndTime - (EndHour * 100)
gt; EndTime = (EndHour * 60) EndMinute
gt;
gt; TimeDiff = (EndTime - BeginTime) / 60
gt;
gt; End Function
gt;
gt; Function FindCase(Ws As Integer, We As Integer, Rs As Integer, Re As
gt; Integer) As Single
gt; Dim WsRs As Single, RsWe As Single, WsWe As Single, WsRe As Single,
gt; RsWs As Single, WeRe As Single, RsRe As Single
gt;
gt; WsRs = TimeDiff(Ws, Rs)
gt; RsWe = TimeDiff(Rs, We)
gt; WsWe = TimeDiff(Ws, We)
gt; WsRe = TimeDiff(Ws, Re)
gt; RsWs = TimeDiff(Rs, Ws)
gt; RsRe = TimeDiff(Rs, Re)
gt;
gt; FindCase=0
gt;
gt; If WsRs RsWe = WsWe Then FindCase = FindCase 1
gt; If RsWs WsWe WeRe = RsRe Then FindCase = FindCase 10
gt; If WsRe ReWe = WsWe Then FindCase = FindCase 100
gt; If WsRs RsRe ReWe = WsWe Then FindCase = FindCase 1000
gt;
gt; End Function
gt;
gt;
gt; --
gt; millsy
gt; ------------------------------------------------------------------------
gt; millsy's Profile:
gt; www.excelforum.com/member.php...foamp;userid=5122
gt; View this thread: www.excelforum.com/showthread...hreadid=494261
gt;

I know that the first calculation is correct by changing the code so
that I can see its result. One of the inputs I am using is:

Ws = 2300, We=700,Rs=2330, Re=900

If I place a ' in front of all the if statements in the Findcase
function to disable them and change Findcase=0 to Findcase=WsRs I get
the answer 0.5 which I would expect. If I then change Findcase=WsRs to
Findcase=RsWe I get 16.8333 instead of 7.5. If I then put a ' in front
of the line WsRs=Timediff(Ws,Rs) I get the correct answer of 7.5 for
RsWe. The first function call seems to mess up the second and
subsequent ones.

If I use the Timediff function in the spreadsheet I get the correct
answer for RsWe so it is capable of producing the correct answer.

As to the expected output, I would expect Findcase to produce a value
of 1 using the above inputs.--
millsy
------------------------------------------------------------------------
millsy's Profile: www.excelforum.com/member.php...foamp;userid=5122
View this thread: www.excelforum.com/showthread...hreadid=494261If you insert quot;Option Explicitquot; in your module you'll find that you use an
undeclared variable ReWe (twice)
Try and see if correcting just that solves your problem

BTW Toolsgt;Optionsgt;Edit, check quot;Require variable declarationquot; inserts the
quot;Option Explicitquot; in you modules automatically, which is strongly
recommended.

--
Kind regards,

Niek Ottenquot;millsyquot; gt; wrote in
message ...
gt;
gt; I know that the first calculation is correct by changing the code so
gt; that I can see its result. One of the inputs I am using is:
gt;
gt; Ws = 2300, We=700,Rs=2330, Re=900
gt;
gt; If I place a ' in front of all the if statements in the Findcase
gt; function to disable them and change Findcase=0 to Findcase=WsRs I get
gt; the answer 0.5 which I would expect. If I then change Findcase=WsRs to
gt; Findcase=RsWe I get 16.8333 instead of 7.5. If I then put a ' in front
gt; of the line WsRs=Timediff(Ws,Rs) I get the correct answer of 7.5 for
gt; RsWe. The first function call seems to mess up the second and
gt; subsequent ones.
gt;
gt; If I use the Timediff function in the spreadsheet I get the correct
gt; answer for RsWe so it is capable of producing the correct answer.
gt;
gt; As to the expected output, I would expect Findcase to produce a value
gt; of 1 using the above inputs.
gt;
gt;
gt; --
gt; millsy
gt; ------------------------------------------------------------------------
gt; millsy's Profile:
gt; www.excelforum.com/member.php...foamp;userid=5122
gt; View this thread: www.excelforum.com/showthread...hreadid=494261
gt;

The change makes no difference other than to highlight the lack of a
declaration. I agree with your advice but I don't see how it was going
to make any difference anyway. Here's the revised code with all
variable declared. I also changed one of the if statements but that
isn't relevant to the error:

Option ExplicitFunction TimeDiff(BeginTime As Integer, EndTime As Integer) As Single

Dim BeginHour As Integer, BeginMinute As Integer
Dim EndHour As Integer, EndMinute As Integer

If EndTime lt; BeginTime Then EndTime = EndTime 2400

BeginHour = Int(BeginTime / 100)
BeginMinute = BeginTime - (BeginHour * 100)
BeginTime = (BeginHour * 60) BeginMinute

EndHour = Int(EndTime / 100)
EndMinute = EndTime - (EndHour * 100)
EndTime = (EndHour * 60) EndMinute

TimeDiff = (EndTime - BeginTime) / 60

End Function
Function FindCase(Ws As Integer, We As Integer, Rs As Integer, Re As
Integer) As Single
Dim WsRs As Single, RsWe As Single, WsWe As Single, WsRe As Single,
RsWs As Single, WeRe As Single, RsRe As Single, ReWe As Single

WsRs = TimeDiff(Ws, Rs)
RsWe = TimeDiff(Rs, We)
WsWe = TimeDiff(Ws, We)
WsRe = TimeDiff(Ws, Re)
RsWs = TimeDiff(Rs, Ws)
RsRe = TimeDiff(Rs, Re)
WeRe = TimeDiff(We, Re)

FindCase = 0

If WsRs RsWe = WsWe Then FindCase = FindCase 1
If RsWs WsWe WeRe = RsRe Then FindCase = FindCase 10
If WsRe ReWe = WsWe Then FindCase = FindCase 100
If WsRs RsRe ReWe = WsWe Then FindCase = FindCase 1000

End Function--
millsy
------------------------------------------------------------------------
millsy's Profile: www.excelforum.com/member.php...foamp;userid=5122
View this thread: www.excelforum.com/showthread...hreadid=494261You are changing input parameters in Timediff, like BeginTime. If you add a
watch for Ws in FindCase, you can see it changing.

a. Better not do that!
b. Changing the definition of the arguments to ByVal instead of the default
ByRef causes the function to return something, but I don't know enough of
what you're trying to do to say anything about the validity of the result.
ex:

Function TimeDiff(ByVal BeginTime As Integer, ByVal EndTime As Integer) As
Single

BTW I'd always use Long instead of Integer and Double instead of Single:
a. to avoid overflow as much as possible
b. to make optimal use of the 32-bit architecture of your processor

--
Kind regards,

Niek Ottenquot;millsyquot; gt; wrote in
message ...
gt;
gt; The change makes no difference other than to highlight the lack of a
gt; declaration. I agree with your advice but I don't see how it was going
gt; to make any difference anyway. Here's the revised code with all
gt; variable declared. I also changed one of the if statements but that
gt; isn't relevant to the error:
gt;
gt; Option Explicit
gt;
gt;
gt; Function TimeDiff(BeginTime As Integer, EndTime As Integer) As Single
gt;
gt; Dim BeginHour As Integer, BeginMinute As Integer
gt; Dim EndHour As Integer, EndMinute As Integer
gt;
gt; If EndTime lt; BeginTime Then EndTime = EndTime 2400
gt;
gt; BeginHour = Int(BeginTime / 100)
gt; BeginMinute = BeginTime - (BeginHour * 100)
gt; BeginTime = (BeginHour * 60) BeginMinute
gt;
gt; EndHour = Int(EndTime / 100)
gt; EndMinute = EndTime - (EndHour * 100)
gt; EndTime = (EndHour * 60) EndMinute
gt;
gt; TimeDiff = (EndTime - BeginTime) / 60
gt;
gt; End Function
gt;
gt;
gt;
gt; Function FindCase(Ws As Integer, We As Integer, Rs As Integer, Re As
gt; Integer) As Single
gt; Dim WsRs As Single, RsWe As Single, WsWe As Single, WsRe As Single,
gt; RsWs As Single, WeRe As Single, RsRe As Single, ReWe As Single
gt;
gt; WsRs = TimeDiff(Ws, Rs)
gt; RsWe = TimeDiff(Rs, We)
gt; WsWe = TimeDiff(Ws, We)
gt; WsRe = TimeDiff(Ws, Re)
gt; RsWs = TimeDiff(Rs, Ws)
gt; RsRe = TimeDiff(Rs, Re)
gt; WeRe = TimeDiff(We, Re)
gt;
gt; FindCase = 0
gt;
gt; If WsRs RsWe = WsWe Then FindCase = FindCase 1
gt; If RsWs WsWe WeRe = RsRe Then FindCase = FindCase 10
gt; If WsRe ReWe = WsWe Then FindCase = FindCase 100
gt; If WsRs RsRe ReWe = WsWe Then FindCase = FindCase 1000
gt;
gt; End Function
gt;
gt;
gt; --
gt; millsy
gt; ------------------------------------------------------------------------
gt; millsy's Profile:
gt; www.excelforum.com/member.php...foamp;userid=5122
gt; View this thread: www.excelforum.com/showthread...hreadid=494261
gt;
BTW it does produce the expected 1 with your example inputs if you introduce
ByVal
But I suggest you reconsider the design of your functions and not not change
input arguments

--
Kind regards,

Niek Otten

quot;Niek Ottenquot; gt; wrote in message
...
gt; You are changing input parameters in Timediff, like BeginTime. If you add
gt; a watch for Ws in FindCase, you can see it changing.
gt;
gt; a. Better not do that!
gt; b. Changing the definition of the arguments to ByVal instead of the
gt; default ByRef causes the function to return something, but I don't know
gt; enough of what you're trying to do to say anything about the validity of
gt; the result. ex:
gt;
gt; Function TimeDiff(ByVal BeginTime As Integer, ByVal EndTime As Integer) As
gt; Single
gt;
gt; BTW I'd always use Long instead of Integer and Double instead of Single:
gt; a. to avoid overflow as much as possible
gt; b. to make optimal use of the 32-bit architecture of your processor
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt;
gt; quot;millsyquot; gt; wrote in
gt; message ...
gt;gt;
gt;gt; The change makes no difference other than to highlight the lack of a
gt;gt; declaration. I agree with your advice but I don't see how it was going
gt;gt; to make any difference anyway. Here's the revised code with all
gt;gt; variable declared. I also changed one of the if statements but that
gt;gt; isn't relevant to the error:
gt;gt;
gt;gt; Option Explicit
gt;gt;
gt;gt;
gt;gt; Function TimeDiff(BeginTime As Integer, EndTime As Integer) As Single
gt;gt;
gt;gt; Dim BeginHour As Integer, BeginMinute As Integer
gt;gt; Dim EndHour As Integer, EndMinute As Integer
gt;gt;
gt;gt; If EndTime lt; BeginTime Then EndTime = EndTime 2400
gt;gt;
gt;gt; BeginHour = Int(BeginTime / 100)
gt;gt; BeginMinute = BeginTime - (BeginHour * 100)
gt;gt; BeginTime = (BeginHour * 60) BeginMinute
gt;gt;
gt;gt; EndHour = Int(EndTime / 100)
gt;gt; EndMinute = EndTime - (EndHour * 100)
gt;gt; EndTime = (EndHour * 60) EndMinute
gt;gt;
gt;gt; TimeDiff = (EndTime - BeginTime) / 60
gt;gt;
gt;gt; End Function
gt;gt;
gt;gt;
gt;gt;
gt;gt; Function FindCase(Ws As Integer, We As Integer, Rs As Integer, Re As
gt;gt; Integer) As Single
gt;gt; Dim WsRs As Single, RsWe As Single, WsWe As Single, WsRe As Single,
gt;gt; RsWs As Single, WeRe As Single, RsRe As Single, ReWe As Single
gt;gt;
gt;gt; WsRs = TimeDiff(Ws, Rs)
gt;gt; RsWe = TimeDiff(Rs, We)
gt;gt; WsWe = TimeDiff(Ws, We)
gt;gt; WsRe = TimeDiff(Ws, Re)
gt;gt; RsWs = TimeDiff(Rs, Ws)
gt;gt; RsRe = TimeDiff(Rs, Re)
gt;gt; WeRe = TimeDiff(We, Re)
gt;gt;
gt;gt; FindCase = 0
gt;gt;
gt;gt; If WsRs RsWe = WsWe Then FindCase = FindCase 1
gt;gt; If RsWs WsWe WeRe = RsRe Then FindCase = FindCase 10
gt;gt; If WsRe ReWe = WsWe Then FindCase = FindCase 100
gt;gt; If WsRs RsRe ReWe = WsWe Then FindCase = FindCase 1000
gt;gt;
gt;gt; End Function
gt;gt;
gt;gt;
gt;gt; --
gt;gt; millsy
gt;gt; ------------------------------------------------------------------------
gt;gt; millsy's Profile:
gt;gt; www.excelforum.com/member.php...foamp;userid=5122
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=494261
gt;gt;
gt;
gt;

The change to ByVal does seem to work. Thanks very much, I never would
have found that.

I don't understand what you mean about changing the arguments, I
thought that was how functions worked.--
millsy
------------------------------------------------------------------------
millsy's Profile: www.excelforum.com/member.php...foamp;userid=5122
View this thread: www.excelforum.com/showthread...hreadid=494261
Having looked again I can see what you mean, I have changed the values
of BeginTime and Endtime as part of the function. I don't usually do
that!--
millsy
------------------------------------------------------------------------
millsy's Profile: www.excelforum.com/member.php...foamp;userid=5122
View this thread: www.excelforum.com/showthread...hreadid=494261Fine, millsy, cause you shouldn't!
That's why I recommended changing that instead of just using ByVal

--
Kind regards,

Niek Otten

quot;millsyquot; gt; wrote in
message ...
gt;
gt; Having looked again I can see what you mean, I have changed the values
gt; of BeginTime and Endtime as part of the function. I don't usually do
gt; that!
gt;
gt;
gt; --
gt; millsy
gt; ------------------------------------------------------------------------
gt; millsy's Profile:
gt; www.excelforum.com/member.php...foamp;userid=5122
gt; View this thread: www.excelforum.com/showthread...hreadid=494261
gt;