Prototype and develop software leanly — writing macro for Spreadsheet

Software Engineering is a branch of Computer Science that is responsible for managing and driving the process of software development. Computers have lots of applications in everyday life of people’s masses. Each company, organization and department uses computers in some way. Not every one of them has the IT system that helps go through all processes which are necessary to proper work of the organization.

Creating new system from scratch or adding a new functionality to existing one mostly costs a lot of money. It takes also a time for planning, developing and testing software. Sometimes it is much better to use some simpler tool like spreadsheet (e.g. LibreOffice Calc or Microsoft Excel) and drive with it through business processes that the organization needs to work. Spreadsheet is very helpful tool for prototyping, testing business processes. It is easy to build simple “program” with this tool and experiment with it. The organizations can easily experiment with processes by introducing changes in the very simple way, with not so high cost.

Experimenting with Basic

Sometimes Calc or Excel does not provide us an out-of-the-box function that we would like to use. In that case, macros with its Basic interpreter comes.

In the case I would like to create a template sheet and copy it multiple times. Each sheet should have a name that is the next integer number. It should have that name to be able to easily link to any sheet with using a list in another sheet.

LibreOffice Calc

We have an example spreadsheet in LibreOffice:

To get into macros, click Tools > Macros > Organize Macros > LibreOffice Basic…

Then LibreOffice Basic Macro window appears. Click Module1 in Standard of MyMacros. Then select New.

The code editor is shown:

The example procedure that duplicate sheet with the name Sheet1 into asked number of sheets is given below:

Sub CopySheet1()
    Dim x As Integer
    x = InputBox("Enter number of times to copy Sheet1")
    For numtimes = x To 1 Step -1
        ThisComponent.sheets.copyByName("Sheet1", numtimes, 1)
    Next
End Sub

Interpretation of the code: Procedure CopySheet1 introduce variable x with type integer. InputBox with question “Enter number of times to copy Sheet1” returns the result as a number. That number is then iteration starter. For loop iterates from x to 1. The names of new sheets are the numbers given from loop counter.

Copy-paste the code into the code editor’s window, just like in the picture:

When you click on the button that is marked red, you run the macro. Program asks you about number of copies of Sheet1:

Finally, you can see the result:

Microsoft Excel

You can run the module manager by typing Alt and F11 from the spreadsheets view. I took a code from the tutorial and modified it a little bit. Procedure looks as follows:

Sub CopySheet1()
    Dim x As Integer
    x = InputBox("Enter number of times to copy Sheet1")
    For numtimes = x To 1 Step -1
        ActiveWorkbook.Sheets("Sheet1").Copy _
        After:=ActiveWorkbook.Sheets("Sheet1")
        ActiveSheet.Name = numtimes
    Next
End Sub

The interpretation of the code is the exact same to the interpretation of the snippet for LibreOffice Calc.

If you would like to change the value of cell, e.g. R7 in the new sheet do it in this way:

ActiveSheet.Range("R7").Value = "new value"

Impressions

First observation was that copying sheets is very slow. Copying 80 big sheets took around 20–30 seconds. After all, we do not need high speed to do these operations but sometimes it can be a bottleneck.

Excel’s module manager which is also known as Microsoft Visual Basic for Applications reminded me working with Nav Dynamics during the course at the University. Probably when you are familiar with one specific Microsoft’s technology, it is easier to work with some similar from the same vendor. Even Visual Basic is similar to Nav’s Pascal-like language.

Macros are big chance to develop prototypes, they allow to create form applications, reports and many, many more.