# Reduce number of function calls



## mookyon (Dec 8, 2022)

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 (Dec 8, 2022)

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 (Dec 8, 2022)

mumps said:


> Does this help?
> 
> ```
> Dim x As Long
> ...


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 (Dec 8, 2022)

You can store all 4 values as array

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


----------



## mookyon (Dec 8, 2022)

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 (Dec 9, 2022)

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 (Dec 12, 2022)

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 (Dec 12, 2022)

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 (Dec 12, 2022)

mookyon said:


> 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 (Dec 16, 2022)

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!


----------



## mookyon (Dec 8, 2022)

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*


----------



## Peter_SSs (Dec 16, 2022)

mookyon said:


> (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)


I missed this earlier. Please do not write in such small font size unless there is a particular reason to do so.

If varTests is dimed as, say, (1 to 10, 1 to 5) then as far as I am aware you cannot read values into, say varTests(3,2), varTests(3,3), varTests(3,4), varTests(3,5) other than either individually as you originally showed or individually via a loop as already suggested.


----------



## mookyon (Dec 16, 2022)

1000 THX Peter_SSs.
I will mark it complete (and will watch for font size)


----------

