Returning value > 32767

**irishnutter** · 05-16-2008, 03:52 PM

Hi all, simple question hopefully...although I can't seem to find an easy answer on the Net. I have one of the simplest user-defined functions on the planet. It takes one variable as input (currency), does some simple math and the return value is also currency. The function works fine until the return value exceeds the 2-byte 32767 limit. I have the return value data type defined to be currency (As Currency) and I also tried (As Long) buut both result in an error when the value exceeds 32767. I have to be missing something very simple. BTW, I'm using Excel2007. Thanks in advance...Mike

**jasoncw** · 05-16-2008, 03:59 PM

Can you post up the code so I can try it out?

FYI, declaring a variable as long will result in an integer, so if you are working with fractions of a whole, you should define as double.

Jason

**irishnutter** · 05-16-2008, 04:10 PM

Thanks Jason....function is below. When the input > 3000000, it errs out.

Please Login or Register  to view this content.

**jasoncw** · 05-16-2008, 04:34 PM

Well, you stumped me. Hopefully someone else can help out.

One thing I would like to mention is using Select Case for multiple If conditions. See below for example:

Please Login or Register  to view this content.

Jason

**irishnutter** · 05-16-2008, 04:49 PM

Thanks. Yes, I was going to clean up the structure as I have more logic to add anyway but thanks for the example. Hopefully someone else will have some idea why the currency variable is erroneously acting like an Int.

**irishnutter** · 05-16-2008, 05:13 PM

Hey Jason, i actually cut and paste in your version and modified it a bit to change the rates and tier maxes into variables, etc. and all of a sudden the thing seems to work! I have no idea why but thanks :-)