Get formatted values from a multi-cell range

Question

Dim myText As String
myText= Range("a3").Text

Returns the formatted value in cell A3, but

myText= Range("a3:c7").Text

gives me an error.

How do I get strings representing formatted values from a multi-cell range, while preserving the number format? i.e. the format of the output text would be the same as if copy-pasting from the range to a text editor.

Answer 1

The only way to get multiple cell values into an array with one single statement (no loops) is with a Variant array.

Dim varItemName As Variant
varItemName = Range("a3:c7")

If you really absolutely need the names to be type String, then just CStr them later when you use them.

output = FunctionRequiringStringArgument(CStr(varItemName(1,2))

---

EDIT: Okay, okay, you want strings with same format as in sheet.

Here's a full working example.

Dim strMyFormat1 As String
Dim varItemName As Variant
Dim strItemName() As String
Dim strItemNameBF() As String
Dim iCol As Long
Dim iRow As Long
Dim rngMyRange As Range

Set rngMyRange = Range("A3:C7")
varItemName = rngMyRange
ReDim strItemName(LBound(varItemName, 1) To UBound(varItemName, 1), _
    LBound(varItemName, 2) To UBound(varItemName, 2))

'// Take a sample of the format
strMyFormat1 = Range("A3").NumberFormat

'// Apply format sample to all values
For iRow = LBound(varItemName, 1) To UBound(varItemName, 1)
    For iCol = LBound(varItemName, 2) To UBound(varItemName, 2)
        strItemName(iRow, iCol) = Format(varItemName(iRow, iCol), strMyFormat1)
    Next iCol
Next iRow
'// Can also apply to only some values -- adjust loops.
'// More loops go here if many format samples.

'// If all cells have different formats, must use brute force -- slower.
ReDim strItemNameBF(1 To rngMyRange.Rows.Count, _
    1 To rngMyRange.Columns.Count)
For iRow = 1 To rngMyRange.Rows.Count
    For iCol = 1 To rngMyRange.Columns.Count
        strItemNameBF(iRow, iCol) = rngMyRange.Cells(iRow, iCol).Text
    Next iCol
Next iRow