A Calc Example for labeling columns

Labeling Columns macro examples

An Excerpt From my book OpenOffice Calc Solutions CookBook

Sometimes, if you are building a large spreadsheet system that you may want to duplicate the setup over and over again. It's worth the time to have a macro routine create and name sheets and label areas of the individual sheets.

I actually documented this coding example using three different methods. The method below will increment through a series of column positions using the .getCellByPosition. In my book I  demonstrate two other ways to label the columns using the .getCellRangeByName command.

Labeling Columns Examples

Method 1 Accessing Cells with  .getCellByPosition

The subroutine below demonstrates how a macro can access a sheet called “Sheet6” and pre-label a set of cells for the columns “D” through “G” on row 2. To run this code, edit this code into a module. Open a spreadsheet and make sure you actually have a Sheet6 or modify the code for a sheet name that already exists. NOTE: Sheet names are case sensitive. From the spreadsheet click on tools-run macros and from the module tree and your macro lists pop up. Select the macro. 

sub SheetSetupTest1
   'first create variable as an array of label strings
    Labels=array("","Product Name","Model Number","Unit Cost","Time to Install")
    dSprdSht=ThisComponent 'call the already opened spreadsheet
    CurrentSht=dSprdSht.getSheets().getByName( "Sheet6" ) ' Access calc sheet by its name
    'loop thru and label the cells 
    for x=1 to 4
       'place labels on columns D2 through G2
       oCell=CurrentSht.getCellByPosition( x+2, 1 )
       oCell.setString(Labels(x))
   next x
end sub

Here is the result:

 You'll notice that cell spacing is too good, but that can be fixed by keeping an array of cell sizes, using fixed size fonts, or a couple of other techniques.

Method 2 Accessing Cells with  .getCellRangeByName

Another method is to use two arrays. One array for the labels and one for the column letters.

This method uses the .getCellRangeByName command

sub SheetSetupTest2
   'create variable as an array of label strings

   Labels=array("","Product Name","Model Number","Unit Cost","Time to Install")
   'create variable as an array of column names
   Columns=array("","D","E","F","G”)
   'loop thru and label column heading cells
   for x=1 to 4
      oCell=CurrentSht.getCellRangeByName(Columns(x) & "2")
      oCell.setString(Labels(x))
   next x
end sub

Method 3 Incrementing the Column name string

The third method uses a custom function I created and describe in my book. The function

is called nxtColLtr (ColumnString)   The next-column-letter function takes a column letter or

letters as an argument and returns the next column letter(s) value.  The advantage to this method is you just need to specify a starting column value. This is handy if you have to shift the labels a few times in your design. The nxtColLtr function was tested to increment from column "A" through column "AMJ".

nxtColLtr(columnString)  function Examples:

nxtColLtr("A")  returns "B"

nxtColLtr("Z")  returns "AA"

nxtColLtr("AZ") returns "BA"

Here is the example using the nxtColLtr function

sub SheetSetupTest3
   'create variable as an array of label strings 

   Labels=array("","Product Name","Model Number","Unit Cost","Time to Install")
   'Set StartCol variable for first column heading to label
   StartCol="D"
   for x=1 to 4
      oCell=CurrentSht.getCellRangeByName(StartCol & "2")
      oCell.setString(Labels(x))

      StartCol=nxtColLtr(StartCol)   'get the next column name string
   next x

end sub