Excel - User Defined Function - format cells as well as value

[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,

I already gave you an answer in forum68, regarding changing the return type to String, which would give your TEXT in your sheet. This may work for you but it may alos present problems under some circumstances.

Skip,

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

 

[vmauro]

Thanks skip, sorry,

I though you mention I should post here instead of the other forum.

I just played around with changing it to a string and I see why its a problem, I cant get a total since its a text.

I will just format each column and used the funaction as is