Some Excel Query Tools for BOM Sheets

Some Query Tools for Excel Sheets

A sheet is a named table. One column has all unique values. We search the column for a particular value and return the row. If the table is called PART, if the primary key column is PART_ID, if the search value is “2020_SD1”, the equivalent SQL would return the row –

SELECT *
FROM Part
WHERE Part_ID=”2020_SD1”;

Maybe missing some table qualifiers or parentheses, but essentially.

In VBA the FIND method of the RANGE object returns a RANGE of the first cell found. The END property of the RANGE will return the row.

https://docs.microsoft.com/en-us/office/vba/api/excel.range(object)


Function find_row(strtable As String, strfind As String) As Range
 'assume column1 is key index unique search column
 'return a single row as range
    Dim ws1 As Worksheet, rng As Range
    Set ws1 = ThisWorkbook.Sheets(strtable)
    Set rng = ws1.Range("A1")
    Set rng = ws1.Range(rng.Address, rng.End(xlDown).Address)
    Set rng = ws1.Range(rng.Address).find(strfind, LookIn:=xlValues, lookat:=xlWhole)
    Set rng = ws1.Range(rng.Address, rng.End(xlToRight).Address)
    Set find_row = rng
    'returns error if not found, cant use .address or .end of nothing
End Function

This returns a range. Anytime we have a range, we can load it into directly into an array. Excel VBA autosizes the array with 2 dimensions, rows and columns, even if there is only 1 row.

This table has 3 columns – the return range has 1 row.

Sub test()
    Dim rng As Range
    Dim strtable As String, strfind As String
    strtable = "PART"
    strfind = "2020_SD1"

    Set rng = find_row(strtable, strfind)
    
    Dim ar As Variant
    ar = rng
    
    MsgBox ar(1, 1) & ar(1, 2) & ar(1, 3)
End Sub

In the BOM project, we need to join two tables. We need to select all rows in COMPONENT that have the Assy_ID we want and join those to the rows in PART where COMPONENT.Comp_ID = PART.Part_ID to get a list of all sub-parts paired with their attributes from both tables.

I am not an SQL expert, but I got two different versions to work in an old copy of MS Access.
The difference is whether the join is called out in the FROM (preferred in new version SQL) or the WHERE clause.

SELECT COMPONENT.Assy_ID, COMPONENT.Comp_ID, COMPONENT.QTY, PART.DESC, PART.UM
FROM PART INNER JOIN COMPONENT ON PART.Part_ID = COMPONENT.Assy_ID
Where COMPONENT.Assy_ID = “string”;

SELECT COMPONENT.Assy_ID, COMPONENT.Comp_ID, COMPONENT.QTY, PART.DESC, PART.UM
FROM PART, COMPONENT
Where COMPONENT.Assy_ID = “string” and COMPONENT.Comp_ID = PART.Part_ID;

In SQL we would do it all in one query, but in VBA the code will be easier to write and read if we do it in steps.

First if we are to present the list of available assemblies, we need distinct values in the COMPONENT.Assy_ID column.

I found this code, modified it to my needs. It returns a zero index single dimension array. We are going to use arrays for the product of any search. Another great feature of an array is that you can load a listbox on a form simply by Listbox1.List= ar.

Function GetUniqueValues() As Variant
    Dim data As Variant
    Dim temp As Variant
    Dim obj As Object
    Dim i As Long
    
    Set obj = CreateObject("scripting.dictionary")
    'gets col A less header
    'you can even sometimes forget you are passing a range to a variant array
    data = return_col("COMPONENT", "A2")
    'excel sizes the array per the range
    'format data(1 to 50, 1 to 1)
        
    For i = 1 To UBound(data, 1)
        obj(data(i, 1) & "") = ""
    Next
    temp = obj.keys
    GetUniqueValues = temp
End Function

function return_column is just like find_row.

 Function return_col(str_sheet As String, str_cell As String) As Range
    Dim ws1 As Worksheet, rng As Range
    Set ws1 = ThisWorkbook.Sheets(str_sheet)
    Set rng = ws1.Range(str_cell)
    Set rng = ws1.Range(rng.Address, rng.End(xlDown).Address)
    Set return_col = rng
End Function

We have a list of unique COMPONENT.Assy_ID. One is chosen. The next step is to return an array of the COMPONENT table filtered for the rows where COMPONENT.Assy_ID = “string”

Here are our table definitions. Part_ID is a unique key. Assy_ID and Comp_ID are a composite key.

PART ( Part_ID, DESC, UM)
COMPONENT ( Assy_ID, Comp_ID, QTY)

We want to SELECT all columns in COMPONENT for a particular Assy_ID and join them to PART where Comp_ID = Part_ID and show the remaining 2 columns from PART. First lets return the filtered Component table for Assy_ID.

There is a bit going on here, but the function still only does one thing. Basically I run the same loop twice, first to find out how many rows there are, to use that number to re-dimension the array, then run the loop again to save the data. It might not be art but it works fine. The first thing done is to return the entire table COMPONENT to an array for searching.

        'returns COMPONENT table as array filtered for rows with assy_id
