My Account | Newsletters | User Groups | Contact | About Us

Learn Spreadsheet Macro Programming: OOoBasic and Calc automation

Dr Mark Alexander Bain

Jump to: Overview | Reviews | The Author | Sample Chapters
spacer Click here to view more images
eBook: €18.99
Formats: spacer
save 50%!
Print + free eBook + free PacktLib access to the book: €55.98
save 41%!
spacer Free Shipping! UK, US, Europe and selected countries in Asia.
Also available on: spacer spacer spacer spacer
Table of Contents
The Author
  • Discover the power of the macro language and Calc object model
  • Fast, fun, friendly step-by-step OOoBasic tutorial
  • Practical and realistic examples to apply to your daily work
  • This book covers version 2.0.4 for Windows and version 2.0.2 for Linux

Book Details

Language : English
Paperback : 220 pages [ 235mm x 191mm ]
Release Date : December 2006
ISBN : 1847190979
ISBN 13 : 9781847190970
Author(s) : Dr Mark Alexander Bain
Topics and Technologies : All Books, Open Source

Table of Contents

Chapter 1: Working with OOo's Basic IDE
Chapter 2: Libraries, Modules, Subroutines, and Functions
Chapter 3: The OOo Object Model
Chapter 4: Using Macros with Spreadsheets
Chapter 5: Formatting your Spreadsheets
Chapter 6: Working with Databases
Chapter 7: Working with Other Documents
Chapter 8: Developing Dialogs
Chapter 9: Creating a Complete Application
Chapter 10: Using Excel VBA
  • Preface
  • Chapter 1: Working with OOo's Basic IDE
    • Before We Start
    • Accessing the OOo IDE
      • Controls in IDE
      • Navigating around the IDE
        • The Object Catalog
        • Select Macro
        • The Basic Macro Organizer
    • Designing Dialogs with the IDE
    • Summary
  • Chapter 2: Libraries, Modules, Subroutines, and Functions
    • Using Libraries
      • Managing Modules using Libraries
      • Using Libraries in a Multi-User Environment
      • Adding a Library to the Macros Area
    • Using Modules
    • Writing Macros
      • Writing Subroutines
        • Declare Variables
        • Assign Values to the Variables
        • Do the Work!
        • Inputting Variables
      • Writing Functions
    • Getting more Information
    • Subroutines and Functions in Different Libraries
    • Summary
  • Chapter 3: The OOo Object Model
    • Why be Interested in UNOs?
    • Overview of the OOo Object Model
      • The Interface
      • The Service
      • The Module
    • Starting to Work with UNOs
      • Opening and Closing Spreadsheets Automatically
    • Online Reference Material
    • A Real Example: Using the Table UNO to Access a Cell
    • Services within Services
      • Finding Included Services
    • List of Everything You Want to Know About UNOs
    • Summary
  • Chapter 4: Using Macros with Spreadsheets
    • Opening and Closing Spreadsheets
    • Manipulating Spreadsheet Cells
      • Using OOo's Builtin Functions
    • Named Worksheets and Cells
      • Accessing Existing Named Worksheets and Cells
      • Creating New Named Worksheets and Cells
      • Deleting Worksheets
    • Working with Multiple Spreadsheets
    • Using Ranges of Cells
    • Summary
  • Chapter 5: Formatting your Spreadsheets
    • The Most Basic Formatting—Column and Row Dimensions
      • Optimizing Column Widths
      • Optimizing Column Widths across a Whole Worksheet
      • Setting Fixed Widths and Heights
      • Hiding Columns
    • Formatting the Printed Page
      • Adding a Page Break
      • Defining a Print Area
      • Setting the Header and Footer
      • Adding Page Numbers
      • Setting the Page Size and Orientation
    • Customizing Worksheet Names
    • Updating the Document Information
    • Formatting Cells and Ranges of Cells
      • Changing Cell Styles
      • Changing Cell Formats
        • Cell Background Colors
        • Text Colors
        • Cell Fonts
        • Character Heights
        • The Underline
        • Word Wrapping
        • Number Formats
    • Online Reference Material
    • Summary
  • Chapter 6: Working with Databases
    • Accessing Databases
      • Which Databases can We Use?
      • Registering the Database as an OOo Data Source
      • Viewing Registered Data Sources
      • Connecting to a Database
      • Accessing Database Tables
        • Running Queries on the Tables
    • Putting it All into a Spreadsheet
      • Loading Data into Custom Worksheets
    • Adding New Records to the Database
    • Updating the Database
    • Summary
  • Chapter 7: Working with Other Documents
    • The Chart
      • Inserting a Simple Chart into a Spreadsheet
      • Formatting Charts
        • Chart Size
        • Chart Title
        • Adding Chart Axis Labels
        • Y Axis Text Orientation
        • A fully Formatted Bar Chart
        • Other Chart Types
    • Using Documents from Other Sources
      • Stock Market Analysis—Yahoo! Finance
        • Importing an Historical CSV File from Yahoo! Finance
        • Comparing Companies within Yahoo! Finance
    • Processing Web Pages
    • Summary
  • Chapter 8: Developing Dialogs
    • Using's Built-In Dialogs
      • Customizing Message Boxes
      • Customizing Input Boxes
    • Developing your Own Dialogs
      • Creating a Dialog
      • Loading a Dialog
      • Assigning Actions to a Dialog
      • Using Information in a Dialog
      • Populating Controls in a Dialog
      • The Finished Dialog
    • Finding Further Information
    • Summary
  • Chapter 9: Creating a Complete Application
    • Making Macros and Dialogs Available to Everyone
      • Creating a Global Library
    • Using a Global Library to Automate OOo Calc
      • Running Macros Automatically when Calc Opens
    • Adding Macros to the Calc Menu
      • Adding a Macro to the Menu Manually
      • Distributing a Menu
    • Keeping It All Hidden
    • Running Macros from the Command Line
      • Running Macros in Linux
      • Running Macros in MS Windows
    • Creating Background or Batch Processes
      • Running Background Processes on Linux
      • Running Background Processes on Windows
    • Sending Emails
    • Summary
  • Chapter 10: Using Excel VBA
    • The Current State
      •'s Excel VBA Support under MS Windows
      •'s Excel VBA Support under Linux
    • Installing SUSE Linux 10.1
    • Building from Source
      • Building on Linux
      • Support your Local Issue
    • Importing an Excel Spreadsheet that Contains Macros
      • Opening Up an Excel Spreadsheet
      • Viewing Code without VBA Support
      • Viewing Code with VBA Support
      • Closing your Spreadsheet
    • Starting to Code with Excel VBA in Calc
      • Combining VBA Code and OOo Basic Code
    • Comparing VBA and OOo Basic Code
      • Simplifying Code
      • VBA—No Strings Attached
      • Getting the Right Cell Position
        • Using Named Cells and Ranges
    • Further VBA Examples
      • Using Active Cells and Cell Offsets
      • Using the Workbooks Object
      • Using the Worksheets Object
    • Further Information
    • Summary
  • Index

