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

Lotus Formula Input + Disable Capitalization

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets
View previous topic :: View next topic  
Author Message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Feb 13, 2006 5:00 pm    Post subject: Lotus Formula Input + Disable Capitalization Reply with quote

The anti-capitalization is obsolete since version 2.3
The Lotus input is integrated since version 2.4

Edit 2006-02-28:
Enhanced version added to this thread
2006-03-02: Further enhancement: replaced Sub StartFormula(sFmlStart$)
2007-06-28: Added another module, which disables capitalization of letters a-z

In the calc-forum there have been many complaints about missing "Lotus"- formula input starting with "+" or "-"
There is a patch availlable at http://www.openoffice.org/issues/show_bug.cgi?id=20496
but who wants to recompile this monster?
The following basic-code is a lame duck because
1) It does not initialize formula input mode when you start input with "+" or "-". It just changes a string to "=.*" or "=-.*" respectivly after it has been entered into a cell. You can't point to a referred cell(-range) by selection as in real formula input mode.
2) It applies to the currently focussed cell(aka "active cell") only. A multiple input (Alt+Enter) does not convert the other cells in selection.

So it is best used for quick calculation of constants, which raises some questions about separation of data and processing. But, hey it's a spreadsheet and as such it is used in many ways.
BUG: removeModifyListener does not stop the Lotus-mode.
This feature is not saved with the document, so there is no need to remove it on closing
---------WARNING----------
This is not thoroughly tested. For testing call OOo_DocOpen_DocCreate() with a spreadsheet-doc as currently active document rather than assigning any Open/Creation events.
---------------INSTALL----------------------------
Put the entire code to some Module of library "Standard"
in container "My Macros"(user-profile) or "OOoMacros"(install-path)
Assign OOo's events "Doc Creation" and/or "Doc Open" to OOo_DocOpen_DocCreate.
"Creation" applies to new docs, "Open" to existing ones
If you like to use this feature with some documents:
Assign the "Open"-Event of the document to OOo_DocOpen_DocCreate
For a template, use the "Creation"-event as well.
Code:

REM  *****  BASIC  *****
Global globLotusListener as Object
Sub OOo_DocOpen_DocCreate()
   if hasUnoInterfaces(ThisComponent,"com.sun.star.sheet.XSpreadsheetDocument") then
      Activate_Lotus_FormulaInput(ThisComponent.Sheets)
   end if
End Sub
'remove the listener from all sheets of all open spreadsheet-documents
'For some unknown reason this does not work:
Sub DeActivate_ALL_Lotus_FormulaInput()
Dim oDocs,oDoc
   oDocs = StarDesktop.getComponents.createEnumeration
   While oDocs.hasMoreElements
      oDoc = oDocs.nextElement
      if hasUnoInterfaces(oDoc,"com.sun.star.sheet.XSpreadsheetDocument") then
         DeActivate_Lotus_FormulaInput(oDoc.Sheets)
      endif
   Wend
End Sub
'-------------------needs sheets-collection of some spreadsheet-doc as parameter
Sub Activate_Lotus_FormulaInput(oSheets)
Dim i%
   globLotusListener = createUnoListener("LotusInput_","com.sun.star.util.XModifyListener")
   for i = 0 to oSheets.Count -1
      oSheets.getByIndex(i).addModifyListener(globLotusListener)
   next
End Sub
'For some unknown reason this does not work:
Sub DeActivate_Lotus_FormulaInput(oSheets)
Dim i%
   if NOT isNull(globLotusListener)then
      for i = 0 to oSheets.Count -1
         oSheets.getByIndex(i).removeModifyListener(globLotusListener)
      next
   endif
End Sub
'-------------------event-subs, called by the Listener-----------
Sub LotusInput_disposing()
   'does nothing, but don't remove it
End Sub
Sub LotusInput_Modified(oEv)
Dim oCell,sFL$,sChar$
   oCell = getFocussedCell(thisComponent,false)
   sFL = oCell.FormulaLocal
   sChar = left(sFL,1)
   if sChar = "+" then
      oCell.FOrmulaLocal = "="& mid(sFL,2)
   elseif sChar = "-" then
      oCell.FOrmulaLocal = "="& sChar & mid(sFL,2)
   endif
