ACCESSING A COLUMN BY NUMBER
In my first edition of my calc macros book, I described a function that would return the next column name based on a current column name value.  This was useful function to step through column by column, but I found myself in a situation to repetitively create groups of four columns with the same labels and equations of specific rows and it would be easier to work in integer loops to create the same formats over and over again.

A REAL WORLD PROBLEM
The need became apparent when a church contribution sheet was being used. In early January of each year, the practice was to cut and paste the spreadsheet and update the week ending dates to create the next year's contribution spreadsheet. This effort or adding members was prone to equation errors.

Hence, I built a set of macros two automate the construction of the sheet to automate the sheet's construction and eliminate computational errors from the cut-and-paste technique. The user need only run a dialog, select a year value and supply the number of the first Sunday for that year to get a new spreadsheet, pre-configured with equations using the right ranges and cell locations for 52 weeks of collections.

The sheet's layout uses the first three columns holding the contributor names and their year to date total. The next 52 groups that followed were groups of four (spacer column, 'cash', 'check' and 'other' contribution columns); 52 groups that represented the 52 week ending totals. The second row merges the second and third columns cells and displays the week ending date..

So I created a function called Numb2Colmn. Given an integer from 1 to 1024, the function returns the appropriate column string. This allows me to build the same row and column constructs and pass numbers to repetitively access and format appropriate cells, column widths, and text

Examples
   Numb2Colmn(1) returns "A",  Numb2Colmn(28) returns "AB",
   Numb2Colmn(1022) returns AMH  and so on....

The Algorithm breaks the problem down into three groups
1.  single column letters ("A" through "Z" or 1 - 26)
2. double column letters ("AA" through "ZZ" or 27 - 702)
3. three column letters ("AAA" through "AMJ" or 703 - 1024)

'function Numb2Colmn(integer)
'Convert a Number to column text value
'examples "1=A, 26=Z. 27=AA 53=BA 79=CA....702=ZZ 703=AAA  AMJ=1024 (max column value)
'ALGORITHM
' ASCII CHARACTERS A thru Z are ascii 65 thru 90 for number values
'  1. from columns 1 to 26, single character column names are A thru Z using CHR(Cnum + 64)
'  2. columns 27 and above are multiples of 26 (A-Z)
'     for columns 27 through 701 , divide column count (Cnum) by 26
'     2a. if the remainder is zero for the second column subtract one from the first column
'         and force the last column to "Z"
'     2b. if remainder is non-zero subtract 26*whole integer of division from integer value
'                rY=Cnum-(iY*26)
'  Example 52 is an even multiple (2) of 26 and represents column AZ
'          CHR(iY-1) & CHR("Z")
'   Example 53 has a remainder of 1 and represents column BA
'          CHR(iY) & CHR(rY)
'  3. This loop works up to 702  which is 27 multiples of 26
'  4.  703 is the first three character column string "AAA"
'       Subtract 676 (702-26) from Cnum to retrieve the lower significant two column letters
'       and force the highest significant letter to "A

function Numb2Col(CNum as integer)
'first case is single column letter values
if CNum <= 26 then
  'single column
  Numb2Col=Chr(Cnum+64)
endif

'second case is for double char letter values
'702 = "ZZ"
if Cnum >26 and Cnum<703 then
  'return dual column AA - ZZ
'  y=Cnum/26
  iY=int(Cnum/26)
  if iY=27 then
    'special case Cnum=702
    Numb2Col="AAA"
  else
    if Cnum-(iY*26)=0 then
      iY=iY-1
      rY=26
      Numb2Col= (CHR(iY+64) & CHR(rY+64))
    else
      rY=Cnum-(iY*26)
      Numb2Col= (CHR(iY+64) & CHR(rY+64))
    endif
  endif
endif

'from 702 ZZ to 703 AAA
if Cnum >=703 and Cnum < 1025 then
'three column
  Cnum=Cnum-676 'subtract 702 less the first 26
    'treat remaining values like two column letters
  if Cnum >26 then
    iY=int(Cnum/26)
    if Cnum-(iY*26)=0 then
      iY=iY-1
      rY=26
      Numb2Col= ("A" & CHR(iY+64) & CHR(rY+64))
    else
      rY=Cnum-(iY*26)
      Numb2Col= ("A" & CHR(iY+64) & CHR(rY+64))
    endif
  endif
