Display Number as Text using Custom Format

[MartinS]

I have a workbook where, in a summary section, some of the values should be displayed as text using the cell format from the source range.
I have found that using Format doesn't work, i.e.

Function Udf_GetFormattedValue(rngCell As Range) As String
    Dim strResult As String 
    strResult = rngCell.Value
    If IsEmpty(rngCell) Then
        strResult = ""
    Else        
        If StrComp(rngCell.NumberFormat, "General", vbTextCompare) <> 0 Then
            strResult = WorksheetFunction.Text(strResult, rngCell.NumberFormat)
        End If
    End If
    Udf_GetFormattedValue = strResult
End Function

So Udf_GetFormattedValue(7944808.4,"#,##0.0,;(#,##0.0,)") returns 7,944,808.4, when I'd expect 7,944.8.
I have found that swapping Format for WorksheetFunction.Text works, i.e.

strResult = WorksheetFunction.Text(strResult, rngCell.NumberFormat)

But is there any way without using worksheet functions to return the correctly formatted value from the function?
Many thanks
Martin

 

[Micron]

I'm wondering why you think seven million, nine hundred and forty-four thousand, eight hundred and eight point four (7944808.4) could ever be formatted as seven thousand, nine hundred and forty-four point eight. VBA Format function does not change the stored value, it only changes the way it is represented and the two values you posted are not the same. I can't see how the worksheet function Text would do that either. VBA format function does change number data type to string data type though.

 

[MartinS]

The number is being shown in either thousands or millions, as specified by the user. The idea is to display the number in the same format as part of a string.

 

[Cubist]

You can covert numerically per thousands or millions like this...

Book1
	A	B
1		thousands
2	7,944,808.40	7,944.80840
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(B1="thousands",A2/1000,A2/1000000)

 

[Akuini]

How about using Range.Text property :

Range("B1") = Range("A1").Text