Clash Royale CLAN TAG#URR8PPP

VBA Format function, custom format to set numbers as millions with one DP not working

I am running some VBA in Excel 2010 to output numbers from tables within my spreadsheet to an external PowerPoint presentation. Because of the variety of numbers involved, I am using custom formatting strings specified in the tables which the macro is reading to determine the output format like so:

CellData = Format(SourceData,FormattingCode)


I have been able to get this to work with little difficulty for numbers in millions (0,,) and percentages (0.0%). However, I now want to do numbers in millions but displaying one decimal place, that is 1,200,000 should render as 1.2

The format code for doing this normally is (0.0,,) which I can see works in the normal cell formatting settings. However, when I pass this to the format function, I get the number to one decimal place unrounded with no separators like 1200000.0.

Any idea why this is? I know Format function is slightly different to custom formats in that it does not support any cell alignment features for obvious reasons, but can't see why this should be different. Anyone know how I can get the function to output the format I want?

Many thanks,

1 Answer
1

The VBA format function can't handle this format string. So:

Sub dural()
Dim SourceData As Long
Dim CellData As String
SourceData = 1200000
CellData = Format(SourceData, "0.0,,")
MsgBox CellData
End Sub


will make an incorrect string. Use this instead:

Sub dural2()
Dim SourceData As Long, D As Double
Dim CellData As String

SourceData = 1200000
D = SourceData / 1000000
CellData = Format(D, "0.0")

MsgBox CellData
End Sub


EDIT#1:

Use a cell. Process it numerically. Format it. Convert to string:

Sub dural3()
Dim SourceData As Long, D As Double
Dim CellData As String, r As Range
Set r = Range("A1")
SourceData = 1200000
D = SourceData / 1000000

With r
.Clear
.Value = D
.NumberFormat = "0.0"
.Value = .Text 'convert to text
CellData = .Value
End With
MsgBox CellData
End Sub


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.