endif
if Cnum > 1024 then
'ERROR greater than column 1024 or 'AMJ'
 Print("ERROR - Column value greater than 1024 or AMJ")
 Numb2Col=""
endif
end function

graphs in OpenOffice Draw

Automating Graphs in Draw

In my past engineering R&D work, I created graphs to make it easier to pick a value or

see the results of a design based on certain parameters. Engineers and Researchers like to use linear or exponential graph displays. Below are examples of drawing and displaying graphs of data sets.

Building a Linear Graph page

The first thing I worked on was getting a graph page that would fit a regular 8 ½ x 11 inch page of paper and create it in the Openoffice draw program. So I used my DrwLine routine to create horizontal and vertical lines to great the equivalent of large square of graph paper (1cm by 1cm squares). Since the smallest values of x or y on the draw space represents 1/100 of a mm; I choose to draw lines that are 1000 units apart in both vertical and horizontal directions This generates a grid with one square cm units. I started the grid with x=1000 to leave some left margin and y=5190 to more or less center the graph sheet on the page. This leaves some room at the top for future titles

The code below creates the image you see above

Sub DrawCartGraph

  'draw out a linear block 19 x 19 squares ' (1000 by 1000 draw units)

  Drw = ThisComponent

  Page = Drw.DrawPages(0) 'select draw page

  ' Draw top horizontal line

  x= DrwLine(1000,1000,19600,0000)

  ' top left is 1000, 5190

  ' bottom right is 19600,24190

  ' useable graph x space is 19000

  ' useable graph y space is 19000

  ' DRAW Horizontal lines for the cartesian grid, ' with a top margin of 5190

  for y= 5190 to 25000 step 1000

    DrwLine(1000, Y, 19000, 0)'was 17000

  next y

  ' DRAW Vertical lines for Cartesian grid, start x at left margin =1000

  for x=1000 to 20000 step 1000

    DrwLine(X,5190,0,19000) 'was 15000

  next x

end sub

Populating a cartesian graph solution in a Vector Drawing system

In a traditional graph plot in quadrant I of the Cartesian plane, the lower left-hand corner of the graph would be (0,0), but in our vector graphics design example,  the bottom left-hand corner of our graph paper (1000,24190).

The x-axis works the same (as the x value increases the points plot further to the right with larger positive values), but the y-axis is starting with a maximum value in the vector graphics system for a minimum value (zero). So we need to subtract values from the maximum vector drawing system as we get larger y values to grow in altitude. In this example, the zero y-axis is a value represented by a vector y value of 19000.

Let's work a practical example without the need of scaling, which means that each square on our vector graphics graph paper is 1000 by 1000 in vector display units. We'll let every graph square centimeter equal one unit.

Let's graph the equation y=1/3x² and we will limit the range of x values from 0 to 7.5

The table on the right contains data values for x and y to be graphed. We'll limited x range from 0 to 7.5 in x, because values beyond 7.5 would be greater than 19 units (we'll reserve scaling for a more advanced future example).

For each solution of the graph, we multiply X by 1000 to get the X value and multiply the Y value by 1000 and subtract it from the max table Y display value (in this case 19000). Below is the code to draws the cartesian grid and then populates the graph with points.

