[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
r_vinoya
Super User

Joined: 03 Dec 2003
Posts: 619
Location: Somewhere in the Philippines

Posted: Thu Feb 05, 2004 7:07 pm    Post subject: Column Number to String...

I found a code (from AndrewMacro.sxw...Chapter6.6) that converts column number to string...
But it have some bugs ( 0, 1, 2, ... , 11, 12, 13, 14, 15 converts to A, B, C, ... ,L, M, BN, BO)...

I made modification of the macro:
 Code: Function columnnumtostr (ByVal colnum As long) As String Rem This Code converts column index number to letter (ex. 1 => A, 18 => R)    dim r\$    if colnum>26 then       b = int(colnum/26)       a = colnum MOD 26       if a = 0 then          a = 26          b = b-1       endif       r\$ = chr(64 + b) & chr(64+a)    else       r\$ = chr (64 + colnum)    endif        columnnumtostr = r\$ End Function

Hope this help...
Thanks for the original macro that get me started.
_________________
# : - )
Iannz

Joined: 14 Feb 2004
Posts: 494
Location: Christchurch, New Zealand

Posted: Sat Feb 14, 2004 8:24 pm    Post subject: An alternative column number to string and reverse

In these routines column numbers start at zero thus A <-> 0 B <-> 1 etc

 Code: function fnColStr2Num(byval sCol as string) as long dim i as integer, nCol as integer sCol=ucase(sCol) nCol = 0 for i = 1 to len(sCol)    nCol=nCol*26 + asc(mid(sCol,i,1)) - 64 next fnColStr2Num = nCol - 1 end function function fnColNum2Str(byval nCol as long) as string dim sCol as string sCol="" while nCol >= 0    sCol =  chr(65 + (nCol mod 26)) & sCol    nCol = int(nCol / 26) - 1 wend fnColNum2Str = sCol end function

_________________
Cheers, Ian

http://wiki.services.openoffice.org/wiki/Extensions_development_basic a wiki about writing OpenOffice.org extensions.
DannyB
Moderator

Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

 Posted: Thu Jun 10, 2004 6:37 am    Post subject: Just adding some cross references.... Column Number to Column Name and vice versa http://www.oooforum.org/forum/viewtopic.php?p=23013#23013_________________Want to make OOo Drawings like the colored flower design to the left?
pitonyak

Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

Posted: Mon Jun 14, 2004 7:50 am    Post subject:

Hey r_vinoya, next time you find a bug in a macro in my document, let me know so that the fix can be shown there as well... Added bonus, I will credit the fix to you!

I found this bug while writing my book so I also updated that section in my macro document. My current code is as follows:
 Code: 'Given a cell, extract the normal looking address of a cell 'First, the name of the containing sheet is extracted. 'Second, the column number is obtained and turned into a letter 'Lastly, the row is obtained. Rows start at 0 but are displayed as 1 Function PrintableAddressOfCell(the_cell As Object) As String   PrintableAddressOfCell = "Unknown"   If Not IsNull(the_cell) Then     PrintableAddressOfCell = the_cell.getSpreadSheet().getName + ":" + _       ColumnNumberToString(the_cell.CellAddress.Column) + (the_cell.CellAddress.Row+1)   End If End Function ' Columns are numbered starting at 0 where 0 corresponds to A ' They run as A-Z,AA-AZ,BA-BZ,...,IV ' This is esentially a question of how do you convert a Base 10 number to ' a base 26 number. ' Note that the_column is passed by value! Function ColumnNumberToString(ByVal the_column As Long) As String   Dim s\$   'Save this so I do NOT modify the parameter.   'This was an icky bug that took me a while to find   Do while the_column >= 0     s\$ = Chr(65 +  the_column MOD 26) + s\$     the_column =  the_column \ 26 - 1   Loop   ColumnNumberToString = s\$ End Function

Notice that you can use integer division out of the box and it works great! If you really want the address of a cell, however, a new service was introducted in OOo 1.1.1. I document this in both my macro document and my book (the book contains the more complete information). The short version is that the one of the properties returns a different value depending on the currently active sheet.

_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
hansm
General User

Joined: 22 Jun 2004
Posts: 19

Posted: Tue Jul 27, 2004 4:36 am    Post subject:

 Code: Function ColumnNumberToString(ByVal the_column As Long) As String   oDoc = ThisComponent   oSheet = oDoc.Sheets( 0)   ColumnNumberToString = oNSheet.getcolumns().ElementNames( the_column) End Function

It uses the internal index->columnname conversion of the sheet.
NisSAM
Newbie

Joined: 24 Sep 2010
Posts: 3

 Posted: Sat Sep 25, 2010 10:11 am    Post subject: I prefer to use my own function just because it works faster. But !!! I always check at start of the application that for an arbitrary column number I've the same result from my own function and getName. After that I am sure that my quick function works OK.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum