Trying to make Case statements in Excel 2010

[Durlov]

So got some homework that I wanted to make more interesting and test my programming skills. Unfortunately, it came across an obstacle very soon.

What I want is that the depending on the Value of Worksheets("Sheet1").Range("B5") the function will perform different Calculations. I figured I'd get one case working and then attempt to do the rest.

Anyways, this is what I did so far:

Please Login or Register  to view this content.

My thought is that if I enter "=OptionType(B5)" in Cell B9 then It would perform the calculation i.e. Cell B9 = max(0,B2-B6). Note: B5 is a drop-down menu from which the "Long" option is selected.

I have tried a few different codes but I constantly get #NAME?# for the value of Cell B9.

Can anyone tell me what I'm doing wrong? Thanks.

[Leith Ross]

Hello Durlov,

Welcome to the Forum!

A VBA function will calculate when the cell is calculated after it is edited. To make it recalculate when other formulas do, you have to make the function Volatile. I have made the change to your code below.

Please Login or Register  to view this content.

This should fix the problem.

[mikerickson]

Welcome to the Excel Forum !

First, let me suggest that you wrap code in tags (see my sig.) It makes reading the code much easier.

Second, analyizing the posted function

Please Login or Register  to view this content.

If the argument passed to the function is the string "Long" (case sensitive) then it will put the desired MAX(0, Sheet1!B2-Sheet1!B6) in Sheet1!B9.
Otherwise it will do nothing.

There are a couple of problems with that constuction.
1) Functions are for returning values. That function does not return a value, it manipulates cells, but there is no value to the function OptionType.

2) Functions that are called from a worksheet cell cannot/will not manipulate cells. The can read values from cells, but they will not write values to cells (or change cell colors or delete or ....). They only return values.

If you want the value MAX(0, Sheet1!B2-Sheet1!B6) to be put in Sheet1!B9,
You write a UDF like

Please Login or Register  to view this content.

and put the formula =myFunction(B2, B6) in B9.

Notice that the function does not put anything in cells, it returns a value.
The putting in B9 is done by Excel's formula evaluator, the "=" if you will.
MyFunction just give a value for the = to use.
just as it would give a value for =MyFunction(B2, B6) + 1 to use.

I don't understand where B5 and "Long" fit into your problem, but I hope this explained how UDFs work.

[Durlov]

Hey,

Thanks for the help, I figured out how to work the formula, but its a bit weird.

I found out using Shift + F3 that I have to enter
"='Option Type.xlsm'!OptionType.OptionType(B5,B2,B6)" for the formula to work.

Currently as it stands the coding is (I even got the second case working now so thanks a lot):

Please Login or Register  to view this content.

Well the obvious question is, why is excel requiring me to enter
"='Option Type.xlsm'!OptionType.OptionType(B5,B2,B6)" instead of "=OptionType(B5,B2,B6)"

I'm thinking for some reason my function did not get declared as a global function, but I'm a rookie @ VBA programming and am not sure why this is happening. I'm more used to Matlab....

[mikerickson]

Is the UDF code in a normal code module, not a sheet's code module or ThisWorkbook? If not, it should be.

Is the UDF code in a different workbook than the cell with the formula. If so, formula syntax like that will be needed unless or until the workbook with the UDF code is made into an Add-in.