Sub DrawCartGraph

  'draw out a linear block 19 x 19 one cm ' squares (1000 by     '1000, 1 cm by 1 cm)

  Drw = ThisComponent  

  Page = Drw.DrawPages(0)

  x= DrwLine(1000,1000,19600,0000) 'top ' horizontal line

  'top left is 1000,1000

  'bot right is 19600,26000

  '

  'DRAW the Horizontal lines for a Cartesian

  'grid starting at y=1000, to 25000

  for y= 5000 to 25000 step 1000

    DrwLine(1000, Y,19000,0) 

  next y

  'DRAW Vertical lines for a linear Cartesian

  'grid starting at x=1000 to 20000

  for x=1000 to 20000 step 1000

    DrwLine(X,5190,0,19000)

  next x

end sub

So the first routine 'DrawCartGraph' generated our graph paper as shown in the figure above

To populate the graph I created routine called PopulatePtGrph. In this routine I created an array to hold x values 0, .5, 1.0, …. 7.0 and an array that holds y values that satisfied the equation less our graph maximum Y value.

The x values of 0 through 7 are in an array called xPts and the y value solutions in yPts. Remember 500 represents a 0.5 in x distance and the Y points are subtracted from 19000. (So if x=3 then y=3, which xPts=3000 and yPts =16000). We step through those arrays in subroutine PopulatePtGraph and this routine calls the DrwPt subroutine to draw the points on the grid.

Below is the PopulatePtGrph code

sub PopulatePtGrph

' plot Y=1/3x² for range of x from 1 ' to 9 NOTE: 1 unit of grid equals 1000 units on graph

' dim xPts(13) as integer

' dim yPts(13) as integer

xPts=array(0,500,1000,1500,2000,2500,3000,3500,4000,4500,5000,5500,6000,6500,7000)

yPts=array(19000,18917,18667,18250,17667,16917,16000,14917,13667,12250,10667, _ 8917,7000,4917,2667, 250)

for x=0 to 14

  z= DrwPt (xPts(x),y(Pts),24,24) 'DrwPt(xPts(x),yPts(x))

next x

end sub


Here is the resulting graph generated in Openoffice draw.

This graph has no scaling
Each Y horizontal line is 1.0 units
Each X vertical grid line is 1.0 units

Designing the graph point

If you've been looking at the code example for graphing
points on a function then you saw the function DrwPt that draws the plot points.
  z= DrwPt (xPts(x),y(Pts),24,24) 

The DrwPt function takes a location on the grid to plot and draws a circle that has a diameter of 205 draw units where one unit is 1/100 of a mm. So the diameter of the dot is roughly 0.2mm. Now in vector drawing, whatever shape you draw is based on an x,y location at the left hand top corner of the imaginary bounding box that surrounds the circle. This means that shifting the x,y point by half the radius in x and y will center the dot over the actual x,y location value. In this code the offset is 51. Here is the code that draws a circle and fills the center of the circle with green.

function DrwPt (Lft as integer,Top as integer)

  Dim DrwShape As Object     'Define DrwShape as an openoffice drawing object

  Dim Point As New com.sun.star.awt.Point

  Dim Size As New com.sun.star.awt.Size

  Dim rslt As integer

'note graph point is a circle with bounding box =24

'offset of x and y of point is -12

  rslt=1

  Point.x = Lft+1000-51    ' the x value must be offset by the left margin and half the radius

  Point.y = Top+5190-51  ' the y value must be offset the bottom less the half radius of point 

  Size.Width = 205   ' the height and width of the point (circle)

  Size.Height = 205

  Circle =Drw.createInstance("com.sun.star.drawing.EllipseShape")

  Circle.Position = Point

  oPage.add(Circle)  ' Add the point to the drawing page

  Circle.FillColor = RGB(0,255,0) ' Green fill color to the point

  DrwPt=-1

end function

Building a Horizontal Logarithmic graph

While I've used log-log and Deci-log graph paper in my college and engineering years, I've never tried to automate logarithmic graphing with programs or macros. 

In the previous example, I used a simple equation y=1/3x² , but I kept the x values small, because an exponential equation graph quickly runs out of space for y values in a regular cartesian graph.

A logarithmic graph scale can easily allow you to graph a much larger range of numbers. For a span of 1 to 10 or the first decade we can convert them to their base 10 logarithm value. Below are the values of one through ten and their values as base 10 logarithm the second row. as

So this ratio can be repeated and used for any decade

log(10)=1, log(20)=1.301, log(30)=1.477, log(40)=1.602, ….. log(100)=2.0

log(100)=2 log(200)=2.301, log(300)=2.477, log(400)=2.602 …..log(1000)=3.0

log(1000)=3 log(2000)=3.301, log(3000)=3.477, …. log(10000)=4.0

log(10000)=4 log(20000)=4.301, log(3000)=4.477, …. log(100000)=5.0

log(100000)=5 log(200000)=5.301

   o o o

To draw a horizontal logarithmic graph grid (vertical grid lines); I converted the logarithm fractional values to whole numbers by multiplying the fractional log values by 100 and rounded the values to the nearest whole number. 

The variable Itm is an array that holds this basic set of log values

Itm=array(0,30,48,60,70,78,84,90,95,100) and places grids based on the relative ratio of the logarithmic progression for any given decade.

The code to draw a single decade logarithmic grid follows below in the code snippet.  We iterate through the log10 graduations from 1 to 10

Itm=array(0,30,48,60,70,78,84,90,95,100)

for i=0 to 9 'print a decade of graduations

  L=Itm(i)

  L=(L*43)+1000

  x=DrwLine(L,5000,0,15000)

next I

The 43 value was my guess of scaling up to four decades to fit on one page and still have a readable graph. The added '1000 is the start of the left page margin of the log graph field.

To work with multiple decades, we added a variable to the calling routine and added an outer loop to draw each decade. Here is the code:

Sub GrphHLogScales (NoOfDecades as integer)

  for j=0 to NoOfDecades-1

    'Inner loop for each of the nine divisions within the log scale decade

    for i=0 to 9 'print a decade of graduations

      L=Itm(i)+j*100

      L=(L*43)+1000 '43 scales up to four decades to fit page

      x=DrwLine(L,5000,0,15000)

    next i

  next j

end sub    

Dynamic Real Time Indicators in Calc

Real Time Visual Indicators in Calc
Sometimes a spreadsheet is just a blur of numbers and if there is a computation error or inputs that are out of bounds you need to visually and in real time notify user that there is a problem.

Most of the time we pull up a spreadsheet to do some basic calculations or use it as a modern day piece of scratch paper to work out some quick numbers. But, sometimes we can get into some complex problems and you don't want to do the same work twice, so you clean up the spreadsheet so you can reuse the analysis and another tool is born.
Design tools with lots of variables will eventually run you into some limitations with an equation or boundary conditions. This is when you want the tool to warn you or another user when the inputs no longer match the model's assumptions.

STYLE and CURRENT cell commands
The picture above shows a perfect example of a dynamic display. There are a list of values in cells B2 through B6. I summed the values in cell B7, but I also wanted to warn the spreadsheet user when the current sum is larger than 100.

Here is how I did it. I used two cell commands "STYLE" and "CURRENT" to test and modify the background color of the cell. Here is the command used in cell B7

=SUM(B2:B6)+STYLE(IF(CURRENT()>100; "Red"; "Default"))

• The Sum command sums the cells B2 through B6
• The Style command changes the style of the cell to either the default cell style or the custom style called "Red". We'll talk about how to create custom styles in a moment.
• The Current command, takes the current value of the cell. So, within the style command we create a test using the IF command and test if the current cell value is greater than 100. If it is, we use cell style "Red" otherwise, we use the Default cell style.

Creating Custom Styles

I'm sure a couple of you were wondering about how do I create a custom cell style "Red".

Simple, I'll walk you through it. Click on any cell and then hit the F11 key on your keyboard or click the "format" on the spreadsheet toolbar and then click the "Styles and formatting" from the submenu. When you do, you'll get this window:

 

You may only have a Default style in this box and the Default style is automatically selected when you bring up the "Styles and formatting" command.


Right Click the Default style and you will get a sub menu to either modify the selected style or create a new one as shown in the figure below.

Now Click New...

When you click new a new cell style textbox will pop up and you'll have a lot of different options for creating this new style like background color, cell border options, and options in different font or font effects and other style changes:

Here is the cell style textbox.

Give a name for your custom cell style. I used Red, but you could use Warning or any other name that suits your Customized cell style.

Now click on the background tab and select a color for your cells that will use this custom style. I used Red, but your color choices are up to you.

When you're done click the OK button.

If you duplicate the code example, you can put different combinations of values in B2 through B6 and you'll have a regular (Default) cell background until the values sum up to 100 or a greater value.

You might also want to add a text string explaining why you do not want values to be beyond a certain value. I did this in cell D7  

The formula for that cell is =IF(B7>100; "Value exceeds 100"; "")

Two years ago I created a structural analysis spreadsheet that worked out the dead loads for a platform to support a 30 foot diameter yurt and I used green, yellow, and red background styles to notify how close the loads were approaching (and exceeding) the burst strength of the concrete piers

Automated testing Display example

In my software QA test work, I've used MS excel to instrument a series of database tests on a new ETL tool developed by a firm in Massachusetts. My macro tool ran a series of sql command scripts that create a user database, run it through the ETL, which updated a larger data warehouse, and then ran some incremental updates to validate the functionality of the ETL tool.  On one excel spreadsheet sheet I had a column of tests status cells that were colored yellow, green or red, which represented untested, test passed, or test failed, respectively. The cell macro also put the text "untested","pass", or "fail" in the cells.

If I were to rewrite the tool in OpenOffice, I would create three styles for the background colors and use the following formula.

=IF(E18=-1;"FAIL"&T(STYLE("Fail"));IF(E18=1;"PASS"&T(STYLE("Pass"));"UNTESTED"&T(STYLE("Untested"))))
It's not fun to write, but once you've got one cell working, cut and paste makes it easy to replicate into other test cells.

Next month we'll talk about changing styles, colors, and cell display options in macros.

Monthly Solution Examples

I hope everyone had a great summer. Mine was spent architecting an addition to a spiritual center in New Hampshire. Of course after the design I ended up helping with the construction, so no vacation trips except to Southern New Hampshire.

As we enter fall I figured it was time to talk about Real Time Visual Indicators in Calc
Sometimes a spreadsheet is just a blur of numbers and if there is a computation error or inputs that
are out of bounds you need to visually and in real time let the user know there is a issue....More

Here is a late July programming example on building Complex Dialogs. The code is based on a task manager tool I created in Calc, but the discussion is on a programming technique to use flags....More


In May I offered a simple calc example on how to program a macro to label a set of columns and how to set the column widths....See More

Using SW flags to manage complex dialog actions

Managing a complicated macro dialog? Use flags
When you are just filling in a text box or selecting a list item, your OpenOffice macro coding is pretty straight forward. You write some routines and attach them to dialog events.

But this dialog has a bit more complexity:

In this little task management tool, I can move tasks from one listbox to another by selecting a listbox item and then clicking the command button between the lists. But if I double click on a list item in a listbox then a submenu pops up

The submenu allows me to promote or demote the selected item within the current listbox, delete the task item or to save the task on another spreadsheet as a completed or dropped task.

Now here is the rub, Humans change their minds on what to do next.

If the user clicks on an item in ListBox1 and then decides to work instead with an item in ListBox2, how will the program know whether or not the user doubled clicked the last listbox selection or clicked once in the new location?  On top of that, you'll probably have two items highlighted in two different Listboxes at this point.

We need to keep track in real time what the user has clicked on and how many times. So, we create a flag variable for each listbox event. When a different listbox is accessed we clear the click count on all the other listbox flags. If we have two consecutive clicks on the same item we start up the submenu dialog.

First, I created three integer variables called  ItmSlct1, ItmSlct2, and ItmSlct3. These three flag variables correspond to ListBox1, ListBox2, and ListBox3, respectively.
These are set to zero when the main dialog is initially displayed.

The next routines are the three event routines when the user clicks an item in one of the ListBoxes and they are called List1PromTst, List2PromTst, and List3PromTst and they manage the state of flags ItmSlct1, ItmSlct2, and ItmSlct3, respectively.

As an example. If a user clicks on a list item from ListBox1, the click event calls List1PromTst
below is the code:

Sub List1PromTst
   Ctrl1=oPkgDlg.getControl("ListBox1")
   x=Ctrl1.getSelectedItemPos  'Test for a selected ListBox item
   if  x>=0 then
      if ItmSlct1 = 0 then     'Test if this is a first click in this ListBox1
         ClearPrevStates(1)  'Clear all other flags except ItmSlct1
         ItmSlct1 = ItmSlct1 + 1   'Increment ItmSlct to one
         exit sub
      else
         call DsplyPromoDlg  'Item has been selected twice, call submenu dialog
      end if
   end if
end sub

If the flag is zero, this routine calls a routine called ClearPrevStates, which zeros out all the other flags except ItmSlct1, which is incremented to one. If it is greater than one, then the routine DsplyPromoDlg is called to display the submenu.
Now if the user clicks on an item in ListBox1 and then an item in ListBox2, then the event routine ListPrompt2 will be called and ListBox2's flag (ItmSlct2) will be zero by the previous ListBox1 event. The code in ListPrompt2 will clear all the ItmSlct flags and set ItmSlct2 to one by calling routine ClearPrevStates(2)

Here is the code for clearing the all flags except the one specified in "NewState". You'll note the first test is to see that the dialog package reference "oPkgDlg" is active before the rest of the code is run. It then tests for which flag is going to be incremented and which flags are
to be cleared

sub ClearPrevStates(NewState)
    'clear any previous selected listbox states
    if Not(IsNull (oPkgDlg)) then 'is notnull
       for x=1 to 3
          if x<>NewState then
             'This flag does not match the current flag, clear any selected item in the ListBox
             Ctrl1=oPkgDlg.getControl("ListBox" & format(x))
             CurSelectPos=Ctrl1.getSelectedItemPos
             if CurSelectPos >=0 then
                oResetSelection = Ctrl1.SelectItemPos(CurSelectPos, False)
             end if
             select case x
                case 1
                   ItmSlct1 = 0
                case 2
                   ItmSlct2 = 0
                case 3
                   ItmSlct3 = 0
             end select
          end if
       next x
   end if
end sub

Then I created a routine called ClearSelctStates. This routine is called when you close a submenu dialog and it clears all the flag variables and resets any highlighted (selected) listbox items.

sub ClearSelctStates
   'clear any previous selected listbox states
   if Not(IsNull (oPkgDlg)) then 'is notnull
      for x=1 to 3
         Ctrl1=oPkgDlg.getControl("ListBox" & format(x))
         CurSelectPos=Ctrl1.getSelectedItemPos
         if CurSelectPos >=0 then
            oResetSelection = Ctrl1.SelectItemPos(CurSelectPos, False)
         end if
      next x
  end if
  ItmSlct1 = 0
  ItmSlct2 = 0
  ItmSlct3 = 0
end sub

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

Solution Books Table of Contents

- Just to give you an idea of what kinds of solutions and design topics I've explained and documented in my Solution Books, The Table of Contents for each book is listed below:

OpenOffice Calc Macro Solutions Book

OpenOffice Writer Macro Solutions Book

OpenOffice Draw Macro Solutions Book

Appendix A talks about dialog components and is a common appendix to all the books

Calc Solutions Table of Contents

My OpenOffice CookBooks

How did the series come about?

    This OpenOffice Calc Macro CookBook 

What is Calc

Cell functions in Calc versus Excel

Calc API calls you need to know about

Macro code to access calc files

ThisComponent method

Creating a New Calc document method

Opening an Existing Calc document method

Accessing sheets and Cells

Accessing Cells

Examples of Access Cells by CellName or by Column/Row

Labeling Columns Example - By .getCellByPosition

Labeling Columns Example - By .getCellRangeByName

Labeling Columns Example – Adjusting Column Widths

Labels with Fixed width Font

Hiding Columns

Incrementing column names – nxtcolLtr function

Dynamic Test Indicators in Calc

A Calc Cell Style Workaround

Custom Cell Functions

Findnth (item within a) cell function

Personhours cell function

Array or cell reference?

Converting values with labels to standard Units

StatRollUp Cell Function

The formulaLocal Property – accessing the cell range string within the cell function call

AbsoluteName = changing functionality by cell function's location

Writing Calc Subroutines with Dialogs

A Sheet Group Navigator

Sheet Navigator Macro Example

Our first Dialog

Openoffice Bug or Feature

Custom Search Routines

Client Search

ClientSearch dialog and code

Access to Dialog and calc sheet

Basic Search Loop

Aligned columns in a listbox

Adding wildcards to the Search

Birthday Search

Access to Dialog and calc sheet

Search Setup and Preload

BirthDate Search Loop

Adding a search option

Using desktop functions to cut and paste sheets

dispatchURL

CopySheetToNewDoc

The Classic MS Office Example – an interactive two listbox Dialog

Finding an online solution and an API bug

Loading ListBox1 (AddItem and AddItems methods)

The => CommandButton(getSelectedItemPos method)

The <= CommandButton

Iterating Items within a ListBox

Appendix A - Dialog Controls Overview

Appendix B – Cell Formatting Fonts, Bolding, and Color Effects

Using the Debugger to find cell values

Left, Right, and Center Justify Cell text:

Setting Cell Font

Cell Font Height

Setting Font Color

Setting Background Color

Bolding Cell Text

Setting Line Wrapping

Underlining Cell Text

Two Underlining methods

Table Borders

Appendix C – Client Search and Birthday Search Tools

Client search Dialog box

Birthday search Dialog box

Back to top of Page

Writer Solutions Table of Contents

My OpenOffice CookBooks

How did the series come about?

This OpenOffifce Writer Macro CookBook 

Macro code to access writer files

ThisComponent method

Creating a Writer document method

Opening an Existing Write document method

Writer Document's collection paradigm

writer Tables - Mailing Labels Macro

Enumeration objects

Manipulating Documents with Cursors

Using text cursors

MoveCursorToText Example

MoveCursorToText

Automating Documentation

Module Mailer

LoadMailerOptionsDialog

Interactive Dialog Control routines

Basic MoveClient2MailList operation

Accessing Tables within Writer

Globals and main routine setup

Testing for Paragraphs and Tables

Retrieving Table Cell strings

Macro Book Construction

OpenOffice API calls to build writer documents

Using text cursors

BuildNewDoc code

LoadTempltFile

LoadContentAnHdrs

WriteContentLines

InsertingTableOfContent

Working with Frames – The Writer portion of the two listbox label making example

SOAP Example Extended

Dialog SoapSetup

Module Create Soaps

CopySheetToNewDoc(TempltType as Integer)

Appendix A - Dialog Controls Overview

Appendix B – Contract Automation Example and Code 59

Scrubbed Contract Document 61

Back to top of Page

Draw Solutions Table of Contents

My OpenOffice CookBooks 4

How did the series come about? 4

This OpenOffice Draw Macro CookBook 5

What is OpenOffice Draw 5

Vector versus Bitmap drawing systems 6

Draw and Impress Applications 6

Macro code to access draw files 6

ThisComponent method 7

Creating a New Draw document method 7

Opening an Existing Draw document method 7

Drawpages and Masterpages 8

DrawPages linked to MasterPage 8

Pages can have the same name 8

Draw Macro Basics 9

Portrait orientation and dimensions 9

Basic draw macro Routines 10

Adding Shapes to a page 10

Creating shapes routine 11

Erasing Shapes from the drawpage 15

Drawing Metadata 16

ConnectorShapes 16

Scanning a drawing for Metadata 18

Which Gluepoint? 21

How About Drawing Objects with Property Supports 23

Object Description Field 2

Appendix A - Dialog Controls Overview 28

Back to top of Page
 

Appendix A - Dialog Controls Overview

X-Ray or Debugger

Setting the Date in DateField

DateField Options

NumericField Control

CurrencyField Control

TimeField Control

ListBox Control

Aligned Columns in a ListBox

The Global Dialog reference

ComboBox Control

CheckBoxes and Option Controls

OptionButton Control

CheckBox Control

PatternField Control

ImageControl Controls

FixedLine Controls

ScrollBar Controls

ListBox and value list storage decisions

Dynamic ListBox item loading

Back to top of Page