using worksheet functions in code

can i access, in vba, all functions that excel uses on their spreadsheets?
i am looking to use 'yield' and 'price' functions in vba. thanks

Hi Mike,

Many, but not all, Excel worksheet functions are available to VBA.

See 'List of Worksheet Functions Available to Visual Basic' in VBA help.

The Yield and Price functions are part of the Analysis Toolpak.

You need to load the 'Analysis ToolPak - VBA' Addin to use these functions
in VBA.


---
Regards,
Norman



"mike allen" <[email protected]> wrote in message
news:[email protected]...
> can i access, in vba, all functions that excel uses on their spreadsheets?
> i am looking to use 'yield' and 'price' functions in vba. thanks
>

You can access some of Excel's functions using
Application.WorksheetFunctions, but not all. I did not see 'yield' or
'price' when I looked in the Object Browser.


"mike allen" <[email protected]> wrote in message
news:[email protected]...
> can i access, in vba, all functions that excel uses on their spreadsheets?
> i am looking to use 'yield' and 'price' functions in vba. thanks
>

If the worksheet function is available, it's better to use
application.worksheetfunction.functionname (or application.functionname), but if
the function is not available, you can use Evaluate.

I looked at the help for Price and did this in code:

Option Explicit
Sub testme()
Dim myFormula As String

myFormula = "price(" & CLng(DateSerial(2008, 2, 15)) & "," & _
CLng(DateSerial(2017, 11, 17)) & "," & _
"0.0575,0.065,100,2,0)"

MsgBox Application.Evaluate(myFormula)
End Sub

I got the same answer as the help showed.



mike allen wrote:
>
> can i access, in vba, all functions that excel uses on their spreadsheets?
> i am looking to use 'yield' and 'price' functions in vba. thanks

--

Dave Peterson

incredible. it appears to me that this worksheetfunction (price) is NOT
available in vba (as you stated), so "Evaluate" is an alternate way to tap
into excel's function. does "Evaluate" have to be strung together in text
("price(" & "..." & "...") format? thank you very much, mike allen
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> If the worksheet function is available, it's better to use
> application.worksheetfunction.functionname (or application.functionname),
> but if
> the function is not available, you can use Evaluate.
>
> I looked at the help for Price and did this in code:
>
> Option Explicit
> Sub testme()
> Dim myFormula As String
>
> myFormula = "price(" & CLng(DateSerial(2008, 2, 15)) & "," & _
> CLng(DateSerial(2017, 11, 17)) & "," & _
> "0.0575,0.065,100,2,0)"
>
> MsgBox Application.Evaluate(myFormula)
> End Sub
>
> I got the same answer as the help showed.
>
>
>
> mike allen wrote:
>>
>> can i access, in vba, all functions that excel uses on their
>> spreadsheets?
>> i am looking to use 'yield' and 'price' functions in vba. thanks
>
> --
>
> Dave Peterson

Yep.

If you put a:

msgbox myformula

after the line that builds the formula, you'll see what it looks like.

But I cheated by putting the values right in the string.

You could create your own function that did all the work--just once, instead of
each time you need to use the function:

Option Explicit
Sub testme()

MsgBox PriceEval(DateSerial(2008, 2, 15), _
DateSerial(2017, 11, 17), _
0.0575, _
0.065, _
100, _
2, _
0)
End Sub

Function PriceEval(mySettlement As Date, _
myMaturity As Date, _
myRate As Double, _
myYld As Double, _
myRedemption As Double, _
myFrequency As Long, _
myBasis As Long) As Double

Dim myFormula As String

myFormula = CLng(mySettlement) & "," & _
CLng(myMaturity) & "," & _
myRate & "," & _
myYld & "," & _
myRedemption & "," & _
myFrequency & "," & _
myBasis

PriceEval = Application.Evaluate("Price(" & myFormula & ")")

End Function

Ps. There ain't no validation in that routine. Be careful what you pass or add
some validation!



mike allen wrote:
>
> incredible. it appears to me that this worksheetfunction (price) is NOT
> available in vba (as you stated), so "Evaluate" is an alternate way to tap
> into excel's function. does "Evaluate" have to be strung together in text
> ("price(" & "..." & "...") format? thank you very much, mike allen
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > If the worksheet function is available, it's better to use
> > application.worksheetfunction.functionname (or application.functionname),
> > but if
> > the function is not available, you can use Evaluate.
> >
> > I looked at the help for Price and did this in code:
> >
> > Option Explicit
> > Sub testme()
> > Dim myFormula As String
> >
> > myFormula = "price(" & CLng(DateSerial(2008, 2, 15)) & "," & _
> > CLng(DateSerial(2017, 11, 17)) & "," & _
> > "0.0575,0.065,100,2,0)"
> >
> > MsgBox Application.Evaluate(myFormula)
> > End Sub
> >
> > I got the same answer as the help showed.
> >
> >
> >
> > mike allen wrote:
> >>
> >> can i access, in vba, all functions that excel uses on their
> >> spreadsheets?
> >> i am looking to use 'yield' and 'price' functions in vba. thanks
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson