Bill of Quantity - VBA

[mdelandro]

Hi All,

I am just starting to learn how to automise tasks by using VBA.
I have read many people recommending to learn by creating a need, so I guessed I might as well try to work on something that would actually be useful in my day to day life.
As I work in the construction industry, I often have to produce bill of quantities and estimates, however the formatting of this can often become tedious, repetitive and boring.
I have attached two screenshots from an example of a BOQ, one is the breakdown of all items, and the other is the summary of just the "Chapters". These can often contain many more lines than the one shown, and once I finish the estimate and have to modify the summary page, I always have to manually go and match the title in the summary to the chapters in the breakdown sheet. If its only 5-10 chapters this isn't too difficult, but doing it for 30, 40, 50 chapters, it can quickly become very boring.

Unfortunately, I've never programmed before and I'm finding myself a bit stuck in thinking of how to tackle this simple issue with VBA.

if anyone has any pointers, give me some ideas or examples, or even just tell me what I should be thinking about to get me started, that would be hugely appreciated!

Thank you

Mario

 

[GWteB]

Hi Mario, welcome to the board!
It is recommended that you include an example of your worksheet, preferably via xl2bb to give board members more insight on what you are trying to achieve.

 

[mdelandro]

VBA BOQ.xlsx
	A	B	C	D	E	F	G	H	I
1						Project : VBA BOQ
2						Title : VBA BOQ
3
4
5	Item	Description				Quantity	Unit	Rate (£)	Total (£)
6	1	Bill No. 01 : Preliminaries
7
8	1.1	Document Preparation and Review
9		QS - Estimating				2.0	day	560.00	100.00
10	1.2	Site Supervision
11		Engineer - setting out \ site visits				20.0	day	560.00	100.00
12		Project Manager				12.0	day	650.00	100.00
13		Site Supervisor				120.0	day	400.00	100.00
14		H&S Supervisor				8.0	day	560.00	100.00
15		Planner				4.0	day	450.00	100.00
16	1.3	Plant Hire
17		Tools & Equipment				22.0	week	125.00	100.00
18		Digger & Dumper				22.0	week	750.00	100.00
19	Carried to Summary								£800.00
39	2	Bill No. 02 : Excavations & Piles
40
41	2.1	Enabling Works
42		Excavate (assume 300mm) to form piling mat				450.0	m3		100.00
43		muckaway, assume non contaminated soil				540.0	m3		100.00
44	2.1	Pile mat
45		Supply and install pile mate - assume 300mm compacted type 1				1500.0	m2		100.00
46	2.2	Pile
47		Assume 300mm diameter piles. Assumed depth 12m				212.0	item	500.00	100.00
48		Pile cut off				208.0	item	25.00	100.00
49	Carried to Summary								£500.00
50	3	Bill No. 03 : Bund
51
52	3.1	RC for Bund
53		Concrete				250.0	m3	175.00	100.00
54		Steel Reinforcement: (approx 150kg/m3)				37.5	m3	1250.00	100.00
55		Shuttering: Supply & Install				430.0	m2	45.00	100.00
56		1200 Gauge Visqueen membrane - Supply & Install				300.0	m2	12.50	100.00
57		Visqueen - VX25				150.0	m	12.50	100.00
58		Shingle				50.0	m3	30.00	100.00
59		150mm type 1 and 50mm blinding				260.0	m2	25.00	100.00
60	3.2	Structural Steel
61		Galvanised structural steel				2.0	ton	2750.00	100.00
62		38x152mm rectangular moulded superspan phenolic grating				260.0	m2	115.00	100.00
63	3.3	Ducting
64		Excavation of cable pits				5.0	day	750.00	100.00
65		Excavation of duct route				3.0	day	750.00	100.00
66		Installation of cable ducting				1.5	day	750.00	100.00
67		Reinstate				1.0	day	750.00	100.00
68	3.4	Firewalls
69		Firewalls - Supply and install				300.0	m2	330.00
70	Carried to Summary								£1,300.00
71	4	Bill No. 04 : Switchroom
72
73	4.1	Switchroom Foundations
74	4.1.1	Enabling Works
75		Excavate by machine				500.0	m3	15.00	100.00
76		muckaway, assume non contaminated soil				600.0	m3	40.00	100.00
77	4.1.2	Beams
78		Concrete - Supply and Pour				70.0	m3	175.00	100.00
79		Formwork - Supply and install				285.0	m2	45.00	100.00
80	4.1.3	Retaining Walls and Slab
81		Concrete - Supply and Pour				137.0	m3	175.00	100.00
82		Formwork - Supply and install				680.0	m2	45.00	100.00
83		150mm type 1 plus 50mm blinding				680.0	m2	25.00	100.00
84	4.1.4	Membranes
85		Visqueen 1500 tredguard				350.0	m2	8.50	100.00
86		Visqueen Gas Resistant Self Adhesive Membrane				350.0	m2	37.50	100.00
87		Visqueen - VX25 - Waterstops				120.0	m	2.50	100.00
88		HP Tanking Primers				20.0	m2	12.50	100.00
89		Visqueen - VX90				120.0	m	2.50	100.00
90	4.1.5	Steel Reinforcement
91		Steel Reinforcement (approx 150kg/m3)				24.0	ton	1250.00	100.00
92	Carried to Summary								£1,300.00
93	5	Bill No. 05 : Superstructure
94
95	5.1	Structural Steel
96		Galvanised steel - Supply and erect				30.0	tonne.	2500.00	100.00
97		Allow for steel connections				3.0	tonne.	2500.00	100.00
98	5.2	Slab
99		Access Floor - Kingspan FDEB30 on zinc plated pedestals to suit floor height of 800				40.0	m3	215.00	100.00
100		FLOORING - 43mm "dura slab moulded - light" solid top GRP flooring by dura composites with surface mounted fixings by dura composites to steel below.				225.0	m3	125.00	100.00
101		300mm GEN3 concrete fill to store room with 1 layer A393 mesh in top cover				3.0	m3	140.00	100.00
102	5.3	Kingspan and cladding
103		External Walls - 700mm thick KS100, supply and install				484.0	m2	215.00	100.00
104		Roof - Kingspan 800mm thk KS100, supply and install				350.0	m2	225.00	100.00
105	5.4	Other
106		afp airtech "SHX-UN10002 universal pressure vent				4.0	item	250.00	100.00
107		afp airtech "DWL1000! Dynamic weather louvre				15.0	item	75.00	100.00
108		Fire rated canister foam				50.0	m	55.00	100.00
109	5.5	Doors
110		Door: 910x2100				4.0	item	1283.87
111		Door: 910x2485				1.0	item	1787.37
112		Door: 1135x2485				1.0	item	2152.30
113		Door: 1810x3000				3.0	item	4369.64
114	5.6	WC and plumbing
115		supply and fit, including tiles				1.0	item	2500.00	100.00
116	Carried to Summary								£1,100.00
Pricing

