A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

WEND in VBA



 
 
Thread Tools Display Modes
  #1  
Old March 23rd 07, 03:19 PM posted to microsoft.public.excel.misc
Dave F
external usenet poster
 
Posts: 2,574
Default WEND in VBA

What does WEND mean in VBA code, e.g.:

While Abs(kw - Price) > 0.00005
T3 = T1 + (Price - k1) * (T2 - T1) / (k2 - k1)
T1 = T2
k1 = k2
T2 = T3
k2 = (-Application.PV(T2, n, Cpn, 100) + Cpn) / (1 + t) ^ t
- sz
Wend

Does it merely mean While End, sort of like End If?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.
Ads
  #2  
Old March 23rd 07, 03:29 PM posted to microsoft.public.excel.misc
Jim Thomlinson
external usenet poster
 
Posts: 5,942
Default WEND in VBA

While...Wend is just one of the control flow structures. I personally prefer
Do...Loop but there is nothing wrong with While...Wend. Check out this link
on the different control flow structures...

http://msdn.microsoft.com/library/de...7ecff00da4.asp
--
HTH...

Jim Thomlinson


"Dave F" wrote:

> What does WEND mean in VBA code, e.g.:
>
> While Abs(kw - Price) > 0.00005
> T3 = T1 + (Price - k1) * (T2 - T1) / (k2 - k1)
> T1 = T2
> k1 = k2
> T2 = T3
> k2 = (-Application.PV(T2, n, Cpn, 100) + Cpn) / (1 + t) ^ t
> - sz
> Wend
>
> Does it merely mean While End, sort of like End If?
>
> Dave
> --
> A hint to posters: Specific, detailed questions are more likely to be
> answered than questions that provide no detail about your problem.

  #3  
Old March 23rd 07, 03:38 PM posted to microsoft.public.excel.misc
Dave F
external usenet poster
 
Posts: 2,574
Default WEND in VBA

Very helpful, thanks.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Jim Thomlinson" wrote:

> While...Wend is just one of the control flow structures. I personally prefer
> Do...Loop but there is nothing wrong with While...Wend. Check out this link
> on the different control flow structures...
>
> http://msdn.microsoft.com/library/de...7ecff00da4.asp
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Dave F" wrote:
>
> > What does WEND mean in VBA code, e.g.:
> >
> > While Abs(kw - Price) > 0.00005
> > T3 = T1 + (Price - k1) * (T2 - T1) / (k2 - k1)
> > T1 = T2
> > k1 = k2
> > T2 = T3
> > k2 = (-Application.PV(T2, n, Cpn, 100) + Cpn) / (1 + t) ^ t
> > - sz
> > Wend
> >
> > Does it merely mean While End, sort of like End If?
> >
> > Dave
> > --
> > A hint to posters: Specific, detailed questions are more likely to be
> > answered than questions that provide no detail about your problem.

  #4  
Old March 23rd 07, 05:24 PM posted to microsoft.public.excel.misc
Dana DeLouis
external usenet poster
 
Posts: 947
Default WEND in VBA

> While Abs(kw - Price) > 0.00005

As a side note, it is a little unusual here because both kw & Price do not
get changed in the loop.
It appears it could cause an endless loop.
Usually, one or both get change in the loop so that eventually kw-Price
falls below .00005.

--
Dana DeLouis
Windows XP & Office 2007


"Dave F" > wrote in message
...
> Very helpful, thanks.
>
> Dave
> --
> A hint to posters: Specific, detailed questions are more likely to be
> answered than questions that provide no detail about your problem.
>
>
> "Jim Thomlinson" wrote:
>
>> While...Wend is just one of the control flow structures. I personally
>> prefer
>> Do...Loop but there is nothing wrong with While...Wend. Check out this
>> link
>> on the different control flow structures...
>>
>> http://msdn.microsoft.com/library/de...7ecff00da4.asp
>> --
>> HTH...
>>
>> Jim Thomlinson
>>
>>
>> "Dave F" wrote:
>>
>> > What does WEND mean in VBA code, e.g.:
>> >
>> > While Abs(kw - Price) > 0.00005
>> > T3 = T1 + (Price - k1) * (T2 - T1) / (k2 - k1)
>> > T1 = T2
>> > k1 = k2
>> > T2 = T3
>> > k2 = (-Application.PV(T2, n, Cpn, 100) + Cpn) / (1 + t)
>> > ^ t
>> > - sz
>> > Wend
>> >
>> > Does it merely mean While End, sort of like End If?
>> >
>> > Dave
>> > --
>> > A hint to posters: Specific, detailed questions are more likely to be
>> > answered than questions that provide no detail about your problem.



  #5  
