Tag: Hacks

Context: I needed to automate the creation of a Word document from a pre-filled Excel file using VBA. Along the way, I had to take individual values from a cell in Excel to a table in Word.

There are ways to do this relatively quickly, but I had to build it one cell at a time, and when I took numerical values out of the Excel cells, they came over as strings, and lost number formatting.

This is how I formatted numbers up to 999 trillion in the move.

Function AddCommas(textValue As String) As String
    If Len(Round(textValue, 0)) > 12 Then
        AddCommas = "$ " _
            & Mid(Round(textValue, 0), 1, Len(Round(textValue, 0)) - 12) _
            & "," _
            & Mid(Round(textValue, 0), Len(Round(textValue, 0)) - 11, 3) _
            & "," _
            & Mid(Round(textValue, 0), Len(Round(textValue, 0)) - 8, 3) _
            & "," _
            & Mid(Round(textValue, 0), Len(Round(textValue, 0)) - 5, 3) _
            & "," _
            & Right(Round(textValue, 0), 3)
    ElseIf Len(Round(textValue, 0)) > 9 Then
        AddCommas = "$ " _
            & Mid(Round(textValue, 0), 1, Len(Round(textValue, 0)) - 9) _
            & "," _
            & Mid(Round(textValue, 0), Len(Round(textValue, 0)) - 8, 3) _
            & "," _
            & Mid(Round(textValue, 0), Len(Round(textValue, 0)) - 5, 3) _
            & "," _
            & Right(Round(textValue, 0), 3)
    ElseIf Len(Round(textValue, 0)) > 6 Then
        AddCommas = "$ " _
            & Mid(Round(textValue, 0), 1, Len(Round(textValue, 0)) - 6) _
            & "," _
            & Mid(Round(textValue, 0), Len(Round(textValue, 0)) - 5, 3) _
            & "," _
            & Right(Round(textValue, 0), 3)
    
    
    ElseIf Len(Round(textValue, 0)) > 3 Then
        AddCommas = "$ " _
            & Mid(Round(textValue, 0), 1, Len(Round(textValue, 0)) - 3) _
            & "," _
            & Right(Round(textValue, 0), 3)
            
    
    Else
        AddCommas = "$ " & _
            Round(textValue, 0)
    End If

End Function

This is likely not the most efficient way to deal with it. However, it works. And that’s all I care about.

Note: This may cause a type mismatch error when called. I was originally attempting to convert Currency values into strings, but I strong-armed the issue by calling the function this way:

AddCommas(CStr(Cell.Value))

The CStr(x) casts x as a string, forcing the function to be able to handle it.