Create an array variant using text instead of value

Question

In excel-vba, you can create an array variant from a range as follows:

Dim x As Variant x = Range("A1:Z1").Value 

This will obviously put the .Value properties into an array. I try to do the same, but instead get the .Text property for the cells, but I don't think this is possible.

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

---

The reason is this: I have a data row like this:

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

I want to output a string to a pipe delimited text file, I am currently using this:

 With WorksheetFunction x = .Transpose(.Transpose(Cells(1, 1).Resize(1, 3).Value)) End With Print #1, Join(x, "|") 

Which works, but it gets .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 save formatting with this method without having to parse / quote each value in the array first?

Answer 1

Not very elegant, and I'm not sure how well it will work in bulk, but it avoids the loop. You can put the final output in 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