end sub
'-------usefull helper-function, returning focussed cell or it's address
'by UROS > http://www.oooforum.org/forum/viewtopic.phtml?t=19348
REM 2006-08-09: fixed error when row > 8191
'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253/8191/0/0/0/0/2/246/0/0/8158;0/0/0/0/0/0/2/0/0/0/0
'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253+8192+0+0+0+0+2+246+0+0+8158;0/0/0/0/0/0/2/0/0/0/0
Function GetFocussedCell(oDoc as Object,bAddr as Boolean) as Object
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
   as1()  = Split(oDoc.currentController.ViewData, ";")
   lSheet = CLng(as1(1))
   sDum = as1(lSheet +3)
   as1() = Split(sDum, "/")
   on error goto errSlash
      lCol = CLng(as1(0))
      lRow = CLng(as1(1))
   on error goto 0
   if bAddr then
      GetFocussedCell = oDoc.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow).getCellAddress
   else
      GetFocussedCell = oDoc.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
   end if
exit Function
errSlash:
   if NOT(bErr) then
      bErr = True
      as1() = Split(sDum, "+")
      resume
   endif
End Function


Last edited by Villeroy on Sat Apr 05, 2008 5:11 am; edited 5 times in total
Back to top
View user's profile Send private message
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Tue Feb 14, 2006 10:37 am    Post subject: Reply with quote

Villeroy, thank you for this code.
_________________
Linux & Windows OOo3.0
UNO & C++ : WIKI
http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK
In French
http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide
Back to top
View user's profile Send private message Visit poster's website
Duster929
General User
General User


Joined: 06 Feb 2006
Posts: 36

PostPosted: Tue Feb 14, 2006 1:34 pm    Post subject: Reply with quote

Villeroy, thanks for this. I followed the instructions from the other thread and it worked well. I thought I'd post this here so as not to completely change the topic of that other thread.

This works well for entering formulas as straight values, sort of calculator-style. It also works if I am going to type in a formula using the keyboard, for example +c5+c6.

However, it doesn't work if I want to select another cell in the context of the formula, either using the arrow keys or clicking on a cell. For example, to enter +2*c5, I'd have to type it all out, I can't hit "+" then "2" then "*", then click on c5 with the mouse.

It also only works if I save it in OOo format. If I save it in xls, then the macro doesn't work any more. I suppose this has to do with formats, OOo Basic vs. whatever language MS uses for their Excel macros these days.

These are not killer problems - this is still a step in the right direction. But I'm hoping we can go a step further and have it implemented right in the Calc code, making + equivalent to =. Smile

Thank you for the code. I'll keep using/testing it and let you know more of my thoughts on it.

--- D
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 14, 2006 1:51 pm    Post subject: Reply with quote

As I stated before:
Quote:

The following basic-code is a lame duck because
1) It does not initialize formula input mode when you start input with "+" or "-". It just changes a string to "=.*" or "=-.*" respectivly after it has been entered into a cell. You can't point to a referred cell(-range) by selection as in real formula input mode.
2) It applies to the currently focussed cell(aka "active cell") only. A multiple input (Alt+Enter) does not convert the other cells in selection.

Yes, StarBasic is almost the same language as MS Visual Basic For Applications (VBA) but the thing you talk to (API) is a completely different one.
Macros can not change the application. Macros are just some kind of "auto pilot" driving the application. This one is called when a cell has already changed. Then it looks if cell's input is a string-value starting with "+" or "-". Then it retypes the input with a predeeding "=".
Back to top
View user's profile Send private message
Duster929
General User
General User


Joined: 06 Feb 2006
Posts: 36

PostPosted: Tue Feb 14, 2006 1:55 pm    Post subject: Reply with quote

Got it. Now I understand what you meant in your "lame duck" posting.

One day we will all be on OOo, and these MS compatibility issues will all be behind us. Smile

--- D
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 28, 2006 10:03 am    Post subject: ENHANCED VERSION Reply with quote

ANOTHER EDIT 2006-03-02: Replaced Sub StartFormula(sFmlStart$) after ms777 found the final tweak
Credits:
To noranthon, who came up with the idea to start edit mode rather than edit the cell after editing.
To ms777, who spent some time and effort to make it perfect. He found a way how to start a formula while in insert-mode.
To uros who found a way to get the focussed cell within a range selection.
This version listens to the keys "+" and "-", puts a formula prefix ("=" or "=-") into the active cell starting edit mode.