Old March 23rd 07, 05:34 PM posted to microsoft.public.excel.misc
Dave F
external usenet poster
 
Posts: 2,574
Default WEND in VBA

Interesting point. The VBA I pasted here is from a larger UDF that
calculates the yield on a bond.

Here's the entire function. Perhaps this would explain the purpose of the
loop:

Function B_Yield_ISMA(Sett_d As Date, Mat_D As Date, Cpn As Double, Price As
Double) As Double
'Calculates the yield of a coupon bearing bond (ISMA)
Dim L As Double, t As Double, sz As Double, T1 As Double, T2 As Double,
T3 As Double
Dim k1 As Double, k2 As Double
Dim n As Long
L = Application.Days360(Sett_d, Mat_D) / 360
n = Int(L)
t = L - n
sz = Cpn * (1 - t)
T1 = Application.Rate(L, Cpn, -Price, 100)
If IsError(T1) Then T2 = -1 Else k1 = (-Application.PV(T1, n, Cpn,
100) + Cpn) / (1 + T1) ^ t - sz
T2 = T1 + 0.00005
k2 = (-Application.PV(T2, n, Cpn, 100) + Cpn) / (1 + T2) ^ t - sz
While Abs(kw - Price) > 0.00005
T3 = T1 + (Price - k1) * (T2 - T1) / (k2 - k1)
T1 = T2
k1 = k2
T2 = T3
k2 = (-Application.PV(T2, n, Cpn, 100) + Cpn) / (1 + t) ^ t
- sz
Wend
B_Yield_ISMA = T2
End Function
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Dana DeLouis" wrote:

> > While Abs(kw - Price) > 0.00005

>
> As a side note, it is a little unusual here because both kw & Price do not
> get changed in the loop.
> It appears it could cause an endless loop.
> Usually, one or both get change in the loop so that eventually kw-Price
> falls below .00005.
>
> --
> Dana DeLouis
> Windows XP & Office 2007
>
>
> "Dave F" > wrote in message
> ...
> > Very helpful, thanks.
> >
> > Dave
> > --
> > A hint to posters: Specific, detailed questions are more likely to be
> > answered than questions that provide no detail about your problem.
> >
> >
> > "Jim Thomlinson" wrote:
> >
> >> While...Wend is just one of the control flow structures. I personally
> >> prefer
> >> Do...Loop but there is nothing wrong with While...Wend. Check out this
> >> link
> >> on the different control flow structures...
> >>
> >> http://msdn.microsoft.com/library/de...7ecff00da4.asp
> >> --
> >> HTH...
> >>
> >> Jim Thomlinson
> >>
> >>
> >> "Dave F" wrote:
> >>
> >> > What does WEND mean in VBA code, e.g.:
> >> >
> >> > While Abs(kw - Price) > 0.00005
> >> > T3 = T1 + (Price - k1) * (T2 - T1) / (k2 - k1)
> >> > T1 = T2
> >> > k1 = k2
> >> > T2 = T3
> >> > k2 = (-Application.PV(T2, n, Cpn, 100) + Cpn) / (1 + t)
> >> > ^ t
> >> > - sz
> >> > Wend
> >> >
> >> > Does it merely mean While End, sort of like End If?
> >> >
> >> > Dave
> >> > --
> >> > A hint to posters: Specific, detailed questions are more likely to be
> >> > answered than questions that provide no detail about your problem.

>
>
>

  #6  
Old March 23rd 07, 06:10 PM posted to microsoft.public.excel.misc
Dana DeLouis
external usenet poster
 
Posts: 947
Default WEND in VBA

> While Abs(kw - Price) > 0.00005

It appears to me that kw could be a typo.
Looking at the code, my guess is that it should be

While Abs(k2 - Price) > 0.00005

It appears to loop until k2 (Pv) calculation is reduced to a value close to
Price.

Since "2" is next to "w" on the keyboard, my guess is that it should be k2,
and not kw.

--
HTH :>)
Dana DeLouis
Windows XP & Office 2007


