You might not think of Office as a development tool, but it’s been at the heart of many development toolchains for a long time now. The two most popular tools aren’t the obvious ones either, with both Excel and PowerPoint fulfilling different roles in development, as part of a formal application development program and as a way of quickly building apps that solve urgent business needs, using users’ own programming skills.
This article is also available as a download, How Microsoft Office is useful for developers (free PDF).
Excel: a secret programming environment
Excel is a particularly interesting example of a development tool. Under the hood of what looks like a familiar spreadsheet is a set of programming languages that can be used in very sophisticated ways. As Simon Peyton Jones, a principal researcher at Microsoft Research, notes, it’s a tool that’s increasingly moving into the world of functional programming. Peyton Jones works in functional programming, where he’s perhaps best known as one of the main contributors to the development of Haskell, and for his work on the theory of lazy functional programming.
His MSR work has been very influential in the development of Excel’s programming environment, working to move its function model away from the ageing Visual Basic for Applications language and tooling. One key feature that’s come out of this research is the concept of dynamic arrays.
Working with data and formula
With dynamic arrays, a single formula can ‘spill’ into other cells, filling empty cells in your formula’s spill range with the results of a calculation — sorting a list and filtering out duplicates, for example. There’s new cell notation to help manage the dynamic nature of these new arrays, so you can work with the contents of an entire spill range with a single cell reference, simply by adding # to the cell reference of the first cell. To go with this new tool you get new functions, to help manage and test arrays, as well as the ability to work across rows and columns.
Closely related to this is the addition of new data types to Excel, which are linked directly to external data. For example, you can define a cell as geographic data, which brings in lots of extra data that can be extracted either in functions or dragged straight into their own columns, without having to define a source for the data. That same data can be converted into map charts, giving you a framework for building more complex applications.
Debugging with ExceLint
As Excel adds more and more functionality to its formulas, you’re going to need tools to help debug them. That’s where the ExceLint add-in comes into play. Available on GitHub, it’s a tool for debugging and auditing formulas across your workbooks. Once installed, it adds a new tab to the Excel ribbon, with an audit button that starts a guided audit of your spreadsheet.
Suspected errors are highlighted in red, with closely related correct formulas in green. By comparing formulas like this, you’re given a hint to the shape of a solution, with the green highlights a proposed fix for a problem. It’s not saying that this is the correct formula to use, more that the formula should be of this form. You can then step through cells to find more problems.
SEE: Comparison chart: Enterprise collaboration tools (TechRepublic Premium)
Another option is a global view. This uses colour to give you an overview of the structure of the formulas in your spreadsheet, each block of colour being a set of related formulas. It’s still a work in progress, but there’s a lot of promise here, especially in giving you a visual way of finding errors in what can be very complex spreadsheets.
Prototyping applications in PowerPoint
While Excel excels in code, other aspects of the software development lifecycle are embodied in other Office applications. It’s important to get your application user experience right, and as early as possible to be sure that you’re delivering what your users want. That requires mocking up and sharing prototypes, even before you’ve written a line of code.
Office has long been a useful prototyping tool for user interfaces, using PowerPoint’s hyperlinks to show what happens when buttons are pressed. Mapping a link to a section of an image is easy, as is filling a deck with images illustrating the various states. You can start simply with application wireframes and add content as user interface elements are finalised.
There’s a long-standing problem with user interface prototypes, as their fidelity improves it’s easier to mistake them for a completed project. Even putting a small script-based mock behind a button can make it seem that there’s real code running your UI prototype. So how then to use Office tools to demonstrate application concepts?
Using Sketched Shapes to emphasise works-in-progress
One answer comes from the work of Microsoft Research’s Bill Buxton. In his book, Sketching User Experiences, he argues that sketches are a powerful tool for rapid prototyping of user experiences. A notebook and a pen are powerful tools for quickly putting together rough user interface concepts, and for sharing them with colleagues and users. That model became part of Visual Studio’s XAML tooling with the release of sketch-like user interface components for prototypes, where it would be impossible to mistake an experiment for shippable code.
Recent releases of Office bring that same sketch concept to its drawing tools with its new Sketched Shapes feature. Currently shipping in Insider builds of Office, in Word, PowerPoint, and Excel, it’s a new outline option that replaces formerly regular object outlines with line styles which mimic hand-drawn lines. You can pick from a selection of different line styles to give a different feel to different elements, perhaps giving a rougher look to ideas that are still being discussed, and a smoother feel for those that look ready to be locked down for production.
Adding sketches to your prototypes
Adding a sketch look to a shape is easy enough: select the shape, and from the Shape Format tab in the ribbon choose Shape Outline to pick the line style you want to use. The same process can be used to go to a more formal line style when you want to stop showing sketches; so you can update a design without having to redraw it from scratch. It’s possible to set a sketch style as your default for a document, so all your drawings automatically get the same sketch look-and-feel.
SEE: All of TechRepublic’s cheat sheets and smart person’s guides
Sketch styles can be applied to lines as well as Office’s standard library of shapes, using freeform shapes to draw directly on the screen. Another useful trick is to convert icons and other design elements to shapes using the Convert to Shape function, before applying a sketch line style to the object.
Office continues to evolve, and its partnerships with Microsoft Research are giving it more developer-friendly features, without affecting its role as a day-to-day productivity tool. There’s a lot for developers and development teams in the latest builds, and it’s well worth keeping more than a few of your development teams in Insider builds of the Office applications, so you can get access to these features as quickly as possible.
