Retrieve variant array of cells as formatted in the cell

[R Avery]

I often use code like the following to grab a variant array of data
containing cell data.


Sub MySub
dim rng as Range, vnt as Variant
set rng=range("A1")
vnt=rng
end Sub

However, this does not grab the data as they are seen in the cell (the
formatted text of the cell. Is there any way to grab that data without
having to use a function like the following? If not, using this
function is acceptable - i just want to know for my own knowledge.


Public Function GetCellsAsFormatted(rng As Excel.Range) As Variant
Dim i As Long, j As Long

If rng Is Nothing Then Exit Sub

ReDim GetCellsAsFormatted(1 To rng.Rows.Count, 1 To
rng.Columns.Count)
For i = LBound(GetCellsAsFormatted, 1) To
UBound(GetCellsAsFormatted, 1)
For j = LBound(GetCellsAsFormatted, 2) To
UBound(GetCellsAsFormatted, 2)
GetCellsAsFormatted(i, j) = Format(rng(i, j).Value, rng(i,
j).NumberFormat)
Next j
Next i
End Function

 

[Tom Ogilvy]

No, you can't do it in your usual fashion.

You can simplify your function by using the Text property.

Public Function GetCellsAsFormatted(rng As Excel.Range) As Variant
Dim i As Long, j As Long

If rng Is Nothing Then Exit Sub

ReDim GetCellsAsFormatted(1 To rng.Rows.Count, 1 To _
rng.Columns.Count)
For i = LBound(GetCellsAsFormatted, 1) To
UBound(GetCellsAsFormatted, 1)
For j = LBound(GetCellsAsFormatted, 2) To _
UBound(GetCellsAsFormatted, 2)
GetCellsAsFormatted(i, j) = rng(i,j).Text

Next j
Next i
End Function

 

[R Avery]

excellent. The Text property always returns the value as formatted in
the cell. I was looking for that. Thanks.