Variant Array from one Procedure to Another

[GeeWhiz7]

Hi,

I've got a novice question on arrays.

Here is a snippet of code that is embedded in a greater procedure I have. This part of the code does the following:

• Loop calls FirstCalc procedure, passing an input data array (inarr), output array (outarr) and the Loop x value (x)
• Calls FirstCalc procedure which returns its output (outarr)
• Assigns outarr to another array (firstarr) to hold it to use in SecondCalc procedure
• Calls SecondCalc procedure, passing original input data array (inarr), the array output from the SecondCalc (now in firstarr) and Loop x value (x)

My issue is in putting these back in the worksheet. Since both procedure calls are inside the For Loop, I can write to the sheet like shown in the mini-sheet below (left side).
But try as I might I can't figure out how to write it out the way I want.

Goal: Have the write out look like the mini-sheet below (right side)

Book2
	A	B	C	D	E	F	G	H
1	Input	A	B			Input	A	B
2	1	15.58	14.02			1	15.58	14.02
3	2	17.29	14.18			2	17.29	14.18
4	3	14.50	14.39			3	14.50	14.39
5	4	12.14	19.84			4	12.14	19.84
6	5	12.60	16.50			5	12.60	16.50
7
8
9	First Calc x=2	31.17	28.04			First Calc x=2	31.17	28.04
10		34.59	28.36				34.59	28.36
11		29.00	28.77				29.00	28.77
12		24.27	39.69				24.27	39.69
13		25.21	33.00				25.21	33.00
14
15
16	Second Calc x =2	485.6583	393.0168			First Calc x=4	62.33191	56.07258
17		598.1501	402.1612				69.17515	56.72116
18		420.6	413.9062				58.0069	57.54346
19		294.5934	787.627				48.54634	79.37894
20		317.7357	544.492				50.41712	65.99951
21
22
23	First Calc x=4	62.33191	56.07258			Second Calc x =2	485.6583	393.0168
24		69.17515	56.72116				598.1501	402.1612
25		58.0069	57.54346				420.6	413.9062
26		48.54634	79.37894				294.5934	787.627
27		50.41712	65.99951				317.7357	544.492
28
29
30	Second Calc x=4	971.3166	786.0337			Second Calc x=4	971.3166	786.0337
31		1196.3	804.3225				1196.3	804.3225
32		841.2	827.8124				841.2	827.8124
33		589.1867	1575.254				589.1867	1575.254
34		635.4715	1088.984				635.4715	1088.984
35
36	This is what current code writes out					This is how I want it…
Sheet1

Option Explicit

'*************************************************************************
'* Main - assigns input data, calls FirstCalc Procedure
'*************************************************************************
Sub Main()
    
    Dim inarr, outarr, firstarr As Variant
    Dim lc, lr, x As Long
    
    Dim wSh As Worksheet
    Set wSh = Worksheets("Sheet1")
    
    With wSh
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        inarr = Range(Cells(2, 2), Cells(lr, lc))
    End With
    
    ReDim outarr(1 To UBound(inarr, 1), 1 To UBound(inarr, 2))
    ReDim firstarr(1 To UBound(inarr, 1), 1 To UBound(inarr, 2))
    ReDim outarr2(1 To UBound(inarr, 1), 1 To UBound(inarr, 2))
    
    For x = 2 To 4 Step 2
        
        Call FirstCalc(inarr, outarr, x)
        
        firstarr = outarr
        
        Call SecondCalc(inarr, firstarr, outarr2, x)
                                                                    
    With wSh
        .Range("B" & lr + 3).Resize(UBound(outarr, 1), _
        UBound(outarr, 2)).Value = outarr
    End With
        
        lr = wSh.Cells(Rows.Count, 2).End(xlUp).Row
        ReDim outarr(1 To UBound(inarr, 1), 1 To UBound(inarr, 2))
        
    With wSh
        .Range("B" & lr + 3).Resize(UBound(outarr2, 1), _
        UBound(outarr2, 2)).Value = outarr2
    End With
        
        lr = wSh.Cells(Rows.Count, 2).End(xlUp).Row
        ReDim outarr2(1 To UBound(inarr, 1), 1 To UBound(inarr, 2))
        
    Next x

 

[offthelip]

Your code is writing out to the worksheet INSIDE the loop. this is not the correct place to do it all is does is slow you macro down since you are correctly writing the outputs to an output array ( I recognise my standard names) Move the NEXT X line up to immediately after the
call secondcalc like this:

Call SecondCalc(inarr, firstarr, outarr2, x)
Next x

It is not clear what your problem with writing the arrays back to the worksheet is. I tend to use absolute addressing on the ranges rather than using resize because I find it easier to put things where I want it, you have absolute values for your arrays sizes so I would do it like this :

