OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Changing values on input? [Solved]

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Paki
General User
General User


Joined: 21 Mar 2007
Posts: 7

PostPosted: Wed Mar 21, 2007 6:27 pm    Post subject: Changing values on input? [Solved] Reply with quote

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
Back to top
View user's profile Send private message
squenson
Super User
Super User


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

PostPosted: Wed Mar 21, 2007 11:01 pm    Post subject: Reply with quote

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.
_________________
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? <<<
Back to top
View user's profile Send private message
Mark B
Super User
Super User


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

PostPosted: Wed Mar 21, 2007 11:48 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
Paki
General User
General User


Joined: 21 Mar 2007
Posts: 7

PostPosted: Thu Mar 22, 2007 7:46 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Mark B
Super User
Super User


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

PostPosted: Thu Mar 22, 2007 8:24 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Mar 22, 2007 10:24 am    Post subject: Re: Changing values on input? Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Paki
General User
General User


Joined: 21 Mar 2007
Posts: 7

PostPosted: Thu Mar 22, 2007 10:51 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
squenson
Super User
Super User


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

PostPosted: Thu Mar 22, 2007 10:58 am    Post subject: Reply with quote

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...
_________________
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? <<<
Back to top
View user's profile Send private message
Paki
General User
General User


Joined: 21 Mar 2007
Posts: 7

PostPosted: Thu Mar 22, 2007 11:18 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Paki
General User
General User


Joined: 21 Mar 2007
Posts: 7

PostPosted: Thu Mar 22, 2007 11:21 am    Post subject: Reply with quote

Stephane,

Nevermind...I had disabled macros in Calc overall...my stupid mistake. Thanks for the help, it seems to be running nicely now.

Paki
Back to top
View user's profile Send private message
Paki
General User
General User


Joined: 21 Mar 2007
Posts: 7

PostPosted: Thu Mar 22, 2007 11:22 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
squenson
Super User
Super User


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

PostPosted: Thu Mar 22, 2007 11:30 am    Post subject: Reply with quote

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? <<<
Back to top
View user's profile Send private message
Paki
General User
General User


Joined: 21 Mar 2007
Posts: 7

PostPosted: Thu Mar 22, 2007 11:36 am    Post subject: Reply with quote

Right...thanks again. I should be set for now.

Paki
Back to top
View user's profile Send private message
Mark B
Super User
Super User


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

PostPosted: Thu Mar 22, 2007 11:45 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group