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?

I'm trying to avoid having to loop through the values if possible, in reality it's a bunch of considerably large files so a loop would have a significant impact on the processing time. I've updated my question to reflect that as it wasn't immediately clear tbf — SierraOscar
– SierraOscar, Commented Feb 19, 2016 at 12:11

Jordan · Accepted Answer · 2016-02-19 14:38:51Z

2

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

answered Feb 19, 2016 at 14:38

Jordan

4,5342 gold badges22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SierraOscar Over a year ago

I actually ended up having to loop as there were intermittent whitespace characters that needed tidying up with Trim$() but this would have definitely been an acceptable solution so I'll accept it

SierraOscar Over a year ago

Could even do a mini For i = 1 To 12 loop/replace which would still be quicker than looping through all the values in the array

Collectives™ on Stack Overflow

Create variant array using Text instead of Value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related