VBA macro to automate a tree-structure for Bill of Materials

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
marocdea
Posts: 9
Joined: Fri Dec 19, 2014 4:47 pm

VBA macro to automate a tree-structure for Bill of Materials

Post by marocdea »

Cartel1.xlsx
(23.89 KiB) Downloaded 1799 times
Hi everibody
Need someone help

I’m working to a 12-level Production Bill of Materials Excel 2010 spreadsheet with the following data

• Level of BOM (from 0 for the end-item, to 6 in the example)
• Part number
• Description
• Quantity for system
• Unit measure (kg, meters, number of pieces, so on)
• Total cost (calculated formula, at the each level as the intermediate sum of total cost of components for the following level. For example: total cost for end-item A of level 1= the sum of the total cost of each components of A in level 2)
• Price currency (eur, dollars)

Each part number may be repeated more than one time, at different levels of the structure.

I have created the structure manually, with the data/group/create structure/group rows, as you can see in the picture.
For each item of level 2, I have a tree containing the p/n of level 3 to 6, ending to the following p/n of level 2. Then for the first item of level 3 I have another tree, containing the p/n of level 4 to 6 ending with following p/n of level 3.

With 12-levels BOMs, it becomes particular time-spending.
So I want to write a VBA macro that create the same structure automatically.

We have only data, no cells with calculated formula: so I can’t use the option Data/Group/Automated Structure that only works with a group of rows ending with a calculate-formula cell.
Eventually we can use the sum of total cost, for each level.
openoffice 3.1 with windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: VBA macro to automate a tree-structure for Bill of Mater

Post by FJCC »

I think you are in the wrong forum. We are here to support Apache OpenOffice, LibreOffice and their relatives. Your question seems to be purely about Microsoft Office. Someone may be able to help you, but an MSOffice forum would be a better place to try.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
marocdea
Posts: 9
Joined: Fri Dec 19, 2014 4:47 pm

Re: VBA macro to automate a tree-structure for Bill of Mater

Post by marocdea »

i have found a similar problem solved. just look at here:

http://www.excelforum.com/showthread.php?t=991600

multi-level bill of material automatic grouping

if you have a BOM with 9 levels or more, because Excel 2010 only support max 8 outline, you have to modify the VBA code, as indicated in the post.

then if you want to have partial sum for each level (such as working time, cost of materials) or a complexive cost roll-up function, instead of using excel function SUMIF, you may try this

http://www.excelbanter.com/showthread.php?t=134919
wich does not require to specify for each row/level the range of cells you have to sum.
you can "automate" partial sum for each BOM-level, and the cost roll-up: for each SUMLOWERLEVEl we have tho arguments in input, the currentlevel of BOM (the row of the cell we are writing the formula) and a range of cells for sum, which starts with the following row and ends with startingcell.end(xlDown).row-->with the last row of the BOM, for any level we are summing.

1) Like SUMIF function) if any row at position 100 with BOM- Level 2 is followed by another row at position 101 with BOM level 2 or 1 (so that position 100 has no components, is a buy-item with related purchase order and supplier-price), the function return 0 and you don't have a "fork"(if ..then "sum cells at row 100"..else "VB custom funtion")--> to insert a second alternative formula (like = sum cells at position 100) when this condition is verified. You may write if(SumLOWERLEVEL(A1; I2:$I$102) = 0; cell formula; SumLOWERLEVEL(A1; I2:$I$102) )..

2) just to have the sum if some of the cells are text, blank, error of message (Excel o Calc "standard" SUM function does not do so), you may modify SUMLOWERLEVEL public function VB-code, as follows:

If IsNull(Cells(row, colTotal)) = True Then
GoTo CELLA_DOPO
End If
If IsEmpty(Cells(row, colTotal)) = True Then
GoTo CELLA_DOPO
End If
If IsError(Cells(row, colTotal)) = True Then
GoTo CELLA_DOPO
End If
If Cells(row, colTotal).Value = "#N/D" Then
GoTo CELLA_DOPO
End If

'to test, in some case gives "error" message
If IsNumber(Cells(row, colTotal)) = True Then
GoTo CELLA_DOPO
End If
If IsNonText(Cells(row, colTotal)) = False Then
GoTo CELLA_DOPO
End If

Sum = Sum + Cells(row, colTotal)

End If
End If

