Value function error with £ currency

[Batwam]

Hello,

i am trying to apply a currency symbol dynamically using formula rather then VBA. I don’t want to use cell formatting as I am reading these values in other sheets and need to make sure it is clear what currency each number is in.

in order to do some sums on the numbers, I am using the value function which works great for $ and € but not £:
Value(“$100”)=100
Value(“€100”)=100
Value(“£100”)= error

I did quite a few test but couldn’t figure out what the problem is except that the Value function may only “work” for $ and €. would anyone know how to fix this?

 

[RoryA]

Welcome to the forum.

I think you'd make your life easier if you used a separate currency column.

 

[Batwam]

Thanks for the quick response. That’s what I tried originally but the sheet is quite large and this would force me to double the number of columns just for this particular set of rows which isn’t great.

if this is a limitation of the Value function, I could apply a currency symbol using conditional formatting. Would it then be possible to carry this currency “information” accross using the cell(“format” function? I am linking the cells in another sheet using indirect(address(... so I could throw in a format function on top if that gets me the currency symbol.

 

[RoryA]

No, that wouldn't work, I'm afraid.

 

[Batwam]

ok, still working through this an not giving up... Basically, the VALUE the function bugs and can't handle £ formats even though it works with $ and €.

I have managed to get this kind of working by using conditional formatting instead applied to the cells depending on the currency selected. This however creates some issues when copy pasting into other applications as for some reason, cell formatting gets pasted with the symbols fine but not cells with conditional formatting! so, I though I would resort to using a function which calculates the values and applies the formatting to the cell calling it. I got it close to where I need to be as I can effectively change the colour of the cell but not the number format! I could use the format function, that's easy but I'm back to the orignal issue with being unable to do sums on the cells as they are considered as strings if the values are in £ formats (again even though it works with $ and €). I am so close but so far away! any suggestions?

It seems like the only way to make this work is to use the Fomat function for $ and € but not for £ and have the default formatting in £...

Function ReadPrice(SheetName As String, Row As Integer, Column As Integer, CurrencySymbol As String, Rounding As Integer)
Dim rng As String
rng = CurrentCaller()

ReadPrice = Sheets(SheetName).Cells(Row, Column)
'ReadPrice = Format(ReadPrice, CurrencySymbol & "#,0")

Range(rng).Font.Color = RGB(Int(255 * Rnd()), Int(255 * Rnd()), Int(255 * Rnd()))
Range(rng).NumberFormat = "[$£-en-GB]#,##0"


End Function

Public Function CurrentCaller() As String
'find location of cell calling the function
    If TypeOf Application.Caller Is Range Then
        Dim rng As Range
        Set rng = Application.Caller
        CurrentCaller = rng.Address(External:=True)
    Else
        CurrentCaller = CStr(Application.Caller)
    End If
End Function

 

[RoryA]

any suggestions?

Yes, the same one I made earlier. Then you could stop tying yourself in knots trying to make your approach work...

 

[Batwam]

I enjoy the challenge and won't give up! Anyway, I finally got it to work. It doesn't appear possible to change the currency from the function even though the colour can be modified (another thing which doesn't make sense) but the Format Function can be applied and "SUM(VALUE("can be calculated as long as the currency is $ and €, just not £

So I decided to:

• apply £ as default cell formatting and leave the cell alone if £ currency is selected
• If $ and € are selected, format whenever these currencies

In it's most basic form, it works as follows:

Function FormatPrice(Value As Double, CurrencySymbol As String)

If CurrencySymbol = "$" Or CurrencySymbol =" €" Then FormatPrice = Format(Value, CurrencySymbol & "#,0")

End Function

Not that the conditional formatting solution also works and can potentially allow most "exotic" currencies. The solution above wouldn't work if £ wasn't the only non-recognised currency I need to handle. The problem with conditional formatting is that the format is ignored when doing copy-paste (even in Word) which is a problem for me.

 

[Batwam]

Arguably, the above logic can also be included in a simple cell formula. It's just that I'm showing a simplified version and the full version with optional rounding and error handling looks neater as a UDF...