CODEWITHSUNDEEP
excelexcel-2010vba
theta
theta

Reputation: 25611

Convert range formatted as date to text

I have range of cells in date format, formatted as dd.mm.yyyy, like this:

05.10.1993
05.10.1993
05.10.1993
05.10.1993

and I want to convert this range of cells to text format, using VBA, but without iterating each cell in range (as it is slow for large range).

I used this code:

Set rSel = Selection

aDate = rSel.Value
rSel.NumberFormat = "@"
rSel.Value = aDate

So I assign selected range to intermediate array, then convert the range to text format and assign the array back to selected range.

Result is this text:

5/10/1993
5/10/1993
5/10/1993
5/10/1993

and I wonder where did format conversion took place, as if I debug.print for example aDate(1,1) I get expected 05.10.1993 value? Or how can I instruct format in simple snippet I posted so that I get expected text as a result?

Upvotes: 1

Views: 2778

Answers (2)

lori_m
lori_m

Reputation: 5567

In your code, instead of aDate=rSel.Value, try this:

aDate = Application.Text(rSel.Value2,rSel.NumberFormatLocal)

Note

The following range properties are relevant to this example:

.Value returns a variant (number/date/string/boolean/error) with dates in VBA date format.

.Value2 returns a variant (number/string/boolean/error) with dates converted to serial numbers.

.Text returns a string containing the formatted value, this applies to individual cells only.

.NumberFormat returns regional formats in US format (eg entering 1/2/3 in a cell gives m/d/yyyy)

.NumberFormatLocal returns regional formats in local format (eg in European locale d/m/yy)

Upvotes: 2

Doug Glancy
Doug Glancy

Reputation: 27478

Here's something that I posted on my blog. It uses a loop in one direction, but should be a lot faster than your loop attempts. It uses a trick of Selecting a new cell every so often, to account for this bug described by Charles Williams.

Sub NumberToStringWithFormat(rng As Excel.Range)
Dim Texts() As String
Dim i As Long, j As Long

'This might prevent "###" if column too narrow
rng.EntireColumn.AutoFit
'Can't use variables in Dim
ReDim Texts(1 To rng.Rows.Count, 1 To rng.Columns.Count)
For i = 1 To rng.Rows.Count
    'Charles Williams' fix for slow code with Text
   If i Mod 1000 = 0 Then
        rng.Range("A1").Offset(i).Select
    End If
    For j = 1 To rng.Columns.Count
        Texts(i, j) = rng.Cells(i, j).Text
    Next j
Next i
'@ is the Text format
rng.NumberFormat = "@"
rng.Value2 = Texts
End Sub

Upvotes: 1

Related Questions