Guess i have to use some other method other than split?

I believe so. It appears Split() will only ever return an array of strings.
Here's a Split-free example to have a play with.

Sub ArrayToWorksheet()

Dim InputString As String
Dim vArray(1 To 4) As Variant
Dim Comma1 As Long
Dim Comma2 As Long
Dim Comma3 As Long

InputString = "1,Two,3,Four"

Comma1 = InStr(1, InputString, ",")
Comma2 = InStr(Comma1 + 1, InputString, ",")
Comma3 = InStr(Comma2 + 1, InputString, ",")

vArray(1) = Mid(InputString, 1, Comma1 - 1)
vArray(2) = Mid(InputString, Comma1 + 1, Comma2 - Comma1 - 1)
vArray(3) = Mid(InputString, Comma2 + 1, Comma3 - Comma2 - 1)
vArray(4) = Mid(InputString, Comma3 + 1)

Range("A1:D1") = vArray

End Sub




"Bing" wrote:

Ok, i got you know.. i think i know where the problem lies.

Dim columns as Variant
columns = split(inputString, ",") <- i think this returns an array of strings.
range.resize(,UBound(columns)+1) = columns <- puts in text in the cells

I think i need to keepthe columns array as, like you said, as variant.

Guess i have to use some other method other than split?

"Vic Eldridge" wrote:

Hi Bing,

The only way I could get this process to convert from values to text was to
dimension the array as String. Arrays dimmed as Longs,Doubles,Variants all
transferred to the worksheet as values. Is that where the problem lies ?


Regards,
Vic Eldridge


"Bing" wrote:

Hi Vic,

Thanks i tried it.. i got an almost 6x speed improvement. Only 1 thing..
this operation seems to automatically convert numbers as text. So rather
than have to call aRange.PasteSpecial to convert from text back to numbers,
is there a way to do this so that the resize operation treats the data as is,
without converting to text.

Thanks again.

"Vic Eldridge" wrote:

Hi Bing,

Firstly, try replacing the following code ,

for i = 0 to Ubound(newColumnArray)
aRange.Offset(0,i).Value = newColumnArray(i)
next i

with this...

aRange.Resize(, UBound(newColumnArray) + 1) = newColumnArray


What this does is transfer the whole array to the worksheet in one fell
swoop, as opposed to transferring each element one by one. The speed gain
comes from the fact that your calls to Excel's slow Range object are reduced
from multiple calls, to a single call.
Now take this concept a little further. Instead of creating a 1-dimensional
array of each and every row, then transferring each row separately, you
should create a 2-dimensional array of all rows and columns, and transfer the
whole
array in one hit after EOF has been reached.

You'll be removing hundreds of thousands of calls to the Range object.
You're in for a nice surprise. It's gonna fly !


Regards,
Vic Eldridge



"Bing" wrote:

Hi,

I written a macro to read in line by line multiple CSV files of varying
lengths.

Can anyone suggest a faster way in terms of runtime execution time to speed
this code up?

ie. I thought of reading in multiple lines in bulk to reduce I/O but VBA
doesn't seem to have a bulk read mode.

Basically i do the following:

Dim columnArrays as Variant
Dim newColumnArray as Variant
.
.
Do until EOF(inputFile)
Line Input #inputFile, inputLine
columnArray = Split(inputLine, ",")
....merge/massage/apply functions to certain columns in columnArray
and copy into newColumnArray
for i = 0 to Ubound(newColumnArray)
aRange.Offset(0,i).Value = newColumnArray(i)
next i
set aRnage = aRange.Offset(1,0)
Loop

Thanks to everyone for their valuable insights.