[SOLVED] Call user Function from a Calc cell
Running Windows7
Libreoffice Version: 4.3.1.2
Build ID: 958349dc3b25111dbca392fbc281a05559ef6848
Nothing is returned, other cells back Ground Color are changed.
=IF(A2>12/30/1899,=ChangeCellsBackGroundColor(),"")
Code is in
My Macros,
Standard,
Module1,
Function ChangeCellsBackgroundColor() msgbox "entered function" rem----- Get Access to the document--------------------------------------------- dim Document as Object dim dispatcher as object document = ThisComponent.CurrentController.Frame oDoc = ThisComponent.CurrentController.getSelection()Rem-----get Address----------------------------------------------------- dCell = oDoc.CellAddress sRowToChange = dCell.Row cRowToChange = dCell.Row tRowToChange = dCell.Row + 1 document = ThisComponent.CurrentController.frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")Rem-----Point To MTD Cell ------------------------------------------------------- dim CellToChangeMTD(<img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> as new com.sun.star.beans.PropertyValue CellToChangeMTD(<img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/>.Name = "ToPoint" CellToChangeMTD(<img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/>.Value = "C" & cRowToChange + 1 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, CellToChangeMTD()) Cell = ThisComponent.getCurrentSelectionRem-----Point To Current Date Cell ---------------------------------------------- dim CurrentLineDate(<img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> as new com.sun.star.beans.PropertyValue CurrentLineDate(<img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/>.Name = "ToPoint" CurrentLineDate(<img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/>.Value = "A" & cRowToChange + 1 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, CurrentLineDate()) Cell =ThisComponent.getCurrentSelection sCurrentLineDate = Cell.string yCurrentLineDate = cell.valueRem-----Point To Next Date Cell ---------------------------------------------- dim NextLineDate(<img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> as new com.sun.star.beans.PropertyValue NextLineDate(<img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/>.Name = "ToPoint" NextLineDate(<img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/>.Value = "A" & cRowToChange + 2 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, NextLineDate()) Cell = ThisComponent.getCurrentSelection sNextLineDate = Cell.string yNextLineDate = cell.valueRem-----Process current row--------------------------------------------------- If yNextLineDate = 0 Then GoTo TExit Else Call ProcessRow(sCurrentLineDate,sNextLineDate,cRowToChange) End If Rem-----Point To CurrentRow $ column------------------------------------------- dim CurrentLineAmt(<img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> as new com.sun.star.beans.PropertyValue CurrentLineAmt(<img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/>.Name = "ToPoint" CurrentLineAmt(<img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/>.Value = "B" & cRowToChange + 1 dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, CurrentLineAmt()) Cell =ThisComponent.getCurrentSelection ' sCurrentLineDate = Cell.string ' yCurrentLineDate = cell.valueTExit: End Functionrem-----Process Rows Data-------------------------------------------------------- Sub ProcessRow(sCurrentLineDate,sNextLineDate,cRowToChange)Rem-----Extract Year From Date CurrentLineYear = DatePart("yyyy", [scurrentLinedate]) NextLineYear = DatePart("yyyy", [snextLinedate])If Month(sCurrentLineDate) <> Month(sNextLineDate) Then cColumnToChange = "2" Call CalledChangeCellsBackGroundColor(cColumnToChange, cRowToChange) End IfIf CurrentLineYear <> NextLineYear Then cColumnToChange = "2" Call CalledChangeCellsBackGroundColor(cColumnToChange, cRowToChange) cColumnToChange = "3" Call CalledChangeCellsBackGroundColor(cColumnToChange, cRowToChange) End IfEnd SubSub CalledChangeCellsBackGroundColor(cColumnToChange, cRowToChange) Dim oSheet as object Dim oCell as object Dim lngColor as longrem-----Get Access to active spreedsheet------------------------------------------- oSheet = ThisComponent.CurrentController.ActiveSheet rem-----Change cell background color----------------------------------------------- lngColor = RGB(255, 0, <img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> ' Red oCell = oSheet.getCellByPosition(cColumnToChange,cRowToChange) oCell.CellBackColor = lngColor oCell2 = ThisComponent.CurrentController.getSelection()End Sub
*EDIT (Mod)
Please use the appropriate code formatting for code examples. This will help us help you. Thanks.
=ChangeCellsBackGroundColor()
What shall the equal sign be for?
Your answer?
Why are there no parameters? Which kind of value shall it return?
Your answer?
The code (or a relevant part of it) is obviously recorded by experimental means. This rarely will produce the expected results. In addition the code isn’t actually “readable”. Declaring the main routine as a function and then using code like Cell = ThisComponent.getCurrentSelection and calling Subs seems a strange idea to me.
Might you clearly describe what you expect the function to accomplish, please?
The Function looks at dates in column A if there is a change in MTD then the MTD Columns back ground color is changed to red. The same is true for YTD column.
Could not paste a Screen shot, so I attached The spreed sheet.
Code is in My Macros, Standard, Module1
There is nothing returned in the function. Maybe I my doing this all wrong. I thought that
calling a user function needed the = sign. I use the calls so that I do not repeat the code in the main routine.
Maybe I am doing this all wrong, since I never used macro’s before.
| Attachment | Size |
|---|---|
| TomTest.ods | 28.67 KB |
Before talking again about a macro or a user defined function the intentions must be clear. In many cases a solution without macros can be found. Even spreadsheet software cannot be reasonably applied without some preliminaries.
The “=” at the beginning of an expression is the syntactical signal for “expression beginning”. Inside of an expression it cannot occur (except as a character inside a text string).
A function must return a value.
…
You might start with “Getting Started” and “Calc Guide” from http://www.libreoffice.org/get-help/documentation/ .
| Attachment | Size |
|---|---|
| TomTest.ods | 34.43 KB |
Might you be so kind to explain what condition is meant by “is a change in MTD”. You have a sheet, the cells have addresses (names). It should be possible to tell something clearly understandable for someone who doesn’t spend his days with exactly the same stuff as the questioner.
Do you mean “Columns C and D are summing the amounts from column B as long as in column A the (real world) month did not change or the year did not change respectively. The total for one month or one year respectively shall be highlighted with red background.”?
If so you find your example reworked with 3 variants of a solution and some unsolicited advice in the attached file. I would never try to accomplish such a task by programming in BASIC (or by programming at all).
| Attachment | Size |
|---|---|
| TomTestReworked001.ods | 67.87 KB |
=ChangeCellsBackGroundColorWhat shall the equal sign be for? Where is the functionChangeCellsBackGroundColordefinied. What is it’s code. Why are there no parameters? Which kind of value shall it return?Currently installed: LibO V5.1.2 on Windows 10
Maybe we might! (Create a powerful UFO: United Free Office!) Lupp from München