Create variant array using Text instead of Value

Question

In excel-vba you can create a variant array from a range like so:

Dim x As Variant

x = Range("A1:Z1").Value

This will obviously place the .Value properties into the array. I'm trying to do the same thing but get the .Text property of the cells instead, but I don't think it's possible.

Dim x As Variant

x = Range("A1:Z1").Text '// <~~ type mismatch

---

The reason is as follows, I have a row of data like so:

  |------A------|------B------|------C------|
 1| 01-Jan-2003   27-Feb-2005   15-Sep-2015

I want to output the row to a text file with a pipe delimiter, currently I'm using this:

With WorksheetFunction
    x = .Transpose(.Transpose(Cells(1, 1).Resize(1, 3).Value))
End With

Print #1, Join(x, "|")

Which works, but it's getting the .Value which is formatted as dd/mm/yyyy so the output looks like this:

01/01/2003|27/02/2005|15/09/2015

---

Q: Can I retain the formatting using this method without having to parse/loop each value in the array first?

Answer 1

Not very elegant and I'm not sure how well this would work in bulk but it avoids a loop. You could put the final output into a string and replace:

Dim x As Variant, y As String

x = Range("A1:Z1")

With WorksheetFunction
    x = .Transpose(.Transpose(ActiveSheet.Cells(1, 1).Resize(1, 5).Value))
End With

y = Join(x, "|")

y = Replace(y, "/01/", "-Jan-")
y = Replace(y, "/02/", "-Feb-")
y = Replace(y, "/03/", "-Mar-")
y = Replace(y, "/04/", "-Apr-")
y = Replace(y, "/05/", "-May-")
y = Replace(y, "/06/", "-Jun-")
y = Replace(y, "/07/", "-Jul-")
y = Replace(y, "/08/", "-Aug-")
y = Replace(y, "/09/", "-Sep-")
y = Replace(y, "/10/", "-Oct-")
y = Replace(y, "/11/", "-Nov-")
y = Replace(y, "/12/", "-Dec-")

Debug.print y