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.
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
'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))
'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
next x
end sub
