Format cell for comma-separated values

excel, string, vba

Solution

This works for me. Notice, formatting the cell first before assigning the number to it

Option Explicit

Sub Sample()
    With Cells(1, 1)
        .NumberFormat = "#,##0"
        .Value = 4.65656553432324E+16 '46565655343232400
    End With
End Sub

RESULT

Cell A1 has `46,565,655,343,232,400`

Problem

Written a VBA script which outputs numbers and I thought I had the correct format string for thousand-separating (4,656,565 5,343 232,434 etc) but its not working for certain magnitudes of numbers. So far I am using `Cells(x,y).NumberFormat = "#,###"` can someone provide me with the correct format string to thousand-comma-separate any number, no matter the magnitude?

Original source