Write a user defined function in VBA called 'Recommendation' that returns whether the stock you are analyzing is a buy (1), hold (0) or sell (-1).

Question

You will have two parameter inputs: 1) a projected price for the stock (as determined by the analyst) and 2) the current market price. If the stock's projected price is more than $10 per share greater than the current market price the function will return a 1 indicating 'buy'. If the stock's projected price is more than $10 below (absolute value) the current market price then the function will return a -1 indicating 'sell'. Otherwise, if the projected price is within $10 of the current market price then the function will return a 0 indicating 'hold'. If Microsoft is currently selling for $183 per share, use the function to compute recommendations for the following projected prices: A) $170, B) $180, and C) $195. Report these output values in the Answers Tab using your function.

Answer 1

Hi Erica, this question involves writing a conditional test within a VBA function. I'll assume that you know how to build a function within a Visual Basic module in Excel, so I'll focus on just the code syntax here. There are usually multiple ways to approach a coding solution, and I'll outline two methods below. Overall, though, there are three basic steps to this function:

1. Declare the Recommendation function as an Integer with two inputs, which you could call CurrentPrice and Projection. Both of the inputs should be Integers as well.
2. Calculate the intended value of 1, -1, or 0. See details below.
3. Close the function.

Following are two ways to handle the calculation for step 2:

Method 1: If/Else syntax. Since the formula can generate three potential values (1, -1, and 0), you'll need a sequence of If Then, ElseIf Then, and Else before closing with End If. Each test will compare the Projection and CurrentPrice inputs.

Method 2: Case syntax. This approach is a little more complex because you'll need to first Define a variable that contains the difference between Projection and CurrentPrice (a simple subtraction). You can then use this variable in a Case statement, with Case Is for 1, Case Is for -1, and Case Else for 0, before closing the statement with End Select.

For both methods, be sure to type the code using standard format practices, putting code on separate lines as needed. For example,

If Projection > (CurrentPrice + 10) Then

Recommendation = 1

Note, when you test this function in a spreadsheet, be sure to set CurrentPrice as an absolute cell reference (with dollar signs), not a relative cell reference, if you've put CurrentPrice into a single cell.

Hope this helps! Let me know if you have any questions on this.

Bill