| View previous topic :: View next topic |
| 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 |
|
| Back to top |
|
 |
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.
_________________
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 |
|
|
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
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...
_________________
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 |
|
|
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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? <<< |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
|
|
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
|
