- Blogs
- Ian Stewart
- Writing BASIC Macros for Calc spreadsheets

At the wlug meeting on 28 April, my presentation covered some of the differences between scripting macros in BASIC for OpenOffice/LibreOffice Calc, and scripting in Visual Basic Applications for Microsoft Office Excel.

Part of my presentation included a Calc spreadsheet in which each sheet was designed to present an aspect of BASIC macros scripting in Calc. At the time I had only completed 6 Calc sheets. I have now completed 12 x Calc sheets which provide a more complete presentation for understanding BASIC macro scripting.

My Calc spreadsheet "Calc Basic Macro Tutorials V1.ods" can be down-loaded. The file size is ~128KB...

The spreadsheet is designed as a set of 12 tutorials, each one using 1 Calc sheet and assigned one BASIC module for the macro scripts. Each tutorial uses the calculation of squares and cubes of a sequence of integers to provide the example data, however any other equation can be easily substituted.

The 12 mini-tutorials are as follows:

1. Using Calc's cell based formulas to calculate the value of the cells.

2. Using a BASIC subroutine to do the calculations and fill the Calc cells with their values.

3. Using a Calc array formula to call a BASIC function to calculate and supply values to the array formula.

4. Adjusting input values to the array formula / BASIC function using a scroll bar.

5. Manually adding a chart that is linked to Calc array formula data.

6. Dynamically creating a Chart that is linked to the array formula data.

7. Dynamically creating a simple Chart that is supplied its data from an array generated by BASIC.

8. Dynamically creating a fancy Chart that is supplied data from an array generated by BASIC.

9. Dynamically creating a mode-less Window which has buttons and a text field.

10. Dynamically creating a Window with a Scroll bar and text field.

11. Dynamically creating a Window with a simple Chart, Scroll bar and Text Fields.

12. Dynamically creating a Window with a Fancy Chart.

Regards, Ian.