INSTALLATION:
1. Copy this code into any library. If you save this in a document's library the feature is only availlable for documents having this code. Normally you would choose some library in container "My Macros"
2. Edit the three constants libContainer, libName and modName accordingly.
3. Assign a shortcut or button to "addLotusKeyEvents" in order to activate this.
3.1. If you like to use this with any spreadsheet, assign the events "Doc Open" and "Doc Creation" to "addLotusKeyEvents" (Menu:Tools>Customize>Tab:Events).
"removeLotusKeyEvents" turns the feature off.
Code:

REM  *****  BASIC  *****
'container ("document" or "application")
Const libContainer$ = "application"
'Name of this library
Const libName$ = "Standard"
'name of this module
Const modName$ = "Lotus"

Sub removeLotusKeyEvents
on error resume next
Dim oSM,oEv as new com.sun.star.awt.KeyEvent
   oSM =  thiscomponent.getUIconfigurationmanager.shortcutmanager
   oEv.KeyCode = com.sun.star.awt.Key.ADD
   oEv.KeyChar = "+"
   oEv.KeyFunc = 0
   oSM.removeKeyEvent(oEv)
   oEv.KeyCode = com.sun.star.awt.Key.SUBTRACT
   oEv.KeyChar = "-"
   oEv.KeyFunc = 0
   oSM.removeKeyEvent(oEv)
end sub
Sub addLotusKeyEvents
Dim oSM,oEv as new com.sun.star.awt.KeyEvent
   if hasUnoInterfaces(ThisComponent,"com.sun.star.sheet.XSpreadsheetDocument") then
      oSM =  thiscomponent.getUIconfigurationmanager.shortcutmanager
      oEv.KeyCode = com.sun.star.awt.Key.ADD
      oEv.KeyChar = "+"
      oEv.KeyFunc = 0
      oSM.setKeyEvent(oEv,"vnd.sun.star.script:"& libName &"."& modName &".StartPlusFormula?language=Basic&location="& libContainer)
      oEv.KeyCode = com.sun.star.awt.Key.SUBTRACT
      oEv.KeyChar = "-"
      oEv.KeyFunc = 0
      oSM.setKeyEvent(oEv,"vnd.sun.star.script:"& libName &"."& modName &".StartMinusFormula?language=Basic&location="& libContainer)
   endif
end sub
Sub StartPlusFormula
   StartFormula "="
End Sub
Sub StartMinusFormula
   StartFormula "=-"
End Sub
sub StartFormula(sFmlStart$)
   document   = ThisComponent.CurrentController.Frame
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   dim args1(5) as new com.sun.star.beans.PropertyValue
   oCell = getFocussedCell(thisComponent,false)
   args1(0).Name = "Font.StyleName"
   args1(0).Value = oCell.CharFontStyleName
   args1(1).Name = "Font.Pitch"
   args1(1).Value = oCell.CharFontPitch
   args1(2).Name = "Font.CharSet"
   args1(2).Value = oCell.CharFontCharSet
   args1(3).Name = "Font.Family"
   args1(3).Value = oCell.CharFontFamily
   args1(4).Name = "Font.FamilyName"
   args1(4).Value = oCell.CharFontName
   args1(5).Name = "Symbols"
   args1(5).Value = sFmlStart
   dispatcher.executeDispatch(document, ".uno:InsertSymbol", "", 0, args1())
end sub
'-------usefull helper-function, returning focussed cell or it's address
'by UROS > http://www.oooforum.org/forum/viewtopic.phtml?t=19348
REM 2006-08-09: fixed error when row > 8191
'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253/8191/0/0/0/0/2/246/0/0/8158;0/0/0/0/0/0/2/0/0/0/0
'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253+8192+0+0+0+0+2+246+0+0+8158;0/0/0/0/0/0/2/0/0/0/0
Function GetFocussedCell(oDoc as Object,bAddr as Boolean) as Object
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
   as1()  = Split(oDoc.currentController.ViewData, ";")
   lSheet = CLng(as1(1))
   sDum = as1(lSheet +3)
   as1() = Split(sDum, "/")
   on error goto errSlash
      lCol = CLng(as1(0))
      lRow = CLng(as1(1))
   on error goto 0
   if bAddr then
      GetFocussedCell = oDoc.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow).getCellAddress
   else
      GetFocussedCell = oDoc.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
   end if
exit Function
errSlash:
   if NOT(bErr) then
      bErr = True
      as1() = Split(sDum, "+")
      resume
   endif