Dr Mark Alexander Bain

Dr. Mark Alexander Bain first started customizing CRM systems back in the mid '90s when he was team leader for Vodafone's Cascade project – the team took the 'out-of-the-box' Clarify CRM and turned it into a radio base station planning application, complete with a workflow engine for passing jobs between the different departments involved in the planning, building, and implementation of a radio network. Since then he's lectured at the University of Central Lancashire, and currently Mark writes articles on all things Linux and Open Source for Linux Format, and Linux Journal. SugarCRM customization, therefore, seems the obvious choice for this, his second book, since it combines Mark's knowledge of working with commercial CRMs and the Open Source philosophy. Mark works from his home on the edge of the Lake District in the UK, where he lives with his wife, two dogs and two cats, and gets the odd visit from his sons – Michael and Simon.

Sorry, we don't have any reviews for this title yet.

Sample chapters

You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

Code Downloads

Download the code and support files for this book.


- 1 submitted: last submission 09 Jan 2012

page 13 Typo 01 Apr 09
Under section on Watch button at page bottom: see first bullet. <br />Should read: "The marker on the left-hand side tells you exactly where you are in<br />the code." <br />Currently says "your are in the code".<br />
page 52 Technical 01 Apr 09
At the bottom of the page, there is a reference to an object called StartDesktop,<br />while the correct object is called starDesktop.

Submit Errata

Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.

What you will learn from this book

  • How to create custom Calc applications
  • Creating worksheet functions, automating repetitive tasks, creating new toolbars, menus, and dialog boxes
  • A complete guide to the  IDE, language, and object model
  • Integrating Calc applications with other components of
  • Internet-based collaborative applications
  • How to understand, use and adapt the increasing number of freely available Calc Macros

Special Offers


Annual subscription:

€150.00 per annum

Monthly subscription:

€14.99 per month

Buy any two eBooks and get 50% off

Add any two eBooks to your cart, and 50% will be taken off their total price

spacer +    spacer

Simply click below to add these two eBooks to your cart

View our bestselling Open Source bundles

In Detail

Adding macros to your spreadsheets enables you to add data processing features to your work, automate repetitive tasks, and even create complete data-driven programs that use the spreadsheet as their back end.

This book teaches the OOoBasic language and the Calc object model, so that you can manipulate spreadsheets and data from within your programs. You will also see how to create dialog boxes and windows for friendly user interfaces, and how to integrate your spreadsheets with other applications, for example writing spreadsheet data to a document, or capturing data from a database, and using the spreadsheet for generating advanced calculations and reports.

Calc is's spreadsheet module. Like the rest of Calc can be programmed using the built-in language OOoBasic. Both simple macros and complex applications can be developed in this language by controlling Calc through its object model. The book is compatible with the commercial version of, StarOffice, and the StarBasic language.

Learn how to extend and customize Calc, the spreadsheet, using OOoBasic, OOo's built-in programming language. You don’t need to be a developer to create useful and powerful routines and applications. This well structured and practical tutorial will walk you though every step, and inspire you with great ways to save time and increase your productivity using Calc.


A fast, fun, and friendly tutorial. Full of practical, step-by-step examples and clear explanations of all of the important concepts.

Who this book is for

You don’t need to be a programmer to use this book, but you do need to be familiar with the concept of a program and how simple things like a loop might work. If all you have is a taster of simple programs from high school then you will be fine. is neither affiliated with the authors of this page nor responsible for its contents. This is a safe-cache copy of the original web site.