Excel recursive procedure to create an array

Bow (is already on the other sheet, ...

I doubt this is a good idea.

With your demonstration data, “bow” is the only material that is not a component of something else. Will this be true of your real data? How will you know which elements of the array relate to which row in the worksheet?

Perhaps more importantly, the data required for the next step of processing is spread across two sources. You may be saving space (the array will be a little smaller) but this makes your code more complex and slower. I remember when space was tight (the first commercial computer for which I was a programmer had between 45 and 1000Kb † of memory for the operating system and 16 users) and we would accept increased complexity and slower runtime as a necessary price for fitting our programs into the memory available. You do not have to make that sacrifice. A simple program is quicker to write, easier to maintain and more reliable so start simple.

† I am not joking; I really do mean the maximum memory was 1Mb.

My understanding is you wish to transfer the data within the worksheet to memory so it is arranged more conveniently for processing. I find it difficult to see how your array could be convenient for anything. There is also the complexity of the processing necessary to create it. How long did you spend writing ReDimPreserve?

Please consider the following alternative structures.

  |   1   |     2  | 3| 4| 5| 6| 7| 8| 9|10|11|12|
--|-------|--------|--|--|--|--|--|--|--|--|--|--|
 1|Bow    |Crafted | 2| 2| 3| 1| 5| 3|10| 8| 6| 1|
 2|Wood   |Crafted | 4| 2|12| 9|
 3|Rope   |Crafted | 8|10|
 4|Branch |Crafted |13| 1|
 5|Crystal|Resource|
 6|Plumes |Crafted | 7| 1|
 7|Bird   |Resource|
 8|Web    |Crafted |11| 5|
 9|String |Resource|
10|Shard  |Resource|
11|Spider |Resource|
12|Leaf   |Resource|
13|Tree   |Resource|

This is called a ragged array because each row is a different length. This is logically the same as the worksheet. The values in columns 1, 2, 4, 6, 8, 10 and 12 are unchanged. The words in columns 3, 5, 7, 9 and 11 have been replaced by row numbers. For example: “Wood” has been replaced by “2” and “Rope” has been replaced by “3” where “2” and “3” are the rows holding details of Wood and Rope. (I created this table by hand but I am sure you can see the idea even if there are mistakes.

I hope you can see that getting from Bow to each of its components (Wood, Rope, Crystal, Shard and Plumes), and from Wood to its components (Branch and Leaf), would not be difficult. I also hope you can see it would be no major problems in converting the worksheet to this array.

Don’t worry at this stage how you create a ragged array rather than a square or cube array. At this stage I want you to think about data structures. Get the correct data structure and the program structure will be easy. With the wrong data structure, the program will be from difficult to impossible to code.

The structure above is simple but not self-documenting. Is column 7 a material or a quantity? For this problem, it may not be important for the structure to be self-documenting but for more complex problems it will be.

Long, String, Double and Boolean are intrinsic data types which come with the programming language. Often these intrinsic data types are enough but sometimes they are not. All the general purpose languages I know have some means of building more complex data types from these simple data types. Most languages call these complex data types “structures” but VBA calls them “user types”. Consider:

Type tComponent
  RowMaterial As Long
  Quantity As Long
End Type

Type tMaterial
  Name As String
  Crafted As Boolean
  Component() As tComponent
End Type

The statements Type xxx to End Type define a user type. I always seem to want to use the same name for a type and a variable. One of my conventions is to have a leading “t” for a type name.

I first define a component of a crafted material. A component corresponds to columns (3,4), (5,6) and so on. I then define a material which has a name, a Boolean to record crafted or resource and an array of components. If a material is a resource, Crafted will be False and Component will not be used. If a material is crafted, Crafted will be True and Component will be ReDimed as appropriate and value stored.

Consider how Type tMaterial relates to a worksheet row. Column 1 contains a name and column 2 contains “Crafted” or “Resource”. I have replaced column 2 with a Boolean variable but that is just a different way of encoding the same information. Type tComponent, which contains a row number identifying the component and a quantity, matches the column pairs (3, 4), (5, 6) and so on. The big difference is tMaterial is self-documenting. If you return to these macros in six or twelve months which of these two approaches will be easier to understand? I believe that approach 2 will be the easier. If a macro, or any other program, is to be maintained to meet changing requirements, making life easy for the maintenance programmer is a very important consideration; after all, you might be that maintenance programmer.

The following code shows how these user types would be used:

Sub ShowConcept()

  Dim Materials() As tMaterial

  ReDim Materials(1 To 13)

  Materials(1).Name = "Bow"
  Materials(1).Crafted = True
  ReDim Materials(1).Components(1 To 5)
  Materials(1).Components(1).RowMaterial = 2
  Materials(1).Components(1).Quantity = 2
  Materials(1).Components(2).RowMaterial = 3
  Materials(1).Components(2).Quantity = 1
  Materials(1).Components(3).RowMaterial = 5
  Materials(1).Components(3).Quantity = 3
  '  : : :
  Materials(2).Name = "Wood"
  Materials(2).Crafted = True
  ReDim Materials(2).Components(1 To 2)
  Materials(2).Components(1).RowMaterial = 4
  Materials(2).Components(1).Quantity = 2
  Materials(2).Components(2).RowMaterial = 12
  Materials(2).Components(2).Quantity = 8
  '  : : :

End Sub

The two data structures above are logically the same; they just demonstrate two methods of achieving the same effect. I have not mentally tested the data structure yet although it feels about right. The next step is to “use” this structure. It may be necessary to modify or even discard my first attempt at an appropriate data structure for your problem but I hope not.

You need three macros. You need one macro to create the array from the original worksheet and another to create the new worksheet from the array. With your demonstration data there is only one material that is not a component of another. You could create a macro that output the components of row 1 of the array (row 2 of the worksheet). But your real data might have several such “unused” materials and, I assume, you would wish all of them to be output to the new worksheet. You need a control macro that calls the array-creating macro and then calls the output macro for each unused material.

How does the macro recognise an unused material? With the worksheet and the current array, it is not immediately obvious which materials are unused. Is the material described by row 9, for example, used? I would need to look at all the other rows. Only if no other row used row 9’s material would it be unused. I need a new attribute of Type tMaterial:

Type tMaterial
  Name As String
  Crafted As Boolean
  Used As Boolean
  Component() As tComponent
End Type

For each material Used would have an initial value of False. As the array was built, any use of the material would be recorded by setting Used to True.

Now to design our main two macros.

The first step of the array-creating macro is to import the worksheet to a Variant. The first data row is 2. You can identify the last used row as 14. The first column is 1. You can identify the last used column as 12. A single statement will load this range to a Variant which creates an array. I will import an extra blank column to give:

  |   1   |     2  |   3  | 4| 5  | 6| 7     | 8|  9  |10|  11  |12|13| 
--|-------|--------|------|--|----|--|-------|--|-----|--|------|--|--|
 1|Bow    |Crafted |Wood  | 2|Rope| 1|Crystal| 3|Shard| 8|Plumes| 1|  |
 2|Wood   |Crafted |Branch| 2|Leaf| 9|       |  |     |  |      |  |  |
 3|Rope   |Crafted |Web   |10|    |  |       |  |     |  |      |  |  |
 4|Branch |Crafted |Tree  | 1|    |  |       |  |     |  |      |  |  |
 5|Crystal|Resource|      |  |    |  |       |  |     |  |      |  |  |
 6|Plumes |Crafted |Bird  | 1|    |  |       |  |     |  |      |  |  |
 7|Bird   |Resource|      |  |    |  |       |  |     |  |      |  |  |
 8|Web    |Crafted |Spider| 5|    |  |       |  |     |  |      |  |  |
 9|String |Resource|      |  |    |  |       |  |     |  |      |  |  |
10|Shard  |Resource|      |  |    |  |       |  |     |  |      |  |  |
11|Spider |Resource|      |  |    |  |       |  |     |  |      |  |  |
12|Leaf   |Resource|      |  |    |  |       |  |     |  |      |  |  |
13|Tree   |Resource|      |  |    |  |       |  |     |  |      |  |  |

I now need to go down each row and check columns 3, 5, 7, 9 and 11. Any material names must be replaced by the relevant row number. Since this is a variant array, I can replace a string value with a numeric value.

For example, in element R1C3, I find “Wood”. I need to look down column 1 for “Wood” which I find in row 2. I set R1C3 to 2 and R2C13 to “U” to indicate wood is used giving:

  |   1   |     2  |   3  | 4| 5  | 6| 7     | 8|  9  |10|  11  |12|13| 
--|-------|--------|------|--|----|--|-------|--|-----|--|------|--|--|
 1|Bow    |Crafted |     2| 2|Rope| 1|Crystal| 3|Shard| 8|Plumes| 1|  |
 2|Wood   |Crafted |Branch| 2|Leaf| 9|       |  |     |  |      |  |U |
 3|Rope   |Crafted |Web   |10|    |  |       |  |     |  |      |  |  |

I repeat for R1C5 where I find “Rope”. I look down column 1 for “Rope” which I find in row 3. I set R1C5 to 3 and R3C13 to “U” giving:

  |   1   |     2  |   3  | 4| 5  | 6| 7     | 8|  9  |10|  11  |12|13| 
--|-------|--------|------|--|----|--|-------|--|-----|--|------|--|--|
 1|Bow    |Crafted |     2| 2|   3| 1|Crystal| 3|Shard| 8|Plumes| 1|  |
 2|Wood   |Crafted |Branch| 2|Leaf| 9|       |  |     |  |      |  |U |
 3|Rope   |Crafted |Web   |10|    |  |       |  |     |  |      |  |U |

To transform the original worksheet to the form at the top of this answer (except for column 13), I need:

• An outer loop for each row (1 to 13).
• An inner loop for each of columns 3, 5 and so on containing a material name.
• An inner-inner loop searching the rows for the material name.

I do not need recursion to create this structure. I could use the modified array on this form but I believe it would make the new-worksheet-creating macro easier to understand if the data was moved to an array of Type tMaterial.

As I understand it, there is a specific column of a specific worksheet to which you are to output values. The name of this worksheet, the column letter/number and first row number could be hard-coded into the macro, be defined as constants or be parameters to the macro. I will ignore the worksheet and column but will make row number a parameter of the macro.

For the first row of the macro you may want:

Bow - 1

I first read your question to mean you wanted this row suppressed but I am no longer sure if that was the correct interpretation. No matter; I will explain how this row could be suppressed or made different from the other rows later.

Under this first row you want rows listing the components of Bow:

Bow - 1
>Wood – 2
>Rope – 1
»Crystal – 3
»Shard – 8
>Plumes – 1

I am using “>” to represent indent because I assume the hyphens following the name are real hyphens. The 1, 2, 1, 3, 8 and 1 are quantities.

Under the row for Wood, you want rows listing its components but you want the quantities multiplied by 2, the number of Woods:

Bow - 1
>Wood – 2
>>Branch – 4
>>Leaf – 18
>Rope – 1
>Crystal – 3
>Shard – 8
>Plumes – 1

Branch and Leaf are resources and do not have components but, if they did have components, you would want those components listed under the row for Wood.

This is definitely a requirement for which recursion will be the easiest technique.

The recursive routine (let’s call it OutMatRow) will need a number of parameters:

• Materials: the array created by the first macro.
• RowMaterial: the row within Materials for the current material.
• RowOutput: the row within the output column.
• Quantity: the quantity of the current material.
• NumIndents: the number of indents for the current material.

I say “parameters” but Materials could be a global variable since OutMatRow does not change this array. RowOutput could also be a global because the source variable is updated each time a row is output. RowMaterial, Quantity and NumIndents MUST be parameters because each call needs its own values for these parameters.

The control routine will call OutMatRow for each unused material. With your demonstration data the only unused material is Bow so the call will be:

Call OutMatRow(Materials, 2, X, 1, 0)

where X represents the number of the first output row.

There will be little code in OutMatRow.

• The row for the material must be output. NumIndents, Materials(RowMaterial).Name and Quantity give the values for this row. You can have a different format or suppress output when NumIndents = 0 if you want.
• RowOutput must be stepped ready for the next output row.

• For each component of a crafted material, the routine will call itself so:

  Call OutMatRow(Materials, _
                 Materials(RowMaterial).Component(N).RowMaterial, _
                 RowOutput, _
                 Quantity * _
                     Materials(RowMaterial).Component(N).Quantity, _
                 NumIndents + 1)
  

If you are not familiar with recursive routines, it is a little difficult to understand the sequence in which the calls of OutMatRow occur:

• The control macro calls OutMatRowfor Bow.
• OutMatRow outputs the row for Bow and calls itself for Bow’s first component which is Wood.
• OutMatRow outputs the row for Wood and calls itself for Wood’s first component which is Branch.
• OutMatRow outputs the row for Branch. Branch has no components so the routine returns to its caller.
• OutMatRow calls itself for Wood’s second component which is Leaf.
• OutMatRow outputs the row for Leaf. Leaf has no components so the routine returns to its caller.
• Wood has no more components so the routine returns to its caller.
• OutMatRow calls itself for Bow’s second component which is Rope.
• And so on.

This will be difficult to get your head around. Try with the explanation I have given. If you are still struggling come back with questions and I will attempt a different explanation.