PDA

View Full Version : [SOLVED] More precise number formats with "$" symbol?



helpplease
05-25-2014, 02:14 AM
Does anyone know how to create a number format that contains the $ symbol but which Excel will not limit to 4 decimal place precision as is done with the currency and accounting formats? I have values that I would like to input with greater precision and show with a $ symbol on the worksheet.

For example, if you have the value $0.12345 in a cell using the currency or accounting formats then the value when this cell is referenced via VBA is either 0.1234 or 0.1235 but not 0.12345. This problem goes away if you use a number format that does not contain a $ sign.

Any help would be much appreciated.

GTO
05-25-2014, 02:36 AM
Greetings helpplease and welcome to vbaexpress!

Have you tried a custom number format? By quick example:

$#,##0.00######;[Red]$#,##0.00######

Would that help?

Mark

westconn1
05-25-2014, 02:45 AM
on my old excel, currency format can be up to 30 decimal places, in the format cells dialog

Paul_Hossler
05-25-2014, 07:21 AM
For example, if you have the value $0.12345 in a cell using the currency or accounting formats then the value when this cell is referenced via VBA is either 0.1234 or 0.1235 but not 0.12345. This problem goes away if you use a number format that does not contain a $ sign.

Any help would be much appreciated.


There is a difference between the data value in a cell, and the way the data is displayed.

You could enter 12345.678912345678 and Excel will retain full precision internally. You can apply Mark's format and it will display like that.

Personally, I'd change the Currency Style definition to the desired format. That way any cells that I styled with the [$] style button would be the way I wanted it

GTO
05-25-2014, 09:34 AM
on my old excel, currency format can be up to 30 decimal places, in the format cells dialog


...Personally, I'd change the Currency Style definition to the desired format. That way any cells that I styled with the [$] style button it'd be the way I wanted it

Oopsie :-(

I did not look before answering; my bad. I would certainly agree and would use an app supplied format/method/etc. when available.

I'll go sit in the corner...

Mark

helpplease
05-25-2014, 03:12 PM
There is a difference between the data value in a cell, and the way the data is displayed.

You could enter 12345.678912345678 and Excel will retain full precision internally. You can apply Mark's format and it will display like that.

Personally, I'd change the Currency Style definition to the desired format. That way any cells that I styled with the [$] style button would be the way I wanted it

Thanks for comments. I don't think I explained myself well the first time so trying again. The issue is that I seem to be losing internal precision when I use any number format that includes a $ sign. This issue persists with custom number formats as well as standard currency and accounting formats. This happens when referencing the values in VBA.

I have used the following function to test this out:

Function TestFunction(dblInputVal As Double) As Double
TestFunction= dblInputVal
End Function

If you then put the value 1.234567 in cell A1, the result of TestFunction(A1) will be 1.234567 IF cell A1 uses Number format. IF cell A1 uses Currency or Accounting format (or a custom format using a $ sign) then the result is 1.234600.

I have been surprised by this behavior and am wondering if anyone has dealt with this before or can point out where I'm going wrong.

Thanks.

Paul_Hossler
05-25-2014, 04:08 PM
OK, try using .Value2 instead of just .Value in your VBA macro

From Help ...



The only difference between this property and the Value property is that
the Value2 property doesn’t use the Currency and Date data
types. You can return values formatted with these data types as floating-point
numbers by using the Double data type.

helpplease
05-25-2014, 04:25 PM
OK, try using .Value2 instead of just .Value in your VBA macro

From Help ...

Thanks. Can I ask how you would re-write my simple function belwo to accommodate this? I looked up .Value2 and it appears to relate only to a Range object. Everything I have implemented is through simple formulas like the one below that do not incorporate Range objects.

Function TestFunction(dblInputVal As Double) As Double
TestFunction= dblInputVal
End Function

Paul_Hossler
05-25-2014, 04:57 PM
Actually I decided that I should make a little example for the technique as was doing it



Option Explicit

Function AsValue2(r As Range) As Double

'Decimal variables are stored as 96-bit (12-byte) signed integers scaled by a variable power of 10.
'The power of 10 scaling factor specifies the number of digits to the right of the decimal point, and
'ranges from 0 to 28. With a scale of 0 (no decimal places), the largest possible value is
'+/-79,228,162,514,264,337,593,543,950,335. With a 28 decimal places, the largest value is
'+/-7.9228162514264337593543950335 and the smallest, non-zero value is +/-0.0000000000000000000000000001.
' Note At this time the Decimal data type can only be used within a Variant, that is, you cannot declare
'a variable to be of type Decimal. You can, however, create a Variant whose subtype' is Decimal using the CDec function.

AsValue2 = CDec(r.Value2)
End Function




In the screen shot A1 is 1234.567890123 formatted as Currency, B1 is the function above

helpplease
05-25-2014, 05:26 PM
Thanks Paul. Much appreciated.