2

In a spreadsheet formula, =VALUE("$100") will evaluate to the numeric value of 100. I then tried to access this function in VBA via WorksheetFunction object, however it is missing.

In VBA I tried the conversion function Val("$100"), however that returns 0. So how can I accomplish this via VBA?

TylerH
  • 20,799
  • 66
  • 75
  • 101
Allen
  • 33
  • 6
  • `In a spreadsheet formula, =VALUE("$100") will evaluate to the numeric value of 100` - Are you sure about this ? It didn't evaluate to 100 in my excel. http://imgur.com/a/cEb2k – Stupid_Intern Nov 03 '16 at 13:27
  • @newguy mine doesn't either but interestingly, it's perfectly happy to evaluate "£100" and "€100" to 100 – danl Nov 03 '16 at 13:31
  • 1
    @danl are you passing a literal string to that function or you are referencing to a cell that is currency formatted. Because if it's a string it wouldn't work. It will work with cell referencing which has the value but it's just formatted. – Stupid_Intern Nov 03 '16 at 13:35
  • @newguy both - I have two cells that are literally typed in as =Value("£100") and =Value("€100"), and two cells that are =Value(B2) for example, where B2 = '£100 or '€100 . Both worked fine, and both methods throw an error when using $ instead of £ or € – danl Nov 03 '16 at 13:38
  • @newguy besides, why would you use Value on a cell that was just formatted as opposed to a string - you're just getting the same value as what's in the cell, which seems a bit of a waste of time (unless you had a mix of values/strings in the column and were trying to get them all the same) – danl Nov 03 '16 at 13:40
  • @danl Yes you are right passing "€100" to Value works but not "$100" don't know why. – Stupid_Intern Nov 03 '16 at 13:44
  • 1
    @Allen:Whether `=VALUE("$100")` works or not depends on the locale and Excel's language version. The [Type Conversion Functions](https://msdn.microsoft.com/en-us/library/gg278896.aspx) are working the same way. So maybe `CDbl("$100")` will work for your Excel in VBA. – Axel Richter Nov 03 '16 at 13:54
  • I'm using Excel2013, and positive about =VALUE("$100") equaling 100. – Allen Nov 03 '16 at 15:38
  • @newguy - I'm cleaning up data exported from some other app, so not under my control. Axel - Yes, CDbl() does work in VBA, however it doesn't make sense why VALUE would be missing from the WorksheetFunction object. – Allen Nov 03 '16 at 15:44
  • @Allen `It doesn't make sense why VALUE would be missing from the WorksheetFunction object` maybe because you can access value using `Range("A1").Value` – Stupid_Intern Nov 03 '16 at 15:57
  • @newguy - Range("A1").Value will still return the full string, not the numerical value. So it seems that Axel's recommendation of Cdbl() is the best way to go. – Allen Nov 03 '16 at 17:57
  • @Allen : "it doesn't make sense why VALUE would be missing from the WorksheetFunction object". Yes, it doesn't make sense, but I did find that at least the omission is documented: https://learn.microsoft.com/en-us/office/vba/excel/concepts/events-worksheetfunctions-shapes/list-of-worksheet-functions-available-to-visual-basic – NewSites Nov 04 '18 at 01:46

2 Answers2

0

Val() only really works if the string is all numbers I'm afraid - currency signs cause it a problem.

If you're always going to have the same currency sign in the string, it might be worth using something like

StringName = replace(StringName, "$", "")

to take out the $ by replacing it with "" - otherwise if your strings aren't always going to be this predictable the below question might help:

How to find numbers from a string?

Community
  • 1
  • 1
danl
  • 430
  • 3
  • 6
  • *Val() only really works if the string is all numbers I'm afraid* - `? Val("42 Not true")`. – GSerg Jul 23 '19 at 13:59
0

see https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.numbervalue

example of using above, which will return a value of -1234.56:

MsgBox WorksheetFunction.NumberValue("-$1,234.56", ".", ",")

Note that if the result is non-numeric, it throws an error. For example (swapping the comma grouping and decimal character params which is invalid in this case):

MsgBox WorksheetFunction.NumberValue("-$1,234.56", ".", ",")

error detail

I don't understand why the above link doesn't have any version info. It is currently dated 2019‎-‎05‎-‎23 - no idea if that's because it is new or if it was recently updated.

spioter
  • 1,829
  • 1
  • 13
  • 19