Which is better?
 

This may be just simple, I read in some articles of Excel VBA that use of variants will put unnecessary overhead on execution, so it better to define every variable. but writing to a range of cells with a variant array is supposed to be fast
some thing like,
range("A1").Resize(1,17)=getVariantArra

But will this variant array affect the performance? Is looping through all the individual cells a better option

Any help in this regard will be appreciate

Thanks and Regards
Amar

Which is better?
 

I doubt it will have any significant effect on performance - certainly much
less performance impact than looping through cells.

You actually only need a variant to pick the data up. You can write an
array of other types - however, if you pick the data up, then modify it,
then want to put it down again, there is no reason to not use a variant.

--
Regards,
Tom Ogilvy

"Amar" wrote in message
...
This may be just simple, I read in some articles of Excel VBA that use of
variants will put unnecessary overhead on execution, so it better to define
every variable. but writing to a range of cells with a variant array is
supposed to be fast.
some thing like,
range("A1").Resize(1,17)=getVariantArray

But will this variant array affect the performance? Is looping through all
the individual cells a better option?

Any help in this regard will be appreciated

Thanks and Regards,
Amar

Which is better?
 

Tom Ogilvy wrote:
. . . You can write an
array of other types-however, if you pick the data up, then modify it,
then want to put it down again, there is no reason to not use a variant.

Unless while modifying it you want to restrict the type of data to which
an element can be changed, or, if you are going to ReDim Preserve it,
you want to restrict the type of data to be added to it.

Alan Beban

Which is better?
 

Since your code will be doing the adding and modifying, I think you could
enforce any conditions you wanted without declaring the array as a specific
type.

Not sure what you are suggesting with redim preserve, but his fails for me:

Sub ABCE()
Dim varr As Variant
varr = Array(1, 2, 3, 4, 5)
ReDim Preserve varr(0 To 100) as Long
End Sub
and the failure is consistent with the help on redim: If the array is
contained in a Variant, the type of the elements can be changed using an As
type clause, unless you're using the Preserve keyword, in which case, no
changes of data type are permitted.

if that is what you were suggesting.

--
Regards,
Tom Ogilvy


"Alan Beban" wrote in message
...
Tom Ogilvy wrote:
. . . You can write an
array of other types-however, if you pick the data up, then modify it,
then want to put it down again, there is no reason to not use a
variant.

Unless while modifying it you want to restrict the type of data to which
an element can be changed, or, if you are going to ReDim Preserve it,
you want to restrict the type of data to be added to it.

Alan Beban

Which is better?
 

I guess one example of what I was suggesting might be something like
with 1,2,3 in A1:C1 you want to pick up those elements in an array
[e.g., of type Long()], ReDim the array to (1 to 1, 1 to 5), and add a
third and fourth element that cannot be, e.g., "ok", or 1.23, or
anything else not accepted as is in the type Long() array.

Alan Beban

Tom Ogilvy wrote:
Since your code will be doing the adding and modifying, I think you could
enforce any conditions you wanted without declaring the array as a specific
type.

Not sure what you are suggesting with redim preserve, but his fails for me:

Sub ABCE()
Dim varr As Variant
varr = Array(1, 2, 3, 4, 5)
ReDim Preserve varr(0 To 100) as Long
End Sub
and the failure is consistent with the help on redim: If the array is
contained in a Variant, the type of the elements can be changed using an As
type clause, unless you're using the Preserve keyword, in which case, no
changes of data type are permitted.

if that is what you were suggesting.

Which is better?
 

Hi

Thanks for the overwhelming response to my post. What I necessarily plan to do is to pick the data from oracle db using recordset, then using the getRows I will be able to dump all the data into a variant array. Basically I will be using inner joins to get all the data from various tables. This variant array can then be used to display in a particular format in excel. Is this method feasible or should I write individual cells through looping of recordset fields and records

Thanks again for replying to the post

Amar

Which is better?
 

the more you can let Excel/VBA do with a single command, usually the better
off you are. I would look at copyfromrecordset. If that isn't an option,
I put assign the variant to the range. My last choice would be one cell at
a time.

--
Regards,
Tom Ogilvy

"Amar" wrote in message
...
Hi,

Thanks for the overwhelming response to my post. What I necessarily plan
to do is to pick the data from oracle db using recordset, then using the
getRows I will be able to dump all the data into a variant array. Basically
I will be using inner joins to get all the data from various tables. This
variant array can then be used to display in a particular format in excel.
Is this method feasible or should I write individual cells through looping
of recordset fields and records?

Thanks again for replying to the post,

Amar

Which is better?
 

Thanks Tom, It was great help!!!