Hello! I am curious to learn about something called an ‘Argument List’. I
learned this term just recently. I hope I am using the term correctly. I am
talking about the value “Fee” at this site:
http://articles.techrepublic.com.com...1-5300300.html


I guess the argument list receives arguments passed to the procedure. Is
that right? Can someone please educate me as to how this works, and maybe
post a simple example? Or, if someone knows of a good site that explains how
this technique works, please copy/paste the link here.

Also, I am trying to understand how Excel knows in which order to execute
each argument. I found the following code (below) online and am trying to
get it to work, but it doesn’t seem to be cooperating. I named two arrays
and called the function, but I just get a resule of #VALUE! My two ranges
are A1:A4, named ‘loss_amount’ and B1:B4, named ‘probability’. Then in D! I
have: =expval(A1:A4,B1:B4).


Function ExpVal(probability, loss_amount) 'calcs expected value for
arrays named probability and loss_amount
If Application.Sum(probability) <> 1 Or _
Application.Count(loss_amount) <> Application.Count(probability) Then
ExpVal = -1
Exit Function
ElseIf probability.Rows.Count <> loss_amount.Rows.Count Then loss_amount =
Application.Transpose(loss_amount)
End If
ExpVal = Application.SumProduct(loss_amount, probability)
End Function


Is ‘probability’ listed as the first argument because it is tested first in
the If…Then logic sequence above?

Any help would be greatly appreciated.


Regards,
Ryan---


--
RyGuy

UDFs like this are limited in what they can do. Except for a couple of minor
things, they're limited to returning a value to the cell that contains that
formula.

When you tried to transpose a range (not the values), your code broke and
stopped. It returned that #Value! error because of this.

But you can pick up the values from the range and transpose that.

Option Explicit
Function ExpVal(ProbRng As Range, LossAmtRng As Range) As Double

Dim ProbVals As Variant
Dim LossAmtVals As Variant

If Application.Sum(ProbRng) <> 1 _
Or Application.Count(LossAmtRng) <> Application.Count(ProbRng) Then
ExpVal = -1
Exit Function
End If

ProbVals = ProbRng.Value

If ProbRng.Rows.Count <> LossAmtRng.Rows.Count Then
LossAmtVals = Application.Transpose(LossAmtRng.Value)
Else
LossAmtVals = LossAmtRng.Value
End If

ExpVal = Application.SumProduct(ProbVals, LossAmtVals)

End Function

ryguy7272 wrote:
>
> Hello! I am curious to learn about something called an ‘Argument List’. I
> learned this term just recently. I hope I am using the term correctly. I am
> talking about the value “Fee” at this site:
> http://articles.techrepublic.com.com...1-5300300.html
>
> I guess the argument list receives arguments passed to the procedure. Is
> that right? Can someone please educate me as to how this works, and maybe
> post a simple example? Or, if someone knows of a good site that explains how
> this technique works, please copy/paste the link here.
>
> Also, I am trying to understand how Excel knows in which order to execute
> each argument. I found the following code (below) online and am trying to
> get it to work, but it doesn’t seem to be cooperating. I named two arrays
> and called the function, but I just get a resule of #VALUE! My two ranges
> are A1:A4, named ‘loss_amount’ and B1:B4, named ‘probability’. Then in D! I
> have: =expval(A1:A4,B1:B4).
>
> Function ExpVal(probability, loss_amount) 'calcs expected value for
> arrays named probability and loss_amount
> If Application.Sum(probability) <> 1 Or _
> Application.Count(loss_amount) <> Application.Count(probability) Then
> ExpVal = -1
> Exit Function
> ElseIf probability.Rows.Count <> loss_amount.Rows.Count Then loss_amount =
> Application.Transpose(loss_amount)
> End If
> ExpVal = Application.SumProduct(loss_amount, probability)
> End Function
>
> Is ‘probability’ listed as the first argument because it is tested first in
> the If…Then logic sequence above?
>
> Any help would be greatly appreciated.
>
> Regards,
> Ryan---
>
> --
> RyGuy

--

Dave Peterson