"Dave F" > wrote in message
...
> Interesting point. The VBA I pasted here is from a larger UDF that
> calculates the yield on a bond.
>
> Here's the entire function. Perhaps this would explain the purpose of the
> loop:
>
> Function B_Yield_ISMA(Sett_d As Date, Mat_D As Date, Cpn As Double, Price
> As
> Double) As Double
> 'Calculates the yield of a coupon bearing bond (ISMA)
> Dim L As Double, t As Double, sz As Double, T1 As Double, T2 As Double,
> T3 As Double
> Dim k1 As Double, k2 As Double
> Dim n As Long
> L = Application.Days360(Sett_d, Mat_D) / 360
> n = Int(L)
> t = L - n
> sz = Cpn * (1 - t)
> T1 = Application.Rate(L, Cpn, -Price, 100)
> If IsError(T1) Then T2 = -1 Else k1 = (-Application.PV(T1, n, Cpn,
> 100) + Cpn) / (1 + T1) ^ t - sz
> T2 = T1 + 0.00005
> k2 = (-Application.PV(T2, n, Cpn, 100) + Cpn) / (1 + T2) ^ t -
> sz
> While Abs(kw - Price) > 0.00005
> T3 = T1 + (Price - k1) * (T2 - T1) / (k2 - k1)
> T1 = T2
> k1 = k2
> T2 = T3
> k2 = (-Application.PV(T2, n, Cpn, 100) + Cpn) / (1 + t) ^ t
> - sz
> Wend
> B_Yield_ISMA = T2
> End Function
> --
> A hint to posters: Specific, detailed questions are more likely to be
> answered than questions that provide no detail about your problem.
>
>
> "Dana DeLouis" wrote:
>
>> > While Abs(kw - Price) > 0.00005

>>
>> As a side note, it is a little unusual here because both kw & Price do
>> not
>> get changed in the loop.
>> It appears it could cause an endless loop.
>> Usually, one or both get change in the loop so that eventually kw-Price
>> falls below .00005.
>>
>> --
>> Dana DeLouis
>> Windows XP & Office 2007
>>
>>
>> "Dave F" > wrote in message
>> ...
>> > Very helpful, thanks.
>> >
>> > Dave
>> > --
>> > A hint to posters: Specific, detailed questions are more likely to be
>> > answered than questions that provide no detail about your problem.
>> >
>> >
>> > "Jim Thomlinson" wrote:
>> >
>> >> While...Wend is just one of the control flow structures. I personally
>> >> prefer
>> >> Do...Loop but there is nothing wrong with While...Wend. Check out this
>> >> link
>> >> on the different control flow structures...
>> >>
>> >> http://msdn.microsoft.com/library/de...7ecff00da4.asp
>> >> --
>> >> HTH...
>> >>
>> >> Jim Thomlinson
>> >>
>> >>
>> >> "Dave F" wrote:
>> >>
>> >> > What does WEND mean in VBA code, e.g.:
>> >> >
>> >> > While Abs(kw - Price) > 0.00005
>> >> > T3 = T1 + (Price - k1) * (T2 - T1) / (k2 - k1)
>> >> > T1 = T2
>> >> > k1 = k2
>> >> > T2 = T3
>> >> > k2 = (-Application.PV(T2, n, Cpn, 100) + Cpn) / (1 +
>> >> > t)
>> >> > ^ t
>> >> > - sz
>> >> > Wend
>> >> >
>> >> > Does it merely mean While End, sort of like End If?
>> >> >
>> >> > Dave
>> >> > --
>> >> > A hint to posters: Specific, detailed questions are more likely to
>> >> > be
>> >> > answered than questions that provide no detail about your problem.

>>
>>
>>



  #7  
Old March 23rd 07, 06:24 PM posted to microsoft.public.excel.misc
Dave F
external usenet poster
 
Posts: 2,574
Default WEND in VBA

You may be right. I got the code in an email from a colleague and have not
had a chance to test it.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Dana DeLouis" wrote:

> > While Abs(kw - Price) > 0.00005

>
> It appears to me that kw could be a typo.
> Looking at the code, my guess is that it should be
>
> While Abs(k2 - Price) > 0.00005
>
> It appears to loop until k2 (Pv) calculation is reduced to a value close to
> Price.
>
> Since "2" is next to "w" on the keyboard, my guess is that it should be k2,
> and not kw.
>
> --
> HTH :>)
> Dana DeLouis
> Windows XP & Office 2007
>
>
> "Dave F" > wrote in message
> ...
> > Interesting point. The VBA I pasted here is from a larger UDF that
> > calculates the yield on a bond.
> >
> > Here's the entire function. Perhaps this would explain the purpose of the
> > loop:
> >
> > Function B_Yield_ISMA(Sett_d As Date, Mat_D As Date, Cpn As Double, Price
> > As
> > Double) As Double
> > 'Calculates the yield of a coupon bearing bond (ISMA)
> > Dim L As Double, t As Double, sz As Double, T1 As Double, T2 As Double,
> > T3 As Double
> > Dim k1 As Double, k2 As Double
> > Dim n As Long
> > L = Application.Days360(Sett_d, Mat_D) / 360
> > n = Int(L)
> > t = L - n
> > sz = Cpn * (1 - t)
> > T1 = Application.Rate(L, Cpn, -Price, 100)
> > If IsError(T1) Then T2 = -1 Else k1 = (-Application.PV(T1, n, Cpn,
> > 100) + Cpn) / (1 + T1) ^ t - sz
> > T2 = T1 + 0.00005
> > k2 = (-Application.PV(T2, n, Cpn, 100) + Cpn) / (1 + T2) ^ t -
> > sz
> > While Abs(kw - Price) > 0.00005
> > T3 = T1 + (Price - k1) * (T2 - T1) / (k2 - k1)
> > T1 = T2
> > k1 = k2
> > T2 = T3
> > k2 = (-Application.PV(T2, n, Cpn, 100) + Cpn) / (1 + t) ^ t
> > - sz
> > Wend
> > B_Yield_ISMA = T2
> > End Function
> > --
> > A hint to posters: Specific, detailed questions are more likely to be
> > answered than questions that provide no detail about your problem.
> >
> >
> > "Dana DeLouis" wrote:
> >
> >> > While Abs(kw - Price) > 0.00005
> >>
> >> As a side note, it is a little unusual here because both kw & Price do
> >> not
> >> get changed in the loop.
> >> It appears it could cause an endless loop.
> >> Usually, one or both get change in the loop so that eventually kw-Price
> >> falls below .00005.
> >>
> >> --
> >> Dana DeLouis
> >> Windows XP & Office 2007
> >>
> >>
> >> "Dave F" > wrote in message
> >> ...
> >> > Very helpful, thanks.
> >> >
> >> > Dave
> >> > --
> >> > A hint to posters: Specific, detailed questions are more likely to be
> >> > answered than questions that provide no detail about your problem.
> >> >
> >> >
> >> > "Jim Thomlinson" wrote:
> >> >
> >> >> While...Wend is just one of the control flow structures. I personally
> >> >> prefer
> >> >> Do...Loop but there is nothing wrong with While...Wend. Check out this
> >> >> link
> >> >> on the different control flow structures...
> >> >>
> >> >> http://msdn.microsoft.com/library/de...7ecff00da4.asp
> >> >> --
> >> >> HTH...
> >> >>
> >> >> Jim Thomlinson
> >> >>
> >> >>
> >> >> "Dave F" wrote:
> >> >>
> >> >> > What does WEND mean in VBA code, e.g.:
> >> >> >
> >> >> > While Abs(kw - Price) > 0.00005
> >> >> > T3 = T1 + (Price - k1) * (T2 - T1) / (k2 - k1)
> >> >> > T1 = T2
> >> >> > k1 = k2
> >> >> > T2 = T3
> >> >> > k2 = (-Application.PV(T2, n, Cpn, 100) + Cpn) / (1 +
> >> >> > t)
> >> >> > ^ t
> >> >> > - sz
> >> >> > Wend
> >> >> >
> >> >> > Does it merely mean While End, sort of like End If?
> >> >> >
> >> >> > Dave
> >> >> > --
> >> >> > A hint to posters: Specific, detailed questions are more likely to
> >> >> > be
> >> >> > answered than questions that provide no detail about your problem.
> >>
> >>
> >>

>
>
>

  #8  
Old March 23rd 07, 11:58 PM posted to microsoft.public.excel.misc
Sandy Mann
external usenet poster
 
Posts: 2,348
Default WEND in VBA

"Dana DeLouis" > wrote in message
...
>> While Abs(kw - Price) > 0.00005

>
> It appears to me that kw could be a typo.
> Looking at the code, my guess is that it should be
>
> While Abs(k2 - Price) > 0.00005
>
> It appears to loop until k2 (Pv) calculation is reduced to a value close
> to Price.
>
> Since "2" is next to "w" on the keyboard, my guess is that it should be
> k2, and not kw.



I seems that when Gord Dibben said to me that other day:

> That's why there is a great herd of us monitoring these groups.


he wasn't kidding!

These NG's never cease to amaze me!

--


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


  #9  
Old March 24th 07, 07:10 AM posted to microsoft.public.excel.misc
Bernd
external usenet poster
 
Posts: 92
Default WEND in VBA

Hello,

Of course kw was a typo.

The original source of the function:
http://www.vbnum.com/download.asp?co...Bo nd%20(ISMA)

He that will not use OPTION EXPLICIT must feel...

Regards,
Bernd

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:06 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.