End Function


Last edited by Villeroy on Fri Dec 01, 2006 11:50 am; edited 2 times in total
Back to top
View user's profile Send private message
uros
Super User
Super User


Joined: 22 May 2003
Posts: 601
Location: Slovenia

PostPosted: Wed Mar 01, 2006 11:48 pm    Post subject: Re: ENHANCED VERSION Reply with quote

Villeroy wrote:
To uros who found a way to get the focussed cell within a range selection.
Actualy, ms777 contributed the original idea, I just used it to make a function...
Uros
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Mar 02, 2006 12:30 pm    Post subject: Reply with quote

Uros,
Your little function is the best one I ever found on this site Exclamation
It is essential whenever someone has to manipulate the active cell within a current range selection. Search this site for "getFocussedCell" and you get some recent matches where I used it with some simple macros. It is one of the most called functions in my lib "Standard". Before I found this I had to abort some routines with a msgbox like "Please select a single cell..."
Back to top
View user's profile Send private message
uros
Super User
Super User


Joined: 22 May 2003
Posts: 601
Location: Slovenia

PostPosted: Thu Mar 02, 2006 10:12 pm    Post subject: Reply with quote

Hi Villeroy!
I'm glad you find it so usefull!
Uros
Back to top
View user's profile Send private message
paulmath
Newbie
Newbie


Joined: 26 Apr 2007
Posts: 2
Location: Haarlem :: Netherlands

PostPosted: Thu Apr 26, 2007 1:43 am    Post subject: Re: ENHANCED VERSION Reply with quote

Hi,

my wife needs this feature very badly. She uses the keypad very frequently and effectively for bookkeeping pruposes, and the need to type "=" all the time slows the speed down dramaticly! And since she is used to other sofware that allows her to use the keypad only it also annoys her a lot....

This script seems the solution, the only problem is that I will have to implement it and I am a total noob on the subject Sad

It's a bit difficult for me to explain what I tried since my native is Dutch and we use a Dutch version, but I tried to add the code under "My Macro's|Standard|Module1" (would that be the correct place to add it?). Now when I activate the macro and try to enter a calculation I get an error message that says: "getScript: no script!"

