User defined Fuction Question

[vmauro]

I have a simple UDF,

Function Score(Rst As Currency) As Currency

Select Case Rst

Case Is = 1
Score = "$2,500.00"
Case Is = 1.5
Score = "$3,750.00"
Case Is = 2
Score = "$5,000.00"
Case Is = 2.5
Score = "$6,250.00"
Case Is = 3
Score = "$7,500.00"


End Select

End Function

----
I cant seem to get the cell to automatically format to currency, I can manually format the cells. Is there a way to have the cell format to currency when the user uses this function?

thanks in advance

 

[SkipVought]

Hi,

UDFs return a VALUE and only a value, just like SUM or COUNT spreadsheet functions.

Why is the COLUMN not formatted as currency?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vmauro]

Thanks for the fast reply.

I am new to UDFs and wanted to see if it was possiable to do this withing the function it self. I have several worksheets (15+) in this project and will format each column to currency.

I was just wondering if it could be done, I tried serveal times and just couldnt get it to work.

 

[SkipVought]

Its a one time task to format each column. Then each time a udf is inserted, the numeric value will be so formatted.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Also, you might post such question in the future in forum707, which is dedicated to VBA code questions, such as your, while this forum is not.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vmauro]

Thanks Skip,

I thought UDFs would be able to change the cell format as well as the value, but I was wrong.

I just starting to use these and didn't the limitations.

 

[SkipVought]

You could change the type from Currency to String. But then you would have TEXT and not a number in your column, whcich can present problems in some cases.

Function Score(Rst As Currency) As String
...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

I thought UDFs would be able to change the cell format "

It could, if you passed the cell into the function as a cell object.

Gerry