Procedure too Large - Array calculation and Helper Array?

[GeeWhiz7]

I am encountering an error I don't know how to fix. My gut says I need a helper array (new to arrays in general) to achieve this, but maybe someone has a clever alternative.

Info: 'b' array gets its values from division of two array elements in 'a' array

The code below works fine unless I increase the number of calculations and then I get a Procedure too large error. My dataset needs to generate around 1200 columns in array b using this approach. I haven't figured out the syntax to do all the calculations with a loop.

Goal: Be able to make up to 1200 calculations like b(i,1) = a(i,1) / a(i,2) without running into Procedure too large error

Option Explicit

Sub test()
  Dim lr As Long, a As Variant, b As Variant
  Dim i As Long, j As Long
  
  lr = Sheet3.Range("A" & Rows.Count).End(xlUp).row

  a = Sheet2.Range("B2:AY" & lr).Value2

  ReDim b(1 To UBound(a, 1), 1 To 10)
    
'##Nested loop to get cell values from array a to array b with a calculation in between
  
  For i = 1 To UBound(a)

'    For j = 1 To 50  ## not using j at the moment

        b(i, 1) = a(i, 1) / a(i, 2) 'note these calculations are representing non recursive combinations of 5 elements. ie any non repeating combinations of 1 through 5 using two #s.
        b(i, 2) = a(i, 1) / a(i, 3) ' I can't figure out how to do this with a loop
        b(i, 3) = a(i, 1) / a(i, 4) ' maybe I need some sort of helper array as if I simply put in more calculations like this (~1200)
        b(i, 4) = a(i, 1) / a(i, 5) ' eventually the procedure becomes too large even for 64bit.
        b(i, 5) = a(i, 2) / a(i, 3)
        b(i, 6) = a(i, 2) / a(i, 4)
        b(i, 7) = a(i, 2) / a(i, 5)
        b(i, 8) = a(i, 3) / a(i, 4)
        b(i, 9) = a(i, 3) / a(i, 5)
        b(i, 10) = a(i, 4) / a(i, 5)
    'Next

  Next
  Range("B2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  
  
End Sub

 

[kevin9999]

I am encountering an error I don't know how to fix. My gut says I need a helper array (new to arrays in general) to achieve this, but maybe someone has a clever alternative.

Info: 'b' array gets its values from division of two array elements in 'a' array

The code below works fine unless I increase the number of calculations and then I get a Procedure too large error. My dataset needs to generate around 1200 columns in array b using this approach. I haven't figured out the syntax to do all the calculations with a loop.

Goal: Be able to make up to 1200 calculations like b(i,1) = a(i,1) / a(i,2) without running into Procedure too large error

Option Explicit

Sub test()
  Dim lr As Long, a As Variant, b As Variant
  Dim i As Long, j As Long
 
  lr = Sheet3.Range("A" & Rows.Count).End(xlUp).row

  a = Sheet2.Range("B2:AY" & lr).Value2

  ReDim b(1 To UBound(a, 1), 1 To 10)
   
'##Nested loop to get cell values from array a to array b with a calculation in between
 
  For i = 1 To UBound(a)

'    For j = 1 To 50  ## not using j at the moment

        b(i, 1) = a(i, 1) / a(i, 2) 'note these calculations are representing non recursive combinations of 5 elements. ie any non repeating combinations of 1 through 5 using two #s.
        b(i, 2) = a(i, 1) / a(i, 3) ' I can't figure out how to do this with a loop
        b(i, 3) = a(i, 1) / a(i, 4) ' maybe I need some sort of helper array as if I simply put in more calculations like this (~1200)
        b(i, 4) = a(i, 1) / a(i, 5) ' eventually the procedure becomes too large even for 64bit.
        b(i, 5) = a(i, 2) / a(i, 3)
        b(i, 6) = a(i, 2) / a(i, 4)
        b(i, 7) = a(i, 2) / a(i, 5)
        b(i, 8) = a(i, 3) / a(i, 4)
        b(i, 9) = a(i, 3) / a(i, 5)
        b(i, 10) = a(i, 4) / a(i, 5)
    'Next

  Next
  Range("B2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
 
 
End Sub

What happens when you specify the dimension of the UBound of a?

For i = 1 to UBound(a,1)

 

[GeeWhiz7]

What happens when you specify the dimension of the UBound of a?

For i = 1 to UBound(a,1)

It still gives me an error.

Was trying to figure out if I could make a helper array (or two) to hold each row needed to right of equals sign.
So instead of something like this

b(i,1)=a(i,1)/a(i,2)

Is it possible to have something like this?? I have not tried, but if i could figure out then I can put all of the possible combinations into the helper arrays.
helpRow, helpRowb 1D arrays

For i = 1 To UBound(a)
 b(i, 1) = a((helpRow(i,1), 1)) / a((helpRowb(i,1)), 2)

Is that even possible to call an array value to specify a row to get another array value?

 

[kevin9999]

Any chance of a data sample using the XL2BB add-in?

 

[johnnyL]

Any chance of a data sample using the XL2BB add-in?

Read my mind.

 

[GeeWhiz7]

Any chance of a data sample using the XL2BB add-in?

Example below. Sorry for the weird screen fit.
So, basically the top data is first array production data. The procedure I'm trying to write, assigns that to an array (a) and then redims another array (b) which needs to get data as it shows up in the second list of data which is a series of ratios based on the possible non-recursive combinations of A, B and C.

None of that is tricky, except when I started to add more and more formulas for calculation in the procedure to match real data which is much larger. This seems to have a limit.

So what I was thinking was trying to make 'helper' 1D arrays that would let me have the right syntax in the For loop for each iteration since it changes based on the combinations. This would allow me to not have too many in the procedure individually, but just the one formula in the loop.

Sorry if its not that clear...
Maybe what I'm writing doesn't makes sense as even I'm struggling to think about it anymore today.

Maybe the question for me to ask is do you think there is a way to set up lets call them "index helper arrays" that I can put inside the (b) array address so that things get calculated correctly?

For i = 1 To UBound(a)

' maybe something like below where h1Row, h1Col, h2Row, h1Col2 are the helper 1D arrays.

'        b(i, 1) = a(i, 1) / a(i, 2)
    b(i, (h1Col(i))) = a((h1Row(i)), ((hlCol(i)) / a(h2Row(i)), (hlCol2(i))

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	A	B	C
2	7/6/2022	89.74	3.74	89.74				Example Non Recursive Combinations
3	7/5/2022	99.81	3.76	99.81				List	Combinations
4	7/4/2022	4.74	3.57	4.74				A	A	B
5	7/3/2022	50.18	3.67	50.18				B	A	C
6	7/2/2022	42.20	3.65	42.20				C	A	D
7	7/1/2022	25.70	3.55	25.70				D	A	E
8	6/30/2022	10.68	3.59	10.68				E	B	C
9	6/29/2022	48.78	3.66	48.78					B	D
10	6/28/2022	44.18	3.65	44.18					B	E
11	6/27/2022	43.31	3.65	43.31					C	D
12	6/26/2022	15.86	3.60	15.86					C	E
13	6/25/2022	61.96	3.69	61.96					D	E
14	6/24/2022	64.22	3.69	64.22
15	6/23/2022	21.97	3.61	21.97
16	6/22/2022	57.24	3.68	57.24
17	6/21/2022	71.39	3.71	71.39
18	6/20/2022	14.32	3.59	14.32
19	6/19/2022	87.23	3.74	87.23
20	6/18/2022	6.25	3.58	6.25
21	6/17/2022	39.13	3.64	39.13
22
23	Date	A/B	A/C	B/C		With real list variables, I estimate the combinations to be around 1200, so 1200 columns.
24	7/6/2022	23.97	1	0.04
25	7/5/2022	26.51	1	0.04
26	7/4/2022	1.33	1	0.75
27	7/3/2022	13.69	1	0.07
28	7/2/2022	11.56	1	0.09
29	7/1/2022	7.24	1	0.14
30	6/30/2022	2.98	1	0.34
31	6/29/2022	13.32	1	0.08
32	6/28/2022	12.09	1	0.08
33	6/27/2022	11.86	1	0.08
34	6/26/2022	4.41	1	0.23
35	6/25/2022	16.80	1	0.06
36	6/24/2022	17.39	1	0.06
37	6/23/2022	6.09	1	0.16
38	6/22/2022	15.56	1	0.06
39	6/21/2022	19.26	1	0.05
40	6/20/2022	3.98	1	0.25
41	6/19/2022	23.33	1	0.04
42	6/18/2022	1.75	1	0.57
43	6/17/2022	10.74	1	0.09
Sheet1

Cell Formulas
Range	Formula
I4:J13	I4	=Combinations(H4:H8,2)
A2,A24	A2	=TODAY()
A3:A21,A25:A43	A3	=A2-1
B24:B43	B24	=B2/C2
C24:C43	C24	=B2/D2
D24:D43	D24	=C2/D2
Dynamic array formulas.

 

[GeeWhiz7]

Example below. Sorry for the weird screen fit.
So, basically the top data is first array production data. The procedure I'm trying to write, assigns that to an array (a) and then redims another array (b) which needs to get data as it shows up in the second list of data which is a series of ratios based on the possible non-recursive combinations of A, B and C.

None of that is tricky, except when I started to add more and more formulas for calculation in the procedure to match real data which is much larger. This seems to have a limit.

So what I was thinking was trying to make 'helper' 1D arrays that would let me have the right syntax in the For loop for each iteration since it changes based on the combinations. This would allow me to not have too many in the procedure individually, but just the one formula in the loop.

Sorry if its not that clear...
Maybe what I'm writing doesn't makes sense as even I'm struggling to think about it anymore today.

Maybe the question for me to ask is do you think there is a way to set up lets call them "index helper arrays" that I can put inside the (b) array address so that things get calculated correctly?

For i = 1 To UBound(a)

' maybe something like below where h1Row, h1Col, h2Row, h1Col2 are the helper 1D arrays.

'        b(i, 1) = a(i, 1) / a(i, 2)
    b(i, (h1Col(i))) = a((h1Row(i)), ((hlCol(i)) / a(h2Row(i)), (hlCol2(i))

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	A	B	C
2	7/6/2022	89.74	3.74	89.74				Example Non Recursive Combinations
3	7/5/2022	99.81	3.76	99.81				List	Combinations
4	7/4/2022	4.74	3.57	4.74				A	A	B
5	7/3/2022	50.18	3.67	50.18				B	A	C
6	7/2/2022	42.20	3.65	42.20				C	A	D
7	7/1/2022	25.70	3.55	25.70				D	A	E
8	6/30/2022	10.68	3.59	10.68				E	B	C
9	6/29/2022	48.78	3.66	48.78					B	D
10	6/28/2022	44.18	3.65	44.18					B	E
11	6/27/2022	43.31	3.65	43.31					C	D
12	6/26/2022	15.86	3.60	15.86					C	E
13	6/25/2022	61.96	3.69	61.96					D	E
14	6/24/2022	64.22	3.69	64.22
15	6/23/2022	21.97	3.61	21.97
16	6/22/2022	57.24	3.68	57.24
17	6/21/2022	71.39	3.71	71.39
18	6/20/2022	14.32	3.59	14.32
19	6/19/2022	87.23	3.74	87.23
20	6/18/2022	6.25	3.58	6.25
21	6/17/2022	39.13	3.64	39.13
22
23	Date	A/B	A/C	B/C		With real list variables, I estimate the combinations to be around 1200, so 1200 columns.
24	7/6/2022	23.97	1	0.04
25	7/5/2022	26.51	1	0.04
26	7/4/2022	1.33	1	0.75
27	7/3/2022	13.69	1	0.07
28	7/2/2022	11.56	1	0.09
29	7/1/2022	7.24	1	0.14
30	6/30/2022	2.98	1	0.34
31	6/29/2022	13.32	1	0.08
32	6/28/2022	12.09	1	0.08
33	6/27/2022	11.86	1	0.08
34	6/26/2022	4.41	1	0.23
35	6/25/2022	16.80	1	0.06
36	6/24/2022	17.39	1	0.06
37	6/23/2022	6.09	1	0.16
38	6/22/2022	15.56	1	0.06
39	6/21/2022	19.26	1	0.05
40	6/20/2022	3.98	1	0.25
41	6/19/2022	23.33	1	0.04
42	6/18/2022	1.75	1	0.57
43	6/17/2022	10.74	1	0.09
Sheet1

Cell Formulas
Range	Formula
I4:J13	I4	=Combinations(H4:H8,2)
A2,A24	A2	=TODAY()
A3:A21,A25:A43	A3	=A2-1
B24:B43	B24	=B2/C2
C24:C43	C24	=B2/D2
D24:D43	D24	=C2/D2
Dynamic array formulas.

This might help understanding the how the syntax changes. These formulas work if only a few (showing just some), but they dont' have a predictable pattern for incrementing with a loop, so I was thinking to make the values that change into the helper arrays.

ArrayWorking.xlsb
	S
2	b(i,1)=a(i,1)/a(i,2)
3	b(i,2)=a(i,1)/a(i,3)
4	b(i,3)=a(i,1)/a(i,4)
5	b(i,4)=a(i,1)/a(i,5)
6	b(i,5)=a(i,1)/a(i,6)
7	b(i,6)=a(i,1)/a(i,7)
8	b(i,7)=a(i,1)/a(i,8)
9	b(i,8)=a(i,1)/a(i,9)
10	b(i,9)=a(i,1)/a(i,10)
11	b(i,10)=a(i,1)/a(i,11)
12	b(i,11)=a(i,1)/a(i,12)
13	b(i,12)=a(i,1)/a(i,13)
14	b(i,13)=a(i,1)/a(i,14)
15	b(i,14)=a(i,1)/a(i,15)
16	b(i,15)=a(i,1)/a(i,16)
17	b(i,16)=a(i,1)/a(i,17)
18	b(i,17)=a(i,1)/a(i,18)
19	b(i,18)=a(i,1)/a(i,19)
20	b(i,19)=a(i,1)/a(i,20)
21	b(i,20)=a(i,1)/a(i,21)
22	b(i,21)=a(i,1)/a(i,22)
23	b(i,22)=a(i,1)/a(i,23)
24	b(i,23)=a(i,1)/a(i,24)
25	b(i,24)=a(i,1)/a(i,25)
26	b(i,25)=a(i,1)/a(i,26)
27	b(i,26)=a(i,1)/a(i,27)
28	b(i,27)=a(i,1)/a(i,28)
29	b(i,28)=a(i,1)/a(i,29)
30	b(i,29)=a(i,1)/a(i,30)
31	b(i,30)=a(i,1)/a(i,31)
32	b(i,31)=a(i,1)/a(i,32)
33	b(i,32)=a(i,1)/a(i,33)
34	b(i,33)=a(i,1)/a(i,34)
35	b(i,34)=a(i,1)/a(i,35)
36	b(i,35)=a(i,1)/a(i,36)
37	b(i,36)=a(i,1)/a(i,37)
38	b(i,37)=a(i,1)/a(i,38)
39	b(i,38)=a(i,1)/a(i,39)
40	b(i,39)=a(i,1)/a(i,40)
41	b(i,40)=a(i,1)/a(i,41)
42	b(i,41)=a(i,1)/a(i,42)
43	b(i,42)=a(i,1)/a(i,43)
44	b(i,43)=a(i,1)/a(i,44)
45	b(i,44)=a(i,1)/a(i,45)
46	b(i,45)=a(i,1)/a(i,46)
47	b(i,46)=a(i,1)/a(i,47)
48	b(i,47)=a(i,1)/a(i,48)
49	b(i,48)=a(i,1)/a(i,49)
50	b(i,49)=a(i,1)/a(i,50)
51	b(i,50)=a(i,2)/a(i,3)
52	b(i,51)=a(i,2)/a(i,4)
53	b(i,52)=a(i,2)/a(i,5)
54	b(i,53)=a(i,2)/a(i,6)
55	b(i,54)=a(i,2)/a(i,7)
56	b(i,55)=a(i,2)/a(i,8)
57	b(i,56)=a(i,2)/a(i,9)
58	b(i,57)=a(i,2)/a(i,10)
59	b(i,58)=a(i,2)/a(i,11)
60	b(i,59)=a(i,2)/a(i,12)
61	b(i,60)=a(i,2)/a(i,13)
62	b(i,61)=a(i,2)/a(i,14)
Sheet1

Cell Formulas
Range	Formula
S2:S62	S2	=CONCAT("b(i,",N2,")=a(i,",P2,")/a(i,",Q2,")")

 

[kevin9999]

This seems to have a limit.

While @johnnyL and I have a look at this, you might want to have a read of This and This, both of which reference the error you're encountering.

 

[johnnyL]

Since we haven't seen the full code being used .... Look here also.

 

[kevin9999]

This might help understanding the how the syntax changes. These formulas work if only a few (showing just some), but they dont' have a predictable pattern for incrementing with a loop, so I was thinking to make the values that change into the helper arrays.

ArrayWorking.xlsb
	S
2	b(i,1)=a(i,1)/a(i,2)
3	b(i,2)=a(i,1)/a(i,3)
4	b(i,3)=a(i,1)/a(i,4)
5	b(i,4)=a(i,1)/a(i,5)
6	b(i,5)=a(i,1)/a(i,6)
7	b(i,6)=a(i,1)/a(i,7)
8	b(i,7)=a(i,1)/a(i,8)
9	b(i,8)=a(i,1)/a(i,9)
10	b(i,9)=a(i,1)/a(i,10)
11	b(i,10)=a(i,1)/a(i,11)
12	b(i,11)=a(i,1)/a(i,12)
13	b(i,12)=a(i,1)/a(i,13)
14	b(i,13)=a(i,1)/a(i,14)
15	b(i,14)=a(i,1)/a(i,15)
16	b(i,15)=a(i,1)/a(i,16)
17	b(i,16)=a(i,1)/a(i,17)
18	b(i,17)=a(i,1)/a(i,18)
19	b(i,18)=a(i,1)/a(i,19)
20	b(i,19)=a(i,1)/a(i,20)
21	b(i,20)=a(i,1)/a(i,21)
22	b(i,21)=a(i,1)/a(i,22)
23	b(i,22)=a(i,1)/a(i,23)
24	b(i,23)=a(i,1)/a(i,24)
25	b(i,24)=a(i,1)/a(i,25)
26	b(i,25)=a(i,1)/a(i,26)
27	b(i,26)=a(i,1)/a(i,27)
28	b(i,27)=a(i,1)/a(i,28)
29	b(i,28)=a(i,1)/a(i,29)
30	b(i,29)=a(i,1)/a(i,30)
31	b(i,30)=a(i,1)/a(i,31)
32	b(i,31)=a(i,1)/a(i,32)
33	b(i,32)=a(i,1)/a(i,33)
34	b(i,33)=a(i,1)/a(i,34)
35	b(i,34)=a(i,1)/a(i,35)
36	b(i,35)=a(i,1)/a(i,36)
37	b(i,36)=a(i,1)/a(i,37)
38	b(i,37)=a(i,1)/a(i,38)
39	b(i,38)=a(i,1)/a(i,39)
40	b(i,39)=a(i,1)/a(i,40)
41	b(i,40)=a(i,1)/a(i,41)
42	b(i,41)=a(i,1)/a(i,42)
43	b(i,42)=a(i,1)/a(i,43)
44	b(i,43)=a(i,1)/a(i,44)
45	b(i,44)=a(i,1)/a(i,45)
46	b(i,45)=a(i,1)/a(i,46)
47	b(i,46)=a(i,1)/a(i,47)
48	b(i,47)=a(i,1)/a(i,48)
49	b(i,48)=a(i,1)/a(i,49)
50	b(i,49)=a(i,1)/a(i,50)
51	b(i,50)=a(i,2)/a(i,3)
52	b(i,51)=a(i,2)/a(i,4)
53	b(i,52)=a(i,2)/a(i,5)
54	b(i,53)=a(i,2)/a(i,6)
55	b(i,54)=a(i,2)/a(i,7)
56	b(i,55)=a(i,2)/a(i,8)
57	b(i,56)=a(i,2)/a(i,9)
58	b(i,57)=a(i,2)/a(i,10)
59	b(i,58)=a(i,2)/a(i,11)
60	b(i,59)=a(i,2)/a(i,12)
61	b(i,60)=a(i,2)/a(i,13)
62	b(i,61)=a(i,2)/a(i,14)
Sheet1

Cell Formulas
Range	Formula
S2:S62	S2	=CONCAT("b(i,",N2,")=a(i,",P2,")/a(i,",Q2,")")

I'm really struggling to reconcile the description of the formulas above, with your description of the (only) 10 possible combinations you described in post #6.