'point of reference for GoTo instruction
CELLA_DOPO:

'increment the row and loop
row = row + 1
Loop

'return total sum
SumLowerLevel = Sum

End function

'we are using some VB-worksheet function that are not provided for Calc or Excel formulas in worksheet cells
openoffice 3.1 with windows 7
marocdea
Posts: 9
Joined: Fri Dec 19, 2014 4:47 pm

Re: VBA macro to automate a tree-structure for Bill of Mater

Post by marocdea »

again, with a minimal change to VB code (cicle do...loop with no instructions), we have a similar function that gives us for each level of the BOM (in column A), the previous row to the closest cell with the same BOM-level. Es:

BOM level p/n 'RigaFineSomma(.. ; ..)
0 p/n 1 15 =RigaFineSomma(A2;A3:$A$15), 2 is the current row
1 p/n 2 3
1 p/n 3 13
2 p/n 4 5
2 p/n 5 12
3 p/n 6 7
3 p/n 7 8
3 p/n 8 12
4 p/n 9 10
4 p/n 10 12
5 p/n 11 12
2 p/n 12 13
1 p/n 13 14
1 p/n 14 15

so for SUMIF partialsum for each level, we know the last row of the range of cells. For level 0, we have to sum from row 2 to 15; for level 1 (on row 4) we have to sum form row 5 to row 13 (on row 14, we have another level 1).

code:

Code: Select all

Public Function RigaFineSomma(level As Range, answerLoc As Range) As Currency
Application.Volatile


AllowUdfs = True
'Forces Excel to recalculate all values when a
 'cell changes
 
'Variables
 Dim row, col, CurrentLevel As Integer
 Dim Sum As Double
 Sum = 0

'set starting summation row and column cell locations based on input
 'Levellocations
 row = level.row + 1
 col = level.Column
 
'set current row Level based on input Level value
 CurrentLevel = level.Value
 
'set the cell to gather the sums from, should be the same and return
 'Location
 colTotal = answerLoc.Column
 
'interate over all rows below current
 Do While (Cells(row, col) > CurrentLevel)
           
 'increment the row and loop
 row = row + 1
 Loop
 
'return total sum
 RigaFineSomma = row - 1
 
 End Function
openoffice 3.1 with windows 7
marocdea
Posts: 9
Joined: Fri Dec 19, 2014 4:47 pm

Re: VBA macro to automate a tree-structure for Bill of Mater

Post by marocdea »

you may modify the User Defined Function to find the first part-number father, for each row of the Bill of Materials

example as in attachment

NEW CODE:

Code: Select all

Public Function RigaFineSommaP(level As Range, answerLoc As Range) As Currency

'la funzione ritorna la riga del (primo) codice padre
'con livello = quello della riga corrente (Level) - 1

Application.ScreenUpdating = True
 Application.EnableEvents = True
AllowUdfs = True
Application.Calculation = xlManual

'Forces Excel to recalcuate all values when a
 'cell changes
 
'========================================================='
 'Generates sum of all values in "answerLoc2"
 'for all rows where the "Level" is one increment higher
 '========================================================='
 
'Variables
 Dim row, col, CurrentLevel As Long
 Dim Sum As Double
 Sum = 0

On Error GoTo CONTINUA
'set starting summation row and column cell locations based on input
 'Levellocations
 ' seleziona le righe dal livello 0 (end-item) a quella precedente la cella attiva
 row = level.row - 1
 col = level.Column
 
'set current row Level based on input Level value
 CurrentLevel = level.Value
 
'set the cell to gather the sums from, should be the same and return
 'Location
 colTotal = answerLoc.Column
 

'interate over all rows below current
 Do While (Cells(row, col) >= CurrentLevel)
           
 'decrementa the row and loop
 row = row - 1
 Loop
 
 Application.Calculation = xlAutomatic
 
'return total sum
 RigaFineSommaP = row
 'ricalcola la funzione in tutte le celle del foglio attivo
 'evita copia-valori dopo Invio su formula somma.se
 'in celle di altro foglio della stessa cartella
 
Exit Function
CONTINUA:

 
End Function
Attachments
FIND BOM ITEM FATHER UDF example.xlsb
(15.45 KiB) Downloaded 771 times
openoffice 3.1 with windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: VBA macro to automate a tree-structure for Bill of Mater

