Novice problem with If...Then statement

[Peskarik]

Hello, guys
This is my first post.
Moreover, I am novice at VBA.

I got a couple of questions. I have this code for expected value calculation:

Function ExpVal(vvec, pvec)
' returns expected value for 2 arrays

If Application.Sum(pvec) <> 1 Or _
Application.Count(vvec) <> Application.Count(pvec) Then
ExpVal = -1
Exit Function

ElseIf pvec.Rows.Count <> vvec.Rows.Count Then vvec = Application.Transpose(vvec)
End If
ExpVal = Application.SumProduct(vvec, pvec)

End Function
[/color blue]
Question no.1:
The function works only if the first If...Then statement is written the way it is written above. If I change it to an in-line code (everything starting with If and finishing with -1 is written on one line): function does not work.
In fact, it works only if ExpVal = -1[/color blue] is written on a separate line. Otherwise I get error message "Compile Error: Else without If".[/color red]
Can someone explain why it is like that?

Question no.2:
This is not a very flexible function, since one has to choose the pvec and vvec correctly (since there is a count function for pvec, it must sum to one, because sum of probabilities must be one.)
What would be the way to change the function so that one can choose vvec and pvec without specific order and still incorporate the probability count function?

I hope someone finds time to answer these questions. Thanks in advance!

 

[firefytr]

Hi there,

Question #1:
Can you give an example? It'd be hard to tell without seeing what you are trying to do.

Question #2:
Can you give us some background on this function? What will you be inputing to it? Is it to be used in the worksheet environment? What will your formula look like? Will you use ranges, values, arrays, etc?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Peskarik]

Hi, Zack

This is a basic Expected Value calculation function. I call it from an Excel spreadsheet writing =ExpVal(A1:A5; B1:B5), for example. So there are 2 arrays of numerical data in that same spreadsheet - a vector of probabilities in B1:B5, (probabilities sum to 1), and a vector of values in A1:A5.

I thought I explained question 1 quite well. Basically, when in the following piece of code:

If Application.Sum(pvec) <> 1 Or _
Application.Count(vvec) <> Application.Count(pvec) Then
ExpVal = -1

the "ExpVal = -1" part is written on a separate line, then the function works.

If "ExpVal = -1" is NOT written on a separate line, I get an run-time error message saying: "Compile Error: Else without If".

I don't understand how ElseIf can be without If, when there is an IF statement in the code.
Quirk of VB?

Please, explain this to me. Thanx



Sergo

 

[PHV]

Why not simply press the F1 key with the cursor inside the ElseIf word ?

 

[AnotherHiggins]

Q1) The underscore allows you to break a line of code in the middle. You can remove the underscore and put all of this on one line:

If Application.Sum(pvec) <> 1 Or Application.Count(vvec) <> Application.Count(pvec) Then

The part that comes after THEN, however, is placed on the next line. If it is included on the same line as the IF, the IF statement is considered self-contained and closed out at the end of the line. In other words, you can't use an ELSE if you include the "ExpVal = -1" on the same line as the IF statement.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

As John pointed out about your "self-contained" If statement not needing a closing End If, what we need to know now is how you want to calculate/express your function. When you say, "What would be the way to change the function so that one can choose vvec and pvec without specific order and still incorporate the probability count function?" How so? Would you like to have a range of cells which are non-contiguous?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Peskarik]

John, thanks! Now I understand!

Zack, not non-contiguous, no. I ment the following:

the way the function is written now, for it to return the correct value (that is - expected value, not -1), I have to input FIRST vector of values and THEN vector of probabilities. So it is, so to speak, ordered input: first probabilities, then values.
But in the formula it does not matter if you multiply probabilities by values or values by probabilities, the answer is the same.
So, I wondered, is it possible to change the function in such a way that the order of input is irrelevant, but still keep the "sum-of-probabilities-equal-one" test. The function then has to understand which input vector is the probability vector.

Obviously, one can clearly indicate in the function pop-up dialog box that first input is probability vector and not to worry about shoice order mistakes because they are caught in the function anyway.
Still, I wondered if this kind of "dynamic" structure is possible to implement.

Sergo

 

[firefytr]

For what you're going for, no. Education is the tool you need. There is only so much you can do for your users, and this isn't one of them. Documentation, mentoring, help files, they all add to the picture. MSFT certainly doesn't make their formulas intuitive, that's why there is error messages and wrong answers, they expect the users to know how to use the formulas because they are told how in the help files.

To be honest with you, I'm really not sure why you are not just using a native SUMPRODUCT formula in the first place..

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci