Built for Dover Construction
A Final Project for MBA 614
Executive Summary
Dover Construction () is a small construction company headquartered in Layton, UT. The company is currently somewhat of a one man show. Matthias Dover does all the planning, bidding, general contracting, and a good portion of the actual construction by himself with occasional help from a brother. Matthias’ wife, Aurla, does all the accounting and finances. With the recent economic downturn new home construction has slowed and DC has needed to look elsewhere for revenue generation. This new market has come primarily in the form of bathroom and kitchen remodels.
DC has a history of quality work and past clients have been referring a great deal of family and friends. In fact, there have been so many new referrals that Matthias is struggling to keep up. He is currently performing construction work during the day and then drawing plans and creating estimates at night. He asked if I could create a program to speed up the bathroom estimating process. His primary goal was for the user interface to be simple enough that he could quickly train someone and let them do the majority of the estimating. We decided to exclude kitchen remodels because they are much more complex with too many compounding factors to include in a simple model.
The main portion of the system I created walks the user step by step through the different facets of the estimation process. Inputs include such items as demolition hours, countertop length, square feet of floor to be replaced, and hardware type. The majority of the process uses option buttons to select between various options. There are also textboxes to input measurements. Once the estimate is created, the user can save it in a separate spreadsheet, print it, or email it. The user can also find and edit estimates with the click of a button. Upon completion of a job the actual costs can then be entered and quickly compared against the estimated costs. Over time this will help refine the numbers used to create the estimates.
This program will decrease the time needed to create estimates and over time increase the accuracy of the process.
Implementation Documentation
Home Page
The home page of the estimator offers the user several different options. The main activities will be creating new and modifying existing estimates. From the home page the user can also save the current estimate in a new spreadsheet, email an estimate, print an estimate, or enter actual cost data.
Creating/Editing Estimates
The first user form allows the user to enter the customer’s basic information as can be seen to the right. The estimate number is automatically created by finding the most recent number and adding one. If the user is modifying an existing estimate then this info is prefilled following the search. All searches are completed using a simple user form asking for either estimate number or the client’s first and last name. An example of this form can be seen below.
If only an estimate number is entered the program uses this as the search criteria. If an estimate number and name are entered then the search verifies they match correctly or it returns a message saying no estimate number/client name combination was found. If only first and last name are entered the program verifies a match and then returns the corresponding info. If only a first or last name is entered an error message pops up indicating that both a first and last name must be included.
After the customer info dialogue box the actual estimating process begins. The user form for this process is quite extensive with 16 tabs that cover each feature of the bathroom. A screenshot of the form can be seen below. The form is blank for new estimates and prefilled if editing an old estimate.
The user is able to navigate the form by clicking on the tabs or the previous or next buttons. To facilitate ease of use the user is able to navigate through each tab with the tab button on the keyboard. Some tabs are simple such as the one shown above and some contain more info such as the one shown below.
There are several instances throughout the tabs where certain items will appear or disappear based on what has been selected. For instance, in the form to the right the mirror frame only appears when the mirror checkbox is selected. If the checkbox is deselected the mirror frame disappears and the contained info is erased so that it will not be included in the estimate. The info collected about the customer and bathroom is stored on a spreadsheet that has been hidden to avoid accidental changes.
When the finish button is selected the user form closes and the actual estimate is calculated and the estimate form sheet is displayed. A print preview of the estimate is shown below.
The date, estimate number, and customer info are directly filled based on the info entered into the customer info user form. The description and costs are calculated based on the bathroom info user form. A closer look at this portion of the estimate can be found below.
The cost calculations are built directly into the code as are the appropriate descriptions. Items such as demolition and finish work are calculated by multiplying number of hours or feet of trim by a predetermined multiplier. Other items such as the tub begin with a base price calculated by the length and a base model of a fiberglass insert and then the price is adjusted as a percentage depending on the actual material. For example, a jetted tub is calculated to be 150% more expensive than a similarly sized fiberglass insert. As an example, the actual code for calculating the tub price and appropriate description is as follows:
If UserForm1.txtTub.Value = "" Then
tubPrice = 0
Else
tubPrice = UserForm1.txtTub.Value * 50
tubDescrip = UserForm1.txtTub.Value & " ft "
End If
If UserForm1.OptTubFI.Value = True Then
Sheets("Estimate Info").Cells(theRow, 20).Value = "Fiberglass Insert"
tubDescrip = tubDescrip & "fiberglass insert."
ElseIf UserForm1.OptTubAmericast.Value = True Then
Sheets("Estimate Info").Cells(theRow, 20).Value = "Americast"
tubPrice = tubPrice * 1.3
tubDescrip = tubDescrip & "americast."
ElseIf UserForm1.OptTubKohler.Value = True Then
Sheets("Estimate Info").Cells(theRow, 20).Value = "Kohler Castiron"
tubPrice = tubPrice * 1.3
tubDescrip = tubDescrip & "Kohler castiron."
ElseIf UserForm1.OptTubSoaker.Value = True Then
Sheets("Estimate Info").Cells(theRow, 20).Value = "Soaker"
tubPrice = tubPrice * 1.35
tubDescrip = tubDescrip & "soaker."
ElseIf UserForm1.OptTubJetted.Value = True Then
Sheets("Estimate Info").Cells(theRow, 20).Value = "Jetted"
tubPrice = tubPrice * 1.5
tubDescrip = tubDescrip & "jetted."
ElseIf UserForm1.OptTubOversized.Value = True Then
Sheets("Estimate Info").Cells(theRow, 20).Value = "Oversized"
tubPrice = tubPrice * 1.5
tubDescrip = tubDescrip & "oversized."
Else
tubPrice = tubPrice
Sheets("Estimate Info").Cells(theRow, 20).Value = ""
End If
After the estimate has been created another brief procedure runs which places the estimated cost info onto a historical info sheet. The purpose of this sheet is to compare actual costs to estimated costs. The creating and editing of estimates comprises the bulk of the coding for this system.
Enter Actual Costs
After a job has been completed the user is able to click on the Enter Actual Costs button from the home sheet and then enter the actual cost info after searching for the correct estimate. This form can be seen on the right. The form is designed to be simple and easy to navigate quickly. After the cost data has been entered, the information is placed on the historical info sheet and a difference between estimated and actual costs is automatically calculated. The difference line is shaded so it clearly stands out. The purpose of this is to be able to refine the numbers behind the estimate over time. By tracking the differences it will be clear which portions of the estimator need to be corrected. A sample of this sheet can be seen below.
Save Estimate
From the home page the user can also save the current estimate to a separate workbook that can then be emailed to a client. The procedure takes the client’s full name from the estimate and names the new worksheet accordingly—for example “Estimate for John Smith.” A save as dialogue box then appears with the name prefilled as “last name, first name.” The user simply selects ok and the new workbook is saved. There are a few things that go on behind the scenes that make this process work seamlessly.
1. The estimate form sheet in the estimator has a button on it to print the estimate. I didn’t want any code or buttons to be included in the new worksheet, so when the new workbook is created this button is automatically deleted.
2. The new workbook and worksheet are password protected so the client receiving the email is unable to modify or copy the estimate to maintain its integrity.
3. The save procedure automatically opens in the last folder opened on the computer. To remedy this I have the current directory changed to the path of the current workbook plus one more level into an Estimates folder.
4. The file name is created by using the len property to sparse the full name into a first and last name at the space.
Email Estimate
When an estimate has been completed and saved, it can then easily be emailed to the client. DC is looking to participate in upcoming home remodeling and decorating shows and this email feature will be very useful in following up with potential clients who were contacted during these shows. When the user clicks on the email estimate button a search form appears to find the correct client info. This search works the same as the others previously mentioned. When the correct customer is found, the email estimate form is displayed with the correct information prefilled as can be seen below.
The subject and message boxes are intentionally blank initially so the message can be personalized based on the relationship with the client. The settings of the message textbox are set to allow the user to enter carriage returns by using Ctrl+Enter. To avoid accidentally sending a message prematurely when attempting to use the enter key the Ok button was disabled as the default button. To avoid the need to type the closing to each message, all messages automatically includes the final lines of:
Emails are sent using the company’s gmail address and to avoid an extra step the code contains the password. This will be secured by password protecting the code that powers the estimator. The email will arrive in the client’s inbox as shown below and contain the heading as also shown below.
 |
The most important feature of the form is the ability to search for and include the appropriate attachment. When the user clicks the browse button they are automatically taken to the estimates folder where they can find the correct estimate. When they click ok, the path of the file is automatically placed in the attachment textbox and the email is ready to be sent.
Print Estimate
The final activity that can be completed from the home page is printing estimates. As has been mentioned previously, this activity can also be completed using a button on the actual estimate sheet. This procedure uses the default printer and automatically prints one copy of the estimate. If the default printer is a pdf writer, this can be used to create pdf versions of the estimates that can then be emailed to clients.
Learning and Conceptual Difficulties
I learned a great deal while developing this estimating tool. I was able to learn how to accomplish new tasks using VBA as well as how to debug more effectively. The concepts from class such as for and do loops, if/then constructs, email, and user forms were all greatly reinforced through the creation of this project.
A first simple thing I learned was how to work with the tabs on the multipage form. At first I was unsure how to prevent an error when the next button was clicked on the final tab. By playing around with the program I found that the tabs are numbered and when the final tab was reached I could have the next button keep the form on the current tab. Another difficultly that was more tedious than difficult was making sure all the lines of code were in the correct order. There are a lot of variables involved in this process and in some portions of the code the lines look very similar. It was essential I place variables in the right place and sequence.
One of the main problems I encountered was figuring out how to store and subsequently search for the data in the correct rows on different sheets—specifically the sheet that stores all the entered data and the historical data sheet for cost comparisons. The data would not be on the same row in the two different sheets and it took me a little while of thinking to come to the best solution. The solution I came to was actually simpler than I had expected. I had the search procedure run through the rows on the estimate info sheet and set a variable named theRow to the correct row number. If this process successfully finds a match then the procedure continues into another search algorithm, this time on the historical data sheet. It sets a variable named theRow2 to the correct row and is then able pull the correct data. It seems like a simple solution writing it now, but it took several attempts and a few hours of thinking to get it to work correctly.
Another difficulty I had that provided a great learning opportunity was figuring out how to pull in the path of the estimate for the email attachment. Through some research I found how to create a file picker dialogue box and then take the selected file path and insert it in the email attachment textbox. The final code is as follows:
Sub emailAttSearch()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant
With fd
.InitialFileName = ThisWorkbook.Path & "\Estimates"
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
UserForm5.txtEmailAtt.Value = vrtSelectedItem
Next vrtSelectedItem
Else
End If
End With
Set fd = Nothing
End Sub
There were plenty of other difficulties I encountered; however, I would like to discuss one final difficulty that provided a great learning experience. I struggled for quite some time to create and save the estimate in a new workbook with the appropriate worksheet name and prefilled save as name. I was able to figure out how to copy the estimate form into a new worksheet, format the sheet correctly, password protect it, and name it appropriately primarily by recording myself performing the different operations. I was also able to use several of the things we learned at the beginning of class to sparse the full name into a first and last name. The saving portion – creating a save as dialogue box, changing the directory to the appropriate location, and prefilling the form with last name, first name – took some help from google. One part that kept giving me a problem was having the file save as an excel spreadsheet. I thought it would do so automatically, but that was incorrect so I tried including .xlsx at the end of the file name. This also failed and the solution turned out to be that no ‘.’ was required. In the end I was able to get all the problems worked out and the procedure now runs without problems. The final code is shown below.
Sub saveEstimate()
Dim dstFile As String
Dim wb As Workbook
Dim NewEstName As String
Dim fullName As String
fullName = Sheets("Estimate Form").Range("a8").Value
NewEstName = "Estimate for " & fullName
Dim FN As String
Dim LN As String
Dim x As Integer
Dim lenName As Integer
Dim lenFN As Integer
Dim filName As String
Dim filFolder As String
lenFN = 0
'sparse the full name into first and last
lenName = Len(fullName)
For x = 1 To lenName
If Mid(fullName, x, 1) = " " Then
Exit For
Else
lenFN = lenFN + 1
End If
Next x
FN = Mid(fullName, 1, lenFN)
LN = Mid(fullName, lenFN + 2)
filName = LN & ", " & FN
Application.ScreenUpdating = False
Sheets("Estimate Form").Copy
Set wb = ActiveWorkbook
wb.Sheets("Estimate Form").Name = NewEstName
ActiveSheet.Shapes("Button 1").Select
Selection.Delete
ActiveSheet.Protect Password:="**********"
ActiveWorkbook.Protect Password:=" **********"
ActiveWindow.Zoom = 90
ActiveWindow.DisplayGridlines = False
'change the current folder path
filFolder = ThisWorkbook.Path & "\Estimates"
ChDir filFolder
dstFile = Application.GetSaveAsFilename(InitialFileName:=filName, Title:="Save As")
If dstFile = "False" Then
MsgBox "Estimate not saved."
wb.Close 'Close file
Exit Sub
Else
wb.SaveAs dstFile & "xlsx", FileFormat:=xlOpenXMLWorkbook 'Save file
End If
End Sub
In conclusion, I learned a great deal by creating this program and believe it will be extremely useful to DC. Matthias has already expressed his gratitude and excitement to begin implementing it more fully. It was a great experience and I appreciate the opportunity I had to learn while creating something that will make a significant impact.
Supporting Files:
No comments:
Post a Comment