DSAASTAT: A NEW EXCEL VBA MACRO TO ANALYSE THE RESULTS OF FIELD EXPERIMENTS

From the beginning of my activity I felt the need of developing simple software tools that would enable users with a limited background in statistics and computer programming (mainly students, practitioners and technicians) to perform sound statistical analyses directly from within the most widespread spreadsheet, with no additional cost requirements. The set of routines that is hereby presented has been progressively developed over the years, to support the intensive field research activity that is carried out at my institution and it was specifically tailored to the problems posed by routine agriculture experiments.

Download the macro

If you are a lazy reader and want to download the macro straight away, follow this link here. This is usually the latest version. You can also find an example spreadsheet, to test the macro at this link.

References

I spent some of my spare time developing this macro. If you find it useful, I would greatly appreciate if you could cite my work, by referring to one of these papers:

1. Onofri A., 2007. Routine statistical analyses of field experiments by using an Excel extension. Proceedings 6th National Conference Italian Biometric Society: “La statistica nelle scienze della vita e dell’ambiente”, Pisa, 20–22 June 2007, 93–96. (PFD HERE)
2. Onofri, A., Pannacci, E. (2014). Spreadsheet tools for biometry classes in crop science programmes. Communications in Biometry and Crop Science 9 (2), 43–53 (PDF HERE).

Usage

For a smooth usage experience, please note the following steps:

1. download the file DSAASTAT.XLS and put it everywhere on the hard-disk, or external devices (pen-drives, removable media…). Please do not rename it! After done this, you might like to create a shortcut on your desktop to lunch the macro more easily;
2. make also sure that macros are enabled in EXCEL. To enable them, you need to follow a version-dependent procedure. In Excel 2010–2016 : click the FILE MENU and select Options from the left sidebar. Select TRUST CENTER and press TRUST CENTER SETTINGS. In Trust Center Settings, select MACRO SETTINGS from the left sidebar, choose DISABLE ALL MACROS WITH NOTIFICATION and hit the OK button. Save and close Excel completely. In Excel 2007 : click the EXCEL OPTION button in the lower right. Click the TRUST CENTER button on the left. Then, at the bottom right, select TRUST CENTER SETTINGS. In the next window, select MACRO SETTINGS, then select the radio button for DISABLE ALL MACROS WITH NOTIFICATION. To close the Trust Center window, click the lower right OK button. Save and close Excel completely and reopen Excel.
3. Open DSAASTAT.XLS by double-clicking on it. Two things should happen: (1) a SECURITY WARNING pop-up notification appears, beneath the Office ribbon. Click the “Options” button and Select the radio box beside “Enable this content,” then click “OK.” You will have to do this every time, unless you selected the radio button for “Enable all macros” in the window at stage 2 (do so only if you know what you are doing!); (2) A welcome form appears, that will inform you about the Macro name and version.
4. TROUBLE SHOOTING #1: NOTHING OF THE ABOVE HAPPENED!!! This is because you have downloaded DSAASTAT97.XLS from the Internet. Please, follow these steps: (1) from VIEW, click on UNHIDE and select DSAASTAT from the form that appears. (2) A service sheet will appear and, at the same time, you will se a notification pop-up appearing beneath the ribbon. Click on the appropriate button to enable the use of files downloaded from the Internet. (3) If prompted to do so, enable macros, as shown above. (4) Hide the macro (VIEW/HIDE), close Excel and save DSAASTAT.XLS. This latter step is fundamental, to prevent this same problem from arising in the future.
5. Click on the “Add-in” menu on the ribbon: you’ll notice that five new entries are added to the tools menu of Excel (Diagnostic tools, Anova, Multiple comparison tests, Correlation matrices, Regression analyses), which can be used to start the analyses.
6. TROUBLESHOOTING #2. An Excel data sheet must be opened, before clicking on any of the above buttons. Otherwise, you’ll get an error message (‘run-time error ‘91’: Object variable or With block variable not set’)!
7. Please, have your data organised in a database, with observations in rows, and variables in columns. Basically, one column variable plus one or more explanatory variables are required, coding for each effect included in the ANOVA model or for the regression curves to be compared. The first row is reserved for variable names; response variable must be numeric and measured on a continuous scale. Explanatory variables may be either numeric or string. Numeric variables are obviously required for correlation or regression analyses, though when more curves have to be compared, they can be coded by a string variable. Look at this link to see an exemplary dataset.

Some general features of DSAASTAT

The macro was written in the VBA language of EXCEL and runs as an add-in to all versions of EXCEL, from 97 onwards. DSAASTAT performs the following tasks:

1. reads experimental data from the spreadsheet;
2. performs the necessary diagnostic analysis, as requested from the user, to verify whether basic assumptions for ANOVA are met;
3. performs the ANOVA;
4. performs multiple comparison tests;
5. calculate correlation matrices;
6. performs simple linear regression analysis;
7. performs multiple regression;
8. compares regression lines.

The characteristics of this macro have been fully documented with examples in this paper.

Why have I done this?

There are several reasons why I did this work. I’ll try to list them hereafter.

1. Experimental studies in the field, e.g. testing genotypes, fertilisers, pesticides (types and doses), cultural practices and rotations, show several important peculiarities, which have pushed experimenters towards the adoption of certain types of designs, such as the latin square or the split-plot/split-block factorial designs. Furthermore, the replication of experiments across environments (years and/or locations) is common practice, to comply with environmental variability. These types of design are not so often handled by freeware statistical packages, or they require some programming effort.
2. Field experiments are very often carried out by extension and technical services, that frequently employ people with great experience in agriculture, but with a limited training in statistics. This is particularly true in developing countries, wherein the importance of statistical analyses is often not even perceived.
3. The available statistical software is either difficult to use, or very expensive, or not specifically thought and developed for routine field experiments.
4. When it comes to teaching statistics, it is very important that students do not begin their biometry courses with a contemporary training in statistics and computer programming. According to my experience, such an overload makes the student lose focus on statistics and should be avoided by all means.

Conclusions

Work carried out during previous years and the intensive use of DSAASTAT by users with any kind of statistic and computer backgrounds (students, technicians and researchers) has shown that this tool has the flexibility and the simplicity to accomplish the main needs of routine field research experiments. This software does not do anything particularly innovative, but it does it quickly and easily. The advantage over other free statistical software is that EXCEL users may perform statistical analyses with no programming and without the need for learning any other software tool.