Post by Villeroy »

This forum is about a completely different software product. Nobody here runs Excel.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
marocdea
Posts: 9
Joined: Fri Dec 19, 2014 4:47 pm

Re: VBA macro to automate a tree-structure for Bill of Mater

Post by marocdea »

Villeroy wrote:This forum is about a completely different software product. Nobody here runs Excel.
Thank you. I know, but some people may find useful this code to translate it into a macro for OpenOffice Calc
openoffice 3.1 with windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: VBA macro to automate a tree-structure for Bill of Mater

Post by Villeroy »

marocdea wrote:
Villeroy wrote:This forum is about a completely different software product. Nobody here runs Excel.
Thank you. I know, but some people may find useful this code to translate it into a macro for OpenOffice Calc
Hopefully not.

Remove sheet protection.
Convert column C to numbers.
H3: =LOOKUP(C3-1;$C$1:$D2)
copy down
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
marocdea
Posts: 9
Joined: Fri Dec 19, 2014 4:47 pm

Re: VBA macro to automate a tree-structure for Bill of Mater

Post by marocdea »

it works fine in the excel, in attachment.
it does'nt work if you have BOM Level and part number columns given by two Excel formulas, with cell number format.
openoffice 3.1 with windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: VBA macro to automate a tree-structure for Bill of Mater

Post by Villeroy »

marocdea wrote:it works fine in the excel, in attachment.
it does'nt work if you have BOM Level and part number columns given by two Excel formulas, with cell number format.
I don't understand. If the levels need to be text for some reason: =LOOKUP(TEXT(VALUE(C3)-1;"@");$C$1:$D2)

A professional solution for this type of problem (items related to other items) would use a relational database rather than a spreadsheet.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
marocdea
Posts: 9
Joined: Fri Dec 19, 2014 4:47 pm

Re: VBA macro to automate a tree-structure for Bill of Mater

Post by marocdea »

hi, last question, that may help everibody working with Bill of Material.

Is there an Excel formula to find the last child of a p/n parent?
e.g.
look at the file in attachment, we need an Excel formula that for row n. 6 where (you may read BOM Level 4, on cell C6) return the last child (so, with BOM Level 5)...

a) the row of the last child: row 14
b) the p/n of the last child: p/n 13

thank you a lot
openoffice 3.1 with windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: VBA macro to automate a tree-structure for Bill of Mater

Post by Villeroy »

If your data processing relies on a distinct sort order in the stored tables, you are doomed anyway.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
marocdea
Posts: 9
Joined: Fri Dec 19, 2014 4:47 pm

Re: VBA macro to automate a tree-structure for Bill of Mater

Post by marocdea »

there is only one Excel Sheet with the BOM as in attachment. There are no stored tables, data are sorted not by name, but by their position in the Bill of Materials, as shown in the file. I think it may be a common question for many users.
openoffice 3.1 with windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: VBA macro to automate a tree-structure for Bill of Mater

Post by Villeroy »

A click on a sort button destroys everything. It is a common question for many users who try desparately to misuse a spreadsheet as a database without knowing any of them. I guess you find the last position of an item in a column with some array function such as: =max(row(column)*(item=column)) [Ctrl+Shift+Enter]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
marocdea
Posts: 9
Joined: Fri Dec 19, 2014 4:47 pm

Re: VBA macro to automate a tree-structure for Bill of Mater

Post by marocdea »

thanks everybody.
i have created a small help-file, with a Bill of Materials of only 100 items.
But it still works for BOM of 9000 items..

there is the indented bill of materials, and three different ways to find the parent p/n for each row-items of the BOM, with the same result.

the first way, with an Array Formula, may be find in some Excel forums..the secondo with sumproduct function do the same thing..
the third working with a User Defined Function needs the file to be save in .xslm format (macro enabled..so it oes not work with .xls, .xlsx, .xlsb file extensions).

The function CERCA seems to work only in the prevous case of a BOm with one occurence for each p/n item..with no p/n repeated.
Attachments
Bill of Materilas part number code example.xlsm
(127.89 KiB) Downloaded 1194 times
openoffice 3.1 with windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: VBA macro to automate a tree-structure for Bill of Mater

Post by Villeroy »

For Excel compatible solutions you need Excel and nothing but Excel alone, particularly when macros are involved.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply