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

Author Message
Paki
General User

Joined: 21 Mar 2007
Posts: 7

 Posted: Wed Mar 21, 2007 6:27 pm    Post subject: Changing values on input? [Solved] How would you change numeric values on input so that they would remain numeric values? I have a list of numbers that I would like to reverse on input i.e. 1 becomes 7, 2 becomes 6, 3 becomes 5, 4 stays, 5 becomes 3, etc... Standard formulas don't work because they get overwritten on new input. I have no idea how to solve this.Last edited by Paki on Thu Mar 22, 2007 10:52 am; edited 1 time in total
squenson
Super User

Joined: 09 Mar 2007
Posts: 690
Location: Nis, Serbia

Posted: Wed Mar 21, 2007 11:01 pm    Post subject:

Paki,

You can create a new function that will do the transformations you want. To create a function, go to Tools>Macros>Organize Macros>OpenOffice Basic, then "expand" your document on the left column, click on Standard, press the New button on the right, click OK, then paste this code:
 Code: Function ReverseSomeDigits(lInput as double) as double ' This function transforms some digits in other digits according to some rules ' defined in a Select Case statement.    Dim sInput as string    Dim sOutput as String    Dim i as Long                          ' Initialization       sInput = format(lInput, "#.################")    sOutput = ""                          ' Let's go through each character of the input string                      ' and apply the transformation rule                      ' If a character has no rule, we do not change it    For i = 1 to Len(sInput)       Select Case Mid(sInput, i, 1)       Case "1"          sOutput = sOutput & "7"       Case "2"          sOutput = sOutput & "6"       Case "3"          sOutput = sOutput & "5"       Case "4"          sOutput = sOutput & "4"       Case "5"          sOutput = sOutput & "3"       Case "6"          sOutput = sOutput & "2"       Case "7"          sOutput = sOutput & "1"       Case Else          sOutput = sOutput & Mid(sInput, i, 1)       End Select    Next i                          ' The output of the function is a number    ReverseSomeDigits = Val(sOutput) End Function
Now, in a cell to the right of your input column (let's suppose you input your numbers in range A1-Axxx), type =ReverseSomeDigit(A1), drag the range to the last cell and this should do the trick.

Hope this helps,
Stephane Quenson.
_________________
>>> Do you know the new OOo support forum http://user.services.openoffice.org/en/forum/index.php? <<<
Mark B
Super User

Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK

 Posted: Wed Mar 21, 2007 11:48 pm    Post subject: I'm always keen to add a macro whenever it'll save time and effort - but this seems to be a bit if an overkill (unless I'm missing something). Would it not be easier just to select the cells and then click on Data | Sort and then click on 'Decending'? Mark_________________Mark B's Articles
Paki
General User

Joined: 21 Mar 2007
Posts: 7

 Posted: Thu Mar 22, 2007 7:46 am    Post subject: Thanks for the help however... Stephanie - your solution doesn't allow for change of input in the same cell. I would (preferably) like the code to be in the cell's internal structure, not linked to another cell. If this isn't even possible, that's OK and your solution works fine. Mark - the Data | Sort function doesn't change the individual numbers, it just sorts the current numbers in order i.e. 7, 4, 2, 5, 1 doesn't become 1, 4, 6, 3, 7 like I want it too, it becomes 7, 5, 4, 2, 1. Unless I did something different than you suggested? Paki
Mark B
Super User

Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK

Posted: Thu Mar 22, 2007 8:24 am    Post subject:

Paki

Try this then
 Code: Sub change_range_contents   oRange = thisComponent.getCurrentSelection.getRangeAddress   oSheet =  thiscomponent.CurrentSelection.getSpreadSheet   for r = oRange.StartRow to oRange.EndRow     oCell = oSheet.getCellByPosition ( oRange.startColumn, r)     select case  oCell.value      Case "1"        result = 7      Case 2        result = 6      Case 3        result = 5      Case 4        result = 4      Case 5        result = 3      Case 6        result = 2      Case 7        result = 1     end select     oCell.value = result   next r End Sub

Place your numbers in a column, select them all and then run the macro. The new values will then be written back into the correct cell.

Mark
_________________
Mark B's Articles
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

Posted: Thu Mar 22, 2007 10:24 am    Post subject: Re: Changing values on input?

 Paki wrote: How would you change numeric values on input so that they would remain numeric values? I have a list of numbers that I would like to reverse on input i.e. 1 becomes 7, 2 becomes 6, 3 becomes 5, 4 stays, 5 becomes 3, etc... Standard formulas don't work because they get overwritten on new input. I have no idea how to solve this.

What you try to do is running agaist the general concept of spreadsheets, which is about conversion of data by formulae. This way it is always reproducable what has been converted and how. A formula to convert 1...7 to 7...1:
=7-MOD(A1-1;7)
You can restrict input-values to 1...7 by Menu:Data>Validity.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org
Paki
General User

Joined: 21 Mar 2007
Posts: 7

 Posted: Thu Mar 22, 2007 10:51 am    Post subject: Thank you for all your help. It looks like what I want done can't be done without a whole lot of trouble...thankfully the person I'm developing this for is flexible and I'll use one of your workarounds to get close to the desired result. Again thanks.
squenson
Super User

Joined: 09 Mar 2007
Posts: 690
Location: Nis, Serbia

Posted: Thu Mar 22, 2007 10:58 am    Post subject:

paki,

If you like my solution, it is very easy to use it with an event linked to the change of the content of a cell. Here is the additional code that you should add to the library. Don't forget to update your sheet name and cell range at the beginning of "EnableEvents" routine.
 Code: option explicit Global oGroup as Object Global oListener as Object Global oCell as Object Sub EnableEvents     Dim oSheet as Object                           ' Put on the line below the range that will be "listened to"                       ' Update it with your Sheet Name and range cell!!     oListener = createUnoListener("CellChange_","com.sun.star.util.XModifyListener")     oGroup = ThisComponent.getSheets.getByName("ReverseSomeDigits").getCellRangeByName("A1:A6") ' < UPDATE THIS LINE!    oGroup.addModifyListener(oListener)         msgbox "Enabling events..."     End Sub Sub CellChange_modified(aEvent)                      ' Let's suppress screen update    ScreenUpdatingOff                          ' First action is to remove the listener, as we                      ' will change the cells that are listened to, so                      ' we avoid endless loop!     DisableEvents                      ' Save the position of the current cell    oCell = ThisComponent.CurrentSelection                          ' Call the sub that will process the event    ReverseSomeDigits_Process                      ' Last action is to re-enable the listener    oGroup.addModifyListener(oListener)                          ' Let's allow again screen update    ScreenUpdatingOn                      ' We position the cursor on the previous selected cell                      ' in case it was change by the processed event    ThisComponent.CurrentController.Select(oCell)     End Sub Sub DisableEvents    oGroup.removeModifyListener(oListener)   End Sub Sub ScreenUpdatingOn ' This routine allows screen updating    ThisComponent.UnlockControllers    ThisComponent.removeActionLock End Sub Sub ScreenUpdatingOff ' This routine blocks screen updating and therefore allows faster macro execution    ThisComponent.addActionLock    ThisComponent.LockControllers End Sub Sub ReverseSomeDigits_Process    oCell.Value = ReverseSomeDigits(oCell.Value)     End Sub

Once you have copy/pasted the code, you should run the macro "EnableEvents" (you can attach it to a default event like Open document). Now, try to modify a cell in the range you indicated. Once you press Enter, it should automatically change. Magic...
_________________
>>> Do you know the new OOo support forum http://user.services.openoffice.org/en/forum/index.php? <<<
Paki
General User

Joined: 21 Mar 2007
Posts: 7

Posted: Thu Mar 22, 2007 11:18 am    Post subject:

Stephane,

I tried setting up the macro like you suggested, however it doesn't seem to be working. I updated the Sheet name and Range cell as suggested (namely updating them to "Test" and "B6:DR27"). I also ran the EnableEvents Macro, but it isn't doing anything. Here's the macro code as laid out in the macro "Reverse" (my own name for it - does it have to match with something you wrote?).

 Code: REM  *****  BASIC  ***** Sub Main End Sub Function ReverseSomeDigits(lInput as double) as double ' This function transforms some digits in other digits according to some rules ' defined in a Select Case statement.    Dim sInput as string    Dim sOutput as String    Dim i as Long                          ' Initialization       sInput = format(lInput, "#.################")    sOutput = ""                          ' Let's go through each character of the input string                      ' and apply the transformation rule                      ' If a character has no rule, we do not change it    For i = 1 to Len(sInput)       Select Case Mid(sInput, i, 1)       Case "1"          sOutput = sOutput & "7"       Case "2"          sOutput = sOutput & "6"       Case "3"          sOutput = sOutput & "5"       Case "4"          sOutput = sOutput & "4"       Case "5"          sOutput = sOutput & "3"       Case "6"          sOutput = sOutput & "2"       Case "7"          sOutput = sOutput & "1"       Case Else          sOutput = sOutput & Mid(sInput, i, 1)       End Select    Next i                          ' The output of the function is a number    ReverseSomeDigits = Val(sOutput) End Function option explicit Global oGroup as Object Global oListener as Object Global oCell as Object Sub EnableEvents     Dim oSheet as Object                           ' Put on the line below the range that will be "listened to"                       ' Update it with your Sheet Name and range cell!!     oListener = createUnoListener("CellChange_","com.sun.star.util.XModifyListener")     oGroup = ThisComponent.getSheets.getByName("Test").getCellRangeByName("B6:DR27")    oGroup.addModifyListener(oListener)         msgbox "Enabling events..."     End Sub Sub CellChange_modified(aEvent)                      ' Let's suppress screen update    ScreenUpdatingOff                          ' First action is to remove the listener, as we                      ' will change the cells that are listened to, so                      ' we avoid endless loop!     DisableEvents                      ' Save the position of the current cell    oCell = ThisComponent.CurrentSelection                          ' Call the sub that will process the event    ReverseSomeDigits_Process                      ' Last action is to re-enable the listener    oGroup.addModifyListener(oListener)                          ' Let's allow again screen update    ScreenUpdatingOn                      ' We position the cursor on the previous selected cell                      ' in case it was change by the processed event    ThisComponent.CurrentController.Select(oCell)     End Sub Sub DisableEvents    oGroup.removeModifyListener(oListener)   End Sub Sub ScreenUpdatingOn ' This routine allows screen updating    ThisComponent.UnlockControllers    ThisComponent.removeActionLock End Sub Sub ScreenUpdatingOff ' This routine blocks screen updating and therefore allows faster macro execution    ThisComponent.addActionLock    ThisComponent.LockControllers End Sub Sub ReverseSomeDigits_Process    oCell.Value = ReverseSomeDigits(oCell.Value)     End Sub

Thanks for help. I changed the digits manually for the client, but this is for my own info.

Paki
Paki
General User

Joined: 21 Mar 2007
Posts: 7

 Posted: Thu Mar 22, 2007 11:21 am    Post subject: Stephane, Nevermind...I had disabled macros in Calc overall...my stupid mistake. Thanks for the help, it seems to be running nicely now. Paki
Paki
General User

Joined: 21 Mar 2007
Posts: 7

 Posted: Thu Mar 22, 2007 11:22 am    Post subject: One more quick question...will this macro run in Excel? I mean if I save it as a .xls will it save the macro settings and code? Paki
squenson
Super User

Joined: 09 Mar 2007
Posts: 690
Location: Nis, Serbia

 Posted: Thu Mar 22, 2007 11:30 am    Post subject: Paki, No, it will not work in Excel, as the syntax for macros is not the same. Excel is using VBA, OpenOffice is using StarBasic or Java or Python and may be some other languages. Even if the languages would be the same, there are some differences in the object model between OOo and Excel, making the automatic transformation of macros something as complex as (human) language translation._________________Help us to help you: Add [Solved] to the title of the thread if you agree with the answer >>> Do you know the new OOo support forum http://user.services.openoffice.org/en/forum/index.php? <<<
Paki
General User

Joined: 21 Mar 2007
Posts: 7

 Posted: Thu Mar 22, 2007 11:36 am    Post subject: Right...thanks again. I should be set for now. Paki
Mark B
Super User

Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK

Posted: Thu Mar 22, 2007 11:45 am    Post subject:

 Paki wrote: One more quick question...will this macro run in Excel? I mean if I save it as a .xls will it save the macro settings and code? Paki

The macros discussed here will not work in Excel because they've been written specifically for OOo Calc. However, if you need macros that will work in both then you can use a version of OOo with VBA support - for example on SUSE and Ubuntu Linux or the Novell version of OOo for Windows.

Mark
_________________
Mark B's Articles
 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