Tutorial – Using an in-house VBA software to extract diameteralong anaxile root
Anin-housesoftware package(Wu and Guo 2014) based on the Visual Basic Application (VBA) language embedded in Microsoft Excel™ was updated to batch .txt files to obtain the diameter variation along individual axile roots. Each .txt file includes original topological connection and geometry of root sections of each axile root, which is output by the WinRHIZO software after analysis of a scanned image.As axile roots were broken into several sections during sampling after digitising, the software was used to merge the sections of an axile root automatically. The software exported .csv files for individual nodal roots. Each row of the file contained the data for each link (segment) of a root section, e.g. sequence number, length, cumulative length and diameter. “Length” indicates the length of the link. “Cumulative length” indicates the length cumulated from the basal link (the first link) of an axile root to this link. During merging sequential root sections using the software package, the cumulative length was updated by adding the current cumulative length with the total length of previous root sections. The sequence number of links was also updated during the updating of cumulative length of links. The length and diameter of each link was kept the same.The link was then interpolated linearly into 1 mm lengths for calculation of the diameter of each sequential 2 cm and 5 cm axile root section.
Rules for naming .txt files
The name is case insensitivity. There some rules to name and arrange the symbol and number.
e.g. “2011MN_R1_B1F6”
“2011” indicates year; “MN” indicates treatment; “R1” indicates replication; “B1” indicates plant number; “F” indicates nodal roots from the 6th whorl as the letter indicates the whorl sequence of nodal rootsin alphabeticalorder; “F6” indicates the sequence of nodal root from the 6th whorl. “_” is used to as mark to split different symbols.
“2011” should have 4 characters restrictively, errors should occur supposing “11”, “201” or “20110506” was given.
“MN” treatment should follow the year “2011”; The number of letters is not restricted, e.g. “MMNN” or “M” should work.
“R1” replication could be replaced by other symbols with letters and Arabic numerals. The number of letters and numerals is not restricted, e.g. “Replicate2” or “Group13” should work.
“B1” the number of letter and Arabic numerals is restrict. The symbol could only include one letter with one Arabic numeral while the letter and the numerals can be changed. “P2” should work. “PP2” or “P22” should not work.
“F6” The number of letters is restricted while the number of Arabic numerals is not restricted, e.g. “F16” or “G13” should work. Only letters from A to K was recognised by the software, as the whorl sequence could not exceed 11.
Procedure to process the data using VBA software
- Procedure for processing
First, create a folder as main folder and copy the “AxileRootDiameter.xlsm” file in the folder;
Second, create two subfolders in the main folder and name them as “Txt” and “WBook”, respectively;
Third, copy the .txt files data into the “Txt” folder (Fig. 1);
Fourth, open “AxileRootDiameter.xlsm” file, press “Alt + F11” in the keyboard to open Macro in the excel software, double click “CompleteZone” module under project in the left panel; Then change Directory = "D:\ VBA_Software\Txt" to your current directory of the “Txt” folder, e.g. “E:\XXX\XXX\Txt”;
Fifth, press “F5” or click “Run” and then “Run Subprocess / UserForm”.
Sixth, the xlsx and csv files will be created in the “WBook” file after run.
- The meaning of content and codes in the .txt file output by the WinRHIZO software and the files output by the VBA software
For the meaning of content and codes in the txt files, please check the manual of WinRHIZO software;
Open one of .xlsx file in “WBook” folder and check the “Original” sheet, SampleIdindicates axile root section, which is consisted of three major parts: “LINK”, “DEV-MAGNI” and “AXIS”(Fig. 2); Our calculation is based on the data in “LINK” part; In the “LINK” part, only the data in the following columns was used in VBA software: “SampleId”, “LINK”, “Link#”, “Length (cm)”, “AvgDiam(mm)”, “Order”, “Father” and “Baby1”.
Fig. 1 The.txt files output from WinRHIZO software
Fig. 2Columns marked with yellow background and red font used by VBA software
- Functionality of the VBA software
The VBA softwareis used to import the .txt files in the Txt folder (Fig. 1), process them in the “AxileRootDiameter.xlsm” file, and export files containing processed data in the WBook folder (Fig. 3). First, all names of txt files in the Txt folder was imported in “Original” sheet in the “AxileRootDiameter.xlsm” file containing all the codes in the module of Macro (Press Alt + F11 in the keyboard to open Macro);The names was split and then stored in “Split” and “SplitDetail” sheets. Then the software recognises the symbol of the split names, imports the corresponding .txt file in “Original” sheet in corresponding .xlsx file, extracts the topological and geometrical data for each SampleId (Axile root section) and stores in “Extract” sheet.Then, the sections of each axile root werespliced successive to compose a whole axile root. The axile root is interpolated into about 1 mm lengthand stored in “Interpolation” sheet, and the diameter of each 2 cm axile rootsection is recalculated and stored in the “ZoneAll” sheet in the .xlsx file. At last, a “2011_MN_ZoneAll.csv” file is output including all the processed data.In“2011_MN_ZoneAll.csv” file, column names and sample data was listed in Fig 4: “ZoneSequence” indicates the sequence of 2 cm zone; “LengthSum” indicates the cumulative length from the first 2 cm zone to current zone. “ZoneLength” indicates the length of current 2 cm zone; “Mark” the number in this column indicates whether it is the last 2 cm zone which “-1” indicates the last 2 cm zone; “Diameter(mm)” indicates the average diameter of current 2 cm zone.
Fig. 3Files output from the VBA software
Fig. 4 Sample data in “2011_MN_ZoneAll.csv” file
Continue supports
If you have any questions on using VBA software, please contact Jie Wu by .
Appendix: Key parameters setting used in WinRHIZO software
The setting of key parameters in WinRHIZO software required under our purpose was listed in Fig. 5.
Fig. 5Parameter setting in WinRHIZO software