Cell Formulas
Range	Formula
F1:F2	F1	=Summary!G2
I19	I19	=SUM(I9:I18)
F43,F76	F43	=F42*1.2
I49	I49	=SUM(I42:I48)
I70	I70	=SUM(I52:I69)
I92	I92	=SUM(I73:I91)
I116	I116	=SUM(I95:I115)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I76	Cell Value	=0	text	YES
I60:I62	Cell Value	=0	text	YES
I114	Cell Value	=0	text	YES
I115	Cell Value	=0	text	YES
I113	Cell Value	=0	text	YES
I112	Cell Value	=0	text	YES
I111	Cell Value	=0	text	YES
I109	Cell Value	=0	text	YES
I110	Cell Value	=0	text	YES
I105	Cell Value	=0	text	YES
I106:I108	Cell Value	=0	text	YES
I101	Cell Value	=0	text	YES
I74	Cell Value	=0	text	YES
I75	Cell Value	=0	text	YES
I100	Cell Value	=0	text	YES
I99	Cell Value	=0	text	YES
I69	Cell Value	=0	text	YES
I68	Cell Value	=0	text	YES
I102	Cell Value	=0	text	YES
I98	Cell Value	=0	text	YES
I93:I97	Cell Value	=0	text	YES
I91	Cell Value	=0	text	YES
I91	Cell Value	=0	text	YES
I90	Cell Value	=0	text	YES
I48	Cell Value	=0	text	YES
I84:I86	Cell Value	=0	text	YES
I86	Cell Value	=0	text	YES
I85	Cell Value	=0	text	YES
I80	Cell Value	=0	text	YES
I81:I83	Cell Value	=0	text	YES
I77	Cell Value	=0	text	YES
I44	Cell Value	=0	text	YES
I89	Cell Value	=0	text	YES
I87	Cell Value	=0	text	YES
I88	Cell Value	=0	text	YES
I64	Cell Value	=0	text	YES
I65	Cell Value	=0	text	YES
I63	Cell Value	=0	text	YES
I66:I67,I92,I78:I79,I45:I47,I103:I104,I70:I73,I127:I65509,I1:I43,I49:I59,I116:I125	Cell Value	=0	text	YES

 

[mdelandro]

VBA BOQ.xlsx
	A	B	C	D	E	F	G	H	I
7
8	SUMMARY
9
10	ITEM	DESCRIPTION							Total (£)
11
12		FIXED PRICE ELEMENTS (Labour & Material)
13
14
15	1	Document Preparation and Review							£800.00
16
17	2	Bill No. 02 : Excavations & Piles							£500.00
18
19	3	Bill No. 03 : Bund							£1,300.00
20
21	4	Bill No. 04 : Switchroom							£1,300.00
22
23	5	Bill No. 05 : Superstructure							£1,100.00
24
25
26
27
28	TOTAL						£5,000.00		Plus VAT
29
Summary

Cell Formulas
Range	Formula
A15	A15	=+Pricing!A6
B15	B15	=+Pricing!B8
I15	I15	=+Pricing!I19
A17,A21	A17	=+A15+1
B17	B17	=+Pricing!B39
I17	I17	=+Pricing!I49
B19	B19	=+Pricing!B50
I19	I19	=+Pricing!I70
B21	B21	=+Pricing!B71
I21	I21	=+Pricing!I92
B23	B23	=+Pricing!B93
I23	I23	=+Pricing!I116
G28	G28	=+SUM(I15:I26)

 

[GWteB]

Do you use a template when making the estimates? If you did that and set up the summary in advance using the correct formulas, you would never have to type anything again. Whenever a row is inserted or deleted on the sheet with the specified BOQ (of course, the rows with chapter name and subtotal must remain intact) the formulas on the summary BOQ automatically adjust. VBA is then not necessary at all.