Function select_component(assy_id As String) As Variant
        'SELECT * FROM COMPONENT
        'WHERE Assy_ID = "string"
    Dim rows As Integer, cols As Integer
    Dim r As Integer
    Dim findnum As Integer
    
        'return entire table to array for searching
        'dim array (1 to rows, 1 to 3 cols)
    Dim arr As Variant
    arr = return_table("COMPONENT")
    rows = UBound(arr, 1)
    cols = UBound(arr, 2)
 
    'search column1 for assy_id string twice
    'first time to redimension array
    For r = 1 To rows
           If arr(r, 1) = assy_id Then
             findnum = findnum + 1
           End If
    Next r
    
    If findnum <> 0 Then
         Dim ar_result As Variant
         ReDim ar_result(1 To findnum, 1 To cols)
        Else
         Exit Function
        End If
    
     findnum = 0 'reset
         'this is structured for a 3 column table, not any table
     For r = 1 To rows
          If arr(r, 1) = assy_id Then
               findnum = findnum + 1
               ar_result(findnum, 1) = arr(r, 1)
               ar_result(findnum, 2) = arr(r, 2)
               ar_result(findnum, 3) = arr(r, 3)
          End If
     Next r
 
    select_component = ar_result
End Function

the function return_table. The conventions are – tables start on A1, the first row is column labels, anytime a continous range is returned, the returned type is range, anytime a non-continuous range is returned, the return type is array.

Function return_table(str_sheet As String) As Range
    'returns entire table to range
    Dim ws1 As Worksheet, rng As Range
    Set ws1 = ThisWorkbook.Sheets(str_sheet)
    Set rng = ws1.Range("A1")
    
    Set rng = rng.CurrentRegion
    Set rng = rng.Offset(1, 0).Resize(rng.rows.Count - 1)
    'takes out the label row
       
    Set return_table = rng
End Function

The next step is to make the join with all rows from component and desc and um from part.

       'returns array COMPONENT joined with PART for Assy_ID sub_parts
       'COMP.ASSY_ID, COMP.COMP_ID, COMP.QTY, PART.DESC, PART.UM
       '1 to rows, 1 to 5 cols
 Function join_comp_part(ar3 As Variant) As Variant
       'passed in table is Component filtered for Assy_ID
     Dim rng As Range
     Dim r As Integer, rows As Integer, cols As Integer
        
     rows = UBound(ar3, 1)
     cols = UBound(ar3, 2) 'we know is 3
   
     Dim ar_result As Variant
        '3 cols from COMP, 2 cols from PART
     ReDim ar_result(1 To rows, 1 To 5)
 
     Dim assy_id As String
     Dim comp_id As String
     Dim comp_qty As Integer
     Dim part_desc As String
     Dim part_um As String
     
     For r = 1 To rows
        assy_id = ar3(r, 1)
        comp_id = ar3(r, 2)
        comp_qty = ar3(r, 3)
         
            'WHERE Component.Comp_ID = Part.Part_ID
            'search PART table column1 for part_id, return row as range
        Set rng = find_row("PART", comp_id)
        part_um = rng.Cells(1, 3).Value
        part_desc = rng.Cells(1, 2).Value
         
            ar_result(r, 1) = assy_id
            ar_result(r, 2) = comp_id
            ar_result(r, 3) = comp_qty
            ar_result(r, 4) = part_desc
            ar_result(r, 5) = part_um
     Next r

    join_comp_part = ar_result
End Function

That brings us to a stopping point, because up til now, if you accept the basic table relation as a workable BOM structure, we have simply joined the tables. From here on though, everyone has different needs, and will want to write the next manipulation according to their on-dwg bom style, or formatted to paste into their ERP structure.

So lets recap.

We have two tables. One table is the main PART table. Its a list of every PART identification number in the system. For the excel version, its of course a subset. It has 3 columns, part number, description and unit of measure. In the real world, especially if the intent is to interface directly to ERP, there would be some additional foreign key columns to purchase, track cost, show inventory etc. It has no information about the part using or being used by assemblies.

The second table is the assembly information table I called COMPONENT. It has a double key, the assembly number and the component number, parent and child, the part and sub-part. Neither of these numbers is unique singly in this table but is unique taken together. Both numbers are found in the PART table. I could have called the table ASSEMBLY. A third column has the quantity needed for the assembly.

Pic here

The first main function select_component searches COMPONENT for an Assy_ID and returns the table just for those parts.

Pic here

The second function join_comp_part joins the two tables

Pic here

Thats where we are at. Some subs are called but not shown. I am re-writing code as i go, having the first prototype working, making some fairly major revisions hopefully to make the code simpler, less chunky, more modular. its not finished code that has been in production. it is subject to change. You might notice there is no error checking. My goal is to figure out how i want it to work, includng a form interface, then break it with bad data and add error checking. The next step makes the actual dwg bom with an assembly quantity, extended quantities for sub-parts and loops thru the list looking for sub-parts to the sub-parts. I will show my version of an indented bill of material.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.