I think the problem is that I don't understand this:
Villeroy wrote:
[1. Copy this code into any library. If you save this in a document's library the feature is only availlable for documents having this code. Normally you would choose some library in container "My Macros"
2. Edit the three constants libContainer, libName and modName accordingly.
I have no idea what these constants must be changed to (or even if it is related to the error I get) and so I left them unchanged.

Screenshot of the full error message:
Back to top
View user's profile Send private message Visit poster's website
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Thu Apr 26, 2007 4:00 am    Post subject: Reply with quote

The first and second constants are correct. The third is the name of the module into which you have placed the script. In your case the name is probably the equivalent of "Module1". Whatever its name, that is the detail you should enter as modName.
_________________
search forum by month
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Apr 27, 2007 11:18 am    Post subject: Reply with quote

noranthon,
Thank you for answering this. I was a few days off.
Let me add another macro which enables the feature for each opened spreadsheet.
Code:

Sub on_Document_Open_Create()
   If ThisComponent.supportsService("com.sun.star.sheet.SpreadsheetDocument")then
      REM load library where addLotusKeyEvents is saved (if not this one or "Standard"):
      REM GlobalScope.BasicLibraries.LoadLibrary("Calc")
      addLotusKeyEvents
   REM same for other components. Add your stuff here:
   elseif ThisComponent.supportsService("com.sun.star.text.TextDocument")then
      REM GlobalScope.BasicLibraries.LoadLibrary("Writer")
   elseif ThisComponent.supportsService("com.sun.star.presentation.PresentationDocument") then
   elseif ThisComponent.supportsService("com.sun.star.drawing.DrawingDocument") then
   endif
End Sub

Menu:Tools>Customize... Tab:Events
Save in: "OpenOffice.org"
Assign macro "on_Document_Open_Create" to events "Create Document" and "Open Document".

@paulmath
See my lengthy posting on organizing macros:
http://www.oooforum.org/forum/viewtopic.phtml?p=221111
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
paulmath
Newbie
Newbie


Joined: 26 Apr 2007
Posts: 2
Location: Haarlem :: Netherlands

PostPosted: Sat Apr 28, 2007 11:24 am    Post subject: Reply with quote

Yes it works! Thank you both a lot! Smile

(
Reinstalled the script in a Calc "folder" editted the libName to Calc and modName to module1 (will probably rename the module name later, have to install it on another PC anyway).
)
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Jun 28, 2007 12:27 pm    Post subject: Reply with quote

The following code disables auto-capitalization of chars a-z, when entered as first letter in a spreadsheet cell. Use and install exactly as the Lotus key handler above. Together with that one in one library or only this one together with the other code beginning at:
Code:

sub StartFormula(sFmlStart$)
....

Don't forget to adjust the constants libName and modName on top of the module.
Code:

REM  *****  BASIC  *****
REM to be used with StartFormula and GetFocussedCell from my LotusKeyEvents for Calc
REM letters a-z only, for other small letters you still have to type the 'äfix manually
'container ("document" or "application")
Const libContainer$ = "application"
'Name of this library
Const libName$ = "KeyEvents"
'name of this module
Const modName$ = "TextInput"

Sub testTextInput
'addCalcTextInputHandler
'removeCalcTextInputHandler
End Sub
Sub removeCalcTextInputHandler
on error resume next
Dim oSM, oEv(25) as new com.sun.star.awt.KeyEvent
   oSM = thiscomponent.getUIconfigurationmanager.shortcutmanager
   iA = com.sun.star.awt.Key.A
      for i = 0 to 25
         oEv(i).KeyCode = iA +i
         oEv(i).KeyChar = Chr(97 +i)
         oEv(i).KeyFunc = 0
      oSM.removeKeyEvent(oEv(i))
   next
end sub
Sub addCalcTextInputHandler
Dim oSM, sChar$, sURL$, oEv(25) as new com.sun.star.awt.KeyEvent
   if hasUnoInterfaces(ThisComponent,"com.sun.star.sheet.XSpreadsheetDocument") then
      oSM = thiscomponent.getUIconfigurationmanager.shortcutmanager
      iA = com.sun.star.awt.Key.A
         for i = 0 to 25
            oEv(i).KeyCode = iA +i
            sChar = Chr(97 +i)
            oEv(i).KeyChar = sChar
            oEv(i).KeyFunc = 0
            sURL = "vnd.sun.star.script:"& libName &"."& modName &".StartTextInput_"& sChar &"?language=Basic&location="& libContainer
         oSM.setKeyEvent(oEv(i), sURL)
      next
   endif
end sub
Sub StartTextInput_a()
   StartFormula "'a"
'beep
End Sub
Sub StartTextInput_b()
   StartFormula "'b"
End Sub
Sub StartTextInput_c()
   StartFormula "'c"
End Sub
Sub StartTextInput_d()
   StartFormula "'d"
End Sub
Sub StartTextInput_e()
   StartFormula "'e"
End Sub
Sub StartTextInput_f()
   StartFormula "'f"
End Sub
Sub StartTextInput_g()
   StartFormula "'g"
End Sub
Sub StartTextInput_h()
   StartFormula "'h"
End Sub
Sub StartTextInput_i()
   StartFormula "'i"
End Sub
Sub StartTextInput_j()
   StartFormula "'j"
End Sub
Sub StartTextInput_k()
   StartFormula "'k"
End Sub
Sub StartTextInput_l()
   StartFormula "'l"
End Sub
Sub StartTextInput_m()
   StartFormula "'m"
End Sub
Sub StartTextInput_n()
   StartFormula "'n"
End Sub
Sub StartTextInput_o()
   StartFormula "'o"
End Sub
Sub StartTextInput_p()
   StartFormula "'p"
End Sub
Sub StartTextInput_q()
   StartFormula "'q"
End Sub
Sub StartTextInput_r()
   StartFormula "'r"
End Sub
Sub StartTextInput_s()
   StartFormula "'s"
End Sub
Sub StartTextInput_t()
   StartFormula "'t"
End Sub
Sub StartTextInput_u()
   StartFormula "'u"
End Sub
Sub StartTextInput_v()
   StartFormula "'v"
End Sub
Sub StartTextInput_w()
   StartFormula "'w"
End Sub
Sub StartTextInput_x()
   StartFormula "'x"
End Sub
Sub StartTextInput_y()
   StartFormula "'y"
End Sub
Sub StartTextInput_z()
   StartFormula "'z"
End Sub


_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets 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