Reduce number of function calls

[mookyon]

Hi Everybody,



1) I am trying to reduce the number of functions calls in a loop (from 4 to one);

2) If possible, I also wish to find an alternative to “Application.Index” calls. Please advise.

Please look at the following --
(In short: we are transposing a vertical worksheet region into a four-consecutive-columns in a certain row of a VBA two-dimentional variant array)

' STORE worksheet's four results into vba array: varTests

varTests(t, 2) = Application.Index(wrkshtMAIN.Range("rgn?RunScore"), 1, 1)

varTests(t, 3) = Application.Index(wrkshtMAIN.Range("rgn?RunScore"), 2, 1)

varTests(t, 4) = Application.Index(wrkshtMAIN.Range("rgn?RunScore"), 3, 1)

varTests(t, 5) = Application.Index(wrkshtMAIN.Range("rgn?RunScore"), 4, 1)

I wish to replace these four calls with a more efficient code (maybe executing one call instead of four).

I’ve tried placing “Array(1,2,3,4)” at the row index of Application.Index but failed (probably did it wrong).

I am not experienced in VBA. I hope one of the members will be able to provide a hint.



1000 THX

 

[mumps]

Does this help?

Dim x As Long
For x = 1 To 4
    varTests(t, x + 1) = Application.Index(wrkshtMAIN.Range("rgn?RunScore"), x, 1)
Next x

 

[mookyon]

Does this help?

Dim x As Long
For x = 1 To 4
    varTests(t, x + 1) = Application.Index(wrkshtMAIN.Range("rgn?RunScore"), x, 1)
Next x

Thank you mumps.
Unfortunately, the time it'll take to perform is similar to four separate calls. I am trying to perform the same with a single call. I hope it is possible.
Thanks for your attention and effort

 

[kvsrinivasamurthy]

You can store all 4 values as array

Ary=Application.Index(wrkshtMAIN.Range("rgn?RunScore"), array(1,2,3,4) 1)

 

[mookyon]

Dear kvsrinivasamurthy,

Thank you very much for your help. Appreciated.
Had I known VBA better, it would have been sufficient. But I do have a follow-on question as follows.

In my case Ary is replaced by a two-dimensional VBA variant array varTest.

Question (sorry for the length…):

Is there any method to assign (in a single statement) these four values “Application.Index(wrkshtMAIN.Range("rgn?RunScore"),array(1,2,3,4),1)”

to this "varTest" array at the following four consecutive columns in row "t" starting from (t, 2), thru (t, 5)?

THX

Rgds,

 

[kvsrinivasamurthy]

It is not possible to store the values as part of array. But it can be assigned to a range of cells in the worksheet .
Range("B2:B5")=Application.Index(wrkshtMAIN.Range("rgn?RunScore"),array(1,2,3,4),1)

 

[Peter_SSs]

Unsure, but it seems to me that the accepted solution transfers a vertical set of cells to a vertical set of cells whereas the original question seems to be asking for a vertical set of cells to be transferred to a horizontal set of values.
If I was right then perhaps this would be closer to the mark?

Cells(t, 2).Resize(, 4).Value = Application.Transpose(wrkshtMAIN.Range("rgn?RunScore").Value)

 

[mookyon]

Hi Peter_SSs,

Thank you so much for tour contribution. I think your comment is right. My travel schedule will allow verification only next week.
In the meantime, I think (due to my miserable VBA background -sorry) I will need further clarification for:
How the .Value in your proposal is replacing array(1,2,3,4)?

Rgds,

 

[Peter_SSs]

My travel schedule will allow verification only next week.
In the meantime, I think (due to my miserable VBA background -sorry) I will need further clarification for:
How the .Value in your proposal is replacing array(1,2,3,4)?

Let's wait & see if it works before we worry about such clarification.

 

[mookyon]

Hi again,

Background:
I intend to replace the following lines (by a single line in order to gain performance):
' varTests(t, 2) = Application.Index(wrkshtMAIN.Range("rgn?RunScore"), 1, 1)
' varTests(t, 3) = Application.Index(wrkshtMAIN.Range("rgn?RunScore"), 2, 1)
' varTests(t, 4) = Application.Index(wrkshtMAIN.Range("rgn?RunScore"), 3, 1)
' varTests(t, 5) = Application.Index(wrkshtMAIN.Range("rgn?RunScore"), 4, 1)

I used the following suggested single code line:
varTests(t, 2).Resize(, 4).Value = Application.Transpose(wrkshtMAIN.Range("rgn?RunScore").Value)
and received an "Object Required" error (424).

varTests() is defined as Variant, and later Redim-ed as two dimensional (1 to rows, 1 to columns).

Can you suggest how to rectify error 424?

Thank you!