Value function error with £ currency

Batwam

New Member
Joined
Nov 10, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,834
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Welcome to the forum. :)

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

Batwam

New Member
Joined
Nov 10, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,834
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No, that wouldn't work, I'm afraid.
 

Batwam

New Member
Joined
Nov 10, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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 £...

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,834
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
any suggestions?
Yes, the same one I made earlier. :) Then you could stop tying yourself in knots trying to make your approach work...
 

Batwam

New Member
Joined
Nov 10, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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.
 
Solution

Batwam

New Member
Joined
Nov 10, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,119,098
Messages
5,576,121
Members
412,697
Latest member
ahem27
Top