.Range(.cells(1,1),.cells(ubound(outarr,1),ubound(outarr,2))=outarr

which puts it starting in column A asnd if you want it moved across a few columns

.Range(.cells(1,5),.cells(ubound(outarr,1),4+ubound(outarr,2))=outarr

if on one line you have written out an array you start the next write from there e.g.

.Range(.cells(lr,5),.cells(lr+ubound(outarr,1),4+ubound(outarr,2))=outarr

 

[GeeWhiz7]

Your code is writing out to the worksheet INSIDE the loop. this is not the correct place to do it all is does is slow you macro down since you are correctly writing the outputs to an output array ( I recognise my standard names) Move the NEXT X line up to immediately after the
call secondcalc like this:

Call SecondCalc(inarr, firstarr, outarr2, x)
Next x

It is not clear what your problem with writing the arrays back to the worksheet is. I tend to use absolute addressing on the ranges rather than using resize because I find it easier to put things where I want it, you have absolute values for your arrays sizes so I would do it like this :

.Range(.cells(1,1),.cells(ubound(outarr,1),ubound(outarr,2))=outarr

which puts it starting in column A asnd if you want it moved across a few columns

.Range(.cells(1,5),.cells(ubound(outarr,1),4+ubound(outarr,2))=outarr

if on one line you have written out an array you start the next write from there e.g.

.Range(.cells(lr,5),.cells(lr+ubound(outarr,1),4+ubound(outarr,2))=outarr

Let me try some of these things out and I think it is making sense to me what you are saying. New to this stuff so I appreciate your guidance for sure.

 

[GeeWhiz7]

Let me try some of these things out and I think it is making sense to me what you are saying. New to this stuff so I appreciate your guidance for sure.

Now that I'm looking at it, here was my reasoning for writing out to worksheet while inside the loop. Probably a better way to do it, but not sure how. I'm trying to get an understanding of things before approaching my real data in earnest with harder math inside.

- The x variable that starts the For Loop in Main and then gets passed to FirstCalc procedure causes FirstCalc to produce a matrix for each iteration (in the example one matrix for 2 and one for 4). I need both matrices or whatever number of them that x is set to. I'm not sure how to pass multiple x's to FirstCalc and get back multiple arrays and write them to a sheet sequentially without putting it inside the loop.

- My input arrays can change sizes so I was using this way to allow for output arrays to always be placed sequentially below and with a little space between the previous one.

I guess one fix for some of this might be done by this idea, but I'm not sure it is possible....see image attached. Right now I'm mainly trying to get a grip on how to handle the arrays properly and want to check each step.

 

[offthelip]

I would use one great big array for the output the key is to work out what size it is going to be. Your inputs are the size of the initial array which is given by lr and lc.
you then have two iterations round the loop and it appears that each calculation creates a row for each of the input rows. i.e Lr rows ( if you include the header)
You need some gaps between each calculation and betweeen each of the columns.
So the firstcalc 1st iteration goes out in A1 the next iteration goes in the same row but offset column by Lc plus 2
the secondcalc 1st iteration goes out in Row (1 +lr +2) and the second iteration has the same column offset as first calc.
So the key is to calculate where to write the information.
your iteration value goes 2 the 4 , while it probably easier to work out the offset using 0 and 1
So the index for the offset:
Inoff=(x/2)-1
So something like this:

Dim globalarr()
ReDim globalarr(1 To (lr + 2) * 2, 1 To (lc + 2) * 2)
For x = 2 To 4 Step 2
 inoff = (x / 2) - 1
        Call FirstCalc(inarr, outarr, x)
        For i = 1 To lr
         For j = 1 To lc
          globalarr(i, ((lc + 2) * inoff) + j) = outarr(i, j) ' this writes the first iteration into column A and the second into column offset by lc +2
         Next j
        Next i
        Call SecondCalc(inarr, outarr, outarr2, x)
        For i = 1 To lr
         For j = 1 To lc
          globalarr(i + ((lr + 2) * inoff), (lc + 2) * inoff + j) = outarr2(i, j) ' this writes the first iteration into column A and the second into column offset by lc +2
         Next j
        Next i
Next x

.Range(.Cells(1, 1), .Cells((lr + 2) * 2, (lc + 2) * 2)) = globalarr

 

[GeeWhiz7]

I would use one great big array for the output the key is to work out what size it is going to be. Your inputs are the size of the initial array which is given by lr and lc.
you then have two iterations round the loop and it appears that each calculation creates a row for each of the input rows. i.e Lr rows ( if you include the header)
You need some gaps between each calculation and betweeen each of the columns.
So the firstcalc 1st iteration goes out in A1 the next iteration goes in the same row but offset column by Lc plus 2
the secondcalc 1st iteration goes out in Row (1 +lr +2) and the second iteration has the same column offset as first calc.
So the key is to calculate where to write the information.
your iteration value goes 2 the 4 , while it probably easier to work out the offset using 0 and 1
So the index for the offset:
Inoff=(x/2)-1
So something like this:

Dim globalarr()
ReDim globalarr(1 To (lr + 2) * 2, 1 To (lc + 2) * 2)
For x = 2 To 4 Step 2
 inoff = (x / 2) - 1
        Call FirstCalc(inarr, outarr, x)
        For i = 1 To lr
         For j = 1 To lc
          globalarr(i, ((lc + 2) * inoff) + j) = outarr(i, j) ' this writes the first iteration into column A and the second into column offset by lc +2
         Next j
        Next i
        Call SecondCalc(inarr, outarr, outarr2, x)
        For i = 1 To lr
         For j = 1 To lc
          globalarr(i + ((lr + 2) * inoff), (lc + 2) * inoff + j) = outarr2(i, j) ' this writes the first iteration into column A and the second into column offset by lc +2
         Next j
        Next i
Next x

.Range(.Cells(1, 1), .Cells((lr + 2) * 2, (lc + 2) * 2)) = globalarr

I think this is making sense to me, let me see if I can implement it. My input array size changes sometimes so that is what usually throws me off for addressing, but I think I understand what you're doing here and I'll give it a shot. Thank you.