VBA

Recently I spent a day or two learning to write a VBA macro – a piece of code which can be run as part of an excel spreadsheet.

This is not something I expected to be doing as an architectural assistant.

In this case it was because we had the FF&E room data for a secondary school, with each room on a separate worksheet and a mastersheet at the start with all the data compiled.  Some of the items were being updated – 28 chairs in the English classroom, not 30; only three pinboards in a music studio… Minor changes, but each involved changing the sheet for the individual room and then finding the entry on the mastersheet and updating that to match.  With each entry, with each change, the likelihood of human error increases.  We also didn’t know whether the existing mastersheet was up to date or whether changes had already been made to the room without being copied through.  The obvious way to address this would have been to go through copying the data for each room onto the mastersheet, one at a time.  It would work, but it would be tedious and time-consuming and the scope for future errors would remain.  Add in the fact that blank lines had been left between entries on most sheets (which would then have to be deleted from the mastersheet) and the job looks longer still.  I wanted instead to find a way of automatically compiling the data, of getting excel to extract the information from each room sheet and copy it onto the mastersheet, so I started digging.  I found commands in the program that were too limited for it to be possible, but bits of code that could potentially work if put together in the right way.  I played around with it until it worked – the process was automated, and the macro could be run any time the spreadsheet had been modified to update the master.

The process showed me a few things:

1. Architects, and contractors and surveyors and clients, have a habit of setting up spreadsheets (and, by extension, using the programs at their disposal) in a way that can make it hard to actually use the programs efficiently and to leverage their full potential.  (Pro tips: don’t merge cells; don’t leave lines between data entries; set your column headings and so on consistently across all the worksheets…)

2. Putting in the time to develop a process which can be applied effectively to save time and effort and reduce errors is often well-spent.  Equally, it can sometimes be a time-sink: sometimes it’s better to just get on with doing the job the regular way rather than playing around with new ideas.  Establishing which is the case is a judgement call which gets easier with experience.

3. Thinking critically about work processes as well as the work itself is interesting and valuable.  Learning about tools and programs is rewarding.  Part of what we are able to do as architects is think analytically about methods as well as outputs and look strategically at how things are done.  It’s a valuable skill, especially in a profession which relies on generating vast amounts of information in order to guide the building process.  Handling this information – schedules, specifications, data and drawings – is a main part of my job and luckily it’s one I enjoy.

4. One of the advantages of architectural education is the way in which we are expected to explore and develop our own skills, and this is just as relevant in practice.

And the final product:

Sub SheetNames()

Dim J As Integer
On Error Resume Next
For J = 2 To Sheets.Count
‘insert column for room names
Sheets(J).Activate
Range(“B1”).EntireColumn.Insert
‘add sheet name to title cell
Sheets(J).Activate
Range(“B3”).Value = ActiveSheet.Name

Next

End Sub

Sub RoomValues()

Dim J As Integer
On Error Resume Next
For J = 2 To Sheets.Count

Sheets(J).Activate
Range(“B6:B400”).Copy
Range(“B6”).Select
Selection.PasteSpecial xlPasteValues

Next

End Sub

Sub CreateSchedule()
Dim J As Integer
On Error Resume Next
‘clear existing schedule and create new
Sheets(1).Delete
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = “Schedule”

‘set up title row
Sheets(2).Activate
Range(“A5”).EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range(“A5”)

‘collate data
For J = 2 To Sheets.Count
Sheets(J).Activate
Range(“B6:P100”).Select
Selection.Copy Destination:=Sheets(1).Range(“B65536”).End(xlUp)(2)

‘delete empty rows
Sheets(1).Activate
Range(“I6:I65536”).SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Select
Selection.Delete

Next
End Sub