A user can enter a Part Code consisting of a letter followed by four numbers, in order to search for the price of that part, if that part exists in the list. In this version, you will implement the system using a modular design that includes both Public and Private Subs and Functions. Using the provided Workbook, you will need to complete the code in modDecoupled. The Main() Sub has already been written but it makes calls to two other Subs and one Function.

You will need to complete the Sub LoadArray() and you will have to write the entire ShowMessage() Sub. To do this, use the comments and existing code already contained in the module. Do not remove existing code, Debug statements, or comments, as these are part of your required deliverable. When you have completed the required code, you will need to connect the button on the Worksheet to the entry point for your program. Note that even though there are three Subs and one Function, you will have only one Sub to choose from: This is one of the benefits of denoting some Subs as Public and others as Private.

Option Explicit Option Base 1 Public Sub Main) Dim productCode() As Variant Dim unitPrice) As Variant Dim requestedCode As String Dim index As Integer Find the number of products, redimension the arrays, and fill them with the data in the lists LoadArray "A3", productCode LoadArray "", unitPrice 'Get a product code from the user (no error checking) requestedCode-UCase (InputBox("Enter a product code (one letter followed by four digits).")) Look for the code in the list. Record its unit price if it is found. index - GetIndex(requestedCode, productCode.) 'Display an appropriate message, ShowMessage requestedCode, index, unitPrice End Sub Private Sub LoadArray(titleRow As String, arr As Variant) Dim capacity As Integer Dim counter As Integer Debug.Print "Loading array starting one row below " & titleRow With wsData.Range(titleRow) Find the size of the column Set the capacity of the array to the column size Copy every value from the column to the array End With Debug.Print "Built array of size " & CStr((UBound (arr)- LBound(arr))1) End Sub Private Function GetIndex(target As String, arr() As Variant) As Integer Dim counter As Integer GetIndex - 0 For counter LBound (arr) To UBound (arr) If arr(counter)target Then GetIndex - counter Exit For End If Next Debug.Print "Returning index - " & CStr(GetIndex) End Function Create a Private Sub called ShowMessage that takes: A requested part number code An index referring to an array element A Variant array of prices that can be searched using the index Your sub must do the following: 'If index >0 use it to lookup the price in the provided array and display the popup Otherwise display a popup that tells the user that the requested part wasn't found Print the following debug message: "Displayed message to user" Private Sub ShowMessage() End Sub Option Explicit Option Base 1 Public Sub Main) Dim productCode() As Variant Dim unitPrice) As Variant Dim requestedCode As String Dim index As Integer Find the number of products, redimension the arrays, and fill them with the data in the lists LoadArray "A3", productCode LoadArray "", unitPrice 'Get a product code from the user (no error checking) requestedCode-UCase (InputBox("Enter a product code (one letter followed by four digits).")) Look for the code in the list. Record its unit price if it is found. index - GetIndex(requestedCode, productCode.) 'Display an appropriate message, ShowMessage requestedCode, index, unitPrice End Sub Private Sub LoadArray(titleRow As String, arr As Variant) Dim capacity As Integer Dim counter As Integer Debug.Print "Loading array starting one row below " & titleRow With wsData.Range(titleRow) Find the size of the column Set the capacity of the array to the column size Copy every value from the column to the array End With Debug.Print "Built array of size " & CStr((UBound (arr)- LBound(arr))1) End Sub Private Function GetIndex(target As String, arr() As Variant) As Integer Dim counter As Integer GetIndex - 0 For counter LBound (arr) To UBound (arr) If arr(counter)target Then GetIndex - counter Exit For End If Next Debug.Print "Returning index - " & CStr(GetIndex) End Function Create a Private Sub called ShowMessage that takes: A requested part number code An index referring to an array element A Variant array of prices that can be searched using the index Your sub must do the following: 'If index >0 use it to lookup the price in the provided array and display the popup Otherwise display a popup that tells the user that the requested part wasn't found Print the following debug message: "Displayed message to user" Private Sub ShowMessage() End Sub