| View previous topic :: View next topic |
| Author |
Message |
Ozgur Emre
General User
Joined: 18 Sep 2008
Posts: 8
|
 Posted: Tue Jan 13, 2009 1:59 am Post subject: Trying to understand the perspective |
 |
|
Hi.
I "have to" use Open Office Calc in business cos the bank I work in enforce this. For one year I tussle with Calc; I need some macros but I can not write them. I can write VBA code. I have Pitonyak's book somehow but there is some questions on my mind. If an experienced one replies me; I'll be glad.
1) For clearing contents of a cell:
Sub ClearDefinedRange
Dim oDocument As Object, oSheet As Object, oSheets As Object
Dim oCellRange As Object
Dim nSheets As Long
oDocument = ThisComponent
oSheets = oDocument.Sheets
nSheets = oDocument.Sheets.Count
oSheet = oSheets.getByIndex(2)
oCellRange = oSheet.getCellRangeByName("A1:C4")
oCellRange.clearContents(_
com.sun.star.sheet.CellFlags.VALUE | _
com.sun.star.sheet.CellFlags.DATETIME | _
com.sun.star.sheet.CellFlags.STRING | _
com.sun.star.sheet.CellFlags.ANNOTATION | _
com.sun.star.sheet.CellFlags.FORMULA | _
com.sun.star.sheet.CellFlags.HARDATTR | _
com.sun.star.sheet.CellFlags.STYLES | _
com.sun.star.sheet.CellFlags.OBJECTS | _
com.sun.star.sheet.CellFlags.EDITATTR)
End Sub
is this functional? About 20 lines of code and the process is same as the button "Delete"?
in VBA this is 3 lines; or 2 if you work with active sheet.
Besides ; does Open Office clear value, datetime, string etc. seperately?
what for "com.sun.star"? is it a respect symbol for holy Sun Microsystems?
2) Some says VBA doesn't work on Open Office cos it includes Excel objects.
For instance "Cell" is not a special Excel object; the programs which claim themselves as spreadsheet software have "cells". I think it must not be hard in code to refer a common cell. Everytime define variables; appoint sheets, cells...
This is not like an interior environment; as if Open Office basic is a third party software for Open Office.
Naturally if we say "sheet" we mean one of Calc sheets; when you describe an address to your friend every time you say the city? or begining with street? the default is clear; if I doesn't specify the city; I mean "this city".
is there another way to refer sheets ; cells?
3) Pitonyak's book (which is downloadable) is good; is there another book as good as Pitonyak's?[/b] |
|
| Back to top |
|
 |
etfloyd
General User
Joined: 12 Jan 2009
Posts: 5
|
| Posted: Tue Jan 13, 2009 11:09 am Post subject: |
|
|
You can use -1 to indicate all bits for clearing, and you can string together the property and method references, thus:
| Code: | Sub ClearDefinedRange
Dim oSheet
oSheet = ThisComponent.Sheets.getByIndex(2)
oSheet.getCellRangeByName("A1:C4").clearContents(-1)
End Sub
|
..is equivalent to your subroutine. Also, you can get a sheet object by name, and refer to ranges by column / row numbers (zero-based!), which can be expressions, and you can string together as many references as you wish. Thus, the following one-liner is the equivalent to the above, assuming the second sheet is named "Sheet2":
| Code: | Sub ClearDefinedRange
ThisComponent.Sheets.getByName("Sheet2").getCellRangeByPosition(0, 0, 2, 3).clearContents(-1)
End Sub
|
getCellRangeByPosition's parameters define the range by the upper-left to lower-right corners: (leftcolumn, toprow, rightcolumn, bottomrow), zero-based, so (0, 0, 2, 3) is equivalent to "A1:C4".
You can also get/set data into individual cells by column and row number. The following subroutine adds 1 to the value of a cell:
| Code: |
Sub AddOne(ByVal sheetname as String, ByVal col as Long, ByVal row as Long)
Dim oSheet
Dim v as Double
oSheet = ThisComponent.Sheets.getByName(sheetname)
v = oSheet.getCellByPosition(col, row).getValue()
v = v + 1
oSheet.getCellByPosition(col, row).setValue(v)
End Sub
|
Note that all "by position" cell addresses are column first, then row, and are zero-based. Thus "AddOne("Sheet1", 3, 4)" would increment cell D